March 9, 2010 at 10:47 pm
Greetings.
My SQL Server has a link server, e.g. with IP X. I want to query data as follows:
select t1.col1, t1.col2, t2.col1
from [X].db_name.dbo.table1 t1,
[X].db_name.dbo.table2 t2
where t1.col3 = t2.col2
and t2.col4 in ('1', '2', '3')
I want to know how does the query runs? Is all processes of tables join happened in remote server, or all data will be downloaded first to local server than join it in local?
Thanks in advance.
MP.
March 10, 2010 at 4:51 am
Hi,
I'm not sure exactly how the query is executed. Some simple queries might be run on the remote server and only the answer is returned. But rather quick you will probably have problems with execution time. At least that is my experience.
If the whole query is to run at the remote server, try OPENQUERY instead:
SELECT T1_col1, T1_col2, T2_col1
FROM OPENQUERY([X], 'select t1.col1 AS T1_col1, t1.col2 AS T1_col2, t2.col1 AS T2_col1
from db_name.dbo.table1 t1,
db_name.dbo.table2 t2
where t1.col3 = t2.col2
and t2.col4 in (''1'', ''2'', ''3'')')
The problem you might encounter with OPENQUERY is that the passed query needs to be static, i.e. you can't construct it in the same batch:
declare @value varchar(100)
set @value = '1'
--Will NOT work
SELECT T1_col1, T1_col2, T2_col1
FROM OPENQUERY([X], 'select t1.col1 AS T1_col1, t1.col2 AS T1_col2, t2.col1 AS T2_col1
from db_name.dbo.table1 t1,
db_name.dbo.table2 t2
where t1.col3 = t2.col2
and t2.col4 = ''' + @value + '''')
In that case you need to build the query as a NVARCHAR (the complete query, including OPENQUERY) and run it through sp_executesql.
-- Example of how to get it to work
-- (I haven't run this code, but it shows the principle)
declare @value nvarchar(100)
set @value = '1'
declare @SQL nvarchar(max)
set @SQL = '
SELECT T1_col1, T1_col2, T2_col1
FROM OPENQUERY([X], ''select t1.col1 AS T1_col1, t1.col2 AS T1_col2, t2.col1 AS T2_col1
from db_name.dbo.table1 t1,
db_name.dbo.table2 t2
where t1.col3 = t2.col2
and t2.col4 = ''''' + @value + ''''')'
sp_executesql @SQL
I hope this helps, even though I can't really answer your question.
/Markus
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply