MS SQLServer: SET IDENTITY_INSERT ON / OFF

[Robbemans]

SET IDENTITY_INSERT On kun je maar op 1 tabel doen, als je het op een andere tabel doet wordt er een foutmelding gegenereerd:

code:

Server: Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'MyDB.dbo.MyTable'. Cannot perform SET operation for table 'MyOtherTable'.

Mijn vraag is: hoe vind ik die table met een query? Een select in 1 van de system-tables?
Uiteraard heb ik gezocht op google, maar tot nu toe kom ik niet verder dan het voornoemde: het kan maar op 1 tabel.

[ Voor 3% gewijzigd door Robbemans op 08-12-2005 14:30 ]

[P_de_B]

De foutmelding is dat het al aan staat, natuurlijk kun je het dan niet nog een keer aanzetten. Ik snap niet precies wat je vraag is?

[Robbemans]

Mijn vraag is: SQL Server geeft deze melding. Logischerwijs weet de server welke tabel het is die de IDENTITY_INSERT aan heeft staan. Hoe vraag ik dat zelf in een query op?

[ Voor 8% gewijzigd door Robbemans op 08-12-2005 14:30 ]

[whoami]

Als je de foutmelding leest, dan weet je toch voor welke table die optie aan staat ?
(Zie jouw foutmelding, en zie ook dit:

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

Hmm, ok.
ff niet goed gelezen; je wilt opvragen welke table dit is...

[ Voor 8% gewijzigd door whoami op 08-12-2005 14:46 ]

[Robbemans]

Klopt. Dat heb je dus een foutmelding.

Wat ik wil isniet de foutmelding krijgen, maar op 1 of andere manier achterhalen welke tabel (eventueel) een IDENTITY_INSERT aan heeft staan.

Ik wil dit bijvoorbeeld vanuit een user-defined-function of een stored procedure kunnen achterhalen. Dat heb ik niet zo veel aan een foutmelding...

Hmm, ok.
ff niet goed gelezen; je wilt opvragen welke table dit is...

Hmmz, ik was iets te snel . Het is dus inderdaad niet direct een simpele vraag...

[ Voor 23% gewijzigd door Robbemans op 08-12-2005 14:49 ]

[kenneth]

code:

if ((syscolumns.status && 0x80) == 0x80)
    WeHebbenEenIdentity();

syscolumns.id kan je joinen op sysobjects.id als je de tabelnaam wil hebben.

Argh, ik snap je vraag ook al niet

[ Voor 15% gewijzigd door kenneth op 08-12-2005 15:08 ]

[P_de_B]

Ik heb even gegoogled maar volgens mij is het niet mogelijk dit op te vragen.

Ik vraag me wel af waarom je dit nodig hebt. Deze instelling is geldt alleen voor de huidige batch, zodra je dus bijv. de connectie verbreekt staat de instelling weer 'OFF". Als je er dus voor zorgt dat je altijd het volgende doet heb je denk ik geen problemen:

SET IDENTITY_INSERT ON
doe je statement
SET IDENTITY INSERT OFF

[Robbemans]

P_de_B schreef op donderdag 08 december 2005 @ 15:18:
Ik vraag me wel af waarom je dit nodig hebt. Deze instelling is geldt alleen voor de huidige batch, zodra je dus bijv. de connectie verbreekt staat de instelling weer 'OFF". Als je er dus voor zorgt dat je altijd het volgende doet heb je denk ik geen problemen:

SET IDENTITY_INSERT ON
doe je statement
SET IDENTITY INSERT OFF

Het is me bekend de instelling voor de huidige connectie geldt. Hier ligt ook het probleem:

Stel, het stukje "doe je statement" genereert een fout, dan wordt SET IDENTITY INSERT OFF niet uitgevoerd, met alle gevolgen van dien. Het gebeurt bijvoorbeeld in een stored proc, dan is het een probleem... Dit is de reden dat ik dit echt wil weten.

[P_de_B]

Volgens mij is het niet erg als er een fout is hoor? Ik heb het volgende even gedaan:

SQL:

set identity_insert gebruikers on
insert into gebruikers (niet_bestaand_veld) values ('a')
set identity_insert gebruikers off

set identity_insert groepen on
insert into groepen (grid, grnaam) values (5,'testing')
set identity_insert groepen off

Ik krijg een foutmelding over de niet bestaande kolom, maar ik kan direct daarna wel het statement op de groepen tabel uitvoeren.

[ Voor 4% gewijzigd door P_de_B op 08-12-2005 16:09 ]

[Robbemans]

Het probleem ligt iets anders. Ik snap dat dit werkt, maar ik heb echt een IDENTITY_INSERT op mijn eigen tabel nodig op het specifieke moment, waardoor ikzelf (in het geval van een foutsituatie) de SET IDENTITY_INSERT OFF moet kunnen doen, om vervolgens mijn eigen IDENTITY_INSERT te doen.

[P_de_B]

Leg de situatie dan eens uit?

[Robbemans]

De code komt overeen met wat je al aangaf.

Ik heb een functie of stored procedure waarin (onder andere) het volgende gebeurt:

SQL:

set identity_insert gebruikers on

... do something ...

set identity_insert gebruikers off

In het stukje 'do something' heb ik een critical error, waardoor 'set identity_insert gebruikers off' niet wordt uitgevoerd. Nu kan ik dus niet meer bepalen welke tabel in IDENTITY_INSERT mode staat voor de huidige connectie.

Wat ik in het begin van deze procedure dus wil doen in het geval van een exception is:

SQL:

declare @Err int

set identity_insert gebruikers on
set @err = @@ERROR
if @err <> 0
begin
  set identity_insert ??? off
  set identity_insert gebruikers on
end
... do something ...

set identity_insert gebruikers off

of in de ideale situatie:

SQL:

declare 
  @Err int
  @tabname varchar(256)

set @tabname =  (select identity_insert_table from sysobjects) -- pseudo code
if @tabname is not null
  set identity_insert @tabname off

set identity_insert gebruikers on

... do something ...

set identity_insert gebruikers off

[P_de_B]

Dat laatste gaat sowieso niet werken, omdat dat alleen met dynamic sql mogelijk is, en dat draait weer in zijn eigen scope. Dus @tablename voor de SET functie werkt niet.

Maar ik denk nog steeds dat je het niet nodig hebt.

SQL:

CREATE proc test

as

set identity_insert gebruikers on
insert into gebruikers (gid) values (9999999999999999)
set identity_insert gebruikers off

set identity_insert groepen on
insert into groepen (grid, grnaam) values (5,'testing')
set identity_insert groepen off

Als ik nu deze proc uitvoer:

SQL:

exec test

Krijg ik onderstaand resultaat:

Server: Msg 8115, Level 16, State 2, Procedure test, Line 6
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.

(1 row(s) affected)

De foutmelding geeft aan dat het eerste statement niet goed gaat, maar de 1 row affected melding geeft aan dat het 2e statement gewoon uitgevoerd wordt.

[Robbemans]

En onderstaande?

SQL:

set identity_insert groepen on
exec test
set identity_insert groepen off

Dat faalt dus...

Het probleem ligt op een mogelijk hoger niveau. Ik geef toe dat dit geen nette code is, maar soms het je dingen niet in de hand...

[ Voor 34% gewijzigd door Robbemans op 09-12-2005 09:06 ]

[cannibal]

Afhankelijk van het type (zwaarte) van de Error in sql, gaat sql door met het uitvoeren van statements. Bij bepaalde errors breekt het zaakje wel volledig af.

Niet voor niets zijn ze in SQL2005 bezig met try / catch oplossing in sql, zodat je bepaalde zaken kunt afvangen.

Maar ik vraag me wel af of het niet af te vangen is dat er zo'n grote fout optreed, dit zou toch eigenlijk niet moeten gebeuren.

[P_de_B]

SQL:

ALTER proc test

as

insert into gebruikers (gid) values (9999999999999999)

set identity_insert gebruikers off

set identity_insert groepen on
insert into groepen (grid, grnaam) values (5,'testing')
set identity_insert groepen off

SQL:

set identity_insert gebruikers on
exec test

Dit gaat goed.

---

SQL:

ALTER proc test

as

insert into gebruikers (gid) values (9999999999999999)

SQL:

set identity_insert gebruikers on
exec test
set identity_insert gebruikers off

set identity_insert groepen on
insert into groepen (grid, grnaam) values (5,'testing')
set identity_insert groepen off

En dit gaat ook goed. Of ik snap je niet, of je ziet een probleem dat er niet is

Misschien nog wat duidelijker uitleggen?

[Robbemans]

P_de_B: je past op regel 7 de magische code toe om de IDENTITY_INSERT uit te zetten. Hoe weet je welke tabel de IDENTITY_INSERT heeft? Je zegt 'gebruikers', omdat je dit weet. Ik weet het echter niet...

Dat is het probleem. Ik wil graag die SET IDENTITY_INSERT OFF doen, maar op welke tabel, als je niet weet welke tabel hem heeft?

Het kan natuurlijk heel smerig met een loopje door alle tabellen, maar ik vermoed dat het toch netter moet kunnen...

[P_de_B]

Natuurlijk weet ik welke tabel het is, in de regel erboven gebruik ik de tabel nog. Heb je alles dynamisch opgebouwd of zo?

Ik ben er na veel googlen wel achter dat er volgens mij geen mogelijkheid is om dit uit te vragen, dus je zult iets anders moeten verzinnen. Weet je echt zeker dat je het niet kunt oplossen door een iets andere workflow?

Sowieso is dit niet echt een vel gebruikte optie, meestal heb je dit alleen nodig bij het bulkladen van externe files oid. Toch niet in de normale gang van zaken?

[Robbemans]

Als ik het anders kon oplossen (zo 1, 2, 3) dan had ik dit wel gedaan. Het is echt een crime om de (inderdaad dynamische) sql queries te beheren op deze manier. Ik ben gaan grasduinen en heb inmiddels het volgende gebakken, maar ik snap niet wat het probleem direct is. Eerst de code:

SQL:

SET IDENTITY_INSERT gebruikers ON

SET NOCOUNT ON
DECLARE @SQLStatement nvarchar(4000)

SET @SQLStatement = N'IF OBJECT_ID(N''tempdb..##DBCCResults'
   + N''') IS NOT NULL
   DROP TABLE ##DBCCResults'

EXEC(@SQLStatement)

SET @SQLStatement = N'CREATE TABLE ##DBCCResults' +
      N'(DBCCOutputLine varchar(1000))'
EXEC(@SQLStatement)

SET @SQLStatement = N'INSERT INTO ##DBCCResults' +
  N' EXEC master..xp_cmdshell ''OSQL /Q"DBCC TRACEON(3406) SET IDENTITY_INSERT groepen ON" /E /S' + @@SERVERNAME + N''''
EXEC(@SQLStatement)
select * from ##DBCCResults

De dynamische query wordt uitgevoerd, maar het resultaat is leeg, terwijl ik verwachtte de foutmelding in ##DBCCResults te krijgen. Misschien zie ik het verkeerd, maar dit zou zo toch moeten kunnen?

[ Voor 24% gewijzigd door Robbemans op 09-12-2005 09:39 ]

[Robbemans]

Het lijkt er echt op dat dit niet gaat lukken. Het commando om de IDENTITY_INSERT uit te zetten moet in dezelfde scope worden uitgevoerd als het commando om het aan te zetten. Het kan dus niet met dynamisch SQL.

Mocht iemand een lumineus idee hebben en dit werken krijgen, dan hoor ik het graag. Tot die tijd ben ik terug naar de tekentafel dan maar.

[P_de_B]

Robbemans schreef op vrijdag 09 december 2005 @ 09:45:
Het lijkt er echt op dat dit niet gaat lukken. Het commando om de IDENTITY_INSERT uit te zetten moet in dezelfde scope worden uitgevoerd als het commando om het aan te zetten. Het kan dus niet met dynamisch SQL.

P_de_B in "MS SQLServer: SET IDENTITY_INSERT ON / O..."

Mocht iemand een lumineus idee hebben en dit werken krijgen, dan hoor ik het graag. Tot die tijd ben ik terug naar de tekentafel dan maar.

Ik heb geen idee om dit werkend te krijgen, maar ik denk echt dat de query wel iets anders opgebouwd zou kunnen worden, als je daar een frisse blik voor nodig hebt zeg je het maar. Soms kan het erg helpen om eens met andere ogen naar ingewikkelde code te kijken

[Robbemans]

P_de_B schreef op vrijdag 09 december 2005 @ 09:53:
Ik heb geen idee om dit werkend te krijgen, maar ik denk echt dat de query wel iets anders opgebouwd zou kunnen worden, als je daar een frisse blik voor nodig hebt zeg je het maar. Soms kan het erg helpen om eens met andere ogen naar ingewikkelde code te kijken

Hmmm... De ene hand wast de andere

In ieder geval bedankt voor het meedenken. Van mij mag dit topic dicht.

[Robbemans]

Ik ga er nu vanuit dat ik de enige ben die de SET IDENTITY_INSERT doet. In dat geval is dit de proc geworden om records te kopieren:

SQL:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_ResetRecord]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[P_ResetRecord]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.P_ResetRecord
  @ID int,
  @Table varchar(40),
  @ArchiveTable varchar(40)
AS
BEGIN 
  DECLARE
    @Result int,
    @FieldName1 varchar(40), 
    @KeyField varchar(40),
    @FieldList varchar(8000),
    @SQL varchar(8000),
    @Err int,
    @TranOwner int,
    @TranName varchar(32)

  SET NOCOUNT ON 

  --Create a unique transaction / savepoint name
  SET @TranName = 'REST' + CONVERT(VARCHAR(3), @@NESTLEVEL)

  --For now all went OK
  SET @Err = 0

  --Are we the one who starts the outermost transaction?
  IF @@TRANCOUNT = 0
    SET @TRANOWNER = 1
  ELSE
    SET @TRANOWNER = 0
  --We begin a transaction or set a savepoint so we can reverse if needed
  IF @TRANOWNER = 1
    BEGIN TRANSACTION
  ELSE
    SAVE TRANSACTION @TranName

  SET NOCOUNT ON 

  --For now all went OK
  SET @Err = 0
  set @SQL = ''
  set @FieldList = ''
  ----------------------------------------------
  --              START THE CODE              --
  ----------------------------------------------

  -- Make sure there is an IDENTITY column present
  set @KeyField = (select name from syscolumns where id = object_id(@Table) and autoval is not null)

  if @Result = 0 
  begin
    set @Err = 50001
    goto OnErrorLabel
  end

  -- Compose complete SQL script to be executed. No checking on NOT NULL fields, this will be handled by the server anyway
  declare FieldCursor cursor for
  select
    s1.name F1
  from
    syscolumns s1
  join syscolumns s2 on
    s1.name = s2.name and s1.xusertype = s2.xusertype
  where
    s1.id = object_id(@Table)
   and
    s2.id = object_id(@ArchiveTable)
  order by
    s1.colid

  open FieldCursor
  fetch next from FieldCursor into @FieldName1
  while @@FETCH_STATUS = 0
  begin
    set @FieldList = @FieldList + '  ' + @FieldName1
    fetch next from FieldCursor into @FieldName1
    if @@FETCH_STATUS = 0 
      set @FieldList = @FieldList + ','
    set @FieldList = @FieldList + char(13)
    
  end
  deallocate FieldCursor

  set @SQL = 'set identity_insert ' + @Table + ' on;' + char(13) + 'insert ' + @Table + ' (' + char(13)
  set @SQL = @SQL + @FieldList + ')' + char(13)
  set @SQL = @SQL + 'select' + char(13) + @FieldList
  set @SQL = @SQL + 'from ' + @ArchiveTable + char(13)
  set @SQL = @SQL + 'where ' + @KeyField + ' = ' + cast(@ID as varchar(10)) + char(13)
  set @SQL = @SQL + 'set identity_insert ' + @Table + ' off' + char(13)

  execute (@SQL)

  ----------------------------------------------
  --              END NORMAL PROC CODE        --
  ----------------------------------------------
  SET @Err = @@ERROR

  IF @Err <> 0
    GOTO OnErrorLabel
  --Check for a error
  IF @TranOwner = 1 
    COMMIT TRANSACTION 

  OnNormalLabel:
    RETURN @Err

  OnErrorLabel:
    IF @TRANOWNER = 1
      ROLLBACK TRANSACTION
    ELSE
      ROLLBACK TRANSACTION @TranName

    GOTO OnNormalLabel
END
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Questions and comments zijn welkom

[ Voor 17% gewijzigd door Robbemans op 09-12-2005 13:30 ]