execute query using linked server

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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

execute query using linked server

Author

Topic

Amirthalingam
Starting Member

7 Posts

Posted - 2011-04-04 : 05:37:13

I got this error when i execute query after created linked server

OLE DB provider "SQLNCLI10" for linked server "xxx" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "xxx" 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 126, Level 16, State 1, Line 0
VIA Provider: The specified module could not be found.

Can anybody help me how to resolve this error

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-04-04 : 10:48:51

Sounds like you mis-typed the name (or IP) of the linked server. How did you create the linked server?

Amirthalingam
Starting Member

7 Posts

Posted - 2011-04-05 : 04:52:16

I executed this below query
its working

EXEC sp_addlinkedserver 'SERVERNAME','SQL server'

EXEC sp_addlinkedsrvlogin
@rmtsrvname=N'SERVERNAME',
@useself=N'false',
@locallogin=null,
@rmtuser=N'UN',
@rmtpassword=N'PWD';

Amirthalingam
Starting Member

7 Posts

Posted - 2011-04-06 : 07:49:42

I executed this below queries to make a linked server(from sql server 2008 to sql server 2000).

EXEC sp_addlinkedserver
@server=N'servername',
@srvproduct=N'',
@provider=N'SQLNCLI10',
@datasrc=N'servername';

EXEC sp_addlinkedsrvlogin
@rmtsrvname='servername',
@useself=N'false',
@locallogin=null,
@rmtuser=N'UN',
@rmtpassword=N'PWD';

After added i tried to execute this query
SELECT * FROM [Server].db.dbo.tablename

but i got below error

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "servername" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "SQLNCLI10" for linked server "servername". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

Amirthalingam
Starting Member

7 Posts

Posted - 2011-04-06 : 07:50:38