none
Was ist mit "scope" gemeint , SELECT SCOPE_IDENTITY() AS newId

    Frage

  • Hallo,

    ich komme von MySQL. Hier gibt es ja das "last_insert_id()" um die letzte ID zu bekommen das bei einem Insert gemacht wurde, wenn es ein Auto_Increment Feld ist.

    Ich habe die ganze Datenbank auf SQL Server umgestellt. Die letzte ID wird nun versucht mit

    SELECT SCOPE_IDENTITY() AS newId

    auszulesen. Leider sieht es aber so aus als ob nur immer NULL zurückkommt. Es wird also zuerst ein INSERT in eine Tabelle text gemacht. Die TextID ist als IDentity definiert. Diese neue textDI soll nun für den nächsten Insert verwendet werden.

    Kann es sein das hier SELECT SCOPE_IDENTITY() nicht greift?

    vielen Dank

    Gruss

    hawk

    Dienstag, 15. Mai 2012 12:38

Antworten

  • Hallo zusammen,

    in Bezug auf die Multifunktionalität haben die Vorredner ja schon erklärt, daß es nicht so einfach ist. Ich habe das so gelöst als das ich weiß, daß ein simples SELECT Attributn, Attributn, ... FROM table ÜBERALL funktioniert.

    Dadurch habe ich "indirekt" eine Multiplattform-Lösung entwickeln können, indem ich alle SQL-Statements (SELECT, FUNCTIONS, PROCEDURES) in eine Tabelle tbl_sys_SQL implementiere.

    Meine "Klasse" zieht nun also aus der aktuellen Verbindung mittels eines simplen Statements:

    SELECT SQLText FROM tbl_sys_SQL WHERE SQLFlag = 'MeinEindeutigesSQLFlag' das benötigte SQL Statement und verarbeitet es weiter. Der Vorteil liegt darin, daß ich für jede Plattform (DB/2, ORACLE, mySQL / SQL Server, Access) unterschiedliche SQL-Statements implementieren kann. Somit ist meine Lösung unabhängig von der darunter liegenden Plattform.

    In Bezug auf den Ursprung [SCOPE_IDENTITY()] wurde ja schon alles gesagt. Insbesondere ist es halt wichtig, daß SCOPE_IDENTITY immer nur für die aktuelle Session gilt. Weiterhin sollte SCOPE_IDENTITY() auch unmittelbar nach dem INSERT abgerufen werden!


    Uwe Ricken

    MCM SQL Server 2008
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    • Als Antwort markiert hawk-master Freitag, 18. Mai 2012 06:21
    Mittwoch, 16. Mai 2012 11:35
  • Hi,

    schau mal hier, dort findest Du eine Erklärung, warum es bei Verwendung von SQL Statements mit Parametern in .NET zu einem solchen Verhalten kommt.

      http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/080280b7-9c2d-4ee5-afe0-5c07d2affc7c#e0acad8b-0dff-4369-94ec-24ffd823fd09

    Weitere Hinweise findest Du hier:

      http://stackoverflow.com/questions/3913115/scope-identity-in-c-sharp-range

    SELECT @@IDENTITY solltest Du nicht verwenden, das kann zu falschen Ergebnissen führen (bspw. wenn durch den INSERT noch ein Trigger weitere INSERT Statements ausführt, ...)

    Verschiedene SQL Server Versionen liefern allerdings auch bei SCOPE_IDENTITY() falsche Werte. Für SQL Server 2008 R2 SP1 gibt es einen Fix im CU 5. Falls dich das betrifft, solltest Du überlegen, ob Du das installieren kannst.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

    • Als Antwort markiert hawk-master Freitag, 18. Mai 2012 06:21
    Mittwoch, 16. Mai 2012 13:24
  • Hi,

    SCOPE_IDENTITY() funktioniert nur, wenn Du es innerhalb derselben Sitzung/Verbindung direkt nach dem INSERT aufrufst. Zu einem späteren Zeitpunkt bzw. in einer anderen Sitzung/Verbindung liefert der Aufruf NULL.

    Ich würde es so machen:

    Dim SqlCommand As String
        SqlCommand = "INSERT INTO Tabelle( ... ) VALUES( ... );SELECT SCOPE_IDENTITY();"

        <Variable> = <SqlCommand>.ExecuteScalar()

    Falls Du was anderes benötigst, mit was genau arbeitest Du? Innerhalb einer Stored Procedure? ...?


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community


    Dienstag, 15. Mai 2012 13:24
  • Hallo Hans,

    ein universelles Statement, das in allen oder auch nur in MySql+MS SQL funktioniert, wirst Du nur in den seltesten Fällen hinbekommen. Du müsstest dazu alles auf ein Subset an Funktionalitäten einschränken, das überall gleich ist und das wird ein kleines Subset.

    P.S.: Statt mit den Identity-Funktionen zum ermitteln des letzten Wertes zu arbeiten, könntest Du auch die Output Klausel (ab SQL Server 2005) verwenden, das ist sicherer und funktioniert auch dann, wenn Du mal mehr als nur einen Datensatz einfügst:

    CREATE TABLE #test (id int NOT NULL IDENTITY(1, 1) PRIMARY KEY, Feld varchar(20));
    
    INSERT INTO #test (Feld) 
    OUTPUT inserted.id
    VALUES ('Hello World');
    
    GO
    DROP TABLE #test


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing


    Mittwoch, 16. Mai 2012 06:35

