sql server (alternate): Input Paramter as part of OPENDATASOURCE -- sql server (alternate)

Re: Input Paramter as part of OPENDATASOURCE

Steve
1/26/2007 10:40:22 AM

sql server (alternate): Try puting the entire statement into a string variable and then
executing the string.

[quoted text, click to view]

Input Paramter as part of OPENDATASOURCE

paddy_nyr
1/26/2007 11:20:57 AM

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'.

Re: Input Paramter as part of OPENDATASOURCE

Erland Sommarskog
1/26/2007 10:57:40 PM

paddy_nyr (mpprpp@yahoo.com) writes:
[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server (alternate) group.