• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1709
  • Last Modified:

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!



0
PePi
Asked:
PePi
  • 7
  • 3
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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:
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:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now