Want a VIEW of remote table to appear in my DB

Printable View

Oct 17th, 2005, 01:22 PM
szlamany

Want a VIEW of remote table to appear in my DB

How can I take a table in a database on server B and make a VIEW of it in my database on server A?
Oct 17th, 2005, 01:32 PM
kaffenils

Re: Want a VIEW of remote table to appear in my DB

Quote:

Originally Posted by 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?

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:

Code:

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.
Oct 17th, 2005, 01:35 PM
szlamany

Re: Want a VIEW of remote table to appear in my DB

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...
Oct 17th, 2005, 01:48 PM
szlamany

Re: Want a VIEW of remote table to appear in my DB

Thanks again - I tried to rep you, but apparently I must spread some around first...
Oct 17th, 2005, 02:02 PM
kaffenils

Re: Want a VIEW of remote table to appear in my DB

Quote:

Originally Posted by 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...

Are you performing any joins between tables on the two servers?
Oct 17th, 2005, 02:03 PM
szlamany

Re: Want a VIEW of remote table to appear in my DB

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?
Oct 17th, 2005, 02:06 PM
szlamany

Re: Want a VIEW of remote table to appear in my DB

Quote:

Originally Posted by kaffenils

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...
Oct 17th, 2005, 02:34 PM
kaffenils

Re: Want a VIEW of remote table to appear in my DB

Quote:

Originally Posted by 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?

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.
Oct 17th, 2005, 02:37 PM
kaffenils

Re: Want a VIEW of remote table to appear in my DB

Quote:

Originally Posted by szlamany

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