Database View

[DTRNTR]

Is it possible to create a SQL Server 2000 database View that references a Table or View from another SQL Server database (on different server)?

If so, any direction (samples) would be appreciated....specifically how to reference the server name and database containing the data from within the View definition.

Thank You

 

[mrdenny]

Yes you can. First you need to create a linked server to the remote machine.

CREATE VIEW vw_TestView AS
SELECT *
FROM SERVER02.master.dbo.sysobjects
GO

Make sure to only use return the columns that you need.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[DTRNTR]

Thanks for the quick response Denny....

After trying this I receive the following ODBC dialog box message:
"Could Not find server 'server name' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."

Where should this SP be executed from and where would it be attempting to register this server to?

 

[mrdenny]

Before you can query a remote server you need to setup a linked server between the two servers. This is easier done in Enterprise Manager than via the sp_addlinkedserver procedure.

You will need to have sysadmin rights to do this.

Connect to the Server under security you should see linked servers. Right Click, new. Select SQL Server and fill in the name of the remote server. On the security tab setup the security how ever your company requires it and click ok.

You will now be able to use the linked server.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[DTRNTR]

Working as you described....Thank You for your guidance Denny!

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com