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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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"

}