sys.sp_testlinkedserver

Hot Topic

Conversations & Machine Restart
Setting up Query Notification and Performance issue
Slow backups and restores
SQLServer uninstall
Express and Developer - performance counter errors
Updating Records Instead of Insert
Viewing Asian Fonts in SQL Server Management Studio
Logging in SSIS
Failure audit - the basics
Graceful Exit...

SQL Server

Need to convert NULL values to 0, (zero) in order to perform math calculations
Connection to remote server fails, but not to LAN-Server
One large database or many small....
how can I obtain message_id?
How to open a RDL report in Report Builder ?
Linked Server Connectivity Error
SET NEW_BROKER statement hangs system
Create new database as a user instance
transaction inside a data flow task
help with query syntax

sys.sp_testlinkedserver

According to BOL extended stored procedure sys.sp_testlinkedserver returns 0 on successfully connecting to linked server and 1 when it fails. But I tried this

Created a linked server to default instance of 2000 called TEST

and used following script to test the extended stored procedure and instead of printing my PRINT statement it generated error

-- CHECK LINKED SERVER CONNECTION
DECLARE @ServerName sysname
DECLARE @a int
SET @ServerName = 'TEST'
IF EXISTS(SELECT 1 FROM master.dbo.sysservers WHERE srvname LIKE @ServerName)
BEGIN
EXEC @a = sys.sp_testlinkedserver @servername = @ServerName
IF @a = 0
print 'LINKED SERVER ''' + ISNULL(@ServerName,'') + ''' IS CONNECTED.'
ELSE
print 'LINKED SERVER ''' + ISNULL(@ServerName,'') + ''' IS NOT CONNECTED!'
END
ELSE
BEGIN
PRINT 'LINKED SERVER '''+ ISNULL(@ServerName,'') + ''' DOES NOT EXIST!'
END

Here is the error message

----------------------------------------------------------------------------------------------------------

OLE DB provider "SQLNCLI" for linked server "TEST" returned message "Invalid authorization specification".

Msg 7399, Level 16, State 1, Procedure sp_testlinkedserver, Line 1

The OLE DB provider "SQLNCLI" for linked server "TEST" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1

Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "TEST".

----------------------------------------------------------------------------------------------------------

Can anyone guess why



Answer this question

sys.sp_testlinkedserver

  • Tryin2Bgood

    This is a known issue with sp_testlinkedserver. There are cases where it will still throw error. We will consider fixing the behavior for a future version of SQL Server. Only way now is to use a TRY..CATCH block to catch the exceptions. See the blog post below for a sample:
     
     
    Btw, you may want to send feedback in the BOL topic page to correct the documentation.


  • chub xbox

    Unfortunately there is no way to control this. When you give a non-existant server name, SQL Server is trying to resolve the name of the server using Windows API - this means that depending on the network protocols and your network topology it can take several minutes. For example, DNS name resolution is more than 1 1/2 minutes.
    Btw, you can use Windows Registry tweaks to control some of the timeout values. Search MSKB for more details.


  • Val P

    What do you mean by external source I ran the script in Management Studio.

  • aztec2_step

    You're running this from an external source...not from within SQL Server Analyzer or Management Studio...

    It's trying to authenticate you through a portal.

    My guess, you're using ASP or PHP and you don't have authentication privileges through SQL Server authentication.

    Add ASP.net or PHP with a password to your connection string.

    Adamus



  • johnny_no1_boy

    Thank you I will submit feedback in BOL topic page.

  • GiampaoloSanRemo

    I added this thread and i got a message saying error occured in forum so I tried again and 3 seperate threads got created.

    Tried to delete ...aint workin!!!!

    Moderator plz delete 2 of those extra threads please!

  • Ariel Valentin

    if you execute

    sys.sp_testlinkedserver @servername = N'NON_EXISTANT_SRVR'

    on a server that does not exist this call hangs forever.

    I'm not quite sure if it's forever but 6 minutes plus is way to long.

    Is there anything that can be done to make this command usable



  • sys.sp_testlinkedserver
Answer this question