June 2, 2011 at 9:08 am
Hello Everyone
I am just fooling around writing a little bit is code, just bored here at work. So I am trying some new things that may come in handy in the future.
I would like to query one server, any server on the network, just to see if it is responsive. Is there a function or a query that will tell me either way, if that SQL Instance is responsive and running, or not?
Thanks in advance
Andrew SQLDBA
June 2, 2011 at 9:39 am
June 2, 2011 at 9:41 am
do you want a query or function that returns the status of instances from server? is that?
June 2, 2011 at 9:51 am
Either, it does not matter.
Andrew SQLDBA
June 2, 2011 at 10:05 am
I agree with Laerte, PowerShell's Get-Service will give you more control and IMHO is the right tool for the job.
http://technet.microsoft.com/en-us/library/dd347591.aspx
However, if you must do this through T-SQL, you can use OPENDATASOURCE.
Run this once to reconfigure your server:
EXEC sys.sp_configure
@configname = 'show advanced options',
@configvalue = 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure
@configname = 'Ad Hoc Distributed Queries',
@configvalue = 1
GO
RECONFIGURE WITH OVERRIDE
GO
Then you can do this:
IF EXISTS ( SELECT TOP 1
*
FROM OPENDATASOURCE('SQLNCLI', 'Data Source=.\STD2008R2;Integrated Security=SSPI').[master].sys.objects )
PRINT 'Server is Alive!'
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply