pennypiper
asked on
Parameterized OpenDataSource call
Hello,
I need to pass as a parameter the "Data Source" portion of the OpenDataSource call:
SELECT @foo = col1 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\foo.xsl";User ID=Admin;Password=;Extende d properties=Excel 8')...sheet1
the "c:\foo.xsl" portion above needs to be in a variable.
I've tried the following:
set @FileName = 'c:\foo.xls'
SELECT @foo = col1 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=' + @FileName + ';User ID=Admin;Password=;Extende d properties=Excel 8')...sheet1
but I get the error:
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '+'.
I'm new to SQL Server so any help would be greatly appreciated!
Thanks.
I need to pass as a parameter the "Data Source" portion of the OpenDataSource call:
SELECT @foo = col1 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
the "c:\foo.xsl" portion above needs to be in a variable.
I've tried the following:
set @FileName = 'c:\foo.xls'
SELECT @foo = col1 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
but I get the error:
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '+'.
I'm new to SQL Server so any help would be greatly appreciated!
Thanks.
ASKER
Hello,
When I run the above, I get the following error:
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near '@dsn'.
with line 15 being this line:
SELECT @foo = col1 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', @dsn)...sheet1
I've declared @dsn as: declare @dsn varchar(400)
Is it something obvious I'm missing?
Thanks.
When I run the above, I get the following error:
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near '@dsn'.
with line 15 being this line:
SELECT @foo = col1 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
I've declared @dsn as: declare @dsn varchar(400)
Is it something obvious I'm missing?
Thanks.
Actually, OpenDataSource (as well the other functions related) do not accept ANY variables, only fixed string.
Solution is to put all into dynamic SQL:
declare @sql varchar(1000)
set @sql = '
declare @foo ...
SELECT @foo = col1 FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0' ', ''Data Source="' + @yourfile + '";User ID=Admin;Password=;Extende d properties=Excel 8'')...sheet1
'
exec (@sql)
Now, note that @foo is declared inside the dynamic sql, so you won't be able to access it from outside. To solve THAT, you will need to use xp_executesql:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
Cheers
Solution is to put all into dynamic SQL:
declare @sql varchar(1000)
set @sql = '
declare @foo ...
SELECT @foo = col1 FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'
'
exec (@sql)
Now, note that @foo is declared inside the dynamic sql, so you won't be able to access it from outside. To solve THAT, you will need to use xp_executesql:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
Cheers
ASKER
Hello,
Thanks for the information about xp_executesql. I changed the code to use xp_executesql and it works in the following case:
declare @result varchar(200)
set @SQLString = N'SELECT @foo = col1
FROM OpenDataSource( ' + '''' + 'Microsoft.Jet.OLEDB.4.0' + '''' + ',' + '''' + 'Data Source="' + 'c:\excel\foo.xls' + '";User ID=Admin;Password=;Extende d properties=Excel 8.0'+'''' + ')...sheet1'
SET @ParmDefinition = N'@foo varchar(30) OUTPUT'
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@foo=@result OUTPUT
SELECT @result
@result does indeed contain the correct information. However, getting back to my original question, what I'd *really* like to do is replace the 'c:\excel\foo.xsl' with a variable. If I add an input variable called 'myfile':
declare @result varchar(200)
declare @FileName varchar(200)
set @FileName = 'c:\foo.xls'
set @SQLString = N'SELECT @foo = col1
FROM OpenDataSource( ' + '''' + 'Microsoft.Jet.OLEDB.4.0' + '''' + ',' + '''' + 'Data Source="' + @myfile + '";User ID=Admin;Password=;Extende d properties=Excel 8.0'+'''' + ')...sheet1'
SET @ParmDefinition = N'@foo varchar(30) OUTPUT,
@myfile varchar(200)'
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@myfile=@FileName,
@foo=@result OUTPUT
SELECT @result
it fails:
Server: Msg 137, Level 15, State 2, Line 46
Must declare the variable '@myfile'.
which makes sense because the scope of @myfile is wrong. So, I guess my new question is: how do I quote the variable @myfile to get SQL Server to expand it at within 'SQLString'?
Thanks for your help!
Thanks for the information about xp_executesql. I changed the code to use xp_executesql and it works in the following case:
declare @result varchar(200)
set @SQLString = N'SELECT @foo = col1
FROM OpenDataSource( ' + '''' + 'Microsoft.Jet.OLEDB.4.0' + '''' + ',' + '''' + 'Data Source="' + 'c:\excel\foo.xls' + '";User ID=Admin;Password=;Extende
SET @ParmDefinition = N'@foo varchar(30) OUTPUT'
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@foo=@result OUTPUT
SELECT @result
@result does indeed contain the correct information. However, getting back to my original question, what I'd *really* like to do is replace the 'c:\excel\foo.xsl' with a variable. If I add an input variable called 'myfile':
declare @result varchar(200)
declare @FileName varchar(200)
set @FileName = 'c:\foo.xls'
set @SQLString = N'SELECT @foo = col1
FROM OpenDataSource( ' + '''' + 'Microsoft.Jet.OLEDB.4.0' + '''' + ',' + '''' + 'Data Source="' + @myfile + '";User ID=Admin;Password=;Extende
SET @ParmDefinition = N'@foo varchar(30) OUTPUT,
@myfile varchar(200)'
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@myfile=@FileName,
@foo=@result OUTPUT
SELECT @result
it fails:
Server: Msg 137, Level 15, State 2, Line 46
Must declare the variable '@myfile'.
which makes sense because the scope of @myfile is wrong. So, I guess my new question is: how do I quote the variable @myfile to get SQL Server to expand it at within 'SQLString'?
Thanks for your help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Success!
I discovered I had a type-o in my code: the "hard-coded" directory was "c:\excel\foo.xsl" but my variable @FileName was set to "c:\foo.xsl". Changing that fixed my problem. Thanks for your help!
I discovered I had a type-o in my code: the "hard-coded" directory was "c:\excel\foo.xsl" but my variable @FileName was set to "c:\foo.xsl". Changing that fixed my problem. Thanks for your help!
Build string first and then pass it to OpenDataSource.
set @FileName = 'c:\foo.xls'
set @dsn='Data Source=' + @FileName+';User ID=Admin;Password=;Extende
SELECT @foo = col1 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',