CODEWITHSUNDEEP
sql-serverpowershell
monkeySeeMonkeyDo
monkeySeeMonkeyDo

Reputation: 391

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

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?

Upvotes: 0

Views: 2750

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32145

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.

Upvotes: 1

Related Questions