Post reply

test to see if the SQL server is online?

  • June 27, 2006 at 5:12 am

    #173288

    does anyone know of an easy way to test if the SQL server is online and working from a remote program? I've tried using a function that actually tries to connect to a Stored procedure to see if it exists, if it doesn't then the server must be down, that that could take up to 2 mins to return an error

  • June 27, 2006 at 11:52 am

    #646166

    Execute the connection method / function of your remote program...  (I am making the assumption that you are the programer), set your connection timeout low, if the connection succeeds then the server is running, if the connection fails then either the server isn't running or you haven't supplied appropriate connection params. 

    The other way is to check the status of the MSSQLSERVER service (or service for a named instance)

    -

  • June 28, 2006 at 1:50 am

    #646292

    Thanks Jason

  • June 28, 2006 at 9:06 am

    #646395

    is it possible to set the server timeout in vb code? or in a stored procedure?

  • June 28, 2006 at 12:19 pm

    #646438

    I would assume that it is possible in VB. As for a stored procedure no. Besides, if you can execute a stored procedure you have already connected to the SQL Server. By the way ... the simoplest check once you have connected to a SQL Server is:

    select @@servername

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • June 28, 2006 at 2:18 pm

    #646488

    here is a sample connection that I use with VB.  Set the connection timeout to 30 (lower values still take 30 seconds, part of the MDAC I assume)

    Dim oConn As ADODB.Connection

    oConn.Provider = "sqloledb"

    oConn.Properties("Data Source").Value = sServername

    oConn.Properties("Initial Catalog").Value = sDatabase

    oConn.Properties("User ID").Value = sUsername

    oConn.Properties("Password").Value = sPassword

    oConn.CommandTimeout = 0

    oConn.ConnectionTimeout = 30

    oConn.Open

    If oConn.State = adStateOpen Then MsgBox "Connection Succeeded"

    If oConn.State = adStateOpen Then oConn.Close

    Add references for msado27 (Microsoft ActiveX Data Objects 2.7 Library) or 2.8 etc

    -

  • June 29, 2006 at 2:33 am

    #646576

    Thanks Jason, that's a great help

Viewing 7 posts - 1 through 6 (of 6 total)

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