Retrieving SCOPE_IDENITY() on Insert Into, SQL Server 2005 stored proc

  • i have a somewhat complex INSERT INTO that writes a BLOB to SQL Server:

    'INSERT into dbo.ArchivedAccessDBs(AccessDB) SELECT * FROM OPENROWSET(BULK N''\\<remote machine>\DB\sskelton\' + @AccessDB + ''', SINGLE_BLOB) as Document;'

    I tried all morning to change this to a two-part INSERT INTO as in

    INSERT INTO Table (Field1, Field2) VALUES (Field1Value, Field2Value) and it won't work, perhaps because of the sub-SELECT statement. Anyhoo, I gave up on that and thought, "Just get the ID on the last insert and update the column to the required value". so I have this:

    'INSERT into dbo.ArchivedAccessDBs(AccessDB) SELECT * FROM OPENROWSET(BULK N''\\<remote machine>\DB\sskelton\' + @AccessDB + ''', SINGLE_BLOB) as Document SELECT SCOPE_IDENTITY() AS [ID];'

    Which returns the ID value in the results pane as [ID] (I guess it's a virtual column) but I can't figure out how to load that value to the variable (@ID, say) and use it in another SQL call.

    Any ideas?

    TIA

  • Declare an @ID variable and use "select @ID = scope_identity()", then use as normal.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Ok, tried this:

    DECLARE @SQL nvarchar(2000)

    DECLARE @InsertID int

    Set @SQL = 'INSERT into dbo.ArchivedAccessDBs(AccessDB) SELECT * FROM OPENROWSET(BULK N''\\<servername>\DB\sskelton\' + @AccessDB + ''', SINGLE_BLOB) as Document SELECT @InsertID = SCOPE_IDENTITY();'

    Print @SQL

    EXEC SP_EXECUTESQL @SQL

    And on execute I get "Msg 137, Level 15, State 1, Line 1

    Must declare the scalar variable "@InsertID"."

    The SQL generated is

    INSERT into dbo.ArchivedAccessDBs(AccessDB) SELECT * FROM OPENROWSET(BULK N'\\<servername>\DB\sskelton\Sampler.mdb', SINGLE_BLOB) as Document SELECT @InsertID = SCOPE_IDENTITY();

    ??

  • To do that, you need to set it up as an output parameter for sp_executeSQL to use. Books Online has the rules for that and examples of how to do it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GOT IT WORKING NOW!

    Ok. This compiles:

    ALTER PROCEDURE [dbo].[InsertDB]

    @AccessDB varchar(255)

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @SQL nvarchar(2000)

    DECLARE @InsertID INT

    Set @SQL = 'INSERT into dbo.ArchivedAccessDBs(AccessDB) SELECT * FROM OPENROWSET(BULK N''\\<servername>\DB\sskelton\' + @AccessDB + ''', SINGLE_BLOB) as Document SELECT @InsertID = SCOPE_IDENTITY();'

    Print @SQL

    EXEC SP_EXECUTESQL

    @SQL,

    @InsertID OUTPUT

    End

    and i get the following error:

    Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1

    Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

    Setting @InsertID (which I would expect to be an integer) to, say, nvarchar, it still compiles but on execute we're back to

    Msg 137, Level 15, State 1, Line 1

    Must declare the scalar variable "@InsertID".

    I'm wondering if the issue is wrapping the Select @InsertID = @@SCOPE_IDENTITY(); all in one string to sp_execute SQL is causing the problem. But if I'm following your suggestion correctly, I feel I am going in circles.

  • Change this part:

    EXEC SP_EXECUTESQL

    @SQL,

    @InsertID OUTPUT

    to:

    EXEC SP_EXECUTESQL

    @SQL,

    @params = '@InsertID OUTPUT'

    The single-quotes are the key part. Might be able to leave "@params = " out.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • What I ended up with (and this may not be optimal) is

    USE [targetdb]

    GO

    /****** Object: StoredProcedure [dbo].[InsertDB] Script Date: 10/20/2009 14:11:58 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[InsertDB]

    @AccessDB nvarchar(255)

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @SQL nvarchar(2000)

    DECLARE @InsertID INT

    DECLARE @InsertID_Str as nvarchar(50)

    Set @SQL = N'INSERT into dbo.ArchivedAccessDBs(AccessDB) SELECT * FROM OPENROWSET(BULK N''\\<servername>\DB\sskelton\' + @AccessDB + ''', SINGLE_BLOB) as Document; SELECT @InsertID = SCOPE_IDENTITY();'

    --Print @SQL

    EXECUTE SP_EXECUTESQL @SQL, N'@InsertID INT OUTPUT', @InsertID = @InsertID OUTPUT

    Set @InsertID_Str = Cast(@InsertID as nvarchar(50))

    Set @SQL = N'UPDATE dbo.ArchivedAccessDBs SET AccessDBName = ''' + @AccessDB + ''' WHERE ID=' + @InsertID_Str + ';'

    Print @SQL

    EXECUTE SP_EXECUTESQL @SQL

    End

    Which appears to work - a db is inserted and the name of the inserted db is also written to the corresponding row. None of this is really helpful, however, as the entire procedure is using OPENROWSET, which requires permissions in Access so that SQL Server can read the file. I will need to do AppendChunk in ADO instead. Oh well, a learning experience!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply