webentwicklung-frage-antwort-db.com.de

Wie prüfen Sie, ob IDENTITY_INSERT in SQL Server auf ON oder OFF gesetzt ist?

Ich habe danach gesucht, aber Threads, in denen es so aussah, neigten dazu, Antworten von Leuten zu erhalten, die die Frage nicht verstanden haben. 

Verwenden Sie die folgende Syntax:

SET IDENTITY_INSERT Table1 ON

Wie machst du so etwas mehr:

GET IDENTITY_INSERT Table1

Ich möchte nichts mit den Daten in der Datenbank oder den Einstellungen tun, um diese Informationen zu erhalten. Vielen Dank!

60
Panzercrisis

Da SET IDENTITY_INSERT eine sitzungsempfindliche Funktion ist, wird es auf Pufferebene verwaltet, ohne irgendwo gespeichert zu werden. Dies bedeutet, dass wir den IDENTITY_INSERT-Status nicht überprüfen müssen, da wir dieses Schlüsselwort niemals in der aktuellen Sitzung verwenden.

Entschuldigung, keine Hilfe dafür.

Tolle Frage aber :)

Quelle: Hier

Update Es gibt Möglichkeiten, dies zu tun, auch auf der von mir verlinkten Site IMO.

if

(select max(id) from MyTable) < (select max(id) from inserted)

--Then you may be inserting a record normally

BEGIN
    set @I = 1 --SQL wants something to happen in the "IF" side of an IF/ELSE
END

ELSE --You definitely have IDENTITY_INSERT on.  Done as ELSE instead of the other way around so that if there is no inserted table, it will run anyway


BEGIN
.... Code that shouldn't run with IDENTITY_INSERT on
END
27
Eon

In Summe:

  • Nathans Lösung ist die schnellste:

    SELECT OBJECTPROPERTY(OBJECT_ID('MyTable'), 'TableHasIdentity');
    
    • wenn Sie einen API-Wrapper verwenden, können Sie die gesamte Prüfung auf die Suche nach Zeilen reduzieren. Zum Beispiel, wenn Sie die SqlDataReaders-Eigenschaft von HasRows von C # und ein Abfragekonstrukt wie folgt verwenden:

      SELECT CASE OBJECTPROPERTY(OBJECT_ID('MyTable'), 'TableHasIdentity')
             WHEN 1 THEN '1' ELSE NULL END
      
  • Die Lösung von Ricardo bietet mehr Flexibilität, erfordert jedoch den Identitätsnamen der Spalte

    SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('MyTable', 'U') 
                            AND name = 'MyTableIdentityColumnName';
    
  • Die Bogdan Bodanov-Lösung, die try/catch verwendet, würde ebenfalls funktionieren, aber zusätzliche Prüfungen sollten die Ausnahmebehandlung auf Fälle von IDENTITY_INSERT is already ON for table 'MyTable'. Cannot perform SET operation for table 'MyTable'; beschränken. 

16
Lorenz Lo Sauer

Wenn Sie versuchen, IDENTITY_INSERT für eine andere Tabelle zu deaktivieren, um einen Fehler zu vermeiden, wenn Sie IDENTITY_INSERT aktivieren möchten, funktionieren die folgenden möglicherweise auch für Sie. Wie andere in diesem Thread gesagt haben, ist IDENTITY_INSERT eine Sitzungseinstellung ohne direkte Sichtbarkeit. Ich habe jedoch die interessante Entdeckung gemacht, dass SET IDENTITY_INSERT OFF für keine Tabelle fehlerhaft ist, die eine Identität hat, unabhängig davon, ob IDENTITY_INSERT für diese Tabelle auf ON gesetzt ist. So kam es mir vor, dass ich SET IDENTITY_INSERT ... OFF für jede Tabelle mit einer Identität in der Datenbank aufrufen konnte. Es fühlt sich ein bisschen wie eine Brute-Force-Lösung an, aber ... Ich fand heraus, dass der folgende dynamische SQL-Block den Trick sehr gut gemacht hat. 

