sp_addlinkedserver - 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

General SQL Server Forums

New to SQL Server Programming

sp_addlinkedserver

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2010-09-29 : 22:13:08

EXEC sp_addlinkedserver @server='YOURSERVER'
, @srvproduct=''
, @provider='SQLOLEDB'
, @datasrc='CMISAPP'

i ran this script to link 'MYSERVER' to 'YOURSERVER' server. Now, how to select table records from 'YOURSERVER'?

thanks..

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 22:47:38

[code]select * from OPENQUERY(linkedservername,'select * from databasename..tablename')[/code]

PBUH

chriztoph
Posting Yak Master

184 Posts

Posted - 2010-09-29 : 22:57:52

OLE DB provider "SQLNCLI10" for linked server "YOURSERVER" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "YOURSERVER" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 5, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [5].

got this error when i execute 'select * from OPENQUERY(YOURSERVER,'select * from CMISAPP..a')'

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 23:20:24

You need to enable distribued queries in the server.Copy this script to a query analyzer and run & after that try the OpenQuery


EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1'
RECONFIGURE WITH OVERRIDE

PBUH

chriztoph
Posting Yak Master

184 Posts

Posted - 2010-09-29 : 23:33:09

i'm able to run the OPENQUERY on the Server but on my computer i got this error:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

i already added the Login accounts in the Security of the Linked Server that i'm using..

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 23:36:43

What happens if select the option 'Not be made" in the Security of the Linked Server ?

PBUH

chriztoph
Posting Yak Master

184 Posts

Posted - 2010-09-29 : 23:42:00

Same result.

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 23:47:57

See if this helps.

http://support.microsoft.com/kb/925001

PBUH