Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    1

    Unanswered: sp_remotesql with @@identity

    Hi all,

    I am now facing a difficulty and hope you can help me. It is about sp_remotesql.

    for some reasons (the table name is dynamic), i need to run an insert sql by sp_remotesql. Normally, it should work. However, this time, the table (my_table) contains an identity column "id" and string column "site"

    Try:
    declare @var1 varchar(100)
    select @var1 = "insert my_table(site) values ('AAA')
    exec sp_remotesql local, @var1

    my question is, how can i obtain the @@identity value after the sp_remotesql ?

    Thank you very much!

    Leslie

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524

    Re: sp_remotesql with @@identity

    No chance to obtain it, except using

    Code:
    declare @var1 varchar(100), @id numeric(10,0)
    select @var1 = "insert my_table(site) values ('AAA') select @@identity ID into tempdb..MyID 
    exec sp_remotesql local, @var1
    select @id=ID from tempdb..MyID
    drop table tempdb..MyID

    .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •