At my part this code works without hanging up even when in some cases the connection cannot be made to the SQL Server because SSPI fails, the key is to check for connection state after each connection attempt.
[code]if en_sql_server = "y" then
if debugging > "0" then wscript.echo "SQL info" end if
oReg.GetStringValue HKEY_LOCAL_MACHINE,"SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion\","CSDVersion", db_version If (IsNull(db_version)) Then oReg.GetStringValue HKEY_LOCAL_MACHINE,"SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion\","CurrentVersion", db_version End If oReg.GetDWORDValue HKEY_LOCAL_MACHINE,"SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\","LoginMode", i Select Case i Case 0 db_login_type = "Allow SQL Server Authentication only." Case 1 db_login_type = "Allow Windows Authentication only." Case 2 db_login_type = "Allow Windows Authentication or SQL Server Authentication." Case else db_login_type = "Security type unknown." End Select
db_port = "" oReg.GetStringValue HKEY_LOCAL_MACHINE,"SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\","TcpPort", db_port
presult = "" presult = presult & " <database>" & vbcrlf presult = presult & " <db_type>SQL Server</db_type>" & vbcrlf presult = presult & " <db_version>" & escape_xml(db_version) & "</db_version>" & vbcrlf presult = presult & " <db_port>" & db_port & "</db_port>" & vbcrlf ' todo - fix this presult = presult & " <db_login_type>" & db_login_type & "</db_login_type>" & vbcrlf presult = presult & " <db_service_state>" & en_sql_server_state & "</db_service_state>" & vbcrlf presult = presult & " </database>" & vbcrlf if debugging > "0" then wscript.echo presult end if result = result & presult
if en_sql_server_state = "Running" then on error resume next Connected = 1 strBaseConnection ="Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;DATA SOURCE=" & strComputer Set objDBConnection = CreateObject("ADODB.Connection") objDBConnection.Open strBaseConnection
if debugging > "1" then if objDBConnection.State = Connected then wscript.echo "Status: Connected to" & strComputer & "; " & objDBConnection.Provider & " " & objDBConnection.Version else wscript.echo "Status: Connection failed to " & strComputer & vbcrlf & "Connection string: " & strBaseConnection end if end if if objDBConnection.State = Connected then strQuery = "Select name, dbid, cmptlevel, filename, CONVERT(VARCHAR(19), crdate, 120) as crdate from sysdatabases order by name" Set objRS = CreateObject("ADODB.RecordSet") objRS.open strQuery, objDBConnection, 1
if objRS.State = Connected then result = result & " <database_details>" & vbcrlf cnt = 0 Do Until objRS.Eof ' get the filesize cnt = cnt +1 filename = replace(objRS.Fields("filename"), "\", "\\")
if debugging > "1" then wscript.echo "Step " & cnt & " filename: " & filename end if
set colFiles = objWMIService.ExecQuery ("Select FileSize from CIM_Datafile Where name = '" & filename & "'") for each objFile in colFiles filesize = int(objFile.FileSize / 1024 / 1024) next
presult = "" presult = presult & " <details>" & vbcrlf presult = presult & " <details_name>" & CStr(objRS("Name")) & "</details_name>" & vbcrlf presult = presult & " <details_internal_id>" & escape_xml(objRS("dbid")) & "</details_internal_id>" & vbcrlf presult = presult & " <details_compatibility_mode>" & CStr(objRS("CmptLevel")) & "</details_compatibility_mode>" & vbcrlf presult = presult & " <details_filename>" & escape_xml(objRS("FileName")) & "</details_filename>" & vbcrlf presult = presult & " <details_current_size>" & escape_xml(filesize) & "</details_current_size>" & vbcrlf presult = presult & " <details_creation_date>" & escape_xml(objRS("crdate")) & "</details_creation_date>" & vbcrlf presult = presult & " </details>" & vbcrlf result = result & presult
if debugging > "1" then wscript.echo presult end if objRS.Movenext Loop nNumberOfDatabases = objRS.RecordCount if debugging > "1" then wscript.echo "DB Count: " & nNumberOfDatabases end if
result = result & " </database_details>" & vbcrlf end if objRS.Close end if objDBConnection.Close On Error Goto 0 end if end if[/code]
|