Input Paramter as part of OPENDATASOURCE

[paddy_nyr]

I want to use an input parameter as my filename, but I get a synatax
error message. Howerve, when I hard code the filename the proc compiles
successfully.
Thanks for any help. I'm using SQL Server 2005
LTR_90,
LTI_ELIG_pct,
LTI_REC_pct,
LOW_SALARY,
HIGH_SALARY FROM OPENDATASOURCE("Microsoft.Jet.OLEDB.4.0",
"Data Source=C:\inetpub\wwwroot\ORC_Beta_Companies\"' + @infilename +
'"Extended Properties=Excel 8.0")...[summary_data$]
Syntax message:
Msg 102, Level 15, State 1, Procedure
importExcelSpreadSheetIntoeNavigator_DataORC, Line 244
Incorrect syntax near 'Microsoft.Jet.OLEDB.4.0'.

[Steve]

Try puting the entire statement into a string variable and then
executing the string.

On Jan 26, 8:20 am, "paddy_nyr" <[e-mail]:

I want to use an input parameter as my filename, but I get a synatax
error message. Howerve, when I hard code the filename the proc compiles
successfully.

Thanks for any help. I'm using SQL Server 2005

LTR_90,
LTI_ELIG_pct,
LTI_REC_pct,
LOW_SALARY,
HIGH_SALARY FROM OPENDATASOURCE("Microsoft.Jet.OLEDB.4.0",
"Data Source=C:\inetpub\wwwroot\ORC_Beta_Companies\"' + @infilename +
'"Extended Properties=Excel 8.0")...[summary_data$]

Syntax message:

Msg 102, Level 15, State 1, Procedure
importExcelSpreadSheetIntoeNavigator_DataORC, Line 244
Incorrect syntax near 'Microsoft.Jet.OLEDB.4.0'.

[Erland Sommarskog]

paddy_nyr (mp****@yahoo.com) writes:

I want to use an input parameter as my filename, but I get a synatax
error message. Howerve, when I hard code the filename the proc compiles
successfully.

Dynamic SQL is the only way out. See
http://www.sommarskog.se/dynamic_sql.html#OPENQUERY
for an example with OPENQUERY that you can use as starter.

If you've never seen dynamic SQL before, I recommend that you read the
entire article, before you start using it all over town.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx