Odd problem with primary keys - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Odd problem with primary keys

Author

Topic

hagis
Starting Member

8 Posts

Posted - 2010-03-11 : 07:04:35

Hello all, first post, please be gentle!

I've an odd problem with a table on our database that has been in use for years. One of the applications writing to it has recently started throwing errors about the primary key constraint being violated by an INSERT statement.

The baffling bit of it is that the primary key field is an identity field and the INSERT statement doesn't even try to set this value. If I freeze the application and then copy the SQL it is about to use into Query Analyser and run it then the data is inserted without any problems.

I'm completely stumped by this, Googling it hasn't helped as it just returns lots of people having more obvious problems with primary keys.

Any suggestions would be gratefully received!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-03-11 : 09:20:26

a) Run PROFILER on your SQL code (in operation) - will only be able to track future problems.
or
b) Alter the SP to have some "self-logging" code - i.e. build a poor-mans Profiler, logging what is done when and by whom to a text file for analysis.

At a guess you're getting hit by a timing problem - 2 calls to the code at the same time. Nothing wrong with a specific call in itself, which is why it works 2nd time around.

Post the code and we may be able to comment more.

hagis
Starting Member

8 Posts

Posted - 2010-03-11 : 10:26:50

Thanks for taking the time to post Andrew.

I ran the profiler as you suggested and caught the error:


INSERT INTO [components] ([Barcode],[Master ID],[Description]) VALUES ('4674252770002766164',125302902,'')
INSERT INTO [components] ([Barcode],[Master ID],[Description]) VALUES ('4674252770002766165',125302902,'')
INSERT INTO [components] ([Barcode],[Master ID],[Description]) VALUES ('4674252770002766166',125302902,'')
INSERT INTO [components] ([Barcode],[Master ID],[Description]) VALUES ('4674252770002766167',125302902,'')
2627	Exception	Error: 2627, Severity: 14, State: 1

The [Master ID] is the FK in another table. I can't see anything on here that would cause a problem.

To add some more information about the problem; there is a trigger on the components table that inserts a value into another table.
I've checked over that SQL and I can't see any problems with it. The trigger SQL isn't anything lengthly:


insert into Items (ComponentID,PackID)
	select [Component ID],
		case WHEN CHARINDEX('|',[Barcode]) = 0 THEN 
				[Component ID] 
			ELSE 
				SUBSTRING([Barcode],CHARINDEX('|',[Barcode])+1,CHARINDEX('|',[Barcode],CHARINDEX('|',[Barcode])+1) - (CHARINDEX('|',[Barcode])+1))
			END
	FROM inserted
END

Am I right in assuming that the trigger would be executed after the data being inserted into the components table had been stored and assigned an identifier for the PK?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-03-11 : 10:27:02

Does the table have a trigger on it?

CODO ERGO SUM

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-03-12 : 03:36:18

"Am I right in assuming that the trigger would be executed after the data being inserted into the components table had been stored and assigned an identifier for the PK?"

This depends on the type of trigger...can you show the full trigger code including the preamble (create trigger abc, etc)?

Can you see if disabling the trigger solves your immediate problem - you may then infer the problem is in/not in the trigger code depending on whether or not the problem goes away. What's the key for the "items" table.

Can you get the trigger code to log how many records it is processing in each "inserted" dataset.

hagis
Starting Member

8 Posts

Posted - 2010-03-12 : 03:54:02

Full trigger code is as follows:


ALTER TRIGGER [dbo].[trigCreateComponentsItems] ON [dbo].[Components] 
FOR INSERT
AS

SET NOCOUNT ON
BEGIN

insert into Items (ComponentID,PackID)
	select 
		[Component ID],
		case WHEN CHARINDEX('|',[Barcode]) = 0 THEN 
				[Component ID] 
			ELSE 
				SUBSTRING([Barcode],CHARINDEX('|',[Barcode])+1,CHARINDEX('|',[Barcode],CHARINDEX('|',[Barcode])+1) - (CHARINDEX('|',[Barcode])+1))
			END
	FROM inserted
END

