Right Click Tool
- Karsten Markmann
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:
The information on this page is based on Link 2.10