Reputation: 61
I am wondering why the following doesn't work:
INSERT INTO @Data2 (FileName,Field)
SELECT @FileName as FileName, * FROM OPENROWSET(BULK '\\serverpath\' + @FileName , SINGLE_CLOB) AS Contents
I tried also the second approach but with this one I get the error message that the variable isn't declared:
declare @path nvarchar(255) = 'SELECT @FileName as FileName, * FROM OPENROWSET(BULK ''\\serverpath\' + @FileName + ''', SINGLE_CLOB) AS Contents'
INSERT INTO @Data2 (FileName,Field)
EXEC(@path)
Can somebody help? Thanks
Upvotes: 1
Views: 1083
Reputation: 38073
You can not pass @FileName as FileName
using exec
, but you can using sp_executesql
-- but you still can not pass @FileName
as part of openrowset()
:
declare @path nvarchar(4000) = N'
select @FileName as FileName, *
from openrowset(bulk ''\\serverpath\' + @FileName + '''
, single_clob) as Contents'
insert into @Data2 (FileName,Field)
exec sp_executesql, N'@FileName nvarchar(1024))',@FileName
Or directly concatenating it like you are for the bulk
source:
declare @path nvarchar(4000) = N'
select '+quotename(@FileName,'''')+' as FileName, *
from openrowset(bulk '+quotename('\\serverpath\'+@FileName,'''')+'
, single_clob) as Contents'
insert into @Data2 (FileName,Field)
exec sp_executesql @path
reference:
Upvotes: 2
Reputation: 67341
You cannot use OPENROWSET
with a variable path. Check this:
--This works
DECLARE @Data2 TABLE(Field VARBINARY(MAX));
INSERT INTO @Data2 (Field)
SELECT * FROM OPENROWSET(BULK 'F:\Privat\StackOverflow\test.xml' , SINGLE_BLOB) AS Contents;
SELECT CAST(Field AS NVARCHAR(MAX)) FROM @Data2;
--This breaks at syntax check
DECLARE @FileName AS VARCHAR(1000)='F:\Privat\StackOverflow\test.xml';
INSERT INTO @Data2 (Field)
SELECT * FROM OPENROWSET(BULK @FileName , SINGLE_BLOB) AS Contents;
SELECT CAST(Field AS NVARCHAR(MAX)) FROM @Data2;
--This works, but needs a physically created table. Your table variable is out of scope within the EXEC
-execution
CREATE TABLE Data2(Field VARBINARY(MAX));
DECLARE @FileName AS VARCHAR(1000)='F:\Privat\StackOverflow\test.xml';
DECLARE @cmd VARCHAR(MAX)='INSERT INTO Data2(Field) SELECT * FROM OPENROWSET(BULK ''' + @FileName + ''', SINGLE_BLOB) AS Contents;'
EXEC(@cmd);
SELECT * FROM Data2;
GO
DROP TABLE Data2;
Instead of the physically created table you might use an output parameter.
Upvotes: 1