Error in stored procedure that updates a row

I have the following stored procedure:

CREATE PROCEDURE user1122500.sp_modifyOrganization
(
@.Name nvarchar(100)
,@.Location nvarchar(50)
,@.Url nvarchar (250)
,@.Org_Type nvarchar (50)
,@.Par_Org_Id uniqueidentifier
,@.Row_Id uniqueidentifier
,@.Error_Code int OUTPUT
,@.Error_Text nvarchar(768) OUTPUT
)
AS
DECLARE @.errorMsg nvarchar(512)
DECLARE @.spName sysname

SELECT @.spName = Object_Name(@.@.ProcID)
SET @.Error_Code = 0

IF @.Url > ' '
BEGIN
UPDATE USER1122500.ORGANIZATION
SET URL = @.Url
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'URL', @.Url
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

IF @.Org_Type > ' '
BEGIN
UPDATE USER1122500.ORGANIZATION
SET ORG_TYPE = @.Org_Type
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'ORG_TYPE', @.Org_Type
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

IF @.Par_Org_Id IS NOT NULL
BEGIN
UPDATE USER1122500.ORGANIZATION
SET PAR_ORG_ID = @.Par_Org_Id
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'PAR_ORG_ID', @.Par_Org_Id
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

IF @.Name > ' ' OR @.Location > ' '
BEGIN

IF EXISTS (SELECT ROW_ID FROM USER1122500.ORGANIZATION WHERE NAME = @.Name AND LOCATION = @.Location)
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'NAME', @.Name,
N'LOCATION', @.Location
SET @.Error_Code = 55004 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
-- RAISERROR(@.Error_Text, 10, 1, @.spName, @.Error_Code, 'ORGANIZATION', @.errorMsg)
SELECT @.Error_Text = (SELECT REPLACE(@.Error_Text,'sp_name',@.spName))
SELECT @.Error_Text = (SELECT REPLACE(@.Error_Text,'err_cd',@.Error_Code))
SELECT @.Error_Text = (SELECT REPLACE(@.Error_Text,'tbl_name','ORGANIZATION'))
SELECT @.Error_Text = (SELECT REPLACE(@.Error_Text,'err_msg',@.errorMsg))
RETURN(@.Error_Code)
END

IF @.Name > ' '
BEGIN
UPDATE USER1122500.ORGANIZATION
SET NAME = @.Name
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'PAR_ORG_ID', @.Name
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

IF @.Location > ' '
BEGIN
UPDATE USER1122500.ORGANIZATION
SET LOCATION = @.Location
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'LOCATION', @.Location
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

END
GO

This is the code that runs it:

string strSP = "sp_modifyOrganization";

SqlParameter[] Params =new SqlParameter;

string strParOrgID =null;

if (this.ddlParentOrg.SelectedItem.Value != "")

{

strParOrgID =this.ddlParentOrg.SelectedItem.Value;

}

Params[0] =new SqlParameter("@.Name", txtName.Text);

Params[1] =new SqlParameter("@.Location",this.txtLocation.Text);

Params[2] =new SqlParameter("@.Url",this.txtURL.Text);

Params[3] =new SqlParameter("@.Org_Type",this.txtOrgType.Text);

//Params[4] = new SqlParameter("@.Par_Org_Id", strParOrgID);

Params[4] =new SqlParameter("@.Par_Org_Id", "CA1FBC83-D978-48F1-BCBC-E53AD5E8A321".ToUpper());

Params[5] =new SqlParameter("@.Row_Id", "688f2d10-1550-44f8-a62c-17610d1e979a".ToUpper());

// Params[5] = new SqlParameter("@.Row_Id", lblOrg_ID.Text);

Params =new SqlParameter("@.Error_Code", -1);

Params[7] =new SqlParameter("@.Error_Text", "");

Params[4].SqlDbType = SqlDbType.UniqueIdentifier;

Params[5].SqlDbType = SqlDbType.UniqueIdentifier;

Params.Direction = ParameterDirection.Output;

Params[7].Direction = ParameterDirection.Output;

try

{

this.dtsData = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["SIM_DSN"], CommandType.StoredProcedure, strSP, Params);

if (Params.Value.ToString() != "0")

{

lblError.Text = "There was an error: " + Params.Value.ToString()+ "###" + Params[7].Value.ToString();

lblError.Visible =true;

}

}

//catch (System.Data.SqlClient.SqlException ex)

catch (System.InvalidCastException inv)

{

lblError.Text = lblOrg_ID.Text + "<br><br>" + inv.ToString() + inv.Message + inv.StackTrace + inv.HelpLink;

lblError.Visible =true;

}

catch (Exception ex)

{

lblError.Text = lblOrg_ID.Text + "<br><br>" + ex.ToString();

lblError.Visible =true;

// return false;

}

This is the exception being generated:

System.InvalidCastException: Invalid cast from System.String to System.Guid.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:\Program Files\_vsNETAddOns\Microsoft Application Blocks for .NET\Data Access v2\Code\VB\Microsoft.ApplicationBlocks.Data\SQLHelper.vb:line 542
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:\Program Files\_vsNETAddOns\Microsoft Application Blocks for .NET\Data Access v2\Code\VB\Microsoft.ApplicationBlocks.Data\SQLHelper.vb:line 458
at development.youthleadercert.com.share.ascx.organizationForm.btnAdd_Click(Object sender, EventArgs e) in c:\documents and settings\mark rubin\vswebcache\development.youthleadercert.com\share\ascx\organizationform.ascx.cs:line 352

I have no idea what field is even causing the error, nor do I see that I'm even using a GUID field. I've been stuck on this for 2 days. Any help?

I would guess it's here. You're passing them as strings. You may want to explicitly type them as UniqueIdentifiers. Or, change your proc temporarily and define your parameters as varchars and see what happens. I bet that even though they may look like GUIDs, SQL doesn't see them that way when they're passed in. Just a guess, but that would be where I would start.

Params[4] =new SqlParameter("@.Par_Org_Id", "CA1FBC83-D978-48F1-BCBC-E53AD5E8A321".ToUpper());

Params[5] =new SqlParameter("@.Row_Id", "688f2d10-1550-44f8-a62c-17610d1e979a".ToUpper());

|||

In my code, I already am setting the db type a few rows down...

Params[4].SqlDbType = SqlDbType.UniqueIdentifier;

Params[5].SqlDbType = SqlDbType.UniqueIdentifier;

Error in Identity Column

For some reason my primary key, identity column skipped a couple of numbers. It went from row 734 to 736, 737, 739

Any ideas why this would happen?

thanks

hi

735 & 738 must've been created but deleted somehow.

|||

No, it was not deleted

|||

Rick0194:

No, it was not deleted

Yes they were, that's the only way it could have been skipped. Now, it may be that they get inserted as part of a transaction and something goes wrong and rolls it back -- the identity colum's value will appear to skip the next time a good insert occurs (I just confirmed that on sql 2000)

Error in DB

I have the next problem:
when i try to insert a row inside a table there ir the
next error message:
Could not allocate space for object '<Table Name>' in
database '<DB Name>' because the 'PRIMARY' filegroup is
full.
How can i solve this problem ?
Thanks in advanceTHe problem could either be.
1. lack of disk space on the drive where the primary filegroup resides. (As
another poster suggests ) or
2. The filegroup may have a max size set... In SQL Enterprise Manager, right
click your database ->Properties, and check the data and log tab...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Enrico" <ezerilli@.csc.com> wrote in message
news:0da001c3db7c$e64030e0$a301280a@.phx.gbl...

quote:

---

> I have the next problem:
> when i try to insert a row inside a table there ir the
> next error message:
> Could not allocate space for object '<Table Name>' in
> database '<DB Name>' because the 'PRIMARY' filegroup is
> full.
> How can i solve this problem ?
> Thanks in advance

---

|||

quote:

---

>--Original Message--
>THe problem could either be.
>1. lack of disk space on the drive where the primary

---

filegroup resides. (As

quote:

---

>another poster suggests ) or
>2. The filegroup may have a max size set... In SQL

---

Enterprise Manager, right

quote:

---

>click your database ->Properties, and check the data and

---

log tab...

quote:

---

>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),

---

Charlotte, NC

quote:

---

>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server

---

(PASS) and it's

quote:

---

