Error inside trigger

hi

there is a programing flow that I want to know exactly what will happen

1- A database which has 3 table T1,T2,T3

2-a Storeprocedure called STP

3- Inside stp we insert to T1 and T2

4-T2 has 2 triggers TRG1,TRG2

5 - TRG2 check some thing and raise error .

so, I want to know at this case all rows which inserted will rollback or no

You are inserting in more than one table, so if you want to rollback the whole transaction inside the trigger, you have to initiate an explicit transaction using "begin transaction" (before start inserting), if not, just the insert on T2 will be rolled back.

AMB

|||

TRG2 will ROLLBACK the INSERT into T2 ONLY.

As Alejandro indicated, if you wish to ROLLBACK ALL activites, you need to start a TRANSACTION inside STP.

|||

I did think like u said befor but !!! you try this :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[Cola] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Colb] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

go

exec sys.sp_addmessage @.msgnum= 50001, @.severity= 16, @.msgtext= N'User error', @.replace=N'replace'

go

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

createTRIGGER dbo.trg1

ON dbo.table_1

AFTER INSERT,UPDATE

AS

BEGIN

SETNOCOUNTON;

raiserror(50001,16,1)

END

GO

they make u a table (Table_1) and a trigger (Trg1)

as u can see in Trg1 an error will raise so if we insert any row it will be rollbacked. so try to insert :

INSERTINTO [dbo].[Table_1]

([Cola]

,[Colb])

VALUES

('aaa'

,'bbb')

then u can see a row added !!!

sql

Error in File System Task

Hi,

I am trying to transfer all the data from Excel to SQL Server using the Script Task (since I got some issue with the Data Flow -- that is a different story, let us come to this error), and after it got transferred I am deleting the source file using File System Task.

[File System Task] Error: An error occurred with the following error message: "The process cannot access the file 'C:\Prakash\TestFiles\Names.xls' because it is being used by another process."

The problem here is before transfer the data from Excel to SQL Server in the Script Task, the File System Task is getting executed, that is why it says that "it is being used by another process" like that.

I already set the TransactionOption as "Success" only for all the Tasks, but I don't know why the second task is getting executed before the first task completed his job.

If anyone have solution to this, pls let me know in detail ASAP.

Thanks & Regards,

Prakash Srinivasan

Hi All,

Sorry I forgot to close the Excel Connection after the data got transferred. Now I closed it and the problem is solved. But still I have problem with transferring data between Excel and SQL Server using Data Flow Task.

Thanks & Regards,

Prakash Srinivasan

error handling in OLEDB source in data flow

I am trying to execute a SP like below in OLEDB source in data flow... and this statement include the insert stament ( row by row transaction).. I would like to creat an error hadling logic so that if the trasaction fail to insert the row then ignore that particular row then, move to the next row without stopping the whole process.. how can i do this?

exec usp_Inert_Registration_Episodes_Assessments

@.Unique_ID=?,

@.Gender_Cd=?,

@.Birth_Date=?,

@.Race_Ind=?,

@.Ethnicity_Cd=?,

@.Registration_Dt=? ,

--

--@.Object_Key

Recently i was working on similar thing.

To do this in SSIS the following article will be handy

1) http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx

2) http://www.sqlis.com/55.aspx

3) http://www.sqlis.com/58.aspx

4) http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx?CommentPosted=true#commentmessage

Regarding error handling thats very much possible

Say for instance you getting data through OLE DB source. Then in the editor window, you can find "Error Output". here you can specify what to do with your error set. You can "Fail the component"/ ' Re Direct the row / ' Ignore Failure'

In case you want to redirect the row, then by setting the proper output to the error(Red Line) you can do as you want.

I hope this solves your problem.

|||

thanks,.. but

problem is my oledb source is a SP that execute insert statment.. so there are no output columns that i can map it to are available.. how can i solve this problem?

|||Why are you using an OLE DB source to insert data via a stored procedure? An Execute SQL task in the control flow would be a much better idea.|||

I agree with Phil

1) If you need to Insert with same datbase, use Execute SQl task

2) If in different database, use data flow task in which you can specify the source and destination.

secondly if you have some queries running bnefore running and this all is happening in a sproc then you can run that Sproc using Execute SQL task- Simple!!!

|||

thanks.. but if i use SQLTASK , i don;t have the option to use "Error Output" in data flow which i can specify what to do with the error row. in my case skip that row ( put that row in the error log table) and go to the next row..

how to handle the error row in the SQLTask if i want to redirect that row and go to the next row without stopping the whole process?

|||You can use an OLE DB Command component in the data flow to call the procedure. That will let you redirect error rows. However, you still need a source component to feed rows to the OLE DB Command.

Where were you planning on getting the data to feed into the procedure?

|||

Thanks jwelch -

"That will let you redirect error rows"

--Do i have to do something inside OLEDB command to be able to do that? or is it going to automatically redirect the row and move to the next row?

|||

If you drag and drop the red output arrow from the OLEDB command to another component, it will prompt you to configure it.

|||

ok.. in the data flow, i am using OLE DB source ( SQL command variable) and pass them to the OLE DB command to execute the insert statment ( by passing variables ).. I changed the error output to redirect rows to skip the row which didn't get inserted( because of an error) and move to the next row( it works)...

I drag and drop the red output arrow from the OLEDB command to another OLE DB command which will execute the insert statment to insert the failed row to Error_Log table...however, even though the row which has an error got skipped , that row didn;t get inserted to the error table.. i am not sure what i am doing wrong..

i am trying to insert the error row with the error description to a table.. what is the best way to do this?

|||

When you run it in the debugger, is a row count displayed on the red arrow?

|||

do i have to put a data viewer to be able to see it? i dont see it

but i see the color of insert to error table OLEDB command task turn to green for a sec

|||

If you are not seeing a number, it sounds like no rows are being sent to the error output. You can add a data viewer to confirm this. That indicates that the error output is not configured properly, or that the OLE DB Command is not failing on any rows.

|||but only one row out of two got inserted.. how to insert that failed row to a table?|||

safddddddddddddddddddddd wrote:

but only one row out of two got inserted.. how to insert that failed row to a table?

Hook the error output to a second OLE DB Destination.

error handling in OLEDB source in data flow

I am trying to execute a SP like below in OLEDB source in data flow... and this statement include the insert stament ( row by row transaction).. I would like to creat an error hadling logic so that if the trasaction fail to insert the row then ignore that particular row then, move to the next row without stopping the whole process.. how can i do this?

exec usp_Inert_Registration_Episodes_Assessments

@.Unique_ID=?,

@.Gender_Cd=?,

@.Birth_Date=?,

@.Race_Ind=?,

@.Ethnicity_Cd=?,

@.Registration_Dt=? ,

--

--@.Object_Key

Recently i was working on similar thing.

To do this in SSIS the following article will be handy

1) http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx

2) http://www.sqlis.com/55.aspx

3) http://www.sqlis.com/58.aspx

4) http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx?CommentPosted=true#commentmessage

Regarding error handling thats very much possible

Say for instance you getting data through OLE DB source. Then in the editor window, you can find "Error Output". here you can specify what to do with your error set. You can "Fail the component"/ ' Re Direct the row / ' Ignore Failure'

In case you want to redirect the row, then by setting the proper output to the error(Red Line) you can do as you want.

I hope this solves your problem.

|||

thanks,.. but

problem is my oledb source is a SP that execute insert statment.. so there are no output columns that i can map it to are available.. how can i solve this problem?

|||Why are you using an OLE DB source to insert data via a stored procedure? An Execute SQL task in the control flow would be a much better idea.|||

I agree with Phil

1) If you need to Insert with same datbase, use Execute SQl task

2) If in different database, use data flow task in which you can specify the source and destination.

secondly if you have some queries running bnefore running and this all is happening in a sproc then you can run that Sproc using Execute SQL task- Simple!!!

|||

thanks.. but if i use SQLTASK , i don;t have the option to use "Error Output" in data flow which i can specify what to do with the error row. in my case skip that row ( put that row in the error log table) and go to the next row..

how to handle the error row in the SQLTask if i want to redirect that row and go to the next row without stopping the whole process?

