alien linked servers
-
in my in environment servers may go be on and off at anytime
so my management reports call sp_testlinkedserver to get to find out if there are on
this works for a 200 servers but for the aliens 3
the linked server test connection say ok
i can't remote into any of the 3
if you run a query it fails
and if you use SSMS to look at the catalog you can see all the databases still
-
Sounds like permissions. sp_testlinkedserver only tests the connection it doesn't check any permissions. I don't know what you mean by alien in this context.
What error do you get then you try to run the query
-
look at the metadata that is returned from eXEC sp_tables_ex MyAlienServer
it should return a list of items, and you could potentially drill down from there.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
FridayNightGiant (6/13/2016)
I don't know what you mean by alien in this context.Looks like an alien server:
-
after it says test ok
then get this error
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Session Provider: Connection has been closed by peer [xFFFFFFFF]. (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3368&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
-
dschlobohm (6/13/2016)
after it says test okthen get this error
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Session Provider: Connection has been closed by peer [xFFFFFFFF]. (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3368&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
does exec sp_tables_ex work? what kind of server is the linked server? SQL? DB2? mySQL? Oracle?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
sp_tables_ex returns
OLE DB provider "SQLNCLI11" for linked server "DMXBIOPPVWD01" returned message "Unspecified error".
OLE DB provider "SQLNCLI11" for linked server "DMXBIOPPVWD01" returned message "Communication link failure".
OLE DB provider "SQLNCLI11" for linked server "DMXBIOPPVWD01" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Connection has been closed by peer [xFFFFFFFF].
yes they are sqlservers
@srvproduct=N'SQL Server'
i think i will use sp_tables_ex instead on sp_testlinkerserver
since i should be able at error trap it
-
the first handful of articles from my search points to networking issues, and not anything with SQL server itself.
[this article mentions Distributed Transaction Coordinator on the remote linked server needs to be enabled, and OP reported a router issue
http://www.sqlservercentral.com/Forums/Topic1528061-2799-1.aspx
this link identifes a specific known issue with a specific nic card, and a couple other ideas.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply