How to pass parameters for OPENDATASOURCE


I've been trying to use parameters passed to a stored procedure then using these parameters for the OPENDATASOURCE function. I keep getting Incorrect syntax error on the parameter.

doesn't OPENDATASOURCE accept dynamic parameters? does this have something to do with OPENDATASOURCE being a macro? is there a work around for this?

e.g.:

DECLARE @ds nvarchar(500)
SET @ds = 'Data Source=\\MyServer\MyDBFFiles\;Extended Properties=dBASE III;'

SELECT * INTO #CODES FROM
    OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', @ds)...CODES

will produce the error: Server: Msg 170, Level 15, State 1          Incorrect syntax near '@ds'

But, if I execute this:

SELECT * INTO #CODES FROM
    OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
        'Data Source=\\MyServer\MyDBFFiles\;Extended Properties=dBASE III;')...CODES

it works just fine.


Thanks again!



LVL 6
PePiAsked:
Who is Participating?
 
imrancsCommented:
Please create the #CODES your own before executing the dynamic sql. and then use

DECLARE @ds nvarchar(500)
DECLARE @sql nvarchar(8000)

Create table #CODES (col1 int, col2 varchar(100), .....)


SET @ds = 'Data Source=\\MyServer\MyDBFFiles\;Extended Properties=dBASE III;'

SET @sql = 'SELECT *  FROM
             OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'', '+Char(39)+ @ds +Char(39)+ ')...CODES'


INSERT #CODES EXEC (@Sql)


Imran
0
 
imrancsCommented:
you cannot pass parameter to OPENDATASOURCE, you have to pass hard coded string


Imran
0
 
PePiAuthor Commented:
and there's no work around for this?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
imrancsCommented:
try like this

declare @a varchar(1000)
declare @b varchar(1000)

set @b = 'Data Source=localhost;User ID=uid;Password=password'
set @a = '

SELECT   *
FROM      OPENDATASOURCE(
         ''SQLOLEDB'','+Char(39)+ @b + Char(39) +'
         
         ).Northwind.dbo.Categories'
Exec (@a)



Imran
0
 
imrancsCommented:
and your code should look like this

DECLARE @ds nvarchar(500)
DECLARE @sql nvarchar(8000)

SET @ds = 'Data Source=\\MyServer\MyDBFFiles\;Extended Properties=dBASE III;'

SET @sql = ' SELECT * INTO #CODES FROM
               OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'', '+Char(39)+ @ds +Char(39)+ ')...CODES'


EXEC (@Sql)



Imran
0
 
imrancsCommented:
I think its needless to say that the structure of the ResultSet returned by the SELECT and of #CODE should be same. :o)


Imran
0
 
imrancsCommented:
okey PePi  I am leaving now. If there still problem there may many others to guide you.

Good Luck

Imran
0
 
PePiAuthor Commented:
thanks a lot for all your help Imran. I'll try your suggestions. Cheers!
0
 
PePiAuthor Commented:
it worked like a dream Imran. You rock!!!
0
 
imrancsCommented:
It was the last time I refreshed the explorer .

Glad if your problem got solved.


Imran
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.