|||You can use an OLE DB Command component in the data flow to call the procedure. That will let you redirect error rows. However, you still need a source component to feed rows to the OLE DB Command.

Where were you planning on getting the data to feed into the procedure?

|||

Thanks jwelch -

"That will let you redirect error rows"

--Do i have to do something inside OLEDB command to be able to do that? or is it going to automatically redirect the row and move to the next row?

|||

If you drag and drop the red output arrow from the OLEDB command to another component, it will prompt you to configure it.

|||

ok.. in the data flow, i am using OLE DB source ( SQL command variable) and pass them to the OLE DB command to execute the insert statment ( by passing variables ).. I changed the error output to redirect rows to skip the row which didn't get inserted( because of an error) and move to the next row( it works)...

I drag and drop the red output arrow from the OLEDB command to another OLE DB command which will execute the insert statment to insert the failed row to Error_Log table...however, even though the row which has an error got skipped , that row didn;t get inserted to the error table.. i am not sure what i am doing wrong..

i am trying to insert the error row with the error description to a table.. what is the best way to do this?

|||

When you run it in the debugger, is a row count displayed on the red arrow?

|||

do i have to put a data viewer to be able to see it? i dont see it

but i see the color of insert to error table OLEDB command task turn to green for a sec

|||

If you are not seeing a number, it sounds like no rows are being sent to the error output. You can add a data viewer to confirm this. That indicates that the error output is not configured properly, or that the OLE DB Command is not failing on any rows.

|||but only one row out of two got inserted.. how to insert that failed row to a table?|||

safddddddddddddddddddddd wrote:

but only one row out of two got inserted.. how to insert that failed row to a table?

Hook the error output to a second OLE DB Destination.

error handling in OLEDB source in data flow

I am trying to execute a SP like below in OLEDB source in data flow... and this statement include the insert stament ( row by row transaction).. I would like to creat an error hadling logic so that if the trasaction fail to insert the row then ignore that particular row then, move to the next row without stopping the whole process.. how can i do this?

exec usp_Inert_Registration_Episodes_Assessments

@.Unique_ID=?,

@.Gender_Cd=?,

@.Birth_Date=?,

@.Race_Ind=?,

@.Ethnicity_Cd=?,

@.Registration_Dt=? ,

--

--@.Object_Key

Recently i was working on similar thing.

To do this in SSIS the following article will be handy

1) http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx

2) http://www.sqlis.com/55.aspx

3) http://www.sqlis.com/58.aspx

4) http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx?CommentPosted=true#commentmessage

Regarding error handling thats very much possible

Say for instance you getting data through OLE DB source. Then in the editor window, you can find "Error Output". here you can specify what to do with your error set. You can "Fail the component"/ ' Re Direct the row / ' Ignore Failure'

In case you want to redirect the row, then by setting the proper output to the error(Red Line) you can do as you want.

I hope this solves your problem.

|||

thanks,.. but

problem is my oledb source is a SP that execute insert statment.. so there are no output columns that i can map it to are available.. how can i solve this problem?

|||Why are you using an OLE DB source to insert data via a stored procedure? An Execute SQL task in the control flow would be a much better idea.|||

I agree with Phil

1) If you need to Insert with same datbase, use Execute SQl task

2) If in different database, use data flow task in which you can specify the source and destination.

secondly if you have some queries running bnefore running and this all is happening in a sproc then you can run that Sproc using Execute SQL task- Simple!!!

|||

thanks.. but if i use SQLTASK , i don;t have the option to use "Error Output" in data flow which i can specify what to do with the error row. in my case skip that row ( put that row in the error log table) and go to the next row..

how to handle the error row in the SQLTask if i want to redirect that row and go to the next row without stopping the whole process?

|||You can use an OLE DB Command component in the data flow to call the procedure. That will let you redirect error rows. However, you still need a source component to feed rows to the OLE DB Command.

Where were you planning on getting the data to feed into the procedure?

|||

Thanks jwelch -

"That will let you redirect error rows"

--Do i have to do something inside OLEDB command to be able to do that? or is it going to automatically redirect the row and move to the next row?

|||

If you drag and drop the red output arrow from the OLEDB command to another component, it will prompt you to configure it.

|||

ok.. in the data flow, i am using OLE DB source ( SQL command variable) and pass them to the OLE DB command to execute the insert statment ( by passing variables ).. I changed the error output to redirect rows to skip the row which didn't get inserted( because of an error) and move to the next row( it works)...

I drag and drop the red output arrow from the OLEDB command to another OLE DB command which will execute the insert statment to insert the failed row to Error_Log table...however, even though the row which has an error got skipped , that row didn;t get inserted to the error table.. i am not sure what i am doing wrong..

i am trying to insert the error row with the error description to a table.. what is the best way to do this?

|||

When you run it in the debugger, is a row count displayed on the red arrow?

|||

do i have to put a data viewer to be able to see it? i dont see it

but i see the color of insert to error table OLEDB command task turn to green for a sec

|||

If you are not seeing a number, it sounds like no rows are being sent to the error output. You can add a data viewer to confirm this. That indicates that the error output is not configured properly, or that the OLE DB Command is not failing on any rows.

|||but only one row out of two got inserted.. how to insert that failed row to a table?|||

safddddddddddddddddddddd wrote:

but only one row out of two got inserted.. how to insert that failed row to a table?

Hook the error output to a second OLE DB Destination.

error handling flow

i've got a stored procedure (a) that calls another stored procedure
(b). inside b i become a deadlock victim and i die. how can i "catch"
this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
using sql server 2003 so no try/catch. thanks!
arthur
You're not using SQL Server 2003, since it doesn't exists. I assume a typo and you mean 2000.
You cannot catch a deadlock at the TSQL level. You have to do it in the client. This is one of the
nice things with TRY/CATCH in 2005, that you can catch a deadlock situation at the TSQL level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"arthur" <alangham@.gmail.com> wrote in message
news:1138909906.663154.56220@.z14g2000cwz.googlegro ups.com...
> i've got a stored procedure (a) that calls another stored procedure
> (b). inside b i become a deadlock victim and i die. how can i "catch"
> this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
> using sql server 2003 so no try/catch. thanks!
> arthur
>
|||2000, right. thanks for clearing that up (not the 2000 part, the catch
part)!

error handling flow

i've got a stored procedure (a) that calls another stored procedure
(b). inside b i become a deadlock victim and i die. how can i "catch"
this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
using sql server 2003 so no try/catch. thanks!
arthurYou're not using SQL Server 2003, since it doesn't exists. I assume a typo and you mean 2000.
You cannot catch a deadlock at the TSQL level. You have to do it in the client. This is one of the
nice things with TRY/CATCH in 2005, that you can catch a deadlock situation at the TSQL level.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"arthur" <alangham@.gmail.com> wrote in message
news:1138909906.663154.56220@.z14g2000cwz.googlegroups.com...
> i've got a stored procedure (a) that calls another stored procedure
> (b). inside b i become a deadlock victim and i die. how can i "catch"
> this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
> using sql server 2003 so no try/catch. thanks!
> arthur
>|||2000, right. thanks for clearing that up (not the 2000 part, the catch
part)!

error handling flow

i've got a stored procedure (a) that calls another stored procedure
(b). inside b i become a deadlock victim and i die. how can i "catch"
this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
using sql server 2003 so no try/catch. thanks!
arthurYou're not using SQL Server 2003, since it doesn't exists. I assume a typo a
nd you mean 2000.
You cannot catch a deadlock at the TSQL level. You have to do it in the clie
nt. This is one of the
nice things with TRY/CATCH in 2005, that you can catch a deadlock situation
at the TSQL level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"arthur" <alangham@.gmail.com> wrote in message
news:1138909906.663154.56220@.z14g2000cwz.googlegroups.com...
> i've got a stored procedure (a) that calls another stored procedure
> (b). inside b i become a deadlock victim and i die. how can i "catch"
> this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
> using sql server 2003 so no try/catch. thanks!
> arthur
>|||2000, right. thanks for clearing that up (not the 2000 part, the catch
part)!