How to access Database from other computer using sql query in sql server

Question

I am using a SQL database residing on my local computer. I want to access a table from a database residing on another computer using SQL query.

I have been able to connect the remote database with my database and all its tables are shown in Enterprise manager on my local machine. I have added remote SQL Server in my local SQL Server.

When I use select statement in my local SQL server it gives the message database does not exist or access denied.

Any help in this would highly be appreciated.

EDITED

Select * from [ServerName].DatabaseName.dbo.tableName

You need to create a linked server, but its generally not the best idea. What are you trying to do? sqlserverplanet.com/dba/how-to-add-a-linked-server — Nick.Mc
– Nick.Mc, Commented Oct 8, 2016 at 7:07

M.Hassan · Accepted Answer · 2016-10-08 17:17:07Z

2

Use OPENROWSET
Example

    SELECT t.version FROM 
    OPENROWSET('SQLNCLI', 'server=Myserver;UID=xxxx;pwd=yyyy',
    'select @@version version') t

Note:

SQLNCLI is the name of installed OLE DB provider

Datasource: {server=Myserver;UID=xxx;pwd=yyy}

You have to enable OPENROWSET by executing the following script:

  sp_configure 'show advanced options', 1
  reconfigure

  go
  sp_configure 'Ad Hoc Distributed Queries', 1
   reconfigure

OPENROWSET is like connecting to a linked server

answered Oct 8, 2016 at 17:17

M.Hassan

11.2k5 gold badges74 silver badges96 bronze badges

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

Collectives™ on Stack Overflow

How to access Database from other computer using sql query in sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related