Alle Antworten

  • Hi,

    SCOPE_IDENTITY() funktioniert nur, wenn Du es innerhalb derselben Sitzung/Verbindung direkt nach dem INSERT aufrufst. Zu einem späteren Zeitpunkt bzw. in einer anderen Sitzung/Verbindung liefert der Aufruf NULL.

    Ich würde es so machen:

    Dim SqlCommand As String
        SqlCommand = "INSERT INTO Tabelle( ... ) VALUES( ... );SELECT SCOPE_IDENTITY();"

        <Variable> = <SqlCommand>.ExecuteScalar()

    Falls Du was anderes benötigst, mit was genau arbeitest Du? Innerhalb einer Stored Procedure? ...?


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community


    Dienstag, 15. Mai 2012 13:24
  • Hallo hawk,
    schau Dir auch mal die Online-Doku dazu an:
    http://msdn.microsoft.com/de-de/library/ms190315.aspx

    Unter Hinweise findest Du die verschiedenen Varianten. Für Dich dürfte dann IDENT_CURRENT greifen.

    Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP
    www.insidesql.org/blogs/cmu

    Dienstag, 15. Mai 2012 13:41
  • Hallo zusammen,

    vielen Dank für die klasse Hilfe.

    Das Problem ist: Ich arbeite an einer Webanwendung mit PHP und PDO als Verbindung. Hier habe ich keine Probleme. Das Problem tritt an einer identischen Windows Anwendung auf die ein Kollege in C# programmiert. Daher kann ich nicht so viel Details nennen. Also die Verbindung zur Datenbank wird mit ODBC gemacht. Ich vermute das schon direkt nach dem ersten INSERT gleich das SELECT SCOPE_IDENTITY() gemacht wird, dann hätte es doch eigentlicg den gleichen "SCope" oder?

    Ich habe nun mal ein wenig was ausprobiert. Also es funktioniert wenn ich anstatt SELECT SCOPE_IDENTITY(); das ändere in SELECT @@IDENTIY.

    Nur; so wie ich gelesen habe ist SELECT @@IDENTIY etwas unsicher weil da ja irgend eine letzte Idee aus irgend einem letzten INSERT genommen wird oder?
    Ich habe da etwas bedenken. Wenn jetzt z.b. meine Webanwendung zu einem gleichen Zeitpunkt einen Insert macht, könnte es ja vielleicht sein das die Windows Anwendung einen falschen IDENTITY Wert zurückbekommt?

    Ich kann natürlich meinem Kollegen mal sagen, er soll es mal mit
    SqlCommand = "INSERT INTO Tabelle( ... ) VALUES( ... );SELECT SCOPE_IDENTITY();"
    versuchen. Wir wollen aber immer zweigleisig fahren, also auch mit MySQL. Das heisst wir müssten unterschiedliche SQL Abfragen aufbauen.

    viele Grüße

    Hans

    Mittwoch, 16. Mai 2012 06:17
  • Hallo Hans,

    ein universelles Statement, das in allen oder auch nur in MySql+MS SQL funktioniert, wirst Du nur in den seltesten Fällen hinbekommen. Du müsstest dazu alles auf ein Subset an Funktionalitäten einschränken, das überall gleich ist und das wird ein kleines Subset.

    P.S.: Statt mit den Identity-Funktionen zum ermitteln des letzten Wertes zu arbeiten, könntest Du auch die Output Klausel (ab SQL Server 2005) verwenden, das ist sicherer und funktioniert auch dann, wenn Du mal mehr als nur einen Datensatz einfügst:

    CREATE TABLE #test (id int NOT NULL IDENTITY(1, 1) PRIMARY KEY, Feld varchar(20));
    
    INSERT INTO #test (Feld) 
    OUTPUT inserted.id
    VALUES ('Hello World');
    
    GO
    DROP TABLE #test


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing


    Mittwoch, 16. Mai 2012 06:35
  • Guten Morgen,

    leider werden ihr es nicht vermeiden können, verschiedene SQL Abfragen bauen zu müssen, da sich zwar sowohl TSQL (MS SQL) als auch MySQL an die ANSI Standards halten, allerdings jeder Datenbank mit Ihren eigenarten. Gleiche Probleme treten z.B. auf, wenn auf Oracle und DB2 zugegriffen werden soll.

    Grüße

    Oliver

    Mittwoch, 16. Mai 2012 06:38
  • Hallo zusammen,

    in Bezug auf die Multifunktionalität haben die Vorredner ja schon erklärt, daß es nicht so einfach ist. Ich habe das so gelöst als das ich weiß, daß ein simples SELECT Attributn, Attributn, ... FROM table ÜBERALL funktioniert.

    Dadurch habe ich "indirekt" eine Multiplattform-Lösung entwickeln können, indem ich alle SQL-Statements (SELECT, FUNCTIONS, PROCEDURES) in eine Tabelle tbl_sys_SQL implementiere.

    Meine "Klasse" zieht nun also aus der aktuellen Verbindung mittels eines simplen Statements:

    SELECT SQLText FROM tbl_sys_SQL WHERE SQLFlag = 'MeinEindeutigesSQLFlag' das benötigte SQL Statement und verarbeitet es weiter. Der Vorteil liegt darin, daß ich für jede Plattform (DB/2, ORACLE, mySQL / SQL Server, Access) unterschiedliche SQL-Statements implementieren kann. Somit ist meine Lösung unabhängig von der darunter liegenden Plattform.

    In Bezug auf den Ursprung [SCOPE_IDENTITY()] wurde ja schon alles gesagt. Insbesondere ist es halt wichtig, daß SCOPE_IDENTITY immer nur für die aktuelle Session gilt. Weiterhin sollte SCOPE_IDENTITY() auch unmittelbar nach dem INSERT abgerufen werden!


    Uwe Ricken

    MCM SQL Server 2008
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    • Als Antwort markiert hawk-master Freitag, 18. Mai 2012 06:21
    Mittwoch, 16. Mai 2012 11:35
  • Hi,

    schau mal hier, dort findest Du eine Erklärung, warum es bei Verwendung von SQL Statements mit Parametern in .NET zu einem solchen Verhalten kommt.

      http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/080280b7-9c2d-4ee5-afe0-5c07d2affc7c#e0acad8b-0dff-4369-94ec-24ffd823fd09

    Weitere Hinweise findest Du hier:

      http://stackoverflow.com/questions/3913115/scope-identity-in-c-sharp-range

    SELECT @@IDENTITY solltest Du nicht verwenden, das kann zu falschen Ergebnissen führen (bspw. wenn durch den INSERT noch ein Trigger weitere INSERT Statements ausführt, ...)

    Verschiedene SQL Server Versionen liefern allerdings auch bei SCOPE_IDENTITY() falsche Werte. Für SQL Server 2008 R2 SP1 gibt es einen Fix im CU 5. Falls dich das betrifft, solltest Du überlegen, ob Du das installieren kannst.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

    • Als Antwort markiert hawk-master Freitag, 18. Mai 2012 06:21
    Mittwoch, 16. Mai 2012 13:24
  • Hallo Hans,

    ist die Thematik geklärt?

    Wenn ja, bitte auch die Antworten markieren, die Dir geholfen haben, so dass auch andere davon profitieren können.

    Gruss,
    Raul


    Raul Talmaciu, MICROSOFT 
    Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip „IT-Pros helfen IT-Pros“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.

    Donnerstag, 17. Mai 2012 13:37
  • Hallo Raul, Hallo zusammen,

    nochmals ganz herzlichen Dank für die tolle Hilfe hier. Wirklich ein klasse, aktives Forum hier.
    Ich werde nächste Woche mal mit meinem Kollegen zusammen, der das in C# macht die Varianten ausprobieren.

    vielen Dank nochmals

    Gruss
    Hans

    Freitag, 18. Mai 2012 06:23
  • Hallo zusammen,
    darf ich bitte diesen alten Thread nochmals aufmachen?

    Ich hatte heute Gelegenheit mit meinem Kollegen die Sache mit dem Scope_Identity zu testen. Wir haben es jetzt hinbekommen. Das Hauptproblem war glaube ich weil intern in der C# Anwendung "ExecuteNonQuery" anstatt "ExcuteScalar" verwendet wurde.
    (sorry  ich bin kein C# Programmierer)
    Wir haben es jetzt im Prinzip so gemacht wie hier empfohlen:
    SqlCommand = "INSERT INTO Tabelle( ... ) VALUES( ... );SELECT SCOPE_IDENTITY();"

        <Variable> = <SqlCommand>.ExecuteScalar()

    Ein Problem ist jetzt aber bei Umstellung auf MYSQL. Dann muss man unbedingt den MySQL ODBC Treiber auf "Allow multiple statements" stellen.

    Daher nochmals eine Frage zu @@Identity

    Wenn keine Trigger oder andere Dinge verwendet werden, wäre dann Select @@Identity nicht kritisch?

    vielen Dank
    Gruss
    Hans
    Donnerstag, 2. August 2012 17:05
  • Dann muss man unbedingt den MySQL ODBC Treiber auf "Allow multiple statements" stellen.
    ... wäre dann Select @@Identity nicht kritisch?

    Hallo Hans,

    das "Problem" ist hier weniger, wie Du die letzte Identity abfragst, als mehr dass das Insert + Selektion der letzten Identity in einem Batch = mehrere Abfrage (multiple statements) erfogt.

    Man könnte die 2 Statements separat nach einander ausführen, das umgeht das Problem, kann aber wiederum das Problem erzeugen, das ein anderer Client zwischen den beiden Statements Daten einfügt und somit die zweite Abfrage ein falsches Ergebnis liefert.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Donnerstag, 2. August 2012 18:23