I had wondered whether the "FOR INSERT" should be "AFTER INSERT" but the MS documentation is a bit vague about whether changing this would make a difference:

"AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.

AFTER is the default when FOR is the only keyword specified."

hagis
Starting Member

8 Posts

Posted - 2010-03-12 : 10:09:04

To muddy the waters even further:

I've ran the application that uploads the data on a copy of the database on another server, the error still occurs.

I've changed the application so that it waited a quarter of a second between each write to the components table, the error still occurs.

I've changed the trigger that used to write to the items table so that it doesn't do anything, the error still occurs.

I've also ran the application several times with the same data. Sometimes it runs perfectly other times it fails, but it can fail on different pieces of data each time.

The more I look into this the more confused I become!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-03-15 : 05:54:26

sounds like not a problem with your trigger.
can you post the code inserting into your main table? any chance there is a loop?

hagis
Starting Member

8 Posts

Posted - 2010-03-16 : 05:20:11

This is the code that's doing the insert. It inserts the components in succession.
Up until last week this worked fine for 6 years!

I've changed the code so that it open and closes a separate connection for each insert,
previously it opened one connection used by a SqlCommand for each insert


For Each clsItem In arrConsignmentComponents
     If clsItem.strBarcode.Length > 0 Then
          dbConnection = New SqlClient.SqlConnection("xxx")
          dbConnection.Open()
          strSQL = "INSERT INTO components ([Master ID], Barcode, [Description]) VALUES ("
          strSQL &= intMasterID.ToString() & ", '" & clsItem.strBarcode & "', '" & clsItem.strDescription & "')"
                
          dbCommand = New SqlClient.SqlCommand(strSQL, dbConnection)
          dbCommand.ExecuteNonQuery()
          dbCommand.Dispose()
          dbConnection.Close()
          dbConnection.Dispose()
          dbConnection = Nothing
     End If
Next

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-03-16 : 06:43:25

This might not solve your problem but there are a couple of things you should be doing that's just good standard practice. I don't think you need a new connection for each insert; create one SQL statement that will insert all the values then execute such as:



INSERT INTO components ([Master ID], Barcode, [Description])
SELECT 1,000001,'something'
UNION ALL
SELECT 2,000002,'something else'
UNION ALL
SELECT 3,000003,'something else again'

Also, you should parameterise your query, otherwise you open the application to SQL injection and the query cannot always be optimised in the same way when using straight dynamic SQL. It shoudl be easy to find out how to do this by Googling.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-03-16 : 06:59:14

seperate connections per insert would slow down the application.

open connection - or even re-use 1 connection for the entire application (open/close/open/close connections is a general "performance no-no")
loop through insert
calling a Stored Procedure - best for security, database/code independence & also execution plan-reuse
close connection

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-03-16 : 07:04:13

You don't need a stored procedure for security, a parameterised query is also acceptable, although execution plan re-use can be a benefit from a stored procedure.

hagis
Starting Member

8 Posts

Posted - 2010-03-16 : 07:17:45

Thanks for taking the time to read and for your suggestions, gents. They're much appreciated.

Opening and closing the connection between each command was a deliberate change I made once the error started happening. I suspected that the database wasn't returning the ID in time for the next query, hence the clash. I hoped that by slowing it down it would avoid this. This was just me clutching at straws and it made no difference at all.

Generally, I'd only hold a connection open during an import run and the application would close the connection afterwards and not hold it open.

The code does have features in for stopping injection attacks but I cut them out when I posted the code to make it easier to read.

This morning, I've re-created the components and items tables (with indexes, constraints and triggers) with different names and am running import testing on them as I speak.
So far, they are working perfectly which is making me think that the database has become corrupt.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-03-16 : 07:43:39

Okay, that's good. I hope that these "features" are either a parameterised query or a properly parametrised stored procedure. I've heard of some interesting ways to protect against SQL injection.

hagis
Starting Member

8 Posts

Posted - 2010-03-16 : 08:13:47

I've also ran a DBCC CHECKTABLE, CHECKCONSTRAINTS & CHECKITDENT on both components and items without any reported problems. Testing on the new tables has yet to reveal any errors.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources