SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 select from another database
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

casati74
Posting Yak Master

Italy
109 Posts

Posted - 09/21/2006 :  03:57:12  Show Profile
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)

Singapore
17638 Posts

Posted - 09/21/2006 :  04:02:22  Show Profile
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]



KH

Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5556 Posts

Posted - 09/21/2006 :  04:23:11  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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"
Go to Top of Page

casati74
Posting Yak Master

Italy
109 Posts

Posted - 09/21/2006 :  04:38:53  Show Profile
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5556 Posts

Posted - 09/21/2006 :  04:44:56  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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"
Go to Top of Page

JDB
Starting Member

1 Posts

Posted - 02/12/2007 :  14:20:57  Show Profile
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.
Go to Top of Page

WATERKID
Starting Member

6 Posts

Posted - 03/14/2007 :  17:35:27  Show Profile
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 ?
Go to Top of Page

yumyum113
Starting Member

Philippines
31 Posts

Posted - 03/15/2007 :  15:23:28  Show Profile  Send yumyum113 a Yahoo! Message
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000