Problem with OPENROWSET using BULK and parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with OPENROWSET using BULK and parameter

Author

Topic

Culprit
Starting Member

2 Posts

Posted - 2007-04-17 : 04:14:05

Hi,
could someone help me with selecting content of file using OPENROWSET(BULK @param, SINGLE_CLOB)?

DECLARE @strXML xml
DECLARE @filename NVARCHAR(500)

SET @filename = 'C:\anyfile.xml'
SELECT @strXML = x
    FROM OPENROWSET(BULK @filename, SINGLE_CLOB) AS result(x)

When I create

DECLARE @strXML xml
DECLARE @filename NVARCHAR(500)
DECLARE @sql NVARCHAR(500)

SET @filename = 'C:\anyfile.xml'
SET @sql = 'SELECT @strXML = x FROM OPENROWSET(BULK N'''+@filename+''', SINGLE_CLOB) AS result(x)'
EXECUTE sp_executesql @sql

I become this error: Must declare the scalar variable "@strXML".

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-17 : 12:18:38

Why are you trying to run it as dynamic SQL, it will work the first way and there shouldn't be any need to make it dynamic and use sp_executesql. You can't just use a variable in the dynamic SQL that you declared outside of the dynamic SQL, so that is why you are getting the error.

Culprit
Starting Member

2 Posts

Posted - 2007-04-18 : 01:06:03

I need to do some things with result.
Thanks for reply, I will do without parameter with case :)

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-18 : 10:51:21

The result is in the @strXML variable after you run the code the first way, so you can do whatever you like with the result.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources