Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, that should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value for the identity
column.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts
Monday, March 26, 2012
Error inserting record in IDENTITY column
Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JT
Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JT
Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
Error inserting record in IDENTITY column
Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity
value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, tha
t should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value
for the identity
column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should hav
e
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicat
e
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity
value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, tha
t should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value
for the identity
column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should hav
e
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicat
e
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
Thursday, March 22, 2012
Error in Stored Procedure while working with temp. table
Creating a temporary table in stored procedure and using a sql query to insert the data in temp. table.I am facing the error as :
String or binary data would be truncated.The statement has been terminated.
The procedure i created is as :
ALTER PROCEDUREfetchpersondetails
AS
CREATE Table#tempperson (personIDint,FirstNamenvarchar(200),LastNamenvarchar(250),titlenvarchar(150),Professionnvarchar(200),StreetAddressnvarchar(300),
StateAddressnvarchar(200),CityAddressnvarchar(200),CountryAddressnvarchar(200),ZipAddressnvarchar(200),Telephonenvarchar(200),Mobilenvarchar(200),
Faxnvarchar(200),Emailnvarchar(250),NotesPubntext,Affiliationnvarchar(200),Categorynvarchar(200))
Insert into#tempperson
SELECTdbo.tblperson.personID,ISNULL(dbo.tblperson.fName, N'') +' '+ISNULL(dbo.tblperson.mName, N'')ASFirstName, dbo.tblperson.lnameASLastName,
dbo.tblperson.honorASTitle, dbo.tblperson.titleASProfession, dbo.tblperson.street +' '+ISNULL(dbo.tblperson.suite, N'')ASStreetAddress,
dbo.tblperson.cityAScityaddress, dbo.tblperson.stateASstateaddress, dbo.tblperson.postalCodeASzipaddress,
dbo.tblperson.Phone1 +','+ISNULL(dbo.tblperson.Phone2, N'') +','+ISNULL(dbo.tblperson.Phone3, N'')ASTelephone,
dbo.tblperson.mobilePhoneASmobile, dbo.tblperson.officeFax +','+ISNULL(dbo.tblperson.altOfficeFax, N'') +','+ISNULL(dbo.tblperson.altOfficeFax2,
N'')ASFax,ISNULL(dbo.tblperson.Email1, N'') +','+ISNULL(dbo.tblperson.Email2, N'') +','+ISNULL(dbo.tblperson.Email3, N'')ASEmail,
dbo.tblperson.notesASNotesPub, dbo.tblOrganizations.orgNameASAffiliation, dbo.tblOrganizations.orgCategoryASCategory,
dbo.tblCountry.countryNameFullAScountryaddress
FROMdbo.tblpersonINNER JOIN
dbo.tblOrganizationsONdbo.tblperson.orgID = dbo.tblOrganizations.orgIDINNER JOIN
dbo.tblCountryONdbo.tblperson.countryCode = dbo.tblCountry.ISOCode
please let me know the solurion of this error.
What the error msg means is that you are trying to insert a value that is larger than what the column can take. Check your column lengths in the temp table and the data coming in from your SELECT statment. Apparently, you have crossed the limit somewhere.
||| I made it working.Its not the column length that creating problem.
Wednesday, March 21, 2012
Error In SQL Statement
Hi, I m Trying TO use A sql insert Query but it showing an error
i m trying to insert value in Filed Name PNR from Str.text and Coresspond Field Name PNR1 valuse is 1 less than from pnr.text and PNR1 is a Auto Number Field
my code for insert query is
Dim q1As OleDb.OleDbCommand =New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1='" & pnr.Text - 1 & " ' ", con)
and Error is Shown by browser is as follows
Server Error in '/WebApplication1' Application.
Missing semicolon (;) at end of SQL statement.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.OleDb.OleDbException: Missing semicolon (;) at end of SQL statement.
Source Error:
Line 466: con.Open()Line 467: Dim q1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1='" & pnr.Text - 1 & " ' ", con)Line 468: q1.ExecuteNonQuery()Line 469: con.Close()Line 470: End SubSource File:C:\Inetpub\wwwroot\WebApplication1\2.aspx.vb Line:468
Stack Trace:
[OleDbException (0x80040e14): Missing semicolon (;) at end of SQL statement.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112 System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66 WebApplication1._2.Button2_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\WebApplication1\2.aspx.vb:468 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
I have to ask this, even though it seems so simple. Since the error says the query is missing a semi-colon at the end of the SQL statement, did you add a semi-colon and test it?
Jeff
|||Is this SQL Server or Access?
In any case, your syntax is incorrect. Using a WHERE clause on an INSERT statement is invalid.
Also, use Parameters instead of concatenating UI-supplied text to SQL statements which will be executed.
Try modify you q1 definition.
does you pnr contain number and you would like to subtract 1 from it?
you have to cast you pnr. to integer next subtract 1 and next convert result to string and insert it into query or do it this way:
Dim q1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1=(" & pnr.Text & "-1) ",
Thanks
Wednesday, March 7, 2012
Error in INSERT INTO
Hi,
I'm sure that this is quite a simple program. I am generating an INSERT INTO statement in the Query builder (!) of Visual Basic .NET, and I consistantly get the following error: "Missing semi-colon at the end of the end of the SQL statement." The SQL stat
ement in question is:
INSERT INTO tblBlocks
(ActualTotalHoles, Block_Name, BlockComment, ChargedHoles, PlannedTotalHoles, Site_Name)
VALUES (?, ?, ?, ?, ?, ?)
SELECT tblBlocks.Block_Name, tblBlocks.Site_Name
FROM tblBlocks
WHERE (tblBlocks.Block_Name =?),( tblBlocks.Site_Name = ?);
Would much appreciate some help - Thanks.
It's being interpreted as two SQL statements, not one. The missing
semi-colon should come after VALUES(?, ?, ?, ?, ?, ?);
"Lite" <anonymous@.discussions.microsoft.com> wrote in message
news:C052EA2E-9424-4E0C-9D30-8FAFC93F064B@.microsoft.com...
> Hi,
> I'm sure that this is quite a simple program. I am generating an INSERT
INTO statement in the Query builder (!) of Visual Basic .NET, and I
consistantly get the following error: "Missing semi-colon at the end of the
end of the SQL statement." The SQL statement in question is:
> INSERT INTO tblBlocks
> (ActualTotalHoles, Block_Name, BlockComment,
ChargedHoles, PlannedTotalHoles, Site_Name)
> VALUES (?, ?, ?, ?, ?, ?)
> SELECT tblBlocks.Block_Name, tblBlocks.Site_Name
> FROM tblBlocks
> WHERE (tblBlocks.Block_Name =?),( tblBlocks.Site_Name = ?);
> Would much appreciate some help - Thanks.
I'm sure that this is quite a simple program. I am generating an INSERT INTO statement in the Query builder (!) of Visual Basic .NET, and I consistantly get the following error: "Missing semi-colon at the end of the end of the SQL statement." The SQL stat
ement in question is:
INSERT INTO tblBlocks
(ActualTotalHoles, Block_Name, BlockComment, ChargedHoles, PlannedTotalHoles, Site_Name)
VALUES (?, ?, ?, ?, ?, ?)
SELECT tblBlocks.Block_Name, tblBlocks.Site_Name
FROM tblBlocks
WHERE (tblBlocks.Block_Name =?),( tblBlocks.Site_Name = ?);
Would much appreciate some help - Thanks.
It's being interpreted as two SQL statements, not one. The missing
semi-colon should come after VALUES(?, ?, ?, ?, ?, ?);
"Lite" <anonymous@.discussions.microsoft.com> wrote in message
news:C052EA2E-9424-4E0C-9D30-8FAFC93F064B@.microsoft.com...
> Hi,
> I'm sure that this is quite a simple program. I am generating an INSERT
INTO statement in the Query builder (!) of Visual Basic .NET, and I
consistantly get the following error: "Missing semi-colon at the end of the
end of the SQL statement." The SQL statement in question is:
> INSERT INTO tblBlocks
> (ActualTotalHoles, Block_Name, BlockComment,
ChargedHoles, PlannedTotalHoles, Site_Name)
> VALUES (?, ?, ?, ?, ?, ?)
> SELECT tblBlocks.Block_Name, tblBlocks.Site_Name
> FROM tblBlocks
> WHERE (tblBlocks.Block_Name =?),( tblBlocks.Site_Name = ?);
> Would much appreciate some help - Thanks.
Error in INSERT INTO
Hi,
I'm sure that this is quite a simple program. I am generating an INSERT INTO
statement in the Query builder (!) of Visual Basic .NET, and I consistantly
get the following error: "Missing semi-colon at the end of the end of the S
QL statement." The SQL stat
ement in question is:
INSERT INTO tblBlocks
(ActualTotalHoles, Block_Name, BlockComment, ChargedHoles, PlannedTotalHoles
, Site_Name)
VALUES (?, ?, ?, ?, ?, ?)
SELECT tblBlocks.Block_Name, tblBlocks.Site_Name
FROM tblBlocks
WHERE (tblBlocks.Block_Name =?),( tblBlocks.Site_Name = ?);
Would much appreciate some help - Thanks.It's being interpreted as two SQL statements, not one. The missing
semi-colon should come after VALUES(?, ?, ?, ?, ?, ?);
"Lite" <anonymous@.discussions.microsoft.com> wrote in message
news:C052EA2E-9424-4E0C-9D30-8FAFC93F064B@.microsoft.com...
> Hi,
> I'm sure that this is quite a simple program. I am generating an INSERT
INTO statement in the Query builder (!) of Visual Basic .NET, and I
consistantly get the following error: "Missing semi-colon at the end of the
end of the SQL statement." The SQL statement in question is:
> INSERT INTO tblBlocks
> (ActualTotalHoles, Block_Name, BlockComment,
ChargedHoles, PlannedTotalHoles, Site_Name)
> VALUES (?, ?, ?, ?, ?, ?)
> SELECT tblBlocks.Block_Name, tblBlocks.Site_Name
> FROM tblBlocks
> WHERE (tblBlocks.Block_Name =?),( tblBlocks.Site_Name = ?);
> Would much appreciate some help - Thanks.
I'm sure that this is quite a simple program. I am generating an INSERT INTO
statement in the Query builder (!) of Visual Basic .NET, and I consistantly
get the following error: "Missing semi-colon at the end of the end of the S
QL statement." The SQL stat
ement in question is:
INSERT INTO tblBlocks
(ActualTotalHoles, Block_Name, BlockComment, ChargedHoles, PlannedTotalHoles
, Site_Name)
VALUES (?, ?, ?, ?, ?, ?)
SELECT tblBlocks.Block_Name, tblBlocks.Site_Name
FROM tblBlocks
WHERE (tblBlocks.Block_Name =?),( tblBlocks.Site_Name = ?);
Would much appreciate some help - Thanks.It's being interpreted as two SQL statements, not one. The missing
semi-colon should come after VALUES(?, ?, ?, ?, ?, ?);
"Lite" <anonymous@.discussions.microsoft.com> wrote in message
news:C052EA2E-9424-4E0C-9D30-8FAFC93F064B@.microsoft.com...
> Hi,
> I'm sure that this is quite a simple program. I am generating an INSERT
INTO statement in the Query builder (!) of Visual Basic .NET, and I
consistantly get the following error: "Missing semi-colon at the end of the
end of the SQL statement." The SQL statement in question is:
> INSERT INTO tblBlocks
> (ActualTotalHoles, Block_Name, BlockComment,
ChargedHoles, PlannedTotalHoles, Site_Name)
> VALUES (?, ?, ?, ?, ?, ?)
> SELECT tblBlocks.Block_Name, tblBlocks.Site_Name
> FROM tblBlocks
> WHERE (tblBlocks.Block_Name =?),( tblBlocks.Site_Name = ?);
> Would much appreciate some help - Thanks.
Error in Inintial Replication
I'm Replicating from one SQL200 db to another on our LAN. The target db is
blank, but on some of the tables, it fails with:
Could not bulk insert. Bulk data stream was incorrectly specified as sorted.
(Source: STAN (Data source); Error number: 4819)
I've verified that the collation and sort order are the same. Any ideas?
Jeff
Jeff,
Make sure the publisher and subscriber have the same collations - this
is normally due to differing collations.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jeff Dillon wrote:
> I'm Replicating from one SQL200 db to another on our LAN. The target db is
> blank, but on some of the tables, it fails with:
> Could not bulk insert. Bulk data stream was incorrectly specified as sorted.
> (Source: STAN (Data source); Error number: 4819)
> I've verified that the collation and sort order are the same. Any ideas?
> Jeff
>
|||As I stated, they do have the same collation. Even using an empty database
as the subscriber causes this. So far, it appears the tables that are
failing have text columns.
I did a Google search on this, and the common reply is to check collation.
In neither of the 2 cases I found was collation the issue. It was never
resolved. Appears to be a bug.
I run sp_helpsort in each database, and it returns the same results. Is
there somewhere else I need to check?
Jeff
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:O8JpwvUpEHA.2948@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Jeff,
> Make sure the publisher and subscriber have the same collations - this
> is normally due to differing collations.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Jeff Dillon wrote:
is[vbcol=seagreen]
sorted.[vbcol=seagreen]
|||I found the problem. I had a wide clustered index (by wide I mean several
fields, including varchar).
I changed it to non-clustered, and it works. Bug? maybe...
Jeff
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:eEnFD0WpEHA.2612@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> As I stated, they do have the same collation. Even using an empty database
> as the subscriber causes this. So far, it appears the tables that are
> failing have text columns.
> I did a Google search on this, and the common reply is to check collation.
> In neither of the 2 cases I found was collation the issue. It was never
> resolved. Appears to be a bug.
> I run sp_helpsort in each database, and it returns the same results. Is
> there somewhere else I need to check?
> Jeff
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:O8JpwvUpEHA.2948@.TK2MSFTNGP11.phx.gbl...
db[vbcol=seagreen]
> is
> sorted.
ideas?
>
blank, but on some of the tables, it fails with:
Could not bulk insert. Bulk data stream was incorrectly specified as sorted.
(Source: STAN (Data source); Error number: 4819)
I've verified that the collation and sort order are the same. Any ideas?
Jeff
Jeff,
Make sure the publisher and subscriber have the same collations - this
is normally due to differing collations.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jeff Dillon wrote:
> I'm Replicating from one SQL200 db to another on our LAN. The target db is
> blank, but on some of the tables, it fails with:
> Could not bulk insert. Bulk data stream was incorrectly specified as sorted.
> (Source: STAN (Data source); Error number: 4819)
> I've verified that the collation and sort order are the same. Any ideas?
> Jeff
>
|||As I stated, they do have the same collation. Even using an empty database
as the subscriber causes this. So far, it appears the tables that are
failing have text columns.
I did a Google search on this, and the common reply is to check collation.
In neither of the 2 cases I found was collation the issue. It was never
resolved. Appears to be a bug.
I run sp_helpsort in each database, and it returns the same results. Is
there somewhere else I need to check?
Jeff
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:O8JpwvUpEHA.2948@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Jeff,
> Make sure the publisher and subscriber have the same collations - this
> is normally due to differing collations.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Jeff Dillon wrote:
is[vbcol=seagreen]
sorted.[vbcol=seagreen]
|||I found the problem. I had a wide clustered index (by wide I mean several
fields, including varchar).
I changed it to non-clustered, and it works. Bug? maybe...
Jeff
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:eEnFD0WpEHA.2612@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> As I stated, they do have the same collation. Even using an empty database
> as the subscriber causes this. So far, it appears the tables that are
> failing have text columns.
> I did a Google search on this, and the common reply is to check collation.
> In neither of the 2 cases I found was collation the issue. It was never
> resolved. Appears to be a bug.
> I run sp_helpsort in each database, and it returns the same results. Is
> there somewhere else I need to check?
> Jeff
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:O8JpwvUpEHA.2948@.TK2MSFTNGP11.phx.gbl...
db[vbcol=seagreen]
> is
> sorted.
ideas?
>
Sunday, February 26, 2012
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...
filegroup resides. (As
Enterprise Manager, right
log tab...
Charlotte, NC
(PASS) and it's
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
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
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
Friday, February 24, 2012
Error in Bulk Insert after Windows 2003 Upgrade
My production support group upgraded the version of Windows Server from 2000 to 2003 on a stand-alone SQL 8.0 development server. I am using a bulk insert command in a stored procedure that is now failing as a result of the upgrade.
The error message is: Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'BULKIMPORTSTREAM'.
OLE DB error trace [Non-interface error: CoCreate of DSO for BULKIMPORTSTREAM returned 0x8007045a].
We have checked the following based on other posts we have seen.
1) Re-registered impprov.dll
2) We are at SP 3
3) We are at MDAC 2.8
We also verified by copying the DB to a dev server that has not been upgraded it still works. Does anyone have any other suggestions or experience that may help?
Thank you,
KellyJust being curious, did they do an upgrade without scratch and rebuild the server? If that's the case, your server would have registry problems. If your dev box is a dedicated db box, you can simply run the rebuild registry with sql 2000 installation CD and patch up with sp3 after that. If you still have problem, since it's a dev box, I would recommand that you take backups, sratch the box, install Windows 2003, install sql 2000, patch up to sp3, and then reload the database on to the server.|||Well, I made the suggestion that the server be rebooted after the unregister and re-resgister process and that seems to have done the trick. The DBA thinks that restarting SQL Server may have been enough. Thanks for the idea Joe.
Kelly
The error message is: Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'BULKIMPORTSTREAM'.
OLE DB error trace [Non-interface error: CoCreate of DSO for BULKIMPORTSTREAM returned 0x8007045a].
We have checked the following based on other posts we have seen.
1) Re-registered impprov.dll
2) We are at SP 3
3) We are at MDAC 2.8
We also verified by copying the DB to a dev server that has not been upgraded it still works. Does anyone have any other suggestions or experience that may help?
Thank you,
KellyJust being curious, did they do an upgrade without scratch and rebuild the server? If that's the case, your server would have registry problems. If your dev box is a dedicated db box, you can simply run the rebuild registry with sql 2000 installation CD and patch up with sp3 after that. If you still have problem, since it's a dev box, I would recommand that you take backups, sratch the box, install Windows 2003, install sql 2000, patch up to sp3, and then reload the database on to the server.|||Well, I made the suggestion that the server be rebooted after the unregister and re-resgister process and that seems to have done the trick. The DBA thinks that restarting SQL Server may have been enough. Thanks for the idea Joe.
Kelly
Sunday, February 19, 2012
error in accessing stored procedure
i'm trying to insert into db sql 2000 through a stored procedure .. i got this error " Procedure or function newuser has too many arguments specified "
this is the procedure :
ALTER PROCEDURE newuser
(@.username_1 [nvarchar](80),
@.email_2 [nvarchar](50),
@.password_3 [nvarchar](256),
@.Country_ID_4 [int],
@.city_id_5 [nvarchar](10),
@.gender_6 [nvarchar](50),
@.age_7 [int],
@.fname_8 [nvarchar](50),
@.lname_9 [nvarchar](50),
@.birthdate_10 [datetime])
AS INSERT INTO [Brazers].[dbo].[users]
( [username],
[email],
[password],
[Country.ID],
[city.id],
[gender],
[age],
[fname],
[lname],
[birthdate])
VALUES
( @.username_1,
@.email_2,
@.password_3,
@.Country_ID_4,
@.city_id_5,
@.gender_6,
@.age_7,
@.fname_8,
@.lname_9,
@.birthdate_10)
& that 's the code in asp page :
Dim param As New SqlClient.SqlParameter
SqlConnection1.Open()
param.ParameterName = "@.username_1"
param.Value = TextBox1.Text
param.Direction = ParameterDirection.Input
SqlCommand1.Parameters.Add(param)
SqlCommand1.ExecuteNonQuery()
plz .. waiting any solve for this problem immediatelyit looks to me like you have too FEW arguments specified. the sproc takes 10 params, none of them optional, and you are only passing ONE.|||sorry jazmine but i don't know wt to do to make it works .. plz tell me steps
this is the procedure :
ALTER PROCEDURE newuser
(@.username_1 [nvarchar](80),
@.email_2 [nvarchar](50),
@.password_3 [nvarchar](256),
@.Country_ID_4 [int],
@.city_id_5 [nvarchar](10),
@.gender_6 [nvarchar](50),
@.age_7 [int],
@.fname_8 [nvarchar](50),
@.lname_9 [nvarchar](50),
@.birthdate_10 [datetime])
AS INSERT INTO [Brazers].[dbo].[users]
( [username],
[email],
[password],
[Country.ID],
[city.id],
[gender],
[age],
[fname],
[lname],
[birthdate])
VALUES
( @.username_1,
@.email_2,
@.password_3,
@.Country_ID_4,
@.city_id_5,
@.gender_6,
@.age_7,
@.fname_8,
@.lname_9,
@.birthdate_10)
& that 's the code in asp page :
Dim param As New SqlClient.SqlParameter
SqlConnection1.Open()
param.ParameterName = "@.username_1"
param.Value = TextBox1.Text
param.Direction = ParameterDirection.Input
SqlCommand1.Parameters.Add(param)
SqlCommand1.ExecuteNonQuery()
plz .. waiting any solve for this problem immediatelyit looks to me like you have too FEW arguments specified. the sproc takes 10 params, none of them optional, and you are only passing ONE.|||sorry jazmine but i don't know wt to do to make it works .. plz tell me steps
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?
Friday, February 17, 2012
error handling with linked servers
Hi,
Could someone please tell me how to correctly manage errors with
linked servers?
I make an insert in a linked server. When a foreign key is violated
the whole batch is
aborted so I could not manage the error as I do on a standard server.
I mean:
locally this works fine and I always see the print:
insert into MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
The statement has been terminated.
an error happened!!!
on the contrary with a linked server the following code doesn't show
any print:
insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
The statement has been terminated.
Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
Thanks in advance for any kind suggestion,
Stefano
The batch is aborted with the linked server error due to the SET XACT_ABORT
ON setting, which is required here. If you are using SQL 2005, you can use
a TRY...CATCH block to trap the error in T-SQL. I believe the error must be
handled on the client side in SQL 2000.
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:36b3d389-0d49-428a-91fb-16814832b35d@.q39g2000hsf.googlegroups.com...
> Hi,
> Could someone please tell me how to correctly manage errors with
> linked servers?
> I make an insert in a linked server. When a foreign key is violated
> the whole batch is
> aborted so I could not manage the error as I do on a standard server.
> I mean:
>
> locally this works fine and I always see the print:
> insert into MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
> Msg 547, Level 16, State 0, Line 2
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
> The statement has been terminated.
> an error happened!!!
>
> on the contrary with a linked server the following code doesn't show
> any print:
> insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
>
> The statement has been terminated.
> Msg 547, Level 16, State 1, Line 1
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
>
> Thanks in advance for any kind suggestion,
> Stefano
|||Hi Dan,
thank you for your replay.
Now I understand: this is the correct behaviour with SET XACT_ABORT ON
which is implicitly
used by most OLD DB Providers.
Locally it works because SET XACT_ABORT is OFF by default.
Unfortunately I have to deal with SQL 2000 not 2005.
I could not (I think) handle this easly on the application because
this happens inside stored
procedures and I do not want to change a lot of code.
Since I have some possibility on the linked side I will create some
procedures on the target
database and call the remotely. These procedure on the remote side
will be able to handle
the violation.
I this this could be ok... do you agre?
Thanks again,
Stefano
|||> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
I have not done this myself but I think this approach might work. You'll
need to enable linked server rpc calls.
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:dfedf9ed-3d7d-41c4-a2b5-334cbb52cb47@.f47g2000hsd.googlegroups.com...
> Hi Dan,
> thank you for your replay.
> Now I understand: this is the correct behaviour with SET XACT_ABORT ON
> which is implicitly
> used by most OLD DB Providers.
> Locally it works because SET XACT_ABORT is OFF by default.
> Unfortunately I have to deal with SQL 2000 not 2005.
> I could not (I think) handle this easly on the application because
> this happens inside stored
> procedures and I do not want to change a lot of code.
> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
> Thanks again,
> Stefano
|||ok, thanks!
Could someone please tell me how to correctly manage errors with
linked servers?
I make an insert in a linked server. When a foreign key is violated
the whole batch is
aborted so I could not manage the error as I do on a standard server.
I mean:
locally this works fine and I always see the print:
insert into MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
The statement has been terminated.
an error happened!!!
on the contrary with a linked server the following code doesn't show
any print:
insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
The statement has been terminated.
Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
Thanks in advance for any kind suggestion,
Stefano
The batch is aborted with the linked server error due to the SET XACT_ABORT
ON setting, which is required here. If you are using SQL 2005, you can use
a TRY...CATCH block to trap the error in T-SQL. I believe the error must be
handled on the client side in SQL 2000.
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:36b3d389-0d49-428a-91fb-16814832b35d@.q39g2000hsf.googlegroups.com...
> Hi,
> Could someone please tell me how to correctly manage errors with
> linked servers?
> I make an insert in a linked server. When a foreign key is violated
> the whole batch is
> aborted so I could not manage the error as I do on a standard server.
> I mean:
>
> locally this works fine and I always see the print:
> insert into MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
> Msg 547, Level 16, State 0, Line 2
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
> The statement has been terminated.
> an error happened!!!
>
> on the contrary with a linked server the following code doesn't show
> any print:
> insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
>
> The statement has been terminated.
> Msg 547, Level 16, State 1, Line 1
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
>
> Thanks in advance for any kind suggestion,
> Stefano
|||Hi Dan,
thank you for your replay.
Now I understand: this is the correct behaviour with SET XACT_ABORT ON
which is implicitly
used by most OLD DB Providers.
Locally it works because SET XACT_ABORT is OFF by default.
Unfortunately I have to deal with SQL 2000 not 2005.
I could not (I think) handle this easly on the application because
this happens inside stored
procedures and I do not want to change a lot of code.
Since I have some possibility on the linked side I will create some
procedures on the target
database and call the remotely. These procedure on the remote side
will be able to handle
the violation.
I this this could be ok... do you agre?
Thanks again,
Stefano
|||> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
I have not done this myself but I think this approach might work. You'll
need to enable linked server rpc calls.
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:dfedf9ed-3d7d-41c4-a2b5-334cbb52cb47@.f47g2000hsd.googlegroups.com...
> Hi Dan,
> thank you for your replay.
> Now I understand: this is the correct behaviour with SET XACT_ABORT ON
> which is implicitly
> used by most OLD DB Providers.
> Locally it works because SET XACT_ABORT is OFF by default.
> Unfortunately I have to deal with SQL 2000 not 2005.
> I could not (I think) handle this easly on the application because
> this happens inside stored
> procedures and I do not want to change a lot of code.
> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
> Thanks again,
> Stefano
|||ok, thanks!
error handling with linked servers
Hi,
Could someone please tell me how to correctly manage errors with
linked servers?
I make an insert in a linked server. When a foreign key is violated
the whole batch is
aborted so I could not manage the error as I do on a standard server.
I mean:
locally this works fine and I always see the print:
insert into MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
The statement has been terminated.
an error happened!!!
on the contrary with a linked server the following code doesn't show
any print:
insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
The statement has been terminated.
Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
Thanks in advance for any kind suggestion,
StefanoThe batch is aborted with the linked server error due to the SET XACT_ABORT
ON setting, which is required here. If you are using SQL 2005, you can use
a TRY...CATCH block to trap the error in T-SQL. I believe the error must be
handled on the client side in SQL 2000.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:36b3d389-0d49-428a-91fb-16814832b35d@.q39g2000hsf.googlegroups.com...
> Hi,
> Could someone please tell me how to correctly manage errors with
> linked servers?
> I make an insert in a linked server. When a foreign key is violated
> the whole batch is
> aborted so I could not manage the error as I do on a standard server.
> I mean:
>
> locally this works fine and I always see the print:
> insert into MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
> Msg 547, Level 16, State 0, Line 2
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
> The statement has been terminated.
> an error happened!!!
>
> on the contrary with a linked server the following code doesn't show
> any print:
> insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
>
> The statement has been terminated.
> Msg 547, Level 16, State 1, Line 1
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
>
> Thanks in advance for any kind suggestion,
> Stefano|||Hi Dan,
thank you for your replay.
Now I understand: this is the correct behaviour with SET XACT_ABORT ON
which is implicitly
used by most OLD DB Providers.
Locally it works because SET XACT_ABORT is OFF by default.
Unfortunately I have to deal with SQL 2000 not 2005.
I could not (I think) handle this easly on the application because
this happens inside stored
procedures and I do not want to change a lot of code.
Since I have some possibility on the linked side I will create some
procedures on the target
database and call the remotely. These procedure on the remote side
will be able to handle
the violation.
I this this could be ok... do you agre?
Thanks again,
Stefano|||> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
I have not done this myself but I think this approach might work. You'll
need to enable linked server rpc calls.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:dfedf9ed-3d7d-41c4-a2b5-334cbb52cb47@.f47g2000hsd.googlegroups.com...
> Hi Dan,
> thank you for your replay.
> Now I understand: this is the correct behaviour with SET XACT_ABORT ON
> which is implicitly
> used by most OLD DB Providers.
> Locally it works because SET XACT_ABORT is OFF by default.
> Unfortunately I have to deal with SQL 2000 not 2005.
> I could not (I think) handle this easly on the application because
> this happens inside stored
> procedures and I do not want to change a lot of code.
> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
> Thanks again,
> Stefano|||ok, thanks!
Could someone please tell me how to correctly manage errors with
linked servers?
I make an insert in a linked server. When a foreign key is violated
the whole batch is
aborted so I could not manage the error as I do on a standard server.
I mean:
locally this works fine and I always see the print:
insert into MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
The statement has been terminated.
an error happened!!!
on the contrary with a linked server the following code doesn't show
any print:
insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
The statement has been terminated.
Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
Thanks in advance for any kind suggestion,
StefanoThe batch is aborted with the linked server error due to the SET XACT_ABORT
ON setting, which is required here. If you are using SQL 2005, you can use
a TRY...CATCH block to trap the error in T-SQL. I believe the error must be
handled on the client side in SQL 2000.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:36b3d389-0d49-428a-91fb-16814832b35d@.q39g2000hsf.googlegroups.com...
> Hi,
> Could someone please tell me how to correctly manage errors with
> linked servers?
> I make an insert in a linked server. When a foreign key is violated
> the whole batch is
> aborted so I could not manage the error as I do on a standard server.
> I mean:
>
> locally this works fine and I always see the print:
> insert into MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
> Msg 547, Level 16, State 0, Line 2
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
> The statement has been terminated.
> an error happened!!!
>
> on the contrary with a linked server the following code doesn't show
> any print:
> insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
>
> The statement has been terminated.
> Msg 547, Level 16, State 1, Line 1
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
>
> Thanks in advance for any kind suggestion,
> Stefano|||Hi Dan,
thank you for your replay.
Now I understand: this is the correct behaviour with SET XACT_ABORT ON
which is implicitly
used by most OLD DB Providers.
Locally it works because SET XACT_ABORT is OFF by default.
Unfortunately I have to deal with SQL 2000 not 2005.
I could not (I think) handle this easly on the application because
this happens inside stored
procedures and I do not want to change a lot of code.
Since I have some possibility on the linked side I will create some
procedures on the target
database and call the remotely. These procedure on the remote side
will be able to handle
the violation.
I this this could be ok... do you agre?
Thanks again,
Stefano|||> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
I have not done this myself but I think this approach might work. You'll
need to enable linked server rpc calls.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:dfedf9ed-3d7d-41c4-a2b5-334cbb52cb47@.f47g2000hsd.googlegroups.com...
> Hi Dan,
> thank you for your replay.
> Now I understand: this is the correct behaviour with SET XACT_ABORT ON
> which is implicitly
> used by most OLD DB Providers.
> Locally it works because SET XACT_ABORT is OFF by default.
> Unfortunately I have to deal with SQL 2000 not 2005.
> I could not (I think) handle this easly on the application because
> this happens inside stored
> procedures and I do not want to change a lot of code.
> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
> Thanks again,
> Stefano|||ok, thanks!
Wednesday, February 15, 2012
Error handling in TSQL
I have the following codes in TSQL to run. I set the last insert statement to have error. But I did not get the rollback tran.
What i got is:
Here goes the transactions
(10 rows)
(12 rows)
(13 rows)
Server: Msg 208, Level 16, State 1, Line 6
Invalid object name '#manageril1'.
======================================
begin tran
print 'Here goes the transactions'
insert into profile select * from #profins
insert into prof_compo select * from #compoins
insert into apps_user select * from #appluserins
insert into manager select * from #manageril1
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
else
Commit tran
print 'commit tran'
go
=====================================
What could I have not done or missed out? Why the rollback doesnot work?
Please advice.
Rgds,
Sam.From BOL:
A batch is a group of one or more Transact-SQL statements sent at one time from an application to Microsoft SQL Server for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time.
A compile error, such as a syntax error, prevents the compilation of the execution plan, so none of the statements in the batch are executed.
A run-time error, such as an arithmetic overflow or a constraint violation, has one of two effects:
Most run-time errors stop the current statement and the statements that follow it in the batch.
A few run-time errors, such as constraint violations, stop only the current statement. All the remaining statements in the batch are executed.
Some recommendations about your case:
- check for errors after every insert (update,delete) statement;
- create sp and check return value (rollback if needs).|||The problem isn't in your rollback. Your IF statement is never executed because the procedure fails critically before it gets to it. @.@.ERROR registers constraint and primary key violations, but if a gross syntax error causes the process to crash then its not going to help.
blindman|||And the error handling is incorrect
@.@.error is reset by every sql statement. You might want to set a variable or goto an error handler on errror.
begin tran
print 'Here goes the transactions'
insert into profile select * from #profins
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
insert into prof_compo select * from #compoins
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
insert into apps_user select * from #appluserins
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
insert into manager select * from #manageril1
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
Commit tran
print 'commit tran'
What i got is:
Here goes the transactions
(10 rows)
(12 rows)
(13 rows)
Server: Msg 208, Level 16, State 1, Line 6
Invalid object name '#manageril1'.
======================================
begin tran
print 'Here goes the transactions'
insert into profile select * from #profins
insert into prof_compo select * from #compoins
insert into apps_user select * from #appluserins
insert into manager select * from #manageril1
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
else
Commit tran
print 'commit tran'
go
=====================================
What could I have not done or missed out? Why the rollback doesnot work?
Please advice.
Rgds,
Sam.From BOL:
A batch is a group of one or more Transact-SQL statements sent at one time from an application to Microsoft SQL Server for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time.
A compile error, such as a syntax error, prevents the compilation of the execution plan, so none of the statements in the batch are executed.
A run-time error, such as an arithmetic overflow or a constraint violation, has one of two effects:
Most run-time errors stop the current statement and the statements that follow it in the batch.
A few run-time errors, such as constraint violations, stop only the current statement. All the remaining statements in the batch are executed.
Some recommendations about your case:
- check for errors after every insert (update,delete) statement;
- create sp and check return value (rollback if needs).|||The problem isn't in your rollback. Your IF statement is never executed because the procedure fails critically before it gets to it. @.@.ERROR registers constraint and primary key violations, but if a gross syntax error causes the process to crash then its not going to help.
blindman|||And the error handling is incorrect
@.@.error is reset by every sql statement. You might want to set a variable or goto an error handler on errror.
begin tran
print 'Here goes the transactions'
insert into profile select * from #profins
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
insert into prof_compo select * from #compoins
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
insert into apps_user select * from #appluserins
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
insert into manager select * from #manageril1
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
Commit tran
print 'commit tran'
Error Handling in SQL?
Hi there,
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LX
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LX
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>
Error Handling in SQL?
Hi there,
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LXhttp://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>|||data type mis-match will abort processing, primary key errors won't. Jacco's
already pointed you out to the best articles available on tsql error
handling but I'd add that you really need to handle error conditions in your
client application if you want re-try logic. It's the only place where
that's even possible if TSQL aborts processing..
HTH
Regards,
Greg Linwood
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
stored
> procedure, which is part of a large system. We don't have the error
handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LXhttp://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>|||data type mis-match will abort processing, primary key errors won't. Jacco's
already pointed you out to the best articles available on tsql error
handling but I'd add that you really need to handle error conditions in your
client application if you want re-try logic. It's the only place where
that's even possible if TSQL aborts processing..
HTH
Regards,
Greg Linwood
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
stored
> procedure, which is part of a large system. We don't have the error
handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>
Error Handling in SQL?
Hi there,
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LXhttp://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
--
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>|||data type mis-match will abort processing, primary key errors won't. Jacco's
already pointed you out to the best articles available on tsql error
handling but I'd add that you really need to handle error conditions in your
client application if you want re-try logic. It's the only place where
that's even possible if TSQL aborts processing..
HTH
Regards,
Greg Linwood
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
stored
> procedure, which is part of a large system. We don't have the error
handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LXhttp://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
--
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>|||data type mis-match will abort processing, primary key errors won't. Jacco's
already pointed you out to the best articles available on tsql error
handling but I'd add that you really need to handle error conditions in your
client application if you want re-try logic. It's the only place where
that's even possible if TSQL aborts processing..
HTH
Regards,
Greg Linwood
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
stored
> procedure, which is part of a large system. We don't have the error
handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>
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.
Subscribe to:
Posts (Atom)