Nunos Posted October 13, 2017 Share Posted October 13, 2017 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 ') Link to comment Share on other sites More sharing options...
BigDaddyO Posted October 13, 2017 Share Posted October 13, 2017 you have to switch to Run() and use either StdoutRead or Stderrread. both those functions have nice examples in the help file Link to comment Share on other sites More sharing options...
Nunos Posted October 13, 2017 Author Share Posted October 13, 2017 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 Link to comment Share on other sites More sharing options...
Nunos Posted October 21, 2017 Author Share Posted October 21, 2017 (edited) 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 October 21, 2017 by Nunos Link to comment Share on other sites More sharing options...
Nunos Posted October 25, 2017 Author Share Posted October 25, 2017 Just following up to see if anyone can assist me with how to do the above? Link to comment Share on other sites More sharing options...
gruntydatsun Posted October 26, 2017 Share Posted October 26, 2017 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. Link to comment Share on other sites More sharing options...
Nunos Posted October 27, 2017 Author Share Posted October 27, 2017 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. Link to comment Share on other sites More sharing options...
Nunos Posted November 29, 2017 Author Share Posted November 29, 2017 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? Link to comment Share on other sites More sharing options...
Nunos Posted December 1, 2017 Author Share Posted December 1, 2017 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? Link to comment Share on other sites More sharing options...
kylomas Posted December 1, 2017 Share Posted December 1, 2017 (edited) 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 December 2, 2017 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 Link to comment Share on other sites More sharing options...
kylomas Posted December 2, 2017 Share Posted December 2, 2017 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 Link to comment Share on other sites More sharing options...
kylomas Posted December 2, 2017 Share Posted December 2, 2017 nunos, You might get the array into a combo box like this.. expandcollapse popup#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 Link to comment Share on other sites More sharing options...
Nunos Posted December 3, 2017 Author Share Posted December 3, 2017 (edited) 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 December 3, 2017 by Nunos Link to comment Share on other sites More sharing options...
kylomas Posted December 4, 2017 Share Posted December 4, 2017 (edited) Nunos, Do not have SQL Server installed so cannot verify. This is the doc I got the "Lc" from (note the case). kylomas Edited December 4, 2017 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 Link to comment Share on other sites More sharing options...
Nunos Posted December 6, 2017 Author Share Posted December 6, 2017 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. Link to comment Share on other sites More sharing options...
Nunos Posted December 8, 2017 Author Share Posted December 8, 2017 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 Link to comment Share on other sites More sharing options...
Nunos Posted December 12, 2017 Author Share Posted December 12, 2017 Bump Link to comment Share on other sites More sharing options...
kylomas Posted December 12, 2017 Share Posted December 12, 2017 (edited) Nunos, Create a file of the output that you get so we can work with "live" data... kylomas edit: ...and post it here... Edited December 12, 2017 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 Link to comment Share on other sites More sharing options...
Nunos Posted December 12, 2017 Author Share Posted December 12, 2017 #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. Link to comment Share on other sites More sharing options...
jdelaney Posted December 12, 2017 Share Posted December 12, 2017 (edited) 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 December 12, 2017 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now