October 17, 2015 at 12:49 am
I need to copy a lot of data to a lot of tables so I decided to generate the queries and pass them to a function. Some of the tables have IDENTITY_INSERT so I need to turn it on when I copy the data.
The code works on its own in SSMS.
DECLARE @@Test nvarchar(max) = 'INSERT INTO SKDataNPINew.dbo.[JobWIP] ( [JWip_ID], [JWip_JobNum], [JWip_ReleaseNum], [JWip_Note], [JWip_GLIDLink], [JWip_DateTimeStamp], [JWip_Date], [JWip_PostedBy], [JWip_SourceCode], [JWip_LaborCost], [JWip_LaborOH], [JWip_MatlCost], [JWip_Subcontract], [JWip_Hrs] ) SELECT OLDname.[JWip_ID], OLDname.[JWip_JobNum], OLDname.[JWip_ReleaseNum], OLDname.[JWip_Note], OLDname.[JWip_GLIDLink], OLDname.[JWip_DateTimeStamp], OLDname.[JWip_Date], OLDname.[JWip_PostedBy], OLDname.[JWip_SourceCode], OLDname.[JWip_LaborCost], OLDname.[JWip_LaborOH], OLDname.[JWip_MatlCost], OLDname.[JWip_Subcontract], OLDname.[JWip_Hrs] FROM SKDataNPI.dbo.[JobWIP] OLDname LEFT JOIN SKDataNPINew.dbo.[JobWIP] ON (OLDname.JWip_ID = SKDataNPINew.dbo.[JobWIP].JWip_ID) WHERE SKDataNPINew.dbo.[JobWIP].JWip_ID IS NULL;'
EXEC ('SET IDENTITY_INSERT SKDataNPINew.dbo.JobWIP OFF;' )
EXEC ('SET IDENTITY_INSERT SKDataNPINew.dbo.JobWIP ON;' )
EXEC(@@Test)
But the function doesn't
CREATE PROCEDURE dbo.SetID (@Tbl Nvarchar(60),@QryTxt Nvarchar(max))
AS
BEGIN
--Declare @MText nvarchar(max) = ' SET IDENTITY_INSERT ' + @Tbl + ' ON; ' + CHAR(13) + CHAR(10) + ' GO ' + CHAR(13) + CHAR(10) + @QryTxt + CHAR(13) + CHAR(10) + ' SELECT @@ROWCOUNT;'
Declare @MText nvarchar(max) = ' SET IDENTITY_INSERT ' + @Tbl + ' ON; ' + CHAR(13) + CHAR(10) + ' GO ' + CHAR(13) + CHAR(10) + @QryTxt + CHAR(13) + CHAR(10) + ' SELECT @@ROWCOUNT;'
BEGIN TRY
EXEC(' SET IDENTITY_INSERT ' + @Tbl + ' ON; ')
EXEC(@QryTxt)
END TRY
BEGIN CATCH
EXEC ( @QryTxt + ' SELECT @@ROWCOUNT;')
END CATCH
END;
GO
dbo.SetID [SKDataNPINew.dbo.JobWIP], 'INSERT INTO SKDataNPINew.dbo.[JobWIP] ( [JWip_ID], [JWip_JobNum], [JWip_ReleaseNum], [JWip_Note], [JWip_GLIDLink], [JWip_DateTimeStamp], [JWip_Date], [JWip_PostedBy], [JWip_SourceCode], [JWip_LaborCost], [JWip_LaborOH], [JWip_MatlCost], [JWip_Subcontract], [JWip_Hrs] ) SELECT TOP 10 OLDname.[JWip_ID], OLDname.[JWip_JobNum], OLDname.[JWip_ReleaseNum], OLDname.[JWip_Note], OLDname.[JWip_GLIDLink], OLDname.[JWip_DateTimeStamp], OLDname.[JWip_Date], OLDname.[JWip_PostedBy], OLDname.[JWip_SourceCode], OLDname.[JWip_LaborCost], OLDname.[JWip_LaborOH], OLDname.[JWip_MatlCost], OLDname.[JWip_Subcontract], OLDname.[JWip_Hrs] FROM SKDataNPI.dbo.[JobWIP] OLDname LEFT JOIN SKDataNPINew.dbo.[JobWIP] ON (OLDname.JWip_ID = SKDataNPINew.dbo.[JobWIP].JWip_ID) WHERE SKDataNPINew.dbo.[JobWIP].JWip_ID IS NULL;'
The function says IDENTITY_INSERT is OFF
NOTE @MText in the function. I have also tried putting both the ID INSERT and INSERT commands in ONE EXEC but that too fails.
The CATCH is designed for those tables that have no ID INSERT and it works fine.
PLEASE I am not worried about the niceties of the function it is going to run on about 200 tables at 20 different customers and will be baby sat when it is run as there are data issues that WILL cause it to fail. Part of the purpose of this is to clean up old data and migrate to a new schema.
I would just like to make it work.
Thanks in advance for any help
October 17, 2015 at 1:30 am
IDENTITY_INSERT can only be ON for one table at a time. If it is ON for a different table, already / as part of a previous command that did not turn it OFF, then you won't be able to set it on the new table [until turned OFF on previous table]. Could that be the problem?
In your SProc you are not turning IDENTITY_INSERT OFF so the next table you process will fail (as previous table will still be set to INSERT_IDENTITY ON)
Your TRY / CATCH will run for any error, not just the failure to set IDENTITY_INSERT, so personally I wouldn't program it that way and instead I would test whether the table has an IDENTITY column:
SELECT OBJECTPROPERTY(OBJECT_ID('MyTable'), 'TableHasIdentity')
and then toggle IDENTITY_INSERT only if the table has an Identity column.
October 17, 2015 at 1:01 pm
Your bigger issue is that functions are not allowed to leave side effects. So while you could design a procedure which might do what you need, a function is most likely going to complain about the Exec statement being there, never mind the fact that EXEC doesn't allow you to build the statement inside the EXEC call.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 17, 2015 at 3:43 pm
Thank you all for your help. I was able to resolve the issue! I believe it was a combination of not turning off IDENTITY coupled with a syntax error that caused the problem. The following seems to work:
CREATE PROCEDURE dbo.SetID (@Tbl Nvarchar(60),@QryTxt Nvarchar(max))
AS
BEGIN
--Declare @MText nvarchar(max) = ' SET IDENTITY_INSERT ' + @Tbl + ' ON; ' + CHAR(13) + CHAR(10) + ' GO ' + CHAR(13) + CHAR(10) + @QryTxt + CHAR(13) + CHAR(10) + ' SELECT @@ROWCOUNT;'
Declare @MText nvarchar(max) = ' SET IDENTITY_INSERT ' + @Tbl + ' ON; ' + CHAR(13) + CHAR(10) + @QryTxt + CHAR(13) + CHAR(10) + ' SELECT @@ROWCOUNT;'
BEGIN TRY
EXEC(@MText)
EXEC(' SET IDENTITY_INSERT ' + @Tbl + ' OFF; ')
END TRY
BEGIN CATCH
EXEC ( @QryTxt + ' SELECT @@ROWCOUNT;')
END CATCH
END;
GO
dbo.SetID '[SKDataNPINew].dbo.[JobWIP]', 'INSERT INTO SKDataNPINew.dbo.[JobWIP] ( [JWip_ID], [JWip_JobNum], [JWip_ReleaseNum], [JWip_Note], [JWip_GLIDLink], [JWip_DateTimeStamp], [JWip_Date], [JWip_PostedBy], [JWip_SourceCode], [JWip_LaborCost], [JWip_LaborOH], [JWip_MatlCost], [JWip_Subcontract], [JWip_Hrs] ) SELECT OLDname.[JWip_ID], OLDname.[JWip_JobNum], OLDname.[JWip_ReleaseNum], OLDname.[JWip_Note], OLDname.[JWip_GLIDLink], OLDname.[JWip_DateTimeStamp], OLDname.[JWip_Date], OLDname.[JWip_PostedBy], OLDname.[JWip_SourceCode], OLDname.[JWip_LaborCost], OLDname.[JWip_LaborOH], OLDname.[JWip_MatlCost], OLDname.[JWip_Subcontract], OLDname.[JWip_Hrs] FROM SKDataNPI.dbo.[JobWIP] OLDname LEFT JOIN SKDataNPINew.dbo.[JobWIP] ON (OLDname.JWip_ID = SKDataNPINew.dbo.[JobWIP].JWip_ID) WHERE SKDataNPINew.dbo.[JobWIP].JWip_ID IS NULL;'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply