locked
identifying a transaction / session RRS feed

  • Question

  • Hi,

    I have a table with columns (KeyColumn, col1, col2, col3). .Net code inserts / updates data into this table using a sp that has a signature updateTable(colName, data). i.e. the data for the columns comes in different calls the order is not defined, but a transaction will be started in .net before the first call to sp is made.
    The requirement is, I need to insert a new row in the first call to the SP and in the subsequent calls i must update the data to the columns, is this possible in MS-SQL 2005?

    if yes then how?

    Thanks and Regards,
    BRS
    Tuesday, October 7, 2008 8:22 AM

Answers

  • If you can add a new column in the table to uniquely identifye the transaction

    or a seperate table to map the transaction and GUID you can use @@SPID which will be common in a transaction

    Tuesday, October 7, 2008 11:40 AM

All replies

  • Hi BRSr:

     

    Is this your meaning?

     

    Code Snippet

    use tempdb
    go

    create table tb_test
    (
     id int identity(1,1),
     cola varchar(10),
     colb varchar(10),
     colc varchar(10)
    )
    go

    declare @id int

    insert tb_test select '','',''
    select @id = scope_identity()


    update tb_test
    set cola = 'a',colb = 'b',colc = 'c'
    where id = @id

    select * from tb_test
    go


    drop table tb_test
    go

     

     

     

    Tuesday, October 7, 2008 8:35 AM
  • Yes you can do it . You will need use the same Connection object for both the calls and commit only after the second call.

    Tuesday, October 7, 2008 8:59 AM

  • Starting and closing the transaction is happening from .net, the issue is how to identify the first call in transaction in SP

    Ex.

    in .Net

    Create Connection
    begin Transaction

    Call updateTable("col3", "data1");
    Call updateTable("col1", "data2");
    Call updateTable("col2", "data3");

    commit Transaction
    Close Connection


    What i needs to happen at database is when it receives the first call to SP

    i.e. updateTable("col3", "data1");

    it must create a new row in the table with a GUID in KeyColumn  and value "data1" in col3
    in subsequent calls

    i.e Call updateTable("col1", "data2");
    i want to update the value of the column col1 to "data2" in the earlier created row

    and in the call
    Call updateTable("col2", "data3"); update the the value of col2 to "data3"in the same earlier created row


    Tuesday, October 7, 2008 9:21 AM
  • Where is the GUID generated, DB side or .net application side?

    Tuesday, October 7, 2008 10:09 AM
  • It has to be generated in DB as the SP has only two parameters
    Tuesday, October 7, 2008 10:17 AM
  • Add a new parameter GUID to the procedure. In the first call GUID will be null.
    If GUID is null Insert a record and return the newly generated GUID .
    Pass this GUID as parameters to the next two calls which will update the record if GUID is not null.

     

    Tuesday, October 7, 2008 10:35 AM
  • I cannot do that, thats the reason the problem is posted here
    Tuesday, October 7, 2008 11:02 AM
  • If you can add a new column in the table to uniquely identifye the transaction

    or a seperate table to map the transaction and GUID you can use @@SPID which will be common in a transaction

    Tuesday, October 7, 2008 11:40 AM

  • Thanks, but i found @@SPID is same even accross different transactions

    Tuesday, October 7, 2008 11:57 AM
  • Are you using the same connection for all transactions?please try with different connections.

    Tuesday, October 7, 2008 12:05 PM
  • @@SPID  is the id of each sys process that accessess SQL engine. and it is different for a diffrent session

     

    Mohin is correct. If yo uhave @@SPID with diffrent connections it cannot be same

     

    Tuesday, October 7, 2008 2:45 PM
  • Thanks da...

    Will try to build up some logic aroud this... also i did read about some transaction views in SQL Server thinking about them also
    Friday, October 10, 2008 12:09 PM