none
How to Execute the SQL Query?

    Question

  • Hi,

    We have 2 databases 

    Ex:- server1 - database1 - table1  ( empno, ename, deptno )

           server2 - database2 - table2  (deptno, dname, loc )

    SQL Query:- select table1.*, Table2.* from "[database1].[dbo].[table1]"  "A"  join "[database2].[dbo].[table2]"  "B" on A.deptno=B.deptno; 

    Can we Execute this SQL?

    If Yes, where we will execute the Query (Server1 or Server2) 

    Friday, February 01, 2013 5:03 PM

Answers

  • Hi Vamsi,

    You need to have a linked server either one of your sql server.

    for example you have Server1 as a linked server in Server2 then you need to execute query on Server2

    select table1.*, Table2.* from "[server1].[database1].[dbo].[table1]"  "A"  join "[database2].[dbo].[table2]"  "B" on A.deptno=B.deptno


    Thanks & Regards Prasad DVR

    Friday, February 01, 2013 5:20 PM
  • You need to create linked server first and then use the following syntax to run it in the instance with the linked server to point to the other:

    select A.*, B.* from Server1.[database1].[dbo].[table1] As A  
    join Server2.[database2].[dbo].[table2] as B on A.deptno=B.deptno; 

    Friday, February 01, 2013 5:27 PM
    Moderator

All replies

  • Hi Vamsi,

    You need to have a linked server either one of your sql server.

    for example you have Server1 as a linked server in Server2 then you need to execute query on Server2

    select table1.*, Table2.* from "[server1].[database1].[dbo].[table1]"  "A"  join "[database2].[dbo].[table2]"  "B" on A.deptno=B.deptno


    Thanks & Regards Prasad DVR

    Friday, February 01, 2013 5:20 PM
  • You need to create linked server first and then use the following syntax to run it in the instance with the linked server to point to the other:

    select A.*, B.* from Server1.[database1].[dbo].[table1] As A  
    join Server2.[database2].[dbo].[table2] as B on A.deptno=B.deptno; 

    Friday, February 01, 2013 5:27 PM
    Moderator
  • Thanks DVR & Jingyang Li
    Saturday, February 02, 2013 10:36 AM