Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Querying external server without adding it as linked server

Apr 11 2011 5:46AM by Madhivanan   

One of the members in the SQL Server forum asked a question about querying the remote server without actually adding it as a Linked Server. One of the options to do this is to make use of the OPENDATASOURCE function

Assume that you have two servers Server1 and Server2. You want to query a table from Server2 from Server1. The usaul method is to add Server2 as a linked Server in Server1 using the sp_addLinkedServer system stored procedure. So now the query would be

Select columns from Server2.Db_name.owner_name.table_name

If you want to do it the same without actually adding it as Linked Server, use the following

If the remote server is of Windows Authentication

SELECT *FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=Server2;Integrated Security=SSPI')
    .test.dbo.MyTable

If the remote server is of SQL Server Authentication

SELECT   * FROM OPENDATASOURCE(
            'SQLOLEDB',
            'Data Source=server2;user id=user_name;password=pass_word'
        ).test.dbo.MyTable

Also note that you can query a table from MS Access database or and EXCEL file also using the OPENDATASOURCE function

Tags: t-sql, sql_server, sqlserver, tsql, BRH, Opendatasource,


Madhivanan
3 · 40% · 12899
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • Very useful blog.

    Actually i am looking for a solution like this to avoid linked server.

    thanks. Sathieshkumar.R

    commented on Jun 24 2011 5:56AM
    Sathieshkumar
    713 · 0% · 46
  • Quite useful especially when you don't have permission to add linked server. Need to check it.

    commented on Jun 24 2011 11:09AM
    pk_bohra
    742 · 0% · 44

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]