July 7, 2011 at 3:19 pm
Hello, I am new to SQL so please bear with me as I try to explain myself:
I have a program that inserts a record, returns the record id of the newly inserted record, then inserts another record in another table using the returned row id as the foreign key.
The issue is that intermittently the second record does not get inserted. It is so hard to replicate. Only one user is inserting records at anytime. I would say this happens once every week, with about 70 records being inserted weekly.
I want to catch when this happens and keep inserting until it is recorded.
What is the best way to catch this?
I see all the lingo like transactions, triggers, stored procedures, audits, etc... and I don't know what direction I should look to prevent this error.
Thanks!
July 7, 2011 at 4:01 pm
...
I have a program that inserts a record, returns the record id of the newly inserted record, then inserts another record in another table using the returned row id as the foreign key.
Is this program writting directly to the tables or is it using stored procedures? What is the program written in?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 7, 2011 at 4:48 pm
One thing you should check is how you are retrieving that value to insert as the foreign key.
Make sure you are using SCOPE_IDENTITY(). That is the only reliable way of capturing the value you just inserted. If you are using one of the other two methods, then you might get the wrong value for your key, and then when you try to insert this into the other table, if your foreign key constraints are enforced, you'll get a foreign key violation error.
See: http://msdn.microsoft.com/en-us/library/ms190315.aspx
If you're still for some reason getting an error, you could wrap the INSERT which uses the foreign key, with a TRY-CATCH block. On the CATCH statement, you can write a log statement with the value that would have been written.
July 7, 2011 at 5:10 pm
toddasd (7/7/2011)
...
I have a program that inserts a record, returns the record id of the newly inserted record, then inserts another record in another table using the returned row id as the foreign key.
Is this program writting directly to the tables or is it using stored procedures? What is the program written in?
The program is writing directly to the tables. It is written in VB.Net. I'll post the code below.
Private Function AddToShipmentDatabase()
'Connect to local database and upload the information
'stored in the shipment information structure
Dim objConnection As SqlConnection = New SqlConnection("Server=Server;Database=ShippingLog;User Id=user1;Password=password")
Dim objCommand As New SqlCommand
objCommand.Connection = objConnection
objCommand.CommandText = "INSERT INTO [MailLog] ([CustomerId], [ShipDate], [CustomerName], [Service], [Tracking], [Address], [RxCount], [Charge], [CreditCardInfo], [TransactionId]) VALUES(@CustomerId, @ShipDate, @CustomerName, @Service, @Tracking, @Address, @RxCount, @Charge, @CreditCardInfo, @TransactionId)"
objCommand.Parameters.AddWithValue("@CustomerId", ShipmentInformation.CustomerId)
objCommand.Parameters.AddWithValue("@ShipDate", ShipmentInformation.ShipDate)
objCommand.Parameters.AddWithValue("@CustomerName", ShipmentInformation.CustomerName)
objCommand.Parameters.AddWithValue("@Service", ShipmentInformation.Service)
objCommand.Parameters.AddWithValue("@Tracking", ShipmentInformation.Tracking)
objCommand.Parameters.AddWithValue("@Address", ShipmentInformation.Address)
objCommand.Parameters.AddWithValue("@RxCount", ShipmentInformation.RxCount)
objCommand.Parameters.AddWithValue("@Charge", ShipmentInformation.Charge)
objCommand.Parameters.AddWithValue("@CreditCardInfo", ShipmentInformation.CreditCardInfo)
objCommand.Parameters.AddWithValue("@TransactionId", ShipmentInformation.TransactionId)
'Connect to database and add record
objConnection.Open()
objCommand.ExecuteNonQuery()
objCommand.CommandText = "SELECT @@IDENTITY"
Dim intRecord As Integer = objCommand.ExecuteScalar
objConnection.Close()
'At this point the package is logged and verified.
Return intRecord
End Function
I am using 'Select @@Identity' to return row id.
July 8, 2011 at 6:39 am
That could very well be your problem.
For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.
Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.
July 8, 2011 at 7:17 am
Since you know the table, replace @@IDENTITY with SELECT IDENT_CURRENT('MailLog')
kramaswamy is right about this. if anyone else is working elsewhere in the database, @@IDENTITY could pick up an unrelated ID if the timing is bad. With the infrequent nature of your problem, sounds like the culprit.
EDIT: premature submission :w00t:
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 8, 2011 at 7:30 am
IDENT_CURRENT could have the same problem - if another record is inserted into that same table from another process, you could end up with the wrong ID value.
July 8, 2011 at 7:56 am
True, but the OP said earlier, "Only one user is inserting records at anytime." So if this holds true, that this is the only user inserting into this table, IDENT_CURRENT will work.
I'm not sure @@SCOPE_IDENTITY will work in this context either since the INSERT and the SELECT @@SCOPE_IDENTITY are being sent by two separate connections. SS may see that as different scopes. So, adocity, testing will be key.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 8, 2011 at 7:58 am
That is true. Didn't think of that. A better solution would probably be to just encase the entire thing in a stored procedure, and then have the stored procedure contain a RETURN SCOPE_IDENTITY() statement, then grab that value as the return value from the .NET application.
July 8, 2011 at 8:06 am
Since you're using SS2K5 (as indicated by the forum you posted in) I'd recommend to look into the OUTPUT clause of the INSERT statement. It's much more reliable than the other approaches presented so far and it will allow you to go to the next level: insert more than one row at once.
I also strongly recommend to use a stored procedure rather than a direct table access for several reasons:
1) security: you don't have to grant acces to the db tables to an "outside" application
2) data validation: you could add all sort of data validation (not only integrity check but also busines case related plausibility) that would otherwise completely have to be implemented at the table level (e.g. check constraints and/or trigger)
3) performance: if you're using the same concept (direct table access) with select statements, too, you might end up with a bunch of ad-hoc plans depending on the length of each parameter used in a statement. Therewith, you might not benefit from cache plan reuse.
Another issue might be "monster query" fired against the db that could bring everything to a stop.
4) maintainability: if there's a performance issue, a procedure can be tuned independed of the application
5) documentation: it's a lot easier to add comments at the db level what the statement is supposed to do, who designed it a.s.o.
July 8, 2011 at 8:11 am
you basically thinking something along the lines of
INSERT INTO TABLE1 (Columns)
OUTPUT INSERTED.ID INTO #TempTable
SELECT FROM InputRecordSet
INSERT INTO TABLE2 (Columns)
SELECT ID FROM #TempTable
?
July 8, 2011 at 10:22 am
kramaswamy (7/8/2011)
you basically thinking something along the lines ofINSERT INTO TABLE1 (Columns)
OUTPUT INSERTED.ID INTO #TempTable
SELECT FROM InputRecordSet
INSERT INTO TABLE2 (Columns)
SELECT ID FROM #TempTable
?
Yes.
July 8, 2011 at 10:44 am
LutzM (7/8/2011)
Since you're using SS2K5 (as indicated by the forum you posted in) I'd recommend to look into the OUTPUT clause of the INSERT statement. It's much more reliable than the other approaches presented so far and it will allow you to go to the next level: insert more than one row at once.I also strongly recommend to use a stored procedure rather than a direct table access for several reasons:
1) security: you don't have to grant acces to the db tables to an "outside" application
2) data validation: you could add all sort of data validation (not only integrity check but also busines case related plausibility) that would otherwise completely have to be implemented at the table level (e.g. check constraints and/or trigger)
3) performance: if you're using the same concept (direct table access) with select statements, too, you might end up with a bunch of ad-hoc plans depending on the length of each parameter used in a statement. Therewith, you might not benefit from cache plan reuse.
Another issue might be "monster query" fired against the db that could bring everything to a stop.
4) maintainability: if there's a performance issue, a procedure can be tuned independed of the application
5) documentation: it's a lot easier to add comments at the db level what the statement is supposed to do, who designed it a.s.o.
Pro advice.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 8, 2011 at 10:58 am
I am going to rewrite this weekend and use these suggestions. Thanks for the solid advise. I have never used stored procedures before or other aspects of SQL like 'output' or try/catch blocks. Since my front end is vb.net do I just pass on this new query as the command text? I am used to passing queries and getting a dataset in return and doing any manipulation on the vb.net side.
July 11, 2011 at 6:42 am
your best bet is to write a stored procedure from SQL Server, and then execute that stored procedure in VB.NET. It is *much* better to have all your SQL code exist inside SQL Server, and not in the VB.NET applications, because from a DB maintenance point of view, it is quite a bit harder to be able to do database work when you don't know whether there are any queries that exist outside of the database engine which your modifications may affect.
here's an example of how to execute a stored proc from VB.NET, and retrieve a dataset containing the records returned:
Dim objDataSet As New System.Data.DataSet
Dim objConnection As New System.Data.SqlClient.SqlConnection
Dim objCommand As New System.Data.SqlClient.SqlCommand
Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter
Try
objConnection.ConnectionString = strConnectionString
objConnection.Open()
objCommand.Connection = objConnection
objCommand.CommandType = CommandType.StoredProcedure
objCommand.CommandText = "StoredProcName"
objCommand.CommandTimeout = 0
objCommand.Parameters.Add(New SqlParameter("InputParamName", "Value"))
objAdapter.SelectCommand = objCommand
objAdapter.Fill(objDataSet)
objConnection.Close()
End Try
Catch ex As Exception
Throw
End Try
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply