February 4, 2002 at 9:30 am
Hi,
I'm working on a database migration to SQL Server. All my imports work OK, but I have a request to allow an override of Identity columns. I've reviewed previous comments on working with the Identity property but none of these seemed to fit my scenario.
I am using OLEDB/VB6. I want to execute a SP to turn off the Identity property, add one or more records, and then turn it back on (reseeding the identity as apropriate.)
I know SET IDENTITY ON/OFF does this, but my actual insert is performed via ADO (.Add/.Update). So it sounds like I would need to set the identity col in the SP, and then do my adds, but the SQL doc notes that the SET properties are returned to their original values once an SP returns? Is this true? If so, is there another way?
February 4, 2002 at 9:48 am
You can do the SET in a stored procedure. The issue will be that only one table in the database can have the identity set off at a time. So you will have to work on managing that. If this will happen often, then you will have concurrency issues. You might be able to set the transaction isolation level to serializable, but I'm not sure that will help.
Steve Jones
February 4, 2002 at 3:43 pm
Hi,
I'm missing something... Here is my SP.
CREATE PROCEDURE [Set_Fran_Id]
AS
SELECT *
FROM [Catv2].[dbo].[NewFranchise]
GO
SET IDENTITY_INSERT [Catv2].[dbo].[NewFranchise]
GO
The syntax checks but after I save it the Set statement is dropped from the procedure?
Again, all I want the SP to do is override the Identity temporarily. My imports occur 1 table at a time in pre-determined order
February 4, 2002 at 3:52 pm
The GO command is a command that applies to Query Analyzer. It signifies the end of a SQL batch. It's not executed on the server. As a result, the GO makes Query Analyzer think your stored procedure creation is at an end and it is treating the SET IDENTITY_INSERT as part of another batch. Remove the first GO and you should be fine.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 4, 2002 at 4:47 pm
Hi,
Thanks, that got me moving forward. I'm now using it in my VB code. In this instance, I'm using the VB data Environment and the Command Object point to my SP. My routines to open the file work but it throws an error on the following Code Segment:
rsAddNewRec.AddNew
For iFldIndx = 0 To .Fields.Count - 1
If gAction = "A" And iFldIndx <> 0 Or gAction = "I" Then
'error throw on next statement
rsAddNewRec.Fields(iFldIndx).Value = rs.Fields(iFldIndx).Value
End If
Next
At this point it's simply trying to stick the value of 1 into the new records identity field.
My gut feel is that while my SP is executing correctly, the following doc note is the cause of my problem.
Considerations When Using the SET Statements
If a SET statement is set in a stored procedure, the value of the SET option is restored after control is returned from the stored procedure. Therefore, a SET statement specified in dynamic SQL does not affect the statements that follow the dynamic SQL statement.
Any thoughts?
Kurt
February 5, 2002 at 11:12 am
February 5, 2002 at 12:03 pm
Hi,
Here is the SP. Again, all I want the SP to do is set the identity property off to allow a subsequent ADO .AddNew method to insert a record and take a manual id value.
CREATE PROCEDURE [Set_Fran_Id]
AS
SELECT *
FROM [Catv2].[dbo].[NewFranchise]
SET IDENTITY_INSERT [Catv2].[dbo].[NewFranchise]
GO
Again, all I want the SP to do is set the identity property off to allow a subsequent ADO .AddNew method to insert a record and take a manual id value.
My current import logic works fine and allows for both Automatic and Manual ID generation based on a value passed in the header of the current import file. The caveat is that I have to manually set the identity property for each table to match the which mode I want the import to operate in.
Kurt
February 5, 2002 at 1:44 pm
This will cause problems. Why not use a stored procedure to do the set, insert and unset all at once?
Steve Jones
February 5, 2002 at 2:12 pm
Are you kidding? That would make sense 😉
Expediancy is the primary reason I haven't gone this route as yet. My current routines are already debugged and working so I was hoping to find a simple workaround to this in order to avoid recoding. That and my proficiency with SP isn't great. Oh well, no time like the present to roll up your sleeves and jump in.
Thanks for the input. This is a great site.
kurt
February 5, 2002 at 3:06 pm
February 6, 2002 at 6:37 am
Another solution would be to make the 'set' part of your connection prior to doing the insert, or just before the insert do the set. However, I think Steve Jones soltion of doing it all in the stored procedure would be the best idea.
Good luck!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply