How to run a large sql script in powershell?

There is a SQL script with the following pseudocode:

IF EXISTS DROP PROCEDURE GO CREATE PROCEDURE GO IF EXISTS FUNCTION DROP FUNCTION GO CREATE FUNCTION GO IF EXISTS TRIGGER DROP TRIGGER GO CREATE TRIGGER GO BEGIN TRY BEGIN TRANSACTION TransactionName if(NotAllow) THROW CREATE #tempTable #Insert statements DROP #tempTable COMMIT TRANSACTION TransactionName END TRY BEGIN CATCH PRINT ERROR_MESSAGE() ROLLBACK TRANSACTION TransactionName END CATCH DROP PROCEDURE 

Full code

 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Return_All_Email_To_Test') DROP PROCEDURE Return_All_Email_To_Test GO CREATE PROCEDURE Return_All_Email_To_Test @table nvarchar(100), @column nvarchar(100) AS BEGIN SET NOCOUNT ON; DECLARE @cmd nvarchar(max) SET @cmd = N'UPDATE [dbo].[' + @table +'] SET ' + @column +' = [dbo].[DecodeEmail](' + @column + ')' print @cmd exec sp_executesql @cmd END GO IF EXISTS ( SELECT 1 FROM Information_schema.Routines WHERE Specific_schema = 'dbo' AND specific_name = 'DecodeEmail' AND Routine_Type = 'FUNCTION' ) DROP FUNCTION dbo.DecodeEmail GO CREATE FUNCTION dbo.DecodeEmail ( @mail nvarchar(max) ) RETURNS nvarchar(max) AS BEGIN DECLARE @decodeResult nvarchar(max) if(LEN(@mail) > 3 AND CHARINDEX('SummerInfantTest+', @mail) = 1) SET @decodeResult = SUBSTRING(@mail, CHARINDEX('+', @mail) + 1, LEN(@mail) - CHARINDEX('_', REVERSE(@mail)) - CHARINDEX('+', @mail)) + '@' + SUBSTRING(@mail, LEN(@mail) - CHARINDEX('_', REVERSE(@mail)) + 2, LEN(@mail) - CHARINDEX('@', REVERSE(@mail)) - (LEN(@mail) - CHARINDEX('_', REVERSE(@mail))) - 1) else SET @decodeResult = @mail RETURN @decodeResult END GO BEGIN TRY BEGIN TRANSACTION TurnOffEmailTest SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON IF (SELECT COUNT(*) FROM dbo.VersionInfo WHERE [Description] = 'Testing emails is turned on') = 0 THROW 51000, 'Emails already turned off', 1 IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[SICMS_EncodeEmailsForTest]')) DROP TRIGGER [dbo].[SICMS_EncodeEmailsForTest] IF OBJECT_ID('tempdb..#EmailsInfoCols') IS NOT NULL DROP TABLE #EmailsInfoCols CREATE TABLE #EmailsInfoCols ( [tableName] [nvarchar](100) NULL, [columnName] [nvarchar](100) NULL ) ON [PRIMARY] INSERT #EmailsInfoCols ([tableName], [columnName]) VALUES (N'Blog_Comment', N'CommentEmail') INSERT #EmailsInfoCols ([tableName], [columnName]) VALUES (N'Blog_Comment', N'CommentUrl') INSERT #EmailsInfoCols ([tableName], [columnName]) VALUES (N'Blog_Comment', N'CommentUserName') INSERT #EmailsInfoCols ([tableName], [columnName]) VALUES (N'CMS_Email', N'EmailFrom') INSERT #EmailsInfoCols ([tableName], [columnName]) VALUES (N'CMS_Email', N'EmailTo') INSERT #EmailsInfoCols ([tableName], [columnName]) VALUES (N'CMS_EmailTemplate', N'EmailTemplateBcc') INSERT #EmailsInfoCols ([tableName], [columnName]) VALUES (N'CMS_EmailTemplate', N'EmailTemplateFrom') INSERT #EmailsInfoCols ([tableName], [columnName]) VALUES (N'CMS_User', N'Email') INSERT #EmailsInfoCols ([tableName], [columnName]) VALUES (N'CMS_UserSettings', N'UserMessagingNotificationEmail') INSERT #EmailsInfoCols ([tableName], [columnName]) VALUES (N'COM_Customer', N'CustomerEmail') INSERT #EmailsInfoCols ([tableName], [columnName]) VALUES (N'CONTENT_Blog', N'BlogSendCommentsToEmail') IF EXISTS ( SELECT 1 FROM Information_schema.Routines WHERE Specific_schema = 'dbo' AND specific_name = 'EncodeEmail' AND Routine_Type = 'FUNCTION' ) DROP FUNCTION dbo.EncodeEmail declare @table nvarchar(100) declare @column nvarchar(100) declare cur CURSOR LOCAL for SELECT tableName, columnName FROM #EmailsInfoCols open cur fetch next from cur into @table, @column while @@FETCH_STATUS = 0 BEGIN exec dbo.Return_All_Email_To_Test @table, @column fetch next from cur into @table, @column END DELETE FROM dbo.VersionInfo WHERE [Description] = 'Testing emails is turned on' DROP TABLE #EmailsInfoCols COMMIT TRANSACTION TurnOffEmailTest END TRY BEGIN CATCH PRINT ERROR_MESSAGE() ROLLBACK TRANSACTION TurnOffEmailTest END CATCH DROP FUNCTION dbo.DecodeEmail DROP Procedure dbo.Return_All_Email_To_Test 

The script runs fine in SSMS (Sql Server Management Studio). It is necessary that it can be executed using the powershell script. Powershell:

 param( [string]$sqlPath, [string]$pathToConnectionString ) if(-not $sqlPath) { $sqlPath = ".\finalFilterOff.sql" } if(-not $pathToConnectionString) { $pathToConnectionString = "..\..\wwwroot\CMS\web.config" } $xml = [xml](Get-Content $pathToConnectionString) $connectionString = $xml.configuration.connectionStrings.add | Select "connectionString" $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionString.connectionString $connection.Open() $command = $connection.CreateCommand() $command.CommandText = Get-Content $sqlPath Write-Output $command.CommandText $command.ExecuteNonQuery() $connection.Close() #Push-Location #invoke-sqlcmd -inputfile $sqlPath -serverinstance $serverInstance -database $database -Verbose #Pop-Location 

If you use ExecuteNonQuery:

Exception calling "ExecuteNonQuery" with "0" argument (s): "Incorrect syntax near 'GO'. 'CREATE / ALTER PROCEDURE' Incorrect syntax near 'GO'. Incorrect syntax near 'GO '. Must declare the scalar variable "@mail". Must declare the scalar variable "@mail". Must declare the scalar variable "@mail". A wrong syntax near' GO '. The variable name' @table 'has been defined as the variable name' @column 'has been declared. Incorrect syntax near 'Return_All_Email_To_Test'. "

If you use invoke-sqlcmd:

invoke-sqlcmd: Timeout expired. The timeout period is not responding.

In the case of Invoke-SqlCmd, everything is sad, because in addition, after discarding this error, the transaction remains to hang, i.e. Powershell threw an error, and the server still executes the script. Because of this transaction, without restarting the server, you cannot access the tables.

Once again I will note that in SSMS the script runs without errors. How can I do it with Powershell?

Powershell Version: Major - 5, Minor - 0, Build - 10586, Revision - 122