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 inserting. Show all posts
Showing posts with label inserting. 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
Error inserting into Table Datatype with Identity Column
Hi
We are using the code below to simulate a cursor using the Table Datatype
but we are getting an error when inserting the records.
Server: Msg 8101, Level 16, State 1, Line 19
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Line 28
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Any help would be much appreciated
Thanks
B
CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Type
VARCHAR(55))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.Output VARCHAR(8000)
DECLARE @.Imports VARCHAR(355)
DECLARE @.Description VARCHAR(355)
DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
VARCHAR(355), RowId INT IDENTITY(1, 1))
DECLARE @.count INT
DECLARE @.iRow INT
SET @.Output = ''
IF @.Type = 'All Items'
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
ORDER BY Usr_Imports.ID
END
ELSE
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
WHERE EntityRef = @.Client AND MatterNo =
@.OrderNo
ORDER BY Usr_Imports.ID
END
/** Simulate Cursor
****************************************
****************************/
SET @.count = @.@.ROWCOUNT
SET @.iRow = 1
WHILE @.iRow <= @.count
BEGIN
SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
FROM @.tblImports
WHERE RowId = @.iRow
SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
SET @.iRow = @.iRow + 1
END
RETURN @.Output
ENDAlways specify the column list in an INSERT statement:
INSERT @.tblImports (dm_ImportDesc, [Description])
SELECT dm_ImportDesc, [Description]
FROM ...
I recommend that you don't use ORDER BY in the INSERT statement. It is
not necessarily guaranteed that the IDENTITY column will be populated
in the order you specify.
If you explain your requirement I'm sure someone can suggest something
better. Why do concatentation and formatting in the database anyway?
David Portas
SQL Server MVP
--|||Ben
error clearly states that you are trying to update an identity column in the
function( not in cucrsor)
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
Post DDL
--
Regards
R.D
--Knowledge gets doubled when shared
"Ben" wrote:
> Hi
>
> We are using the code below to simulate a cursor using the Table Datatype
> but we are getting an error when inserting the records.
>
> Server: Msg 8101, Level 16, State 1, Line 19
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
> Server: Msg 8101, Level 16, State 1, Line 28
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
>
> Any help would be much appreciated
>
> Thanks
> B
>
> CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Ty
pe
> VARCHAR(55))
> RETURNS VARCHAR(8000) AS
> BEGIN
>
> DECLARE @.Output VARCHAR(8000)
> DECLARE @.Imports VARCHAR(355)
> DECLARE @.Description VARCHAR(355)
> DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
> VARCHAR(355), RowId INT IDENTITY(1, 1))
> DECLARE @.count INT
> DECLARE @.iRow INT
> SET @.Output = ''
>
> IF @.Type = 'All Items'
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> ORDER BY Usr_Imports.ID
> END
> ELSE
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> WHERE EntityRef = @.Client AND MatterNo
=
> @.OrderNo
> ORDER BY Usr_Imports.ID
> END
>
> /** Simulate Cursor
> ****************************************
****************************/
> SET @.count = @.@.ROWCOUNT
> SET @.iRow = 1
> WHILE @.iRow <= @.count
> BEGIN
> SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
> FROM @.tblImports
> WHERE RowId = @.iRow
>
> SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
> Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
> SET @.iRow = @.iRow + 1
> END
>
> RETURN @.Output
> END
>
>|||David,
It's with yukon.
http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
-oj
"David Portas"
> I recommend that you don't use ORDER BY in the INSERT statement. It is
> not necessarily guaranteed that the IDENTITY column will be populated
> in the order you specify.|||Thank you both for your input, it works perfectly now.
Regards
B
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:6FEF31BA-887B-49DA-A0DD-2EA4B07C5A71@.microsoft.com...
> Ben
> error clearly states that you are trying to update an identity column in
the
> function( not in cucrsor)
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
> Post DDL
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Ben" wrote:
>
Datatype
only be
only be
@.Type
database_default
database_default
MatterNo =
[Description]
'All|||> INSERT queries that use SELECT with ORDER BY to populate rows
> guarantees how identity values are computed but not the order
> in which the rows are inserted
Possibly, but the same claim is documented in a KB for 2000 and in that
case it is inaccurate. See the following link for Gert-Jan's repro.
Haven't tested this on 2005 but even if it works I'm not sure how
confident I would be about it, given the history and the kludgy nature
of this "feature".:
http://groups.google.co.uk/group/mi...bfd47d975aca778
Now that we have RECORD_NUMBER() the ORDER BY in an INSERT is
redundant. RECORD_NUMBER() should be preferred IMO.
David Portas
SQL Server MVP
--sql
We are using the code below to simulate a cursor using the Table Datatype
but we are getting an error when inserting the records.
Server: Msg 8101, Level 16, State 1, Line 19
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Line 28
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Any help would be much appreciated
Thanks
B
CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Type
VARCHAR(55))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.Output VARCHAR(8000)
DECLARE @.Imports VARCHAR(355)
DECLARE @.Description VARCHAR(355)
DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
VARCHAR(355), RowId INT IDENTITY(1, 1))
DECLARE @.count INT
DECLARE @.iRow INT
SET @.Output = ''
IF @.Type = 'All Items'
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
ORDER BY Usr_Imports.ID
END
ELSE
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
WHERE EntityRef = @.Client AND MatterNo =
@.OrderNo
ORDER BY Usr_Imports.ID
END
/** Simulate Cursor
****************************************
****************************/
SET @.count = @.@.ROWCOUNT
SET @.iRow = 1
WHILE @.iRow <= @.count
BEGIN
SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
FROM @.tblImports
WHERE RowId = @.iRow
SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
SET @.iRow = @.iRow + 1
END
RETURN @.Output
ENDAlways specify the column list in an INSERT statement:
INSERT @.tblImports (dm_ImportDesc, [Description])
SELECT dm_ImportDesc, [Description]
FROM ...
I recommend that you don't use ORDER BY in the INSERT statement. It is
not necessarily guaranteed that the IDENTITY column will be populated
in the order you specify.
If you explain your requirement I'm sure someone can suggest something
better. Why do concatentation and formatting in the database anyway?
David Portas
SQL Server MVP
--|||Ben
error clearly states that you are trying to update an identity column in the
function( not in cucrsor)
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
Post DDL
--
Regards
R.D
--Knowledge gets doubled when shared
"Ben" wrote:
> Hi
>
> We are using the code below to simulate a cursor using the Table Datatype
> but we are getting an error when inserting the records.
>
> Server: Msg 8101, Level 16, State 1, Line 19
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
> Server: Msg 8101, Level 16, State 1, Line 28
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
>
> Any help would be much appreciated
>
> Thanks
> B
>
> CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Ty
pe
> VARCHAR(55))
> RETURNS VARCHAR(8000) AS
> BEGIN
>
> DECLARE @.Output VARCHAR(8000)
> DECLARE @.Imports VARCHAR(355)
> DECLARE @.Description VARCHAR(355)
> DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
> VARCHAR(355), RowId INT IDENTITY(1, 1))
> DECLARE @.count INT
> DECLARE @.iRow INT
> SET @.Output = ''
>
> IF @.Type = 'All Items'
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> ORDER BY Usr_Imports.ID
> END
> ELSE
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> WHERE EntityRef = @.Client AND MatterNo
=
> @.OrderNo
> ORDER BY Usr_Imports.ID
> END
>
> /** Simulate Cursor
> ****************************************
****************************/
> SET @.count = @.@.ROWCOUNT
> SET @.iRow = 1
> WHILE @.iRow <= @.count
> BEGIN
> SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
> FROM @.tblImports
> WHERE RowId = @.iRow
>
> SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
> Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
> SET @.iRow = @.iRow + 1
> END
>
> RETURN @.Output
> END
>
>|||David,
It's with yukon.
http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
-oj
"David Portas"
> I recommend that you don't use ORDER BY in the INSERT statement. It is
> not necessarily guaranteed that the IDENTITY column will be populated
> in the order you specify.|||Thank you both for your input, it works perfectly now.
Regards
B
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:6FEF31BA-887B-49DA-A0DD-2EA4B07C5A71@.microsoft.com...
> Ben
> error clearly states that you are trying to update an identity column in
the
> function( not in cucrsor)
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
> Post DDL
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Ben" wrote:
>
Datatype
only be
only be
@.Type
database_default
database_default
MatterNo =
[Description]
'All|||> INSERT queries that use SELECT with ORDER BY to populate rows
> guarantees how identity values are computed but not the order
> in which the rows are inserted
Possibly, but the same claim is documented in a KB for 2000 and in that
case it is inaccurate. See the following link for Gert-Jan's repro.
Haven't tested this on 2005 but even if it works I'm not sure how
confident I would be about it, given the history and the kludgy nature
of this "feature".:
http://groups.google.co.uk/group/mi...bfd47d975aca778
Now that we have RECORD_NUMBER() the ORDER BY in an INSERT is
redundant. RECORD_NUMBER() should be preferred IMO.
David Portas
SQL Server MVP
--sql
Wednesday, March 7, 2012
Error in inserting value by trigger in linked server
Hi Pls help me
I have 2 db servers and its a lnked server
while am inserting value in source table (table having a trigger to raise ti
other table in defferent server) i am facing this error and Data is not
getting inserted.
error is
- Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator. ]
regards
KRP
Do you have the Distributed Transaction Coordinator running? On SQL Server
2000 it is one of the services in the SQL Server suite along with SQL Server
and SQL Agent.
RLF
"KRP" <KRP @.discussions.microsoft.com> wrote in message
news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
> Hi Pls help me
> I have 2 db servers and its a lnked server
> while am inserting value in source table (table having a trigger to raise
> ti
> other table in defferent server) i am facing this error and Data is not
> getting inserted.
> error is
> - Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable
> to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: New transaction cannot enlist in the specified transaction
> coordinator. ]
> regards
> KRP
|||Yes,
Its already started and every pre conf hasbeen done..
Please Can you send me a steps of creating a linked server?
Thanks
KRp
"Russell Fields" wrote:
> Do you have the Distributed Transaction Coordinator running? On SQL Server
> 2000 it is one of the services in the SQL Server suite along with SQL Server
> and SQL Agent.
> RLF
> "KRP" <KRP @.discussions.microsoft.com> wrote in message
> news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
>
>
I have 2 db servers and its a lnked server
while am inserting value in source table (table having a trigger to raise ti
other table in defferent server) i am facing this error and Data is not
getting inserted.
error is
- Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator. ]
regards
KRP
Do you have the Distributed Transaction Coordinator running? On SQL Server
2000 it is one of the services in the SQL Server suite along with SQL Server
and SQL Agent.
RLF
"KRP" <KRP @.discussions.microsoft.com> wrote in message
news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
> Hi Pls help me
> I have 2 db servers and its a lnked server
> while am inserting value in source table (table having a trigger to raise
> ti
> other table in defferent server) i am facing this error and Data is not
> getting inserted.
> error is
> - Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable
> to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: New transaction cannot enlist in the specified transaction
> coordinator. ]
> regards
> KRP
|||Yes,
Its already started and every pre conf hasbeen done..
Please Can you send me a steps of creating a linked server?
Thanks
KRp
"Russell Fields" wrote:
> Do you have the Distributed Transaction Coordinator running? On SQL Server
> 2000 it is one of the services in the SQL Server suite along with SQL Server
> and SQL Agent.
> RLF
> "KRP" <KRP @.discussions.microsoft.com> wrote in message
> news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
>
>
Error in inserting value by trigger in linked server
Hi Pls help me
I have 2 db servers and its a lnked server
while am inserting value in source table (table having a trigger to raise ti
other table in defferent server) i am facing this error and Data is not
getting inserted.
error is
- Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The o
peration
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provi
der returned
message: New transaction cannot enlist in the specified transaction
coordinator. ]
regards
KRPDo you have the Distributed Transaction Coordinator running? On SQL Server
2000 it is one of the services in the SQL Server suite along with SQL Server
and SQL Agent.
RLF
"KRP" <KRP @.discussions.microsoft.com> wrote in message
news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
> Hi Pls help me
> I have 2 db servers and its a lnked server
> while am inserting value in source table (table having a trigger to raise
> ti
> other table in defferent server) i am facing this error and Data is not
> getting inserted.
> error is
> - Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable
> to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB pro
vider returned
> message: New transaction cannot enlist in the specified transaction
> coordinator. ]
> regards
> KRP|||Yes,
Its already started and every pre conf hasbeen done..
Please Can you send me a steps of creating a linked server?
Thanks
KRp
"Russell Fields" wrote:
> Do you have the Distributed Transaction Coordinator running? On SQL Serve
r
> 2000 it is one of the services in the SQL Server suite along with SQL Serv
er
> and SQL Agent.
> RLF
> "KRP" <KRP @.discussions.microsoft.com> wrote in message
> news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
>
>
I have 2 db servers and its a lnked server
while am inserting value in source table (table having a trigger to raise ti
other table in defferent server) i am facing this error and Data is not
getting inserted.
error is
- Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The o
peration
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provi
der returned
message: New transaction cannot enlist in the specified transaction
coordinator. ]
regards
KRPDo you have the Distributed Transaction Coordinator running? On SQL Server
2000 it is one of the services in the SQL Server suite along with SQL Server
and SQL Agent.
RLF
"KRP" <KRP @.discussions.microsoft.com> wrote in message
news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
> Hi Pls help me
> I have 2 db servers and its a lnked server
> while am inserting value in source table (table having a trigger to raise
> ti
> other table in defferent server) i am facing this error and Data is not
> getting inserted.
> error is
> - Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable
> to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB pro
vider returned
> message: New transaction cannot enlist in the specified transaction
> coordinator. ]
> regards
> KRP|||Yes,
Its already started and every pre conf hasbeen done..
Please Can you send me a steps of creating a linked server?
Thanks
KRp
"Russell Fields" wrote:
> Do you have the Distributed Transaction Coordinator running? On SQL Serve
r
> 2000 it is one of the services in the SQL Server suite along with SQL Serv
er
> and SQL Agent.
> RLF
> "KRP" <KRP @.discussions.microsoft.com> wrote in message
> news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
>
>
Error in inserting value by trigger in linked server
Hi Pls help me
I have 2 db servers and its a lnked server
while am inserting value in source table (table having a trigger to raise ti
other table in defferent server) i am facing this error and Data is not
getting inserted.
error is
- Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator. ]
regards
KRPDo you have the Distributed Transaction Coordinator running? On SQL Server
2000 it is one of the services in the SQL Server suite along with SQL Server
and SQL Agent.
RLF
"KRP" <KRP @.discussions.microsoft.com> wrote in message
news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
> Hi Pls help me
> I have 2 db servers and its a lnked server
> while am inserting value in source table (table having a trigger to raise
> ti
> other table in defferent server) i am facing this error and Data is not
> getting inserted.
> error is
> - Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable
> to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: New transaction cannot enlist in the specified transaction
> coordinator. ]
> regards
> KRP|||Yes,
Its already started and every pre conf hasbeen done..
Please Can you send me a steps of creating a linked server?
Thanks
KRp
"Russell Fields" wrote:
> Do you have the Distributed Transaction Coordinator running? On SQL Server
> 2000 it is one of the services in the SQL Server suite along with SQL Server
> and SQL Agent.
> RLF
> "KRP" <KRP @.discussions.microsoft.com> wrote in message
> news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
> > Hi Pls help me
> >
> > I have 2 db servers and its a lnked server
> > while am inserting value in source table (table having a trigger to raise
> > ti
> > other table in defferent server) i am facing this error and Data is not
> > getting inserted.
> > error is
> > - Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> > could not be performed because the OLE DB provider 'SQLOLEDB' was unable
> > to
> > begin a distributed transaction.
> > [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> > message: New transaction cannot enlist in the specified transaction
> > coordinator. ]
> >
> > regards
> > KRP
>
>
I have 2 db servers and its a lnked server
while am inserting value in source table (table having a trigger to raise ti
other table in defferent server) i am facing this error and Data is not
getting inserted.
error is
- Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator. ]
regards
KRPDo you have the Distributed Transaction Coordinator running? On SQL Server
2000 it is one of the services in the SQL Server suite along with SQL Server
and SQL Agent.
RLF
"KRP" <KRP @.discussions.microsoft.com> wrote in message
news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
> Hi Pls help me
> I have 2 db servers and its a lnked server
> while am inserting value in source table (table having a trigger to raise
> ti
> other table in defferent server) i am facing this error and Data is not
> getting inserted.
> error is
> - Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable
> to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: New transaction cannot enlist in the specified transaction
> coordinator. ]
> regards
> KRP|||Yes,
Its already started and every pre conf hasbeen done..
Please Can you send me a steps of creating a linked server?
Thanks
KRp
"Russell Fields" wrote:
> Do you have the Distributed Transaction Coordinator running? On SQL Server
> 2000 it is one of the services in the SQL Server suite along with SQL Server
> and SQL Agent.
> RLF
> "KRP" <KRP @.discussions.microsoft.com> wrote in message
> news:8C37B85A-951E-414C-A6BA-9D7BBFC039D0@.microsoft.com...
> > Hi Pls help me
> >
> > I have 2 db servers and its a lnked server
> > while am inserting value in source table (table having a trigger to raise
> > ti
> > other table in defferent server) i am facing this error and Data is not
> > getting inserted.
> > error is
> > - Unable to preserve trigger 'InsertIntoHPD_HelpDesk'.
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> > could not be performed because the OLE DB provider 'SQLOLEDB' was unable
> > to
> > begin a distributed transaction.
> > [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> > message: New transaction cannot enlist in the specified transaction
> > coordinator. ]
> >
> > regards
> > KRP
>
>
Wednesday, February 15, 2012
Error Handling in Sql Server 2000
I am having a SP (Stored Procedure) I want to handle the error in any case for the SP. My SP is inserting a record in a table when I had changed the Column name of the Table the error is not traced but the error is directly displayed in the TSql. How to Handle any error that occured in the SP? Thanks in Advance.
CREATE PROCEDURE SP_InvoiceFromRBOPInvNoHdr @.AutoBillID int, @.NextBillDate DateTime, @.OPInvNo int OutPut
AS
declare @.InvNo int
declare @.CustID int
declare @.InvAdd1 varchar(50)
declare @.InvAdd2 varchar(50)
declare @.city varchar(50)
declare @.Postal varchar(20)
declare @.Country varchar(50)
declare @.Province varchar(50)
declare @.BillDate datetime
declare @.BillingContact varchar(50)
declare @.BillingCurrencyCode varchar(5)
declare @.StoreID int
declare @.BillingPeriod varchar(10)
declare @.BillingTime varchar(10)
declare @.comment varchar(100)
declare @.TotalBillAmount float
declare @.TotalTax1 float
declare @.TotalTax2 float
declare @.err int
if exists(select * from BillRecurringInvoiceHeader where AutoBillID=@.AutoBillID)
begin
select @.InvNo = IsNull(max(InvoiceNumber),0)+1 from BillInvoiceHeaderHistory
select @.CustID=CustID,@.InvAdd1 = Address1, @.InvAdd2 = Address2, @.city=City,@.Postal=Postal,@.Country=Country,@.Province=Province,
@.BillDate = NextBillDate,@.BillingContact=BillContactName,@.BillingCurrencyCode=BillCurrencyCode,@.StoreID=StoreID,@.BillingPeriod=BillingPeriod,@.BillingTime=PreferredBillingTime
from BillRecurringInvoiceHeader where AutoBillID=@.AutoBillID
Set @.comment = 'Invoice from BillID ' + Cast(@.AutoBillID as varchar(10))
exec SP_RBAmount @.StoreID, @.AutoBillID, 'I',@.TotalBillAmount Output, @.TotalTax1 output, @.TotalTax2 output
--BillInvoiceHeaderHistory
insert into BillInvoiceHeaderHistory (InvoiceNumber,BillingAddress1,BillingAddress2,City,
Province,Postal,Country,BillingContact,BillDate,Comments,TotalBillAmount,TotalTax1,TotalTax2,BillingCurrencyCode,
AutoBillID,TotalRcdAmount,Status,StoreID,CustID) values (@.InvNo,@.InvAdd1,@.InvAdd2,@.city,
@.Province,@.Postal,@.Country,@.BillingContact,@.BillDate,@.comment, @.TotalBillAmount, @.TotalTax1, @.TotalTax2,
@.BillingCurrencyCode,@.AutoBillID,0,Null,@.StoreID,@.CustID)
select @.err = @.@.ERROR
if @.err != 0
begin
--Error Occured
--Set @.OPInvNo = -1
return @.err
end
else
begin
--No Errors
Set @.OPInvNo = @.InvNo
end
end
GO
declare @.err int
select @.InvNo = IsNull(max(InvoiceNumber),0)+1 from BillInvoiceHeaderHistory
set @.err = @.@.error
if (@.err != 0) begin ... end
select @.CustID=CustID,@.InvAdd1 = Address1, @.InvAdd2 = Address2, @.city=City,@.Postal=Postal,@.Country=Country,@.Province=Province,
@.BillDate
=
NextBillDate,@.BillingContact=BillContactName,@.BillingCurrencyCode=BillCurrencyCode,@.StoreID=StoreID,@.BillingPeriod=BillingPeriod,@.BillingTime=PreferredBillingTime
from BillRecurringInvoiceHeader where AutoBillID=@.AutoBillID
set @.err = @.@.error
if (@.err != 0) begin ... end
Set @.comment = 'Invoice from BillID ' + Cast(@.AutoBillID as varchar(10))
set @.err = @.@.error
if (@.err != 0) begin ... end
exec SP_RBAmount @.StoreID, @.AutoBillID, 'I',@.TotalBillAmount Output, @.TotalTax1 output, @.TotalTax2 output
set @.err = @.@.error
if (@.err != 0) begin ... end
--BillInvoiceHeaderHistory
insert into BillInvoiceHeaderHistory (InvoiceNumber,BillingAddress1,BillingAddress2,City,
Province,Postal,Country,BillingContact,BillDate,Comments,TotalBillAmount,TotalTax1,TotalTax2,BillingCurrencyCode,
AutoBillID,TotalRcdAmount,Status,StoreID,CustID) values (@.InvNo,@.InvAdd1,@.InvAdd2,@.city,
@.Province,@.Postal,@.Country,@.BillingContact,@.BillDate,@.comment, @.TotalBillAmount, @.TotalTax1, @.TotalTax2,
@.BillingCurrencyCode,@.AutoBillID,0,Null,@.StoreID,@.CustID)
set @.err = @.@.errorif (@.err != 0) begin ... end
I had changed the column name of the table and this error is what I am unable to handle. The SP stops in the insert statement only. I had changed the column BillingAddress2 to BillingAddress23 and I am getting this error :
Server: Msg 207, Level 16, State 1, Procedure SP_InvoiceFromRBOPInvNoHdr, Line 39
Invalid column name 'BillingAddress2'.
Check this post..
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=944372&SiteID=1
|||Just to add my 2 cents worth, Error Handling in SQL Server 2000 was pretty weak. Check this article: http://www.sommarskog.se/error-handling-I.html for a good bit of information about this, and this one that is about stored procedures: http://www.sommarskog.se/error-handling-II.html. Bottom line, in 2000 every error was sent to the client. In 2005, you can take care of some of this with TRY..CATCH, but not all.
I don't think even 2005 will help you one a column name change, this is something that you need to manage in your code/testing.
|||There is no way in 2000 or 2005 to trap an errror if the column does not exist. The error trapping in 2000 and 2005 is almost non-existent.
You cannot trap a non-existent column in either version.
|||I wouldn't put it that way: "The error trapping in 2000 and 2005 is almost non-existent"
There are only a few errors that cannot be trapped, and I am not even sure if it would be a good idea for this kind of error to be trapped. This is a compilation error, so more or less the code cannot be executed. SQL is a very flexible language (exec ("select ...") for example) but it shouldn't be that flexible. It does catch most errors that it should and is 1000 times better than in 2000.
Subscribe to:
Posts (Atom)