linked server? i think its what its called...

[akalinowski]

I cant find instructions on how to do the following:
i have 1 production server and 1 server as a data warehouse of sorts.
i want one table to be queried live from the production server while running ssrs or odbc queries against the data warehouse.
the production server has only ecommerce transactional data and the datawarehouse has aggrigate data from several systems so it would not be possible to just run the query on the live web server since i am trying to compare new orders coming in against inventory in the separate systems realtime for the web the other systems do not have high transaction rates and are not sql (providex & oracle) so its not important to read them live.

thanks.

akalinowski

 

[gmmastros]

Yes. Linked Servers.

There are tons of sites that can step you through the process of creating a linked server from one database to another. [google]SQL Server Set Up Linked Server[/google]

Once you have the linked server set up, you will need to use the 4 part naming convention to access the data. Ex:

[tt]
Select Col1, Col2, RemoteTable.ColX, RemoteTable.ColY
From MyTableOnLocalServer
Inner Join NameOfLinkedServer.DatabaseName.SchemaName.TableName As RemoteTable
On MyTableOnLocalServer.ColumnID = RemoteTable.ColumnID
[/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[akalinowski]

http://msdn.microsoft.com/en-us/library/ff772782.aspx

that plus your help got me through this one
thanks

akalinowski