---- make sure IDENTITY_INSERT is OFF ----
DECLARE @cmd NVARCHAR(MAX)
SET @cmd = CAST((SELECT 'SET IDENTITY_INSERT ' + 
             QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + 
             QUOTENAME(t.name) + ' OFF' + CHAR(10)
             FROM sys.columns c 
             JOIN sys.tables t ON t.object_id = c.object_id
             WHERE c.is_identity = 1 
             ORDER BY 1 FOR XML PATH('')) AS NVARCHAR(MAX))
EXEC sp_executesql @cmd
6
Mike Stankavich

Sie können feststellen, ob identity_insert aktiviert ist und ob und für welche Tabelle dies unter Verwendung des folgenden Codes gilt.

declare @tableWithIdentity varchar(max) = '';
SET IDENTITY_INSERT ExampleTable ON

begin try
  create table #identityCheck (id int identity(1,1))
  SET IDENTITY_INSERT #identityCheck ON
  drop table #identityCheck
end try
begin catch
  declare @msg varchar(max) = error_message()
  set @tableWithIdentity= @msg;
  set @tableWithIdentity = 
  SUBSTRING(@tableWithIdentity,charindex('''',@tableWithIdentity,1)+1, 10000)

  set @tableWithIdentity = SUBSTRING(@tableWithIdentity,1, charindex('''',@tableWithIdentity,1)-1)

  print @msg;
  drop table #identityCheck
end catch

if @tableWithIdentity<>''
begin
  print ('Name of table with Identity_Insert set to ON: ' + @tableWithIdentity)
end
else
begin
  print 'No table currently has Identity Insert Set to ON'
end 
4
jmoreno

Wenn Sie etwas über die Sitzungsvariable wissen möchten ... Gute Frage, aber ich kann nicht sehen, wo diese Informationen nützlich wären. In der normalen Ausführung, um eine normale Tabellenantwort auf eine Einfügung zu überprüfen, sollte dies funktionieren!

- Wenn Sie nur wissen möchten, ob in einer Tabelle eine Identität eingefügt wird:

select is_identity
from sys.columns
where object_id = OBJECT_ID('MyTable', 'U') and name = 'column_Name'

- Oder ... Verwenden Sie dies, wenn Sie abhängig vom Ergebnis etwas ausführen möchten:

if exists (select *
from sys.columns
where object_id = OBJECT_ID('MyTable', 'U') and is_identity = 1)
... your code considering identity insert
else
... code that should not run with identity insert

Habe Spaß!

3
Ricardo

Sehr gute Frage Ich habe das gleiche Problem. Möglicherweise können Sie versuchen, IDENTITY_INSERT mit TRY/CATCH zurückzusetzen. Sie machen beispielsweise den Job, wissen jedoch nicht, ob der Job abgeschlossen ist und IDENTITY_INSERT auf OFF gesetzt ist.

Warum versuchst du es nicht:

BEGIN TRY 
...
END TRY 
BEGIN CATCH
SET IDENTITY_INSERT table OFF;
END CATCH;

Ich bin auch nicht sicher, ob dies korrekt funktioniert, aber ich sehe, dass das Hinzufügen nur SET IDENTITY_INSERT ... OFF keinen Fehler zurückgegeben hat. So können Sie am Ende nur SET IDENTITY_INSERT ... OFF setzen.

1
Bogdan Bogdanov

sie können auch die ObjectProperty-Methode verwenden, um festzustellen, ob eine Tabelle eine Identität hat:

DECLARE @MyTableName nvarchar(200)
SET @MyTableName = 'TestTable'
SELECT CASE OBJECTPROPERTY(OBJECT_ID(@MyTableName), 'TableHasIdentity') 
WHEN 1 THEN 'has identity' 
ELSE 'no identity columns' 
END as HasIdentity
0