help with t-sql linked server

Question

Based on the 2 databases below:

Database_A on Server_1
Database_B on Server_2

I have created a linked server to Database_B on Server_1 instance by name 'LS_B'.

I have a huge script file which basically creates required tables, functions, views, and stored procs on Database_A.

These functions, views and stored procs in turn refer to Database_B tables.

Problem is I have to now go and include OPENQUERY(LS_B,<query refering to database_b table>) everywhere in the script file.

For instance

create procedure some_proc
as
begin
    select * from openquery(LS_B, 'select * from [Database_B].[dbo].[Table_1]');
end

Is there a better way to do this?

Pls. suggest.

Thanks

update

why does this fail

select top (50) * from LS_B.[Database_B].[dbo].[Table_1]

while the below works

select * from OpenQuery(LS_B, 'select top (50) * from Database_B.dbo.Table_1')

The error message on executing the first query is

Cannot process the object ""Database_B"."dbo"."Table_1"". The OLE DB provider "SQLNCLI10" for linked server "LS_B" indicates that either the object has no columns or the current user does not have permissions on that object

Both servers are on same domain.

OPENQUERY is only useful if you're connecting to a database other than SQL Server (or older versions that don't support particular syntax) because of the query pass-through. The pass-through lets you write for the other database in native SQL - IE: TO_DATE on Oracle, etc. — OMG Ponies
– OMG Ponies, Commented Aug 4, 2010 at 21:36
@OMG Ponies: Thanks for explaining that. I have edited my question to present the new issue that i am facing. Can you pls. provide some suggestion as to what can be done about it? Thanks. — stackoverflowuser
– stackoverflowuser, Commented Aug 4, 2010 at 21:51
Tested on SQL Server 2005 - works for me, with and without hard brackets. Are you sure you're testing from an instance where that linked server instance is present? In Management Studio, in the Object Explorer - what's listed under Server Objects\Linked Servers (assuming you have permissions to see)? — OMG Ponies
– OMG Ponies, Commented Aug 4, 2010 at 22:06
In your error message it says the linked server is called LS_B but in your example it says it is called TFSDBTIER, is this just a typo or does the error message refer to a different linked server. — Coding Flow
– Coding Flow, Commented Aug 4, 2010 at 22:46

Coding Flow · Accepted Answer · 2010-08-04 21:36:18Z

2

If both servers are SQL servers you can use the following syntax:

select * from LS_B.[Database_B].[dbo].[Table_1]

It would depend on your exisitng syntax whether this would make it easier to do a find and replace.

You could also create a bunch of views on server 1 named after the tables refered to in the sps, then have these views refernce the linked server:

CREATE VIEW Table_1
AS
select * from LS_B.[Database_B].[dbo].[Table_1]

edited Aug 4, 2010 at 21:36

answered Aug 4, 2010 at 21:30

Coding Flow

21.9k6 gold badges68 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

OMG Ponies Over a year ago

+1: You beat me, here's a link to the documentation on qualified name syntax: msdn.microsoft.com/en-us/library/aa172676%28SQL.80%29.aspx

OMG Ponies Over a year ago

A synonym is an alternative to a view.

stackoverflowuser Over a year ago

@Ben: I get the below error on executing Cannot process the object ""Database_B"."dbo"."Table_1"". The OLE DB provider "SQLNCLI10" for linked server "LS_B" indicates that either the object has no columns or the current user does not have permissions on that object. But the same thing works when i execute select * from openquery(LS_B, 'select * from [Database_B].[dbo].[Table_1]')

Coding Flow Over a year ago

Yes that might be a better alternative.

OMG Ponies Over a year ago

@stackoverflowuser: No, a synonym just means you don't have to type the qualified name out in full (four part for linked servers). As long as you aren't doing any functions on the columns, SELECT * FROM synonym will perform exactly the same as SELECT * FROM linked_view.

|

Collectives™ on Stack Overflow

help with t-sql linked server

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related