Right Click Tool
Nov 25, 2020
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 EndSaveQuitWithRollback:IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO
If TRANSCOUNT is greater the 0, we still want the script to stop. Therefore we will raise an error, like this
GOTO EndSaveQuitWithRB: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: