Solved

How to pass parameters for OPENDATASOURCE

Posted on 2004-09-16
10
1,462 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
  • 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now