Checking if a SQLServer exists using SQL

I am trying to write a query that looks at a linked server. What I want to
do is return an error through SQL to let me know if that SQLServer instance
is in fact running. Currently I keep getting the following message back
Server: Msg 17, Level 16, State 1,Line 1
SQL Server Does not exist or access denied.
Is there anyway I can trap this error, or a statement that I can use to
determine if it is running?
ThanksPerhaps this type of verification could be best done at the application
level. The following article includes an example of how to use VBScript and
SQLDMO to check the SQLServer.Status property.
http://www.devx.com/vb2themax/Tip/18459
For example:
' show current state of the service
Select Case SQLServer.Status
Case SQLDMOSvc_Paused: lblStatus = "Paused"
Case SQLDMOSvc_Running: lblStatus = "Running"
Case SQLDMOSvc_Starting: lblStatus = "Starting"
Case SQLDMOSvc_Stopped: lblStatus = "Stopped"
End Select
This could be easily incorporated into a VBScript task of a DTS package.
"Miles Cousens II" <mcousens@.clearwater.ca> wrote in message
news:OvCxNKgFGHA.1032@.TK2MSFTNGP11.phx.gbl...
>I am trying to write a query that looks at a linked server. What I want to
> do is return an error through SQL to let me know if that SQLServer
> instance
> is in fact running. Currently I keep getting the following message back
> Server: Msg 17, Level 16, State 1,Line 1
> SQL Server Does not exist or access denied.
> Is there anyway I can trap this error, or a statement that I can use to
> determine if it is running?
> Thanks
>|||Hi Jt,
Thanks for the reply, but I can not do it at application level. Inside one
of my stored procedures, I need to check to see if the linked server is
running. Over the last w one of our SQLServer's stopped and restarted
itself 3 times after the following error was displayed :
Process 4 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
What I would like to do is check to see if the status of the server is
running, if it is not, then do not exectue the code that is looking to
insert a record into the linked server.
Miles
"JT" <someone@.microsoft.com> wrote in message
news:%23bs6AXgFGHA.1452@.TK2MSFTNGP11.phx.gbl...
> Perhaps this type of verification could be best done at the application
> level. The following article includes an example of how to use VBScript
and
> SQLDMO to check the SQLServer.Status property.
> http://www.devx.com/vb2themax/Tip/18459
> For example:
> ' show current state of the service
> Select Case SQLServer.Status
> Case SQLDMOSvc_Paused: lblStatus = "Paused"
> Case SQLDMOSvc_Running: lblStatus = "Running"
> Case SQLDMOSvc_Starting: lblStatus = "Starting"
> Case SQLDMOSvc_Stopped: lblStatus = "Stopped"
> End Select
> This could be easily incorporated into a VBScript task of a DTS package.
> "Miles Cousens II" <mcousens@.clearwater.ca> wrote in message
> news:OvCxNKgFGHA.1032@.TK2MSFTNGP11.phx.gbl...
to
>|||Do you run the insert sql manually or is there a process is executing it? If
it's an application, scheduled job, or DTS package, then you can make a
SQLDMO call.
"Miles Cousens II" <mcousens@.clearwater.ca> wrote in message
news:%23ppiDJhFGHA.216@.TK2MSFTNGP15.phx.gbl...
> Hi Jt,
> Thanks for the reply, but I can not do it at application level. Inside
> one
> of my stored procedures, I need to check to see if the linked server is
> running. Over the last w one of our SQLServer's stopped and restarted
> itself 3 times after the following error was displayed :
> Process 4 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
> SQL
> Server is terminating this process.
> What I would like to do is check to see if the status of the server is
> running, if it is not, then do not exectue the code that is looking to
> insert a record into the linked server.
>
> Miles
> "JT" <someone@.microsoft.com> wrote in message
> news:%23bs6AXgFGHA.1452@.TK2MSFTNGP11.phx.gbl...
> and
> to
>|||What I am actually doing is using Biztalk to take a sales document from
Siebel through Biztalk and into Great PLains. I use the Biztalk SQLAdapter
to poll for the sale on the SIEBEL SQLServer, and then send it to the Great
Plains SQLServer. What I would like to do is in my polling Store Procedure,
test to make sure that the Great Plains SQLServer is running, and if it is
create the sales document for export, if not, then I will just leave the
sale alone and keep retrying the Great Plains server until it can be
reached.
"JT" <someone@.microsoft.com> wrote in message
news:eZIj8QhFGHA.4064@.TK2MSFTNGP10.phx.gbl...
> Do you run the insert sql manually or is there a process is executing it?
If
> it's an application, scheduled job, or DTS package, then you can make a
> SQLDMO call.
> "Miles Cousens II" <mcousens@.clearwater.ca> wrote in message
> news:%23ppiDJhFGHA.216@.TK2MSFTNGP15.phx.gbl...
restarted
package.
want
to
>