2

I'm seeing some really odd behavior with SSMS 2014/2016 that I stumbled accross by pure accident. This was run on SQL2014 SP1+CU6 (the fixed one) in a non-prod environment. Here's the backstory.

I use SQL Compare to deploy changes to our SQL servers. We are moving to a TFS/DB Project approach but this is what we are using right now.

Let's say one of the changes is to add a column to a table. This is the code that SQL Compare generates for that change:

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
USE [DBA_Support]
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ParseExec]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ParseExec] ADD
[BlockType] [int] NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
END
GO

Seems legit, and it is. Load it into an Agent job step, Parse it, no issues.

Let's take it one step further, what happens if you sausage finger the Paste button and accidentally hit it twice, or Ctl+V twice. You get the code, stacked twice, as shown below:

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
USE [DBA_Support]
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ParseExec]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ParseExec] ADD
[BlockType] [int] NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
END
GO

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
USE [DBA_Support]
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ParseExec]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ParseExec] ADD
[BlockType] [int] NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
END
GO

Here's the odd behavior, in SSMS, we get The command was parsed successfully but, it actually performs the operation on the table:

And, no, it wasn't on the table before the parsing, I double checked.

enter image description here

Has anyone ever seen this happen. I can't help think that it has something to with a SET option or the fact that we are suing SERIAL transactions. Regardless of that, should the Parse button simply hit the algebrizer, validate that everything is legit and not actually execute anything?

Is this a bug or am I just overlooking something simple here?

  • 1
    And this only happens within SQL Agent's half-functional editor UI? Personally, even without knowing about this, I would be parsing in a regular, fully-functional query window. – Aaron Bertrand Jun 8 '16 at 19:31
  • Correct, only in the Agent UI. SSMS query window parses and doesn't execute. It was sheer dumb luck that I came across this as I usually parse the scripts in our sandbox using SSMS, this time I got in a hurry and presto, I found this little monster. This is fairly scary behavior as you could accidentally make a change. Is this worth a connect item or some sort of feedback to MS? – Kris Gruttemeyer Jun 8 '16 at 19:36
  • Yeah, sounds like a good Connect item (but I would use a generic and self-enclosed repro, like one that creates the table first, and drops it afterward). – Aaron Bertrand Jun 8 '16 at 19:41
  • Sounds good. I'll build it all out so it can be easily repro'd and submit a Connect item. Thanks for the advice, Aaron. – Kris Gruttemeyer Jun 8 '16 at 19:44
  • That said, I don't know that I would expect a fix. I've probably submitted 30 Connect items against SQL Agent dialogs alone and I don't believe a single one of them has ever been addressed. – Aaron Bertrand Jun 8 '16 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.