select from another database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

select from another database

Author

Topic

casati74
Posting Yak Master

109 Posts

Posted - 2006-09-21 : 03:57:12

Hello,
i must select from a database table, but this table is on another server; ho can i select from another server??

es: server1 - sp1 - select * from table2 on server2

Ho can i do it?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-21 : 04:02:22

use linked server. Create a linked server to server2 on server1 and use 4 part name in table reference

select * from [server2].[database].dbo.[table2]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-21 : 04:23:11

Or if is just one-time activity, use OPENDATASOURCE() function.

Here is an example from BOL:

SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=ServerName;User ID=MyUID;Password=MyPass'
         ).Northwind.dbo.Categories

Harsh Athalye
India.
"Nothing is Impossible"

casati74
Posting Yak Master

109 Posts

Posted - 2006-09-21 : 04:38:53

i try solution [server2].database2.dbo.table2 but i recive error because server2 isn't linked
I exec

EXEC sp_addlinkedserver
'[10.81.0.201]',
N'SQL Server'
and it's ok

but when i try to select from [10.81.0.201]

select * from [10.81.0.201].evisionDonasid.dbo.Replications

i recive this error

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

how can i solve this problem (how can i associate a trust connection?)
if i wrote trustedconnection = true (how c#) i recive error

Thank's all

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-21 : 04:44:56

quote:
Originally posted by casati74

i try solution [server2].database2.dbo.table2 but i recive error because server2 isn't linked
I exec

EXEC sp_addlinkedserver
'[10.81.0.201]',
N'SQL Server'
and it's ok

but when i try to select from [10.81.0.201]

select * from [10.81.0.201].evisionDonasid.dbo.Replications

i recive this error

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

how can i solve this problem (how can i associate a trust connection?)
if i wrote trustedconnection = true (how c#) i recive error

Thank's all

It's because SQL server assumes [10.81.0.201] to be the name of the SQL server you want to link, not the IP address.

Harsh Athalye
India.
"Nothing is Impossible"

JDB
Starting Member

1 Post

Posted - 2007-02-12 : 14:20:57

I'm trying to select data from another server also.

I have linked the server by entering this.

EXEC sp_addlinkedserver
'myservername',
N'SQL Server'

When I try my select statement from query analyzer:

select * from [myservername].[mydbname].dbo.[viewOrTablename]

I receive the error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Any help will be greatly appreciated.
Thanks much.

WATERKID
Starting Member

6 Posts

Posted - 2007-03-14 : 17:35:27

Im one more trying desperately to link to another server and finding it difficult.

I add a linked server2 to server ones linked servers and I can see the tables but no fields are available.

Any suggestions ?

yumyum113
Starting Member

31 Posts

Posted - 2007-03-15 : 15:23:28

Hi casati74,

First you create a linked server like this on server 2:

sp_addlinkedserver @server='linkedserver1',@srvproduct='',@provider='SQLOLEDB',@datasrc='SQLserver1'

Then add a linked server login like this also in server 2:

sp_addlinkedsrvlogin 'linkedserver1','false',null,'sa','password'

You could look at books online for the descriptions of the parameter

Then when you want to query table1 of database1 in server1 from server2 it will be something like this

select * from linkedserver1.database1.dbo.table1

Hope this helps.

-yumyum113

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources