problem with OPENDATASOURCE 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 2008 Forums

Transact-SQL (2008)

problem with OPENDATASOURCE Function

Author

Topic

rammohan
Posting Yak Master

212 Posts

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

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-29 : 08:50:34

Try this

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

exec(@sql)

Madhivanan

Failing to plan is Planning to fail

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-30 : 02:28:26

hi madhivanan,

thanx for u r reply. its working fine.

but when i tried to give server name instead of ip for data source in provider string. i am getting following error:

could not connect. server doesn't exists or access denied.

i am giving correct name only.

any suggestions?

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-30 : 02:58:42

quote:
Originally posted by rammohan

hi madhivanan,

thanx for u r reply. its working fine.

but when i tried to give server name instead of ip for data source in provider string. i am getting following error:

could not connect. server doesn't exists or access denied.

i am giving correct name only.

any suggestions?

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

You need give the domain name too if any

domain\systemname

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources