June 10, 2008 at 4:40 am
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
June 10, 2008 at 2:54 pm
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".
June 10, 2008 at 10:37 pm
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
June 11, 2008 at 11:03 am
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.
June 11, 2008 at 10:02 pm
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
June 12, 2008 at 9:12 am
How about move data into DB1 first, then get'em on DB3?
June 13, 2008 at 11:08 pm
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
June 14, 2008 at 12:34 pm
You could create views/storedprocedures on DB2 for the data you need to fetch from DB3.
June 16, 2008 at 2:56 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply