October 20, 2009 at 8:32 am
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
October 20, 2009 at 8:41 am
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
October 20, 2009 at 11:51 am
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();
??
October 20, 2009 at 11:57 am
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
October 20, 2009 at 12:20 pm
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.
October 21, 2009 at 6:39 am
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
October 21, 2009 at 8:30 am
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