locked
Checking SQL Server Connectivity throught TSQL RRS feed

  • Question

  • Team,

    Do you guys have any suggestions on how to test SQL Server connetivity through TSQL?

    I need to create a condition similar to:

    IF SQLInstanceA is Up and running, proceed to this step

    ELSE, fail the procedure.

    In SSIS, I use the AcquireConnection .NET method using a script component.

    Do you have any suggestions on how to do this in TSQL?

    Maybe SELECT COUNT(*) FROM sys.sysdatabases?


    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com
    Monday, November 21, 2011 9:16 PM

Answers

  • Team,

    Do you guys have any suggestions on how to test SQL Server connetivity through TSQL?

    I need to create a condition similar to:

    IF SQLInstanceA is Up and running, proceed to this step

    ELSE, fail the procedure.

    In SSIS, I use the AcquireConnection .NET method using a script component.

    Do you have any suggestions on how to do this in TSQL?

    Maybe SELECT COUNT(*) FROM sys.sysdatabases?


    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com


    Hi sqlsantos !

    You can ping the SQL Server to check the connectivity using xp_cmdshell. Find the MachineName and Ping your SQL Server machine.

    SELECT SERVERPROPERTY('MachineName')
    EXEC xp_cmdshell 'ping [MachineName]'
    


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

    • Marked as answer by sqlsantos Wednesday, November 23, 2011 8:06 PM
    Monday, November 21, 2011 10:07 PM
    Answerer