Want a VIEW of remote table to appear in my DB

[szlamany]

How can I take a table in a database on server B and make a VIEW of it in my database on server A?

[kaffenils]

How can I take a table in a database on server B and make a VIEW of it in my database on server A?

First of all you must add server B as a linked server on server A. You can do it with sp_addlinkedserver or in EM. It's easier to do it in EM, since you have to specify a security context for the linked server.

Then you can simply create a view by specifying the four part name of the table on server B. So on server a you execute:

CREATE VIEW [VIEW_NAME]
AS
SELECT * FROM SERVERB.DB_NAME.dbo.TABLE_NAME

Read about distributed partitioned views on BOL. You can actually create an updatable view that consists of tables from multiple servers using UNION ALL and by specifying a unique constraint for the ket columns on the different tables. When you update/delete/add records in the view, SQL Server will yse the constraint to determine on what server the record should be added/deleted/modified.

[szlamany]

Thanks a real lot...

I'm trying to map to a server that the office phone system here uses for tracking calls. The customer wants me to report on the data, but I'm afraid to put any views or sprocs into this database...

[szlamany]

Thanks again - I tried to rep you, but apparently I must spread some around first...

[kaffenils]

Thanks a real lot...

I'm trying to map to a server that the office phone system here uses for tracking calls. The customer wants me to report on the data, but I'm afraid to put any views or sprocs into this database...

Are you performing any joins between tables on the two servers?

[szlamany]

Quick question - this is working great for me - I can attach.

But the IT director is getting an error in QA.

Login Failed for user 'NTAUTHORITY\ANONYMOUS LOGON'

Any clue?

[szlamany]

Are you performing any joins between tables on the two servers?

No just straight SELECT's within STORED PROCEDURES - probably with NOLOCK hints to be easy and quick...

[kaffenils]

Quick question - this is working great for me - I can attach.

But the IT director is getting an error in QA.

Login Failed for user 'NTAUTHORITY\ANONYMOUS LOGON'

Any clue?

You must create a user on server a with access to the tables you are selecting from. Then you must specify this user name and password for all connections through the linked server.

[kaffenils]

No just straight SELECT's within STORED PROCEDURES - probably with NOLOCK hints to be easy and quick...

Ok, I though I should mention, if you had joined data from tables on the different servers, that there is a join hint that allows you to specify which server that should execute the join.
You can read about it here