Powershell script to run list of sql files

Question

I want to read a file that contains a line separated list of *.sql file names (all located at the same directory) to execute the following:

$reader = [System.IO.File]::OpenText("_Scripts.txt")
try {
    for(;;) {
        $line = $reader.ReadLine()
        if ($line -eq $null) { break }
        #output
        $out = $line.split(".")[0] + ".txt" ; 

        # -serverinstance  u should change the value of it according to use 
        invoke-sqlcmd -inputfile $line -serverinstance "."   | format-table | out-file -filePath $out 
        $line
    }
}
finally {
    $reader.Close()
}

I'm trying to execute this script file by using a batch file containing the command:

powershell.exe -ExecutionPolicy Bypass -Command "_scripts.ps1"

but I get the error shown below:

enter image description here

Can anyone help me fix my ps1 script please?

user4622594 · Accepted Answer · 2015-05-21 14:44:42Z

2

This works for me:

$lines = Get-Content C:\Temp\TEST\_Scripts.txt

ForEach ($line in $lines)
{
    $out = $line.split(".")[0] + ".txt" ;
    Invoke-Sqlcmd -InputFile $line -ServerInstance "localhost" -Database "master" | Format-Table | Out-File -FilePath $out
}

answered May 21, 2015 at 14:44

user4622594

Sign up to request clarification or add additional context in comments.

5 Comments

user4622594 Over a year ago

of course you can add the try-catch block and so but basically this works on my machine

AMH Over a year ago

what if I want to exclude database name please ?

user4622594 Over a year ago

the delete the -Database "master" option in the command above!? Also works without this Parameter

AMH Over a year ago

I have one small issue that the error not write to output file and appear in cmd line

user4622594 Over a year ago

Maybe you can try the Invoke-Sqlcmd option -OutputSqlErrors.. Have a look a the options in the bottom section of this page: msdn.microsoft.com/de-at/library/cc281720.aspx

Collectives™ on Stack Overflow

Powershell script to run list of sql files

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related