Post reply

Check for linked server - supress messages (TSQL/PHP)

  • August 12, 2015 at 9:37 am

    #398891

    Hello community!

    I found a script to check if a linked server is online.

    ALTER PROCEDURE [dbo].[uspLinkedServerOnline]

    @isOnline bit OUTPUT

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @serverName SYSNAME = N'NB011\SQLDEV2014';

    BEGIN TRY

    EXEC master.sys.sp_executesql N'EXEC sp_testlinkedserver @servername;', N'@serverName SYSNAME', @serverName

    SELECT @isOnline = 1;

    END TRY

    BEGIN CATCH

    SELECT @isOnline = 0;

    END CATCH

    END

    If I stop the linked server, the stored procedure returns "0" in the results window. In the "messages" window i get the following warnings

    << OLE DB provider "SQLNCLI11" for linked server "NB011\SQLDEV2014" returned message "Login timeout expired". >>

    << OLE DB provider "SQLNCLI11" for linked server "NB011\SQLDEV2014" 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." >>

    The stored procedure is executed successfully when executed in SSMS. I checked the profiler and see that the SP is executed and completed when called from the PHP script. However, the calling function in PHP gets the messages as the result and the part of the script fails then.

    Is there an option to suppress the messages within a specific SP?

    Or is this a specific function in PHP?

    Thank you for your help

  • August 13, 2015 at 12:32 am

    #1819560

    Found a solution for this in the configuration of the sqlsrv.

    Before the connection is opened via sqlsrv_connect i execute "sqlsrv_configure('WarningsReturnAsErrors', 0)";

    Now I get the result from the SP that I expected.

    PHP configuration for the connection:

    $sqlServerName = 'HOST\INSTANCE';

    sqlsrv_configure('WarningsReturnAsErrors', 0);

    $sqlConnInfo = array("Database" => "dbname", "UID" => "user", "PWD" => "password", "CharacterSet" => "UTF-8");

    $sqlLink = sqlsrv_connect($sqlServerName, $sqlConnInfo);

    if(!$sqlLink)

    {

    echo "Der MSSQL-Datenbankserver konnte nicht erreicht werden

    ";

    die( print_r( sqlsrv_errors(), true));

    }

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply