|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
'SET IDENTITY_INSERT ' + @TablesName + ' ON' doesn't work in storeI have a big problem: If I try to execute SET IDENTITY_INSERT as below, DECLARE @SqlCheckIdentity varchar (255) SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' ON' EXEC (@SqlCheckIdentity) Or DECLARE @SqlCheckIdentity varchar (255) SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' OFF' EXEC (@SqlCheckIdentity) the command is not executed....in store procedure (in query analyzer say when execute the insert statement: Cannot insert explicit value for identity column in table '@TablesName' when IDENTITY_INSERT is set to OFF). I mean in this way I cannot set the identity ON/OFF Do you know how to execute SET INSERT.... with parameters? Thank a lot The command in the EXEC acts in its own scope, so any SET commands executed
there are no longer in effect when the EXEC is over. You'll have to include the INSERT in the EXEC string. Show quote "erncelen" <ernce***@discussions.microsoft.com> wrote in message news:7FF14719-EF17-4BD2-9F63-4F50023B8CD9@microsoft.com... > Hi, > > I have a big problem: > If I try to execute SET IDENTITY_INSERT as below, > > DECLARE @SqlCheckIdentity varchar (255) > SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' ON' > EXEC (@SqlCheckIdentity) > > Or > > DECLARE @SqlCheckIdentity varchar (255) > SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' OFF' > EXEC (@SqlCheckIdentity) > > > the command is not executed....in store procedure (in query analyzer say > when execute the insert statement: Cannot insert explicit value for > identity > column in table '@TablesName' when IDENTITY_INSERT is set to OFF). > > I mean in this way I cannot set the identity ON/OFF > > Do you know how to execute SET INSERT.... with parameters? > > Thank a lot > erncelen wrote:
Show quote > Hi, Even though the SET IDENTITY_INSERT is a session-level setting, setting > > I have a big problem: > If I try to execute SET IDENTITY_INSERT as below, > > DECLARE @SqlCheckIdentity varchar (255) > SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' ON' > EXEC (@SqlCheckIdentity) > > Or > > DECLARE @SqlCheckIdentity varchar (255) > SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' OFF' > EXEC (@SqlCheckIdentity) > > > the command is not executed....in store procedure (in query analyzer > say when execute the insert statement: Cannot insert explicit value > for identity column in table '@TablesName' when IDENTITY_INSERT is > set to OFF). > > I mean in this way I cannot set the identity ON/OFF > > Do you know how to execute SET INSERT.... with parameters? > > Thank a lot it using EXEC (which runs in its own context) does not keep after exiting the function. Same as if you set it in a stored proc and tried to insert outside the proc. Can you explain why you need to do this dynamically? I need it because I have performing a BULK COPY from a series of files *.dat,
retrieving the name of tables from a catalog table. I am using this approach to avoid writing the same code for more than 400 tables. Of course the statement : 'SET IDENTITY_INSERT ' + @TablesName + ' ON' and OFF is used before and after an INSERT statement.... Beside... KALEN write: You'll have to include the INSERT in the EXEC string. -- Show quoteHow I have to include the INSERT ? Thank for your suggestion will be appreciated "erncelen" wrote: > Hi, > > I have a big problem: > If I try to execute SET IDENTITY_INSERT as below, > > DECLARE @SqlCheckIdentity varchar (255) > SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' ON' > EXEC (@SqlCheckIdentity) > > Or > > DECLARE @SqlCheckIdentity varchar (255) > SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' OFF' > EXEC (@SqlCheckIdentity) > > > the command is not executed....in store procedure (in query analyzer say > when execute the insert statement: Cannot insert explicit value for identity > column in table '@TablesName' when IDENTITY_INSERT is set to OFF). > > I mean in this way I cannot set the identity ON/OFF > > Do you know how to execute SET INSERT.... with parameters? > > Thank a lot > erncelen wrote:
> Beside... What Kalen means is that you use the approach you want, but all the > KALEN write: > > You'll have to include > the INSERT in the EXEC string. inserts have to take place in the EXEC string as well to keep them in the same context as the identity_insert statement. Something like: Exec ('Set identity_insert mytable on; insert into dbo.mytable (col1) values (5)') SQL Injection anyone?
SET @TablesName = 'ON; SELECT * FROM SYSOBJECTS; --' DECLARE @SqlCheckIdentity varchar (255) SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' ON' EXEC (@SqlCheckIdentity) Alternatively, SET @TablesName = 'ON; DELETE FROM [table]; -- replace [table] with a table name' Could be used to wipe out a table. Show quote "erncelen" <ernce***@discussions.microsoft.com> wrote in message news:F6024377-F051-4355-8263-AFDEBEBA9204@microsoft.com... >I need it because I have performing a BULK COPY from a series of files >*.dat, > retrieving the name of tables from a catalog table. > > I am using this approach to avoid writing the same code for more than 400 > tables. > > Of course the statement : > 'SET IDENTITY_INSERT ' + @TablesName + ' ON' and OFF > > is used before and after an INSERT statement.... > > Beside... > KALEN write: > > You'll have to include > the INSERT in the EXEC string. > -- > How I have to include the INSERT ? > > Thank for your suggestion will be appreciated > > > > > > > > "erncelen" wrote: > >> Hi, >> >> I have a big problem: >> If I try to execute SET IDENTITY_INSERT as below, >> >> DECLARE @SqlCheckIdentity varchar (255) >> SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' ON' >> EXEC (@SqlCheckIdentity) >> >> Or >> >> DECLARE @SqlCheckIdentity varchar (255) >> SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' OFF' >> EXEC (@SqlCheckIdentity) >> >> >> the command is not executed....in store procedure (in query analyzer say >> when execute the insert statement: Cannot insert explicit value for >> identity >> column in table '@TablesName' when IDENTITY_INSERT is set to OFF). >> >> I mean in this way I cannot set the identity ON/OFF >> >> Do you know how to execute SET INSERT.... with parameters? >> >> Thank a lot >> If you're performing a BCP or BULK INSERT as your bulk copy operator, there
is a parameter to specify that you want to keep the identity values already in the file you are copying. You do not need to use SET IDENTITY INSERT. Please see the Books Online for details on BCP and BULK INSERT. Show quote "erncelen" <ernce***@discussions.microsoft.com> wrote in message news:F6024377-F051-4355-8263-AFDEBEBA9204@microsoft.com... >I need it because I have performing a BULK COPY from a series of files >*.dat, > retrieving the name of tables from a catalog table. > > I am using this approach to avoid writing the same code for more than 400 > tables. > > Of course the statement : > 'SET IDENTITY_INSERT ' + @TablesName + ' ON' and OFF > > is used before and after an INSERT statement.... > > Beside... > KALEN write: > > You'll have to include > the INSERT in the EXEC string. > -- > How I have to include the INSERT ? > > Thank for your suggestion will be appreciated > > > > > > > > "erncelen" wrote: > >> Hi, >> >> I have a big problem: >> If I try to execute SET IDENTITY_INSERT as below, >> >> DECLARE @SqlCheckIdentity varchar (255) >> SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' ON' >> EXEC (@SqlCheckIdentity) >> >> Or >> >> DECLARE @SqlCheckIdentity varchar (255) >> SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' OFF' >> EXEC (@SqlCheckIdentity) >> >> >> the command is not executed....in store procedure (in query analyzer say >> when execute the insert statement: Cannot insert explicit value for >> identity >> column in table '@TablesName' when IDENTITY_INSERT is set to OFF). >> >> I mean in this way I cannot set the identity ON/OFF >> >> Do you know how to execute SET INSERT.... with parameters? >> >> Thank a lot >> OK,
SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' ON INSERT .....(here my statement.........+ 'SET IDENTITY_INSERT ' + @TablesName + ' OFF EXEC (@SqlCheckIdentity) Show quote "erncelen" wrote: > Hi, > > I have a big problem: > If I try to execute SET IDENTITY_INSERT as below, > > DECLARE @SqlCheckIdentity varchar (255) > SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' ON' > EXEC (@SqlCheckIdentity) > > Or > > DECLARE @SqlCheckIdentity varchar (255) > SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' OFF' > EXEC (@SqlCheckIdentity) > > > the command is not executed....in store procedure (in query analyzer say > when execute the insert statement: Cannot insert explicit value for identity > column in table '@TablesName' when IDENTITY_INSERT is set to OFF). > > I mean in this way I cannot set the identity ON/OFF > > Do you know how to execute SET INSERT.... with parameters? > > Thank a lot >  
Other interesting topics
|
|||||||||||||||||||||||
