How to fetch data using OPENDATASOURCE and Linked Server

Question

How to fetch data using OPENDATASOURCE and Linked Server

Ankit Mathur-481681

SSCrazy

Points: 2074
More actions
June 10, 2008 at 4:40 am

#188740

Hi I'm using the following query to connect to my MSSQL 2000 DB1 and further use a linked server to connect and fetch data from DB2 there to get the data finally into DB3.
Example IPs
DB1: 1.1.1.1
DB2: 1.1.1.2
DB3: 1.1.1.3
My Query
SELECT Identifier, Count(Identifier)
FROM OPENDATASOURCE
(
'SQLOLEDB','Data Source=1.1.1.1;User ID=vUser;Password=getdata;'
)[1.1.1.2].RadiusDB.dbo.Sessions WHERE IDentifier='ankit' GROUP BY IDentifier
But am facing some syntax issues with this query.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '1.1.1.2'.
Can somebody help me correct my query.
Note: I can't connect my linked server (DB2) directly from my third DB i.e. DB3 and have to go via DB1.
Thanks
Ankit

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

Not completely understand your question. But here are my thought.

If you data souce is correctly defined, you should not use [1.1.1.2] because your OPENDATABASE clause has already been defined as a "server name".

Ankit Mathur-481681 SSCrazy Points: 2074 More actions · Answer 2

Hi,

Lemme post my question in another format.

I've three DB Servers (DB1, DB2, DB3). Each having their exclusive set of tables. I'm using each of these servers using linked servers technique.

How are the DBs related to each other?

DB1 is being connected here only with DB2 using OPENDATASOURCE and DB2 is connected to DB3 by linked server.

There's no direct connection between DB1 and DB3.

Now for some particular requirement I require to fetch data present in DB3 into DB1.

So I was trying to use OPENDATASOURCE.

Sorry. I misrepresented the IPs in my original question.

Now to my code:

SELECT Identifier, Count(Identifier)

FROM OPENDATASOURCE

(

'SQLOLEDB','Data Source=1.1.1.2;User ID=vUser;Password=getdata;'

)[1.1.1.3].RadiusDB.dbo.Sessions WHERE IDentifier='ankit' GROUP BY IDentifier

As mentioned above:

Example IPs

DB1: 1.1.1.1

DB2: 1.1.1.2

DB3: 1.1.1.3

So in my query I'm connecting using 1.1.1.2 to my DB2 and then trying to use the linked server syntax to get to DB3.

But I'm getting the error as specified above.

I hope this time my question is clearer and somebody can tell me the correct syntax, if any or, provide me with a workaround to finally get the data flowing.

Thanks for your time.

Ankit

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 3

Ankit Mathur (6/10/2008)
FROM OPENDATASOURCE
(
'SQLOLEDB','Data Source=1.1.1.2;User ID=vUser;Password=getdata;'
)[1.1.1.3].RadiusDB.dbo.Sessions WHERE IDentifier='ankit' GROUP BY IDentifier

First of all, your syntax is wrong.

Please check Books Online.

Ankit Mathur-481681 SSCrazy Points: 2074 More actions · Answer 4

Hi WildCat,

I know the syntax is wrong.

That's why the question is being put here.

If I use the following query I'm able to get resultset from DB2.

SELECT Name, Count(ID)

FROM OPENDATASOURCE

(

'SQLOLEDB',

'Data Source=[1.1.1.2];User ID=vUser;Password=getdata'

).StuDB.dbo.ActiveSessions WHERE Name='ankit' GROUP BY Name

But in my current scenario I need a resultset from DB3 which cannot be directly connected to DB1 and hence I need to go via DB2.

So am stuck with this syntax problem and posted the question here hoping someone would be able to help me out.

Ankit

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 5

How about move data into DB1 first, then get'em on DB3?

Ankit Mathur-481681 SSCrazy Points: 2074 More actions · Answer 6

Well the transactions are being logged at DB3 only and DB1 is our reporting server only where we fetch and store data at continous intervals.

As of now we have no plans to change DB architecture and I need a solution to my problem of accessing data from DB1 to DB3 only.

Any ideas ? Anybody ?

Ankit

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 7

You could create views/storedprocedures on DB2 for the data you need to fetch from DB3.

Ankit Mathur-481681 SSCrazy Points: 2074 More actions · Answer 8

I've started working on that only.

I was hoping that someway I'll be able to get it through the query directly. But I didn't got any solutions so far.

I guess creating temp table in DB2 and storing data there first and then calling it on its final destination seems to be the only way out.

I'm still leaving this question open for all so that maybe sometime later somebody can throw light on how to go about it.

Thanks all for your time.

Ankit Mathur