locked
Powershell foreach to loop through SQL instances... RRS feed

  • Question

  • Greetings. I'm moving this from the SQL forum as this is a more appropriate spot. I have two PS scripts that accomplish the same goal, written in a different way. However, one way throws an error at the end, even though it succeeds. Being brand spankin' new to PS, I'm trying to figure out why. 

    Script 1 completes, as is written the way I'd prefer to write it, but throws an error message at the end. Not that sqlCMD2.txt is the same as the @sqlStatement1 parameter from Script 2 below:

    PS C:\Users\myUser> foreach ($serverINstances in Get-Content "C:\Users\myUser\PS\serverInstances.txt"){
    $serverInstances
    $filePath =  "C:\Users\myUser\PS\"
    $fileIn = $filePath + "sqlCMD2.txt"
      Invoke-Sqlcmd -ServerInstance $serverInstances -InputFile $fileIn }

    Invoke-Sqlcmd : Value cannot be null.
    Parameter name: ServerInstance
    At line:5 char:3
    +   Invoke-Sqlcmd -ServerInstance $serverInstances -InputFile $fileIn }
    +   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidArgument: (:) [Invoke-Sqlcmd], ArgumentNullException
        + FullyQualifiedErrorId : CannotGetServerInstance,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

    Keep in mind, that error is at the end of the expected result set. 

    Script 2 also returns the result set, but no error at the end. How can script 1 be modified to not return an error? Thanks!

    Script 2:

    foreach ($svr in get-content "C:\Users\myUser\PS\serverINstances.txt"){
    $svr
    $dt = new-object "System.Data.DataTable"
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=msdb;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "
     
    DECLARE @sqlStatement1 VARCHAR(200)
    SET @sqlStatement1 = 'master.dbo.xp_readerrorlog'
    CREATE TABLE #Errors (LogDate DATETIME,ProcessInfo NVARCHAR(50),vchMessage varchar(2000))
    INSERT #Errors EXEC @sqlStatement1
    SELECT LogDate, RTRIM(LTRIM(vchMessage)) FROM #Errors WHERE
    ([vchMessage] like '%error%'
    or  [vchMessage] like '%fail%'
    or  [vchMessage] like '%Warning%'
    or  [vchMessage] like '%The SQL Server cannot obtain a LOCK resource at this time%'
    or  [vchMessage] like '%Autogrow of file%in database%cancelled or timed out after%'
    or  [vchMessage] like '%Consider using ALTER DATABASE to set smaller FILEGROWTH%'
    or  [vchMessage] like '% is full%'
    or  [vchMessage] like '% blocking processes%'
    or  [vchMessage] like '%SQL Server has encountered%IO requests taking longer%to complete%'
    )
    and [vchMessage] not like '%\ERRORLOG%'
    and [vchMessage] not like '%Attempting to cycle errorlog%'
    and [vchMessage] not like '%Errorlog has been reinitialized.%'
    and [vchMessage] not like '%found 0 errors and repaired 0 errors.%'
    and [vchMessage] not like '%without errors%'
    and [vchMessage] not like '%This is an informational message%'
    and [vchMessage] not like '%WARNING:%Failed to reserve contiguous memory%'
    and [vchMessage] not like '%The error log has been reinitialized%'
    and [vchMessage] not like '%Setting database option ANSI_WARNINGS%'
    and [vchMessage] not like '%Error: 15457, Severity: 0, State: 1%'
    and [vchMessage] <>  'Error: 18456, Severity: 14, State: 16.'
    AND Logdate > GETDATE() - 2
     
     
    DROP TABLE #Errors
    "
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize


    Thanks in advance! ChrisRDBA

    Tuesday, September 17, 2019 10:47 PM

All replies

  • "ServerInstance" can only take one server name.

    This is how to do this:

    Get-Content C:\Users\myUser\PS\serverInstances.txt |
    	ForEach-Object{
    		Invoke-Sqlcmd -ServerInstance $_ -InputFile C:\Users\myUser\PS\sqlCMD2.txt
    	}
    Overuse of variables is never acceptable.


    \_(ツ)_/


    • Edited by jrv Tuesday, September 17, 2019 11:14 PM
    Tuesday, September 17, 2019 11:14 PM
  • I appreciate the help, but this results in the same error.

    Thanks in advance! ChrisRDBA

    Wednesday, September 18, 2019 10:40 PM
  • What is the exact and complete error.

    \_(ツ)_/

    Wednesday, September 18, 2019 11:05 PM
  • Invoke-Sqlcmd : Value cannot be null.
    Parameter name: ServerInstance
    At line:3 char:3
    +         Invoke-Sqlcmd -ServerInstance $_ -InputFile C:\Users\a_c10076 ...
    +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidArgument: (:) [Invoke-Sqlcmd], ArgumentNullException
        + FullyQualifiedErrorId : CannotGetServerInstance,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

    Thanks in advance! ChrisRDBA

    Wednesday, September 18, 2019 11:34 PM
  • Its telling you that the file has issues. Fix the file and the issue will go away.


    \_(ツ)_/

    Wednesday, September 18, 2019 11:56 PM
  • The same file is used for script 2, and I don't get an error when I run that. I realize the obvious answer is "use script 2", but I'd like to figure this out. 

    Thanks in advance! ChrisRDBA

    Thursday, September 19, 2019 8:06 PM
  • Script 1 is wrong. You are trying to send the whole array to the command.  THat cannot be done which is why I posted the correct method.

    This is how to write script 1:

    Get-Content C:\Users\myUser\PS\serverInstances.txt |
    	ForEach-Object{
    		Invoke-Sqlcmd -ServerInstance $_ -InputFile C:\Users\myUser\PS\sqlCMD2.txt
    	}


    \_(ツ)_/

    Thursday, September 19, 2019 8:19 PM
  • If you would for4mat your code correctly then it would be much easier to read. You first script should look like this:

    The following would be easier to r5ead, understand and makes more sense:

    $filePath = 'C:\Users\myUser\PS\'
    $inputFile = $filePath + 'sqlCMD2.txt'
    
    foreach ($serverInstance in (Get-Content 'C:\Users\myUser\PS\serverInstances.txt')) {
    	Invoke-Sqlcmd -ServerInstance $serverInstance -InputFile $inputFile
    }

    If you are getting an error then you likely have a blank line or bad characters in the file.  That is what the error message is telling you.


    \_(ツ)_/

    Thursday, September 19, 2019 8:28 PM
  • Yes, I've tried your code. I copy it out of here, change nothing other than the "myUser" to my real user name, and get the message I posted. 

    Thanks in advance! ChrisRDBA

    Thursday, September 19, 2019 9:50 PM
  • Once again I will say6 -= FIX THE FILE. Delete all but a couple of lines and try.

    The file must have no header and it must have no blank lines or any unprintable characters.  Systems must be listed one per line.  To be safe the file should be ASCII/ANSI and not Unicode.


    \_(ツ)_/

    Thursday, September 19, 2019 9:56 PM