?
Solved

How to pass parameters for OPENDATASOURCE

Posted on 2004-09-16
10
Medium Priority
?
1,590 Views
Last Modified: 2012-05-05

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
Comment
Question by:PePi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
10 Comments
 
LVL 10

Expert Comment

by:imrancs
ID: 12076076
you cannot pass parameter to OPENDATASOURCE, you have to pass hard coded string


Imran
0
 
LVL 6

Author Comment

by:PePi
ID: 12076260
and there's no work around for this?
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12076279
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 10

Expert Comment

by:imrancs
ID: 12076306
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
 
LVL 10

Accepted Solution

by:
imrancs earned 2000 total points
ID: 12076375
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
 
LVL 10

Expert Comment

by:imrancs
ID: 12076383
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
 
LVL 10

Expert Comment

by:imrancs
ID: 12076546
okey PePi  I am leaving now. If there still problem there may many others to guide you.

Good Luck

Imran
0
 
LVL 6

Author Comment

by:PePi
ID: 12076593
thanks a lot for all your help Imran. I'll try your suggestions. Cheers!
0
 
LVL 6

Author Comment

by:PePi
ID: 12076672
it worked like a dream Imran. You rock!!!
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12076692
It was the last time I refreshed the explorer .

Glad if your problem got solved.


Imran
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question