SQP Query in a cmd script

[Yardyy]

Hi,

Is it possible to run a batch file/ script, that calls a sql query, then returns the data back to the script.

Esentially what i want to achieve is, to check if all four sql servers are up and running..



Many Thanks
Yurov Ardyy

 

[onpnt]

You wouldn't use TSQL to do this. You can enumerate all the sql servers on the network using VBS and compare that to something like a XML document containing what servers you wish to validate against. Using the XML (or any external file) will allow you to easily add/remove instances without altering the script.

I would imagine there are several examples for enumerating sql server instances out there. Just google it.

You can then run this from a DTS/SSIS depending on version package as a scheduled tasks

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[Yardyy]

sounds good i will try to find some docs for this. thanks again

Many Thanks
Yurov Ardyy

 

[mutley1]

Yurov,

Here is a script i use in a DTS package. Basically, create a text file with a list of your 4 servers in it in a specific directory (in this script i use D:\SQLServicesAnalysis and the text file with my servers listed is D:\SQLServicesAnalysis\LISTSQLServers.txt. You might want to change this to suit your needs. It will output to a file called SQLServicesStatus.txt

Create this script as an ActiveX Task in the package.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()

On Error Resume Next

'Set the report output directory (end with a backslash)
dir = "D:\SqlServicesAnalysis\"
'Set the text report file name
filename = "SQLServicesStatus.txt"


'open the file system object
Set oFSO = CreateObject("Scripting.FileSystemObject")
set WSHShell = wscript.createObject("wscript.shell")
'open the data file
Set oTextStream = oFSO.OpenTextFile("D:\SqlServicesAnalysis\LISTSqlServers.txt")
'make an array from the data file
RemotePC = Split(oTextStream.ReadAll, vbNewLine)
'close the data file
oTextStream.Close

For Each strComputer In RemotePC

Set objWMIService = GetObject _
        ("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colRunningServices = objWMIService.ExecQuery _
    ("Select * from Win32_Service")
    For Each objService in colRunningServices
        If InStr(UCase(objService.DisplayName), "SQL") > 0 Then
        Report = Report & vbCrLf & "Computer ," & strComputer & ", reports service ," & objService.DisplayName  & ", is ," & objService.State 
        End If
    Next
set colRunningServices = Nothing
set objWMIService = Nothing
Next

If Not oFSO.FolderExists(dir) Then
    oFSO.CreateFolder(dir)
End If
Set ts = oFSO.CreateTextFile (dir & filename, True)
ts.write report



	Main = DTSTaskExecResult_Success
End Function

It gets all the SQL Services, so you might want to strip out MSADHELPER. What I do in my full package is run this, import the text file into a table in the database and then strip out ADHELPER then dump the results in Excel. As you have 4 servers you might not want to go that far, but I do because we have 50 odd to check.

HTH.

M.

 

[Yardyy]

Thanks very mch for this, will try this out, and let you know the results.

Many Thanks
Yurov Ardyy

 

[onpnt]

What version of SQL Server is this going to run on?

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[Yardyy]

Version 7 on all servers, i think sp3

Many Thanks
Yurov Ardyy

 

[mutley1]

That should work on a SQL 7 box, definately on a 2000 - not played with 2005 yet.