Solved

How to pass parameters for OPENDATASOURCE

Posted on 2004-09-16
10
1,524 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Rebooting Witness SQL Server 2 25
Need sql in string 2 30
MS SQL Server Management Studio R2 4 32
Need to create and populate a column map table 5 21
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

733 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