Openrowset with variables - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Openrowset with variables

Author

Topic

jovball
Starting Member

2 Posts

Posted - 2006-06-21 : 16:05:08

I am attempting to use Openrowset to query an Excel file. I am doing this in Query Analyzer. If I hard code the values, it works fine. If I attempt to use variables for the file name or the sheet name it fails.

I don't see why it should fail since it is a string value. BOL says that the datasource and query argument type is a "string constant" so perhaps the variable is the issue.

Any help would be appreciated.

Joel Reinford
Data Management Solutions LLC

SQL:
DECLARE
@FullFileName varchar(500)
,@SheetName varchar(50)
,@SelectSql varchar(2000)

SET @FullFileName = 'C:\Temp\MyList.xls'
SET @SheetName = 'MailingList'
SET @SelectSql = 'SELECT * FROM [' + @SheetName + '$]'

--this works
SELECT *
FROM OPENROWSET
(
'Microsoft.Jet.OLEDB.4.0'
,'Excel 8.0;Database=C:\temp\MyList.xls;HDR=YES'
,'SELECT * FROM [MailingList$]'
)

--this doesn't work
SELECT *
FROM OPENROWSET
(
'Microsoft.Jet.OLEDB.4.0
,'Excel 8.0;Database=' + @FullFileName + ';HDR=YES'
, @SelectSql
)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-21 : 16:55:24

You will have to put the whole thing into a string and execute it as dynamic sql.

EXEC('SELECT *
FROM OPENROWSET
(
''Microsoft.Jet.OLEDB.4.0''
,''Excel 8.0;Database=C:\temp\MyList.xls;HDR=YES''
,''SELECT * FROM [MailingList$]''
)')

Not elegant, but the thing with variable strings in openrowset just doesn't rock.

rockmoose

jovball
Starting Member

2 Posts

Posted - 2006-06-21 : 21:27:47

Ok, then the SQL would look as below. This works and I knew it would but those triple and double quotes are just so much fun...

I was looking for confirmation that the straight up string variables didn't work. I'd still like to know why it is a problem for the Openrowset function if anybody wants to enlighten me. Perhaps that will help me on something similar in the future.

Joel Reinford
Data Management Solutions LLC

DECLARE
@FullFileName varchar(500)
,@SheetName varchar(50)
,@SelectSql varchar(2000)

SET @FullFileName = 'C:\Temp\MyList.xls'
SET @SheetName = 'MailingList'

SET @SelectSql = 'SELECT * FROM [' + @SheetName + '$]'

Exec(
'select *
FROM OPENROWSET(
''Microsoft.Jet.OLEDB.4.0''
,''Excel 8.0;Database=' + @FullFileName + ';HDR=YES''
,''' + @SelectSql + ''')'
)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-22 : 04:38:35

"Ok, then the SQL would look as below. This works and I knew it would but those triple and double quotes are just so much fun"
You need to try executing dynamic sql inside dynamic sql

!

Why it works this way is I don't know, the documentation says a "string constant", so it's just the way it was designed.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources