Problem with OPEN DATASOURCE function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with OPEN DATASOURCE function

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-29 : 07:58:21

hi,

pls look at below code

declare @RouteToCode varchar(6)
set @RouteToCode = 'mnm'
declare @string varchar(200)
set @string = 'DataSource=148.171.86.413;UserID=reports;Password=iewa'
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
@string
).IIF_INTEGRATION.dbo.tROUTE_CODE where RTE_TO_CD = ltrim(rtrim(@RouteToCode))

i am getting following error while executing this code in sql server 2000:

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 8: Incorrect syntax near '@string'.

How to pass parament as argument to function?

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Sachin.Nand

2937 Posts

Posted - 2009-12-29 : 08:06:59

Try this one
declare @RouteToCode varchar(6)
set @RouteToCode = 'mnm'
declare @string varchar(200)
set @string = 'DataSource=148.171.86.413;UserID=reports;Password=iewa'
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB','DataSource=148.171.86.413;UserID=reports;Password=iewa'
).IIF_INTEGRATION.dbo.tROUTE_CODE where RTE_TO_CD = ltrim(rtrim(@RouteToCode))

PBUH

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-29 : 08:33:37

hi,

the way u suggested is working.
but i need to run function by passing parameter,becoz i need to move this to a stored procedure,where in that sp will get connction string as input parameter

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Sachin.Nand

2937 Posts

Posted - 2009-12-29 : 08:40:12

I guess then u will have to use Dynamic SQL to achieve this.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-29 : 08:52:19

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=137689

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources