use Powershell to input files to MS SQL Server?

Question

Post reply

use Powershell to input files to MS SQL Server?

ddonck

Ten Centuries

Points: 1193
More actions
July 29, 2010 at 7:40 am

#225532

I'm fairly new to Powershell and have run stuck on this. We have almost a hundred scripts that we load on every new MS SQL Server. These scripts create stored procedures and such, and they all look something like this:
USE master
go
IF OBJECT_ID(N'dbo.sp_dmgr_collation') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_dmgr_collation
IF OBJECT_ID(N'dbo.sp_dmgr_collation') IS NOT NULL
PRINT N'<<< FAILED DROPPING PROCEDURE dbo.sp_dmgr_collation >>>'
ELSE
PRINT N'<<< DROPPED PROCEDURE dbo.sp_dmgr_collation >>>'
END
go
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
create procedure sp_dmgr_collation
@TabelInfo char(2) = 'J'
as
<code>
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID(N'dbo.sp_dmgr_collation') IS NOT NULL
PRINT N'<<< CREATED PROCEDURE dbo.sp_dmgr_collation >>>'
ELSE
PRINT N'<<< FAILED CREATING PROCEDURE dbo.sp_dmgr_collation >>>'
go
GRANT EXECUTE ON dbo.sp_dmgr_collation TO public
go
Because loading these all by hand is quite cumbersome, I wanted to make a Powershell script to do this. Problem is, I cannot find a way to input a file with go's in it.
I know I can input a file:
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=MyServer\MySqlServer;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = Get-Content C:\MyDir\input.sql
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
But as soon as there is a go in that script, I get an error:
Exception calling "Fill" with "1" argument(s): "Incorrect syntax near 'go'."
At C:\MyDir\Connect2ToMsq.ps1:11 char:17
+ $SqlAdapter.Fill <<<< ($DataSet)
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
And as you all can see in the script in the beginning, there are plenty of go's in our scripts.
Is what I want possible? If so, how?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

How about using SMO. Something like this works:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.SMO") | Out-Null

$Server = New-Object ('Microsoft.SQLServer.Management.Smo.Server') 'Servername'

$db = $Server.Databases["Test"]

$script = Get-Content c:\scripts\input.sql

$extype = [Microsoft.SQLServer.Management.Common.ExecutionTypes]::ContinueOnError

$db.ExecuteNonQuery($script,$extype)

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ddonck Ten Centuries Points: 1193 More actions · Answer 2

He Grant, your script didn't work for me, not quite sure why. But you did set me on the right path and with some Googling I got there. This one works for me:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.SMO") | Out-Null

$Server = New-Object ('Microsoft.SQLServer.Management.Smo.Server') 'MyServer\MySqlServer'

$db = $Server.Databases["master"]

$sr = New-Object System.IO.StreamReader("C:\MyDir\input.sql")

$script = $sr.ReadToEnd()

$extype = [Microsoft.SQLServer.Management.Common.ExecutionTypes]::ContinueOnError

$db.ExecuteNonQuery($script,$extype)

Thank you a million times over!!!!! 🙂 🙂 🙂

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Not sure why Get-Content didn't work for you. I tested it several times. Still, glad you solved the problem.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Geoff A SSChampion Points: 11511 More actions · Answer 4

interesting way to go about deployment....

I have always just used sqlcmd.

sqlcmd -U priveldge_login -P "secretPassword" -h-1 -t 10 -S TheNewServer\Instance1 -i "C:\tempew_deployment.sql"

Artem Ervits (@dbist) Say Hey Kid Points: 673 More actions · Answer 5

I certainly prefer Powershell but you can also use Central Management Server to push new scripts and changes.

You can do this as well:

$hostname = get-content "c:\serverlist.txt" ##you can replace this with a call to CMS select * from msdb.

or

You can also do a hybrid of both (Powershell with CMS) like this:

$Hostname = Invoke-Sqlcmd -Query "select server_name from dbo.sysmanagement_shared_registered_servers_internal"

and then execute whatever you have to do on all instances in a foreach loop

foreach($server in $servers)

{

invoke-sqlcmd "blah blah"

}