Right Click Tool

Generated scripts with Rollback

When executing SQL scripts that is generated by MIcrosoft SQL Server Management Studio you can get into problems with the Keyword Rollback.

Right click tool will not accept scripts with rollback.

If you generated a script with Rollback, you have to modify the script.

Lets take an example.

I have a SQL job on one server that I what to create on a different server.

I create a scripts this way:

The right click tool will start looking for the word Rollback, and if it finds the word it will stop and come with this message:

So we have to get ride of the Word Rollback.

First we have a label called QuitWithRollback. Replace that word with somethind else, that don't contain Rollback.

Now we want to change this part of the code:

GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

If TRANSCOUNT is greater the 0, we still want the script to stop. Therefore we will raise an error, like this

GOTO EndSave
QuitWithRB:
IF (@@TRANCOUNT > 0) RAISERROR ( 'Whoops, an error occurred.',1,1);
EndSave:
GO

Here is the final script that the right Click tool will accept

USE [msdb]
GO

/****** Object:  Job [NordicWaterproofing_MailReports_SalesInvoice]    Script Date: 10-07-2019 13:20:50 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 10-07-2019 13:20:50 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRB

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'NordicWaterproofing_MailReports_SalesInvoice', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRB
/****** Object:  Step [Send_Emails]    Script Date: 10-07-2019 13:20:50 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send_Emails', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @lDataOwnersId INT,
		@lFromDate DATETIME,
		@lToDate DATETIME,
		@lConversionTableName NVARCHAR(100),
		@lColumnsId_Supplier INT,
		@lColumnsId_Email INT,
		@lSqlSerMailProfile NVARCHAR(100)
		
SET @lSqlSerMailProfile = ''LinkTest''
		
SELECT
	@lDataOwnersId = dow.DataOwnersId
FROM
	DataOwner.tDataOwners dow
WHERE
	dow.DataOwnerName = ''Nordic Waterproofing''

SET @lToDate = GETDATE()
SET @lFromDate = @lToDate-1
SET @lConversionTableName = ''Salgsfaktura_rapport''

SELECT
	@lColumnsId_Supplier = cc.ConversionTableColumnsId
FROM
	partner.tConversionTables ct
	JOIN partner.tConversionTableColumns cc ON cc.ConversionTablesId = ct.ConversionTablesId
WHERE
	ct.ConversionTableName = @lConversionTableName
	AND ct.DataOwnersId = @lDataOwnersId
	AND cc.Name = ''Supplier''
	
SELECT
	@lColumnsId_Email = cc.ConversionTableColumnsId
FROM
	partner.tConversionTables ct
	JOIN partner.tConversionTableColumns cc ON cc.ConversionTablesId = ct.ConversionTablesId
WHERE
	ct.ConversionTableName = @lConversionTableName
	AND ct.DataOwnersId = @lDataOwnersId
	AND cc.Name = ''Email''

-- We attempt to drop the table #SupplierReportReceivers. It might exist if earlier executions have been interupted
BEGIN TRY
	DROP TABLE #SupplierReportReceivers
END TRY
BEGIN CATCH
	-- DO NOTHING
END CATCH	

SELECT
	r.ConversionTableRowsId AS ID,
	(SELECT ctv.Value FROM partner.tConversionTableValues ctv WHERE ctv.ConversionTableRowsId = r.ConversionTableRowsId AND ctv.ConversionTableColumnsId = @lColumnsId_Supplier) AS SupplierName,
	(SELECT ctv.Value FROM partner.tConversionTableValues ctv WHERE ctv.ConversionTableRowsId = r.ConversionTableRowsId AND ctv.ConversionTableColumnsId = @lColumnsId_Email) AS Email
INTO 
	#SupplierReportReceivers
FROM
	partner.tConversionTables ct
	JOIN partner.tConversionTableRows r ON r.ConversionTablesId = ct.ConversionTablesId
WHERE
	ct.ConversionTableName = @lConversionTableName


DECLARE 
	@lCurrentID INT,
	@lCurrentSupplier NVARCHAR(256),
	@lCurrentEmail NVARCHAR(256),
	@lMailBody NVARCHAR(MAX),
	@lMailSubject NVARCHAR(256),
	@lNoOfInvoicesSentForCurrentSupplier INT

WHILE	(SELECT COUNT(*) FROM #SupplierReportReceivers) > 0
BEGIN

	SELECT TOP(1)
		@lCurrentID = ID,
		@lCurrentSupplier = SupplierName,
		@lCurrentEmail = Email
	FROM
		#SupplierReportReceivers
		
	SELECT
		@lNoOfInvoicesSentForCurrentSupplier = COUNT(*)
	FROM
		Tracking.tDocuments d
		JOIN DistributionModel.tDistributionObjects do ON do.DistributionObjectsId = d.DistributionObjectsId
		JOIN Tracking.tStatusTypes st ON st.StatusTypesId = d.StatusTypesId_LatestDocumentStatus
		JOIN Tracking.tTrackingFields tf ON tf.DocumentsId = d.DocumentsId
		JOIN Tracking.tTrackingFieldTypes tft ON tft.TrackingFieldTypesId = tf.TrackingFieldTypesId
	WHERE
		d.DataOwnersId = @lDataOwnersId
		AND d.CreatedDate >= @lFromDate
		AND d.CreatedDate <= @lToDate
		AND do.DistributionObjectName = ''OIOUBL Faktura''
		AND tft.TypeName = ''Supplier Name''
		AND tf.FieldValue = @lCurrentSupplier
		
	IF	@lNoOfInvoicesSentForCurrentSupplier > 0
	BEGIN
	
		SET @lMailBody = ''<p>Nedenstående fakturaer har du i dag afsendt elektronisk</p><table style="border:1px solid black;"><tr><td style="border:1px solid black;">Dokumentnummer</td><td style="border:1px solid black;">Status</td><td style="border:1px solid black;">Til Partner</td><td style="border:1px solid black;">Dato/Tid</td></tr>''
			
		SELECT
			@lMailBody = @lMailBody + ''<tr><td style="border:1px solid black;">'' + ISNULL(d.DocumentNumber, ''N/A'') + ''</td>''
				+ ''<td style="border:1px solid black;">'' + ISNULL(st.StatusTypeName, ''N/A'') + ''</td>''
				+ ''<td style="border:1px solid black;">'' + ISNULL(pOut.PartnerName, ''N/A'') + ''</td>''
				+ ''<td style="border:1px solid black;">'' + CAST(ISNULL(d.CreatedDate, ''N/A'') AS NVARCHAR) + ''</td></tr>''
		FROM
			Tracking.tDocuments d
			JOIN DistributionModel.tDistributionObjects do ON do.DistributionObjectsId = d.DistributionObjectsId
			JOIN Tracking.tStatusTypes st ON st.StatusTypesId = d.StatusTypesId_LatestDocumentStatus
			LEFT JOIN partner.tPartners pOut ON pOut.PartnersId = d.PartnersId_Out
			JOIN Tracking.tTrackingFields tf ON tf.DocumentsId = d.DocumentsId
			JOIN Tracking.tTrackingFieldTypes tft ON tft.TrackingFieldTypesId = tf.TrackingFieldTypesId
		WHERE
			d.DataOwnersId = @lDataOwnersId
			AND d.CreatedDate >= @lFromDate
			AND d.CreatedDate <= @lToDate
			AND do.DistributionObjectName = ''OIOUBL Faktura''
			AND tft.TypeName = ''Supplier Name''
			AND tf.FieldValue = @lCurrentSupplier
		ORDER BY
			pOut.PartnerName,
			d.CreatedDate
			
		SET @lMailBody = @lMailBody + ''</table>''
		SET @lMailSubject = ''Salgsfaktura rapport for leverandør '' + @lCurrentSupplier + '' for perioden '' + CAST(@lFromDate AS NVARCHAR) + '' til '' + CAST(@lToDate AS NVARCHAR)
			
		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @lSqlSerMailProfile,
			@recipients = @lCurrentEmail,
--			@blind_copy_recipients = ''psp@bizbrains.dk''
			@body = @lMailBody,
			@subject = @lMailSubject,
			@body_format = ''HTML''
		
	END
	
	DELETE FROM #SupplierReportReceivers
	WHERE ID = @lCurrentID
	
END
	
DROP TABLE #SupplierReportReceivers', 
		@database_name=N'Link', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRB
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRB
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20150831, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'6d3ef5ce-75b3-49e0-aaf2-bc32edb6bb8b'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRB
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRB
COMMIT TRANSACTION
GOTO EndSave
QuitWithRB:
    IF (@@TRANCOUNT > 0) RAISERROR  ( 'Whoops, an error occurred.',1,1);
EndSave:
GO

Content on this page: