Jump to content
Nunos

Reading Data Returned from @ComSpec

Recommended Posts

Nunos

I am trying to make a script to backup several MS SQL Server Express systems and I find myself on some systems needing to list the Server names and database instances to populate a dropdownlist or maybe a variable in an ini file. I am learning SQLcmd utility a bit and it has a way to list the server names but I am not sure how to capture the output because the switch for the utility doesn't accept any additional switches. The command I have below runs the utiity and returns all the server names and instances to a console window. Can someone advise me if there is a way to get this information into say an ini file or variables in autoIT that I can reference? 

 

Runwait(@ComSpec & ' /c sqlcmd -L ')

 

Share this post


Link to post
Share on other sites
BigDaddyO

you have to switch to Run() and use either StdoutRead or Stderrread.  both those functions have nice examples in the help file


hmm... I guess I have to have a signature...

Share this post


Link to post
Share on other sites
Nunos

Thank you for the assistance I have data in an array and I also was able to write it to a text file now I need to test out how to trim up the data to get the useful bits out to assign to variables I think.  Below is what I have so far in case anyone has ideas.

 

#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3> 

Local $ServerInstancesLog = "C:\Logs\SQLCMDOutput.txt"


Local $iPID = run(@ComSpec & ' /k sqlcmd -L ',"", @SW_HIDE, $STDOUT_CHILD)

; Wait until the process has closed using the PID returned by Run.
    ProcessWaitClose($iPID)

    ; Read the Stdout stream of the PID returned by Run. This can also be done in a while loop. Look at the example for StderrRead.
    Local $sOutput = StdoutRead($iPID)

Local $aArray = StringSplit(StringTrimRight(StringStripCR($sOutput), StringLen(@CRLF)), @CRLF)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "", "It appears there was an error trying to retrieve the SQL Server's and Database Instance Names.")
    Else
        ; Display the results.
        _ArrayDisplay($aArray)
    EndIf


;FileWrite($ServerInstancesLog,$sOutput)

And this is the output in an array I changed the Server1, Server2, Database1, Database2 names to protect the innocent.

 

Row|Col 0
[0]|6
[1]|
[2]|Servers:
[3]|    Server1\Database1
[4]|    Server2\Database2
[5]|
[6]|C:\Scripts\Workstation\Script

 


 

Share this post


Link to post
Share on other sites
Nunos

Can someone teach me how to read the information in the array and record it to variables? 

The sqlcmd returns an array and I need to capture the servername and database instance to put in dropdown boxes in a gui. I thought maybe ArrayFindAll and search for the \ between the servername and the database instance but I couldn't get it to work at all no matter what I tried I get the msgbox that the array wasn't created not to mention I am not sure how to copy or get the data after I find it. 

Edited by Nunos

Share this post


Link to post
Share on other sites
Nunos

Just following up to see if anyone can assist me with how to do the above?

Share this post


Link to post
Share on other sites
gruntydatsun

here's one way to do it

#include <array.au3>

dim $asInput = ["6","","Servers:","   Server1\Database1","   Server2\Database2","","C:\Scripts\Workstation\Script"]

$sFlat = _ArrayToString($asInput,",")

$asList = StringRegExp($sFlat,"\h*(\w+)\\(\w+)\h*,",3)

_ArrayDisplay($asList,"Final answer")

reg exp could be tightened up if you have a standard naming convention on servers and databases.

 

 

Share this post


Link to post
Share on other sites
Nunos

Thank you gruntydatsun I will see if I can modify that to work with the above sqlcmd -L part above. In answer to your question the server name I would be most interested in usually is standardized to be DW-MWS if that helps at all. My understanding is that the SQLCMD -L cmd actually scans the entire subnet so depending on the location that can return a lot of server names and database instances in fact at one site I had 18 servers and the vast majority had 3+ database instances so if you know a better way to make this work better I am very open to learning it. 

I will try tonight after work to merge the code you have with the other and post the results thank you again for the help.

Share this post


Link to post
Share on other sites
Nunos

Finally found time to try this sorry for the slow reply. The code runs and returns a partial name of the Server. It seems to ignore the hyphen and left in the DW-MWS name. I am not sure what to change can you assist me? Also what would be the easiest way to deal with the results to get them into the variables perhaps write them to an ini file as two lines Server Name and Database Name with the iniwrite function? Or is there a better way?

Share this post


Link to post
Share on other sites
Nunos

Following up to see if anyone can help me get the full name then assign that to variables to put into a dropdown list in the gui?

Share this post


Link to post
Share on other sites
kylomas

Nunos,

The example that you posted does not contain a hyphen.  Can you post a representative example of your data (before you turn it into an array)?

kylomas

edit: Incidentally, have you tried "LC" as a parameter to SQLCMD?

Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
kylomas

nunos,

Try this...

#include <Array.au3>

Local $str
$str &= 'Servers:' & @CRLF
$str &= '' & @CRLF
$str &= '   Server1\Database1-MyDB1' & @CRLF
$str &= '   Server2\Database2-MyDB1' & @CRLF
$str &= '   Server2\Database2-MyDB2' & @CRLF
$str &= '   Server2\Database2-MyDB3' & @CRLF
$str &= '   Server3\Database1-Some-One-Elses-DB' & @CRLF
$str &= '' & @CRLF
$str &= 'C:\Scripts\Workstation\Script' & @CRLF

$spattern = '\h+([\w\\-]+)'

;msgbox(0,'',$str)

_ArrayDisplay(StringRegExp($str, $spattern, 3))

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
kylomas

nunos,

You might get the array into a combo box like this..

#include <Array.au3>
#include <GUIConstantsEx.au3>

Local $str
$str &= 'Servers:' & @CRLF
$str &= ' ' & @CRLF
$str &= '   Server1\Database1-MyDB1' & @CRLF
$str &= '   Server2\Database2-MyDB1' & @CRLF
$str &= '   Server2\Database2-MyDB2' & @CRLF
$str &= '   Server2\Database2-MyDB3' & @CRLF
$str &= '   Server3\Database1-Some-One-Elses-DB' & @CRLF
$str &= ' ' & @CRLF
$str &= 'C:\Scripts\Workstation\Script' & @CRLF

$spattern = '\h+([\w\\-]+)'

;msgbox(0,'',$str)

Local $array = StringRegExp($str, $spattern, 3)

; to populate a combo box from the array do...

Local $gui010 = GUICreate('CB Example')
Local $cmb010 = GUICtrlCreateCombo('', 20, 20, 200, 300)

GUICtrlSetData($cmb010, _ArrayToString($array), $array[0])

Local $btn010 = GUICtrlCreateButton('My Freak"in Button', 20, 360, 360, 20)
GUICtrlSetState($btn010, $gui_focus)

GUISetState()

While 1
    Switch GUIGetMsg()
        Case $gui_event_close
            Exit
        Case $btn010
            MsgBox(0, '', GUICtrlRead($cmb010))
    EndSwitch
WEnd

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
Nunos

I am sorry I didn't make it more clear the SQLCMS -L returns the servername and database name in what I think is an array, not sure if it is 1 or 2 dimensional cause I don't understand arrays very well. The servernames across the network are fairly standardized, usually either DW-MWS or MD-MWS. The database names are not they are all over the place. Our goal is to try to get them into matched pairs in a drop down window from a gui so we can run the script to pickup all the server and database names and then choose the one we need to modify and run other cmds against it that we hope to have in another part of the gui.

 

I hope this makes it clearer. I will try to make sense of your above posts and add them into what I have working so far and post the results. 

 

EDIT: I just tried the mentioned SQLCMD -LC from an elevated cmd prompt and it said it wasn't recognized so I tried just SQLCMD _L -? and it said the -L switch can not be used with other switches.

 

Thank you for your help and time.

Edited by Nunos

Share this post


Link to post
Share on other sites
kylomas

Nunos,

Do not have SQL Server installed so cannot verify.  This is the doc I got the "Lc" from (note the case).

kylomas

Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
Nunos

You are correct I used a capital C. The output from an elevated command prompt I ran on a test computer we have here is below. 

 

C:\WINDOWS\system32>sqlcmd -Lc
(local)
DW-MWS
DW-MWS\DINERWARE

 

I am still trying to merge the solutions you mentioned above with what I had that created the array. I will try to post back when I get back to work this week if I can figure it out. Thank you again for the help I am sorry I am struggling so much but this array thing is confusing to me. I don't understand how to create it and then get stuff back out of it once it is created. :(

Share this post


Link to post
Share on other sites
Nunos

Below is the script I have tried to modify to work but when it runs I get the msgbox stating that the array wasn't created. 

 

#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <StringConstants.au3>


Local $iPID = run(@ComSpec & ' /c sqlcmd -Lc ',"", @SW_HIDE, $STDOUT_CHILD)

; Wait until the process has closed using the PID returned by Run.
    ProcessWaitClose($iPID)

Local $sOutput = StdoutRead($iPID)

;msgbox(0,'',$sOutput)

Local $spattern = '\h+([\w\\-]+)'
Local $aArray = StringRegExp($sOutput, $spattern, 3)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "", "It appears there was an error trying to retrieve the SQL Server's and Database Instance Names.")
    Else
        ; Display the results.
        _ArrayDisplay($aArray)
    EndIf

 

Share this post


Link to post
Share on other sites
kylomas

Nunos,

Create a file of the output that you get so we can work with "live" data...

kylomas

edit: ...and post it here...

Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
Nunos
#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <StringConstants.au3>
#include <FileConstants.au3>

Local $iPID = run(@ComSpec & ' /c sqlcmd -Lc ',"", @SW_HIDE, $STDOUT_CHILD)

; Wait until the process has closed using the PID returned by Run.
    ProcessWaitClose($iPID)

Local $sOutput = StdoutRead($iPID)

msgbox(0,'',$sOutput)

$spattern = "\h+([\w\\-]+)"
Local $aArray = StringRegExp($sOutput, $spattern,3)
MsgBox(262144, 'Debug line ~' & @ScriptLineNumber, 'Selection:' & @CRLF & 'StringRegExp($sOutput, $spattern,3)' & @CRLF & @CRLF & 'Return:' & @CRLF & StringRegExp($sOutput, $spattern,3)) ;### Debug MSGBOX
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "", "It appears there was an error trying to retrieve the SQL Server's and Database Instance Names.")
    Else
        ; Display the results.
        _ArrayDisplay($aArray)
    EndIf

I am not sure what you mean but in my tries this morning before work I added that MsgBox Debug line ad I receive a MsgBox that has the previous line listed and then a Return Code of 1.

 

As far as just running the SQLCMD -Lc and putting that in a text file on this particular one I get the three lines below.

 

(local)
DW-MWS
DW-MWS\DINERWARE

 

The last line in this case would be the Server Instance and Database Instance we would need the end user to pick. 

 

Please advise how to create the output file you need and I will make that happen and thank you again for all the help. :D

 

Share this post


Link to post
Share on other sites
jdelaney

This might be easier:

run(@ComSpec & ' /c sqlcmd -Lc  > output.txt')

Then just _filereadtoarray the output.txt.

I don't recall if sqlcmd eats the pipe, but if it does, there is another parameter you can add to output to a file.

Edited by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×