I'm slowly learning to utilize Powershell and as a newbie please be easy on me. I created a Powershell script that would generate an insert statement from a number of excel files and save it into individual text files. How could I utilize Powershell to execute the text files on SQL Server?
-
Is powershell required? It's achievable via windows command line easily.qxg– qxg2016-05-26 15:02:28 +00:00Commented May 26, 2016 at 15:02
Add a comment
|
2 Answers
It will be based on the queries you have in the text files. If they have the GO transaction terminator, then you are limited to using SMO for this because the .NET client System.Data.SqlClient will not accept that command as good syntax. It is strictly a terminator for SSMS and not true SQL syntax.
I in fact had the request for a client to handle the GO so you could do something like this:
$dbServer = MyServer
$ScriptDirectory = 'C:\scripts'
Import-Module SQLPS
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $dbServer
try {
$file = dir $ScriptDirectory
foreach ($f in $file) {
$s = Get-Content $f -Raw
$srv.ConnectionContext.ExecuteNonQuery($s);
}
}
catch {
$error[0].Exception
}
If you do not have to deal with the GO statement and want to use the .NET method you can check out an article published on SQLShack. It walks you through how to make the connection and execute, and also touches a bit more on using PS with SQL Server.
Comments
Your comment was indeed very helpful. Since I don't have GO Statements on my txt files I made use of this method which I derived from yours @ShawnMelton
#Get list of files
$Files = Get-ChildItem 'C:\Users\Me\Documents\Test\*.txt'
try {
$Filename = $File.BaseName
foreach ($file in $files) {
Invoke-sqlcmd -ServerInstance "ServerName" -Database "Test" -InputFile $file -ErrorAction Stop
Write-Host -ForegroundColor Green "EXECUTED SuCCESSFULLY: " $filename
Move-Item $File "C:\Users\Me\Documents\Test\Executed"
}
}
catch {
Write-Host -ForegroundColor RED "FAILED TO EXECUTE: " $filename
}
To avoid confusion I transferred all successfully executed files on a seperate folder. Therefore all unexecuted and failed to execute script would be left on the main folder.