Cant get script to run query against specific data base???

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of PSrookie PSrookie 1 year, 7 months ago.

  • Author
    Posts
  • #32636
    Profile photo of PSrookie
    PSrookie
    Participant

    Hello guys,
    Supper new to powershell. I am trying to edit a script that already works and iam having trouble. The script basically goes to SQL server, runs query and it runs it against all the data bases on sql server and appends the query output to Excel file.

    What i wanted to do is have the script execute query against specific data bases not all of them and output the data to csv file.

    Here is the script that works:

    $db = invoke-sqlcmd -query "select dbname from rsadmin..tblArchive" | select -ExpandProperty dbname
                    foreach ($dba in $db) {
                                    try {
                                                    invoke-sqlcmd -query "use $dba select '$dba', aetitle, case command when 1 then 'Archive' when 16385 then 'Retrieve' end as command, COUNT(distinct studyuid) as StudyCount, sum(bytessent/1024/1024) as MB_Sent, sum(bytesread/1024/1024) as MB_Read, SUM(case when datediff(mi, datestart, dateend)dateadd(minute,-60,getdate()) and CompletionCode=0 and Command in (1,16385) group by AETitle, command" -ConnectionTimeout 0 -QueryTimeout 65535 -abortonerror -severitylevel 24 |export-csv c:\temp\LastHour.csv -Append
     
                                                    }
                                    Catch {}}
    

    Here is my version. What i have done is simply created an array of specific data base name that i know exist on my SQL server. and i am passing that to the foreach loop. In the first version of the script it goes and obtains these names and saves them to $db. In my version i place the names in the array, because i dont want it to grab all the data base names, just the ones i have specified to the $db variable

    This is my version:

    #CREATING "$db" array and assigning the specific data base names to it
    
    $db = @("DATABASE01", "DATABASE02", "DATABASE03")
    
                    foreach ($dba in $db) {
                                    try {
                                                    invoke-sqlcmd -query "use $dba select '$dba', aetitle, case command when 1 then 'Archive' when 16385 then 'Retrieve' end as command, COUNT(distinct studyuid) as StudyCount, sum(bytessent/1024/1024) as MB_Sent, sum(bytesread/1024/1024) as MB_Read, SUM(case when datediff(mi, datestart, dateend)dateadd(minute,-60,getdate()) and CompletionCode=0 and Command in (1,16385) group by AETitle, command" -ConnectionTimeout 0 -QueryTimeout 65535 -abortonerror -severitylevel 24 |export-csv c:\temp\LastHour.csv -Append
     
                                                    }
                                    Catch {}}
    

    When i run this i get error:

    
                                    At line:3 char:50
    + $db = @("DATABASE01", "DATABASE02", "DATABASE03")CLS
    +                                                  ~~~
    Unexpected token 'CLS' in expression or statement.
        + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
        + FullyQualifiedErrorId : UnexpectedToken
    

    Thank you for your help!

  • #32638
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    Are you sure the script you're running is the script you're editing and that you're not editing one version and running a different version? The error implies that you have CLS (PowerShell alias for Clear-Host) erroneously appended to the end of line 3.

    If you Copy & Paste the script you posted straight from this website into the ISE and hit Run Script, do you still get the error?

  • #32646
    Profile photo of PSrookie
    PSrookie
    Participant

    Matt,

    Thanks for your help. I think you were onto something there. I was sure that i was run my version code. In any case i ended up closing Powershell, and start back up, copy/past the code i had edited and it worked the way i expected it.

    I am not sure but i guess it was possible that one version was running and the one i was executing was not? I am not sure, but i am glad that it works now.

    Thanks!

You must be logged in to reply to this topic.