September 15, 2005 at 6:55 am
Is it possible to join 2 tables from 2 separate servers ? I get an error that server 818 cannot be found in the sysservers table, and to execute sp_addlinkedserver to add the server to sysservers. And if I resolve by adding a linked server using this stored procedure, are there any negative effects ?
Here's the code :
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jsv_zim_conv_usage]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[jsv_zim_conv_usage]
GO
CREATE VIEW dbo.jsv_zim_conv_usage
AS
select *
FROM dbo.js_zim_temp_usage LEFT OUTER JOIN
[818]. [database1].[ownerA].[table1] ON OW_ID = PHLITM
go
N Hatt
September 15, 2005 at 7:13 am
If you do not have a partition column, i.e. the view is not a partitioned view, sql needs to load all data from the linked server into your local server. It may have negtive impact on the performance.
September 15, 2005 at 7:15 am
if an error occurred and the linked server was not deleted.
Of course you could make the linked server permament, making sure you have the correct security set.
Alternatively, read OPENROWSET in BOL, it has the affect of creating a linked server and removing it all for you, however the downside is the security is visible in the proc
e.g sample from BOL
USE pubsGOSELECT a.*FROM OPENROWSET('SQLOLEDB','seattle1';'manager';'MyPass', 'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS aGOFar away is close at hand in the images of elsewhere.
Anon.
September 15, 2005 at 7:25 am
You can join to a different server via "linked server", but performance will hover toward the bottom of the toilet. If it's feasible, especially if you're running repeated queries against the linked table in the course of a stored procedure's execution, you should create a replica of the table from the linked server on your local server and use that for any joining.
If we're talking about a lot of data, you could consider replicating the table from the linked server to your local server.
September 15, 2005 at 7:30 am
thats what I needed to hear. Thanks!
N Hatt
September 16, 2005 at 8:43 am
If you use the Security: table in enterprise manager to set the link server using current login ... you can reference the server in your statement.
Then ....
i.e.
From Server1
Select * from server2.pubs.dbo.authors if cluster name reference is similar to .....
[prod\server1].pubs.dbo.authors
Deborah Talley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply