Re: Transactions and Scope_Identity

A batch is anything that ends in a GO. When sent to SQL Server, your code
looks like this:

Command1
GO

SELECT
Scope_Identity()"))
GO

The transaction is probably shielding you here, so you are unlikely to have
an issue with wrong numbers. I would still refactor into a stored procedure
and return scope identity there. Either that or send multiple commands on
the same object and run as a batch. Of the two, the first is more
maintainable.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************************************************
Think outside of the box!
*************************************************
"Joe Fallon" <jfallon1@xxxxxxxxxxxxxxxxxx> wrote in message
news:uMS7iSJ$GHA.1172@xxxxxxxxxxxxxxxxxxxxxxx
I use code like this to Insert a row into a table which has an Identity.
Notice that a running transaction is passed in to the method as the
variable tr. The insert command runs and then next command is SELECT
Scope_Identity().

Protected Overridable Sub InsertData(ByVal tr As IDbTransaction)
DAL.ExecuteNonQuery(tr, CommandType.Text, SQL.Insert(mName, ...))

'retrieve the newly generated Identity and update the PK in the BO so
the child records will save correctly.
mIdentitykey = CDec(DAL.ExecuteScalar(tr, CommandType.Text, "SELECT
Scope_Identity()"))
End Sub

Someone suggested there might be a problem with this code because the 2
commands are not in the same batch.

Is there an issue? The code has worked reliably for a long time - but I am
slightlyt concerned there could be an iusse that I am not aware of.

Would it also work if I just passed a cmd object instead of a transaction?

If I created a 2nd command object and used 2 different cmds to run the 2
statements I would not expect them to work.
But the term "scope" never seems to include the same command object or
transaction.
"SQL Statements that are contained in the same batch, stored procedure, or
trigger are considered to be in the same scope."
OK - but is the same connection considered the same batch? Or is the same
transaction the same scope?


--
Joe Fallon






.

Relevant Pages

  • Re: Script Without The GO Command
    ... determin when a batch ends so that you can have multiple batches in the ... only command in a batch unless you wrap it in a call to sp_executesql). ... Execute the sql read on the transaction ...
    (microsoft.public.dotnet.general)
  • Re: Transactions and Scope_Identity
    ... The transaction is probably shielding you here, ... procedure and return scope identity there. ... commands on the same object and run as a batch. ... The insert command runs and then next command is SELECT ...
    (microsoft.public.dotnet.framework.adonet)
  • Transactions and Scope_Identity
    ... The insert command runs and then next command is SELECT ... commands are not in the same batch. ... Would it also work if I just passed a cmd object instead of a transaction? ... trigger are considered to be in the same scope." ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Multi-zip files -- Windows server
    ... native DOS versions). ... TO BATCH OR NOT TO BATCH ... and using a batch file to try and solve the problem... ... The IBM PC operating system command line is a most ...
    (comp.sys.cbm)
  • Re: Distribution Agent fails with error: Line 1: Incorrect syntax near ,null,
    ... You mentioned manipulating the command in the msrepl_commands table. ... transaction capable: 2 ... Last transaction timestamp: 0x000004810000069f000900000000 ... Transaction seqno: 0x00000481000006e70001 ...
    (microsoft.public.sqlserver.replication)