>community of SQL Server professionals.
>www.sqlpass.org
>"Enrico" <ezerilli@.csc.com> wrote in message
>news:0da001c3db7c$e64030e0$a301280a@.phx.gbl...
>
>.
>

---

I have solve the problem check the option Unrestricted
file Growth under DB properties/Transaction Log.
So i dont have any error about PRIMARY Filegroup.
Thanks a lot to every body.
Bye

Error in DB

I have the next problem:
when i try to insert a row inside a table there ir the
next error message:
Could not allocate space for object '<Table Name>' in
database '<DB Name>' because the 'PRIMARY' filegroup is
full.
How can i solve this problem ?
Thanks in advanceCheck the disk (drive) where your primary filegroup is
located and see how much space is left... try to free up
space (clean up files you dont need) if not create a
second filegroup...
>--Original Message--
>I have the next problem:
>when i try to insert a row inside a table there ir the
>next error message:
>Could not allocate space for object '<Table Name>' in
>database '<DB Name>' because the 'PRIMARY' filegroup is
>full.
>How can i solve this problem ?
>Thanks in advance
>.
>|||THe problem could either be.
1. lack of disk space on the drive where the primary filegroup resides. (As
another poster suggests ) or
2. The filegroup may have a max size set... In SQL Enterprise Manager, right
click your database ->Properties, and check the data and log tab...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Enrico" <ezerilli@.csc.com> wrote in message
news:0da001c3db7c$e64030e0$a301280a@.phx.gbl...
> I have the next problem:
> when i try to insert a row inside a table there ir the
> next error message:
> Could not allocate space for object '<Table Name>' in
> database '<DB Name>' because the 'PRIMARY' filegroup is
> full.
> How can i solve this problem ?
> Thanks in advance|||>--Original Message--
>THe problem could either be.
>1. lack of disk space on the drive where the primary
filegroup resides. (As
>another poster suggests ) or
>2. The filegroup may have a max size set... In SQL
Enterprise Manager, right
>click your database ->Properties, and check the data and
log tab...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Enrico" <ezerilli@.csc.com> wrote in message
>news:0da001c3db7c$e64030e0$a301280a@.phx.gbl...
>> I have the next problem:
>> when i try to insert a row inside a table there ir the
>> next error message:
>> Could not allocate space for object '<Table Name>' in
>> database '<DB Name>' because the 'PRIMARY' filegroup is
>> full.
>> How can i solve this problem ?
>> Thanks in advance
>
>.
>
I have solve the problem check the option Unrestricted
file Growth under DB properties/Transaction Log.
So i dont have any error about PRIMARY Filegroup.
Thanks a lot to every body.
Bye

Error in : MSmerge_tombstone

I when i am try to update data in one table having replication, it gives me the error msg below;

cannot insert duplicate key row in object 'MSmerge_tombstone' with unique index 'uc1MSmerge_tombstone'

why this happens? pls. advise how i can get rid of this message.

I discovered that the table MSmerge_tombstone on subscriber is having triggers. I removed this trigger and the problem doenot reapper. Is it advisable to do that? Pls. advise.

Thanks,

I do not think removing triggers is good idea, MSmerge_tombstone table is used by replication process to track the rows that have been deleted on the subscriber
|||Could you pls. advise how can i get rid of this error?

Error Handling/ Stored Procs

Hi,
I'm doing some fairly basic updates with stored procedures. 99% of them affect one row. I've jsut discovered that I can't get the value of @.@.rowcount and @.@.error to return as output parameters (if I check one, the other one gets reset!). My theory is then to return the rowcount and if it's not = 1, then I know I've had a problem. If I begin a transaction in vb.net and call each proc in the required order and check each step that rowcount = 1, is this a reliable method of ensuring no errors have occurred?
Thanks.As far as dealing with both @.@.ROWCOUNT and @.@.ERROR is concerned, this is what I do:

SELECT @.lError = @.@.ERROR, @.lRowCount = @.@.ROWCOUNT

I select the values into local variables in the stored procedure andthen do whatever is needed based on those values. Note that youmust SELECT both values in the same statement, and I *believe* @.@.ERRORmust appear first because it will be reset when @.@.ROWCOUNT is accessed.
In your case, @.lError should be 0 and @.lRowCount should be 1 when a record is inserted correctly.


|||Thanks, I can now get my two values back!

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.