How to connect to local SQL server 2016 via PowerShell and execute a SQL stored in a .sql file?

Question

I am trying to create a PowerShell script to automate a very simple process, however, I cannot get much (if anything) to work. The documentation is either not what I need, outdated or conflicting.

I've had a few variations of this:

$SQLConnection = New-Object System.Data.SQLClient.SQLConnection 
$SQLConnection.ConnectionString = "Data Source=.\SQL2016;Initial Catalog=TEST;Trusted_Connection=true;" 
$SQLConnection.Open()
$Cmd = new-object system.Data.SqlClient.SqlCommand($SQLConnection)
Invoke-Sqlcmd -InputFile "C:\dev\test\script.sql" | Out-File -filePath "C:\dev\test\output.sql"
$SQLConnection.Close()

I've not managed to connect to the database.

The idea being, script.sql spits out a bunch of SQL (this works fine) which we will put into source control. Once in source control, a Jenkins job will do something with it.

Trying to keep this as basic as possible, no flexibility is needed other than different connection strings. I want to avoid using PSSQL if possible, a user throws in their connecting string (database will be the name) and runs the script, job done.

Can anyone point me in the right direction?

• Bacon Bits: @wp78de That's not the best answer, since there have been significant changes between SQL Server 2008 R2 and SQL Server 2016. The command syntax is similar, but the Powershell module is a module now, and it used to be a snap-in in 2008 R2. Then, when the module first came out it was named SQLPS. Only recently have they renamed it to SqlServer and put it on the Powershell Gallery.

Answer 1

System.Data.SQLClient is only necessary if you don't have the SqlServer module installed or are doing something unusual. It's a much more verbose method.

You just need:

Import-Module SqlServer;
Invoke-Sqlcmd -ServerInstance '.\SQL2016' -Database 'TEST' -InputFile 'C:\dev\test\script.sql' | 
 Out-File -FilePath "C:\dev\test\output.sql"

However... your output file isn't really an .sql file unless the queries in script.sql are actually returning strings that should be executed as SQL. It should probably be a .txt file.

And depending on what exactly you're generating, you might want to consider Export-Csv -Path "C:\dev\test\output.csv" -NoTypeInformation instead of Out-File. I can't tell if you're trying to export data or just logging information.

Additionally, you'll need to make sure that you're not using the batch separator (GO) in script.sql, or relying on any "SQLCMD Mode" (as it's called in SQL Server Management Studio) or other sqlcmd.exe specific syntax. The Invoke-Sqlcmd doc outlines the differences between the two. If you don't know what that is, you're probably safe.

• monkeySeeMonkeyDo: Thanks, that's so simple. I'll give it a go. script.sql returns a bunch of insert statements for a table, which needs putting into source control. It doesn't use any GO statements, but output.sql will have them, so Ill check that out. output.sql is correctly formatted SQL. Simplified, we have a system wherein our entire DB is in source control, aside the one table I'm scripting out. Everyone has their own local database. Jenkins polls the repo, keeping a master database up to date, it was just missing this one table.
• monkeySeeMonkeyDo: is there any reason as to why your suggestion wouldn't work? Running it as is creates the output file but doesn't actually populate it. It connects just fine, if I drop the input and output parameters and run a straight -Query, I'll get the expected result. If I run sqlcmd directly (from PowerShell) using -i and -o respectively, it works. I've tried running as admin as well.
• Bacon Bits: @OnceInALifetime Out-File with nothing piped to it will create an empty file. Make sure that it's running as a single line. The code is formatted here as two for legibility, but it's really one. If that's not what it is, it's hard to say without knowing exactly what script.sql is running. Try creating a file with the query SELECT @@VERSION or some other simple query that should always return a result set. Then I'd try running Invoke-Sqlcmd with the queries using the -Query parameter.
• Bacon Bits: @OnceInALifetime Although, now that I try it Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query 'SELECT @@VERSION AS Version' | Out-File test.txt -Width ([int]::MaxValue) creates really useless output. You need Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query 'SELECT @@VERSION AS Text' | Select-Object -ExpandProperty Text | Out-File test.txt -Width ([int]::MaxValue)
• Bacon Bits: Although, I might switch to Set-Content, which works a bit differently but doesn't have the truncation issue that requires the -Width parameter. You've got to pass through Select-Object, though, or it will just output System.Data.DataRow.