Hello-
I am trying to write a report that combines data from two different databases on two different servers. Wherther I run the query in SQL Server 2005 management studio or reporting services, I get the same error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'King_County_SWD.dbo.vwDriver_Out_Gate'.
There is an error in the query. Invalid object name 'King_County_SWD.dbo.vwDriver_Out_Gate'.
I have written queries that access two different databases on the same server and they work just fine, so I am guessing that I am not providing enough of a fully qualified path to the second server. We have not implemented linked server.
Any ideas on how to properly identify the second server/database/object would be appreciated.
Whenever I had to access data from 2 different servers, I had to setup a Linked Server. Without going into too much detail, here's an example:
Server1(where the sql statement will be run) Server2(other data)
ON Server1 create a linked server to Server2. On Server1, run your sql statement, BUT for the data on Server2 you need to add the qualifier Server2, i.e.,
Select * from DB1.dbo.table1 inner join Server2.DB2.dbo.table2 on ..............
|||You could also use an SSIS package as data source for your report.
Sounds weird but works:
http://www.fits-consulting.de/blog/PermaLink,guid,0e3316ae-c9e7-426e-9e6b-30dab0ea2ed2.aspx
cheers,
Markus
No comments:
Post a Comment