Link to home
Start Free TrialLog in
Avatar of lorigottschalk
lorigottschalk

asked on

OpenDataSource stored proc with parameterized connection string

I would like to create variables to use with OpenDataSource, something like:

CREATE PROCEDURE dbo.GetCustomerTypes

@DataSource varchar(20) = '0.0.0.0',
@UserID varchar(20) = 'UID',
@Password varchar(20) = 'PW'

AS

SELECT   *
FROM      OPENDATASOURCE('SQLOLEDB', 'Data Source=' + @DataSource + ';User ID=' + @UserID + ';Password=' + @Password + '').RemoteDatabase.dbo.CustomerTypes

I can't seem to get the syntax right.  Can anyone help?
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

>>I would like to create variables to use with OpenDataSource<<
It can't be done, without using Dynamic SQL.
Also, please attend to the following questions as they appear to have fallen through the cracks and are now considered abandoned:
2 10/30/2007 125 Exporting data (or better yet, direct access& Open Databases & 
3 02/12/2007 125 Repeating Table Header row not working in cfd& Open CFML
Avatar of lorigottschalk
lorigottschalk

ASKER

Ok, so how would you do this using Dynamic SQL?
Can you give me an example using Dynamic SQL, please?
here we go:
CREATE PROCEDURE dbo.GetCustomerTypes
@DataSource varchar(20) = '0.0.0.0',
@UserID varchar(20) = 'UID',
@Password varchar(20) = 'PW'
AS
DECLARE @SQL VARCHAR(8000)
 
SET @SQL = '
SELECT   *
FROM      OPENDATASOURCE(''SQLOLEDB'', ''Data Source=' + @DataSource + ';User ID=' + @UserID + ';Password=' + @Password + '''').RemoteDatabase.dbo.CustomerTypes
'
exec(@sql)

Open in new window

Thanks, angelIII, I think this is really close, but the proc doesn't compile.  I get the error:

Error 170: Line 10: Incorrect syntax near ')'.
Unclosed quotation mark before the character string '
exec(@sql)
'.

I tried a couple things, but couldn't figure out where it's expecting another quote.  Any ideas?

Thanks!
indeed...
CREATE PROCEDURE dbo.GetCustomerTypes
@DataSource varchar(20) = '0.0.0.0',
@UserID varchar(20) = 'UID',
@Password varchar(20) = 'PW'
AS
DECLARE @SQL VARCHAR(8000)
 
SET @SQL = '
SELECT   *
FROM      OPENDATASOURCE(''SQLOLEDB'', ''Data Source=' + @DataSource + ';User ID=' + @UserID + ';Password=' + @Password + ''').RemoteDatabase.dbo.CustomerTypes
'
exec(@sql)

Open in new window

Thanks, this works perfectly!  Would you mind explaining to me how the quotes work?  Why are there sometimes two quotes and sometimes three?  I realized after the fact that "RemoteDatabase" should also be a variable, since it might have a different name on my production server than in development.  So, I tried to make that a variable using the example you provided (see below).  But I get the error about 'unclosed quotation mark'.  I know this has to do with the placement and number of single quotes around the variables, but am unsure what the rule is.  Any help is greatly appreciated!

SET @SQL = '
SELECT   *
FROM      OPENDATASOURCE(''SQLOLEDB'', ''Data Source=' + @DataSource + ';User ID=' + @UserID + ';Password=' + @Password + ').' + @RemoteDatabase + '.dbo.AR_Type_of_Customer
'
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
now the explanation:

single quote: starts/ends a string.
'some string'
 
2 single quotes: means 1 single quote as part of the string.
'some quote in string goes here >>>''<<< '

3 single quotes: means the single quote that starts/end is direclty close to the 2 single quotes meaning a single quote as part of the string:
'this string ends with a single quote '''


Thanks, I really appreciate the help!
Do you need help attending to these very old abandoned questions or would you like me to contact an EE Moderator?
1 10/30/2007 125 Exporting data (or better yet, direct access& Open Databases & 
2 02/12/2007 125 Repeating Table Header row not working in cfd& Open CFML