Multi-Table Linked Query?

I can access a single table in a query, but I cannot seem to make a
multi-table query work. The following works:
SELECT order_number from
remote.staging.dbo.orders
where
company_code = 'Company1' and
order_number = '005579974620040304'
However, I cannot get joins to work when both sides of the joins are on the
remote server. I always get "column prefix does not match" errors. Is a
linked server good only for one table?
Can someone give me an example ? Just make up an additional table in the
same DB with a join. Thank you.
DaveKCould you please post your join query ?
-jens Suessmeyer.|||Hello Dave,
I belive that linked tables arn't so much for queries but for server
operaitons (backups etc).
As an example of a joined table on the same server, it is often useful to
see it another way - as a simple join using a WHERE statement so you can see
the function of joining:
SELECT name_table.first_name, address_table.street_number
FROM name_table, address_table
WHERE name_table.address_id = address_table.address_id
In this example I am linking the two tables by the address_id field.
So using a JOIN you would use:
SELECT name_table.first_name, address_table.street_number
FROM name_table
INNER JOIN
address_table ON name_table.address_id = address_table.address_id
Does this help you a little bit?
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:509461C5-1A63-4979-AA81-212139DB467A@.microsoft.com...
>I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on
> the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||If you use table identifiers, that should work:
ie:
select top 1* from [Test1].[MyDB].[dbo].[Table1] a inner jo
in
[Test1].[MyDB].[dbo].[Table2] b
on b.Col1=a.Col1
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on th
e
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Hi Dave
You should be fine with more than one table, but without seeing the query or
the exact error text or number it is hard to say exactly which is wrong. It
sounds like you have a typo.
John
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on th
e
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Thanks for the inputs everyone. After looking at your examples, I did get s
ome
progress. Here is the exact query:
select c.test_code, d.result_code
from server1.staging.dbo.tests c
right join server1.staging.dbo.results d
on c.company_code = d.company_code and
c.order_number = d.order_number and
c.test_code = d.test_code
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and d.result_code = '35645AA1'
Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e31: Execution terminated by the provider because a resourc
e
limit was reached.].
These tables have several million rows. The error, after 15 minutes, looks
to me like the linked server was trying to send the whole tables. This
should not be.
A single query with no join, like this one:
select c.test_code, c.result_code, c.value
from server1.staging.dbo.results c
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and c.result_code = '35645AA1'
normally takes 3 seconds
Am I on the right track with the limitations of a linked server? I was able
to get OPENQUERY style querys to work with my join syntax, so I am pretty
sure I am using the correct syntax. They only took 3 seconds as well. BTW,
the linked server is 2,000 miles away, but we have a good connection.|||Hi Dave
Your error is probably the remote query timing out
http://support.microsoft.com/defaul...kb;en-us;314530
As OPENQUERY is working so well I assume that you are going to use that?
John
"DaveK" wrote:

> Thanks for the inputs everyone. After looking at your examples, I did get
some
> progress. Here is the exact query:
> select c.test_code, d.result_code
> from server1.staging.dbo.tests c
> right join server1.staging.dbo.results d
> on c.company_code = d.company_code and
> c.order_number = d.order_number and
> c.test_code = d.test_code
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and d.result_code = '35645AA1'
> Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
> provider because a resource limit was reached.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
> returned 0x80040e31: Execution terminated by the provider because a resou
rce
> limit was reached.].
> These tables have several million rows. The error, after 15 minutes, look
s
> to me like the linked server was trying to send the whole tables. This
> should not be.
> A single query with no join, like this one:
> select c.test_code, c.result_code, c.value
> from server1.staging.dbo.results c
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and c.result_code = '35645AA1'
> normally takes 3 seconds
>
> Am I on the right track with the limitations of a linked server? I was ab
le
> to get OPENQUERY style querys to work with my join syntax, so I am pretty
> sure I am using the correct syntax. They only took 3 seconds as well. BT
W,
> the linked server is 2,000 miles away, but we have a good connection.
>|||I think I have narrowed down the problem some more.
If I use a search string in single quotes, it runs pretty fast. However if
I built the search string as a variable (declare @.search_string ...,) then
use that in my where clause, everything goes to pot timewise.
forinstance:
select c.order_number, c.value
from remote1.staging.dbo.orders c
where c.company_code = 'Company1'
and c.order_number = @.accession_string
this takes forever and times out.
Why would one form of the same data (literals in single quotes vs. string
variable change the query so much?|||Hi Dave
I am not sure why it does this, you may want to try using profiler on the
local and remote machines to see what gets sent to them, to determine if thi
s
is the query optimiser or the OLEDB driver that is having problems.
Assuming @.accession_string is a char(10), you may want to try:
EXEC remote1.master.dbo.sp_executesql
N'select c.order_number, c.value from staging.dbo.orders c
where c.company_code = ''Company1''
and c.order_number = @.accession',
N'@.accession char(10)',
@.accession_string
John
"DaveK" wrote:

> I think I have narrowed down the problem some more.
> If I use a search string in single quotes, it runs pretty fast. However
if
> I built the search string as a variable (declare @.search_string ...,) then
> use that in my where clause, everything goes to pot timewise.
> forinstance:
> select c.order_number, c.value
> from remote1.staging.dbo.orders c
> where c.company_code = 'Company1'
> and c.order_number = @.accession_string
> this takes forever and times out.
> Why would one form of the same data (literals in single quotes vs. string
> variable change the query so much?
>