goeddie / ssdt-devpack Goto Github PK

When I use MergeUI to generate insert + update + delete statements, and the table I generate the statement for has an IDENTITY column, I get a MERGE statement that contains an INSERT

WHEN NOT MATCHED BY TARGET THEN INSERT ([id],...

But here id is an IDENTITY column, so that fails with the error:

[S0001][544] Cannot insert explicit value for identity column in table '<tablename>' when IDENTITY_INSERT is set to OFF.

I could surround the MERGE statement with the statement to allow INSERTs

SET IDENTITY_INSERT <tablename> ON
<MERGE statement>
SET IDENTITY_INSERT <tablename> OFF

But that somehow feels wrong, since the tool itself knows already that it has to generate inserts. I think MergeUI itself should surround SET IDENTITY_INSERT.

Hey, not sure if this project is still alive, but is there any chance there will be support for VS 2019 in the near future? I'm most interested in the SQLCover tool, so in the meantime I'll try working with the powershell version.

In the visual studio tools menu for SSDT Dev Pack, the 'Everything below this is deprecated...' menu item is present, and below it, among others, is MergeUI. Does that mean MergeUI has been superseded with a better way? If so which one?

Would love to use SSDT-DevPack in VS 2017, but VS 2017 completely ignores it while VS 2015 sees it fine.

When i use MergeUI to generate inserts + updates + deletes for a specific table of mine with an 'id' identity key, I get some statements that try to UPDATE identity columns, causing a Cannot update identity column 'id'.

Here is an example:

MERGE INTO [dbo].[...]
 AS TARGET
USING (VALUES(...) AS SOURCE(...) ON [SOURCE].[id] = [TARGET].[id]
WHEN NOT MATCHED ...
WHEN MATCHED AND (...) THEN UPDATE
SET [TARGET].[id] = [SOURCE].[id]

Why try to UPDATE [TARGET].[id] to the value of [SOURCE].[id] when the two where identical in the ON part of the MERGE statement?

I was looking for a VS extension that does two things: 1) replaces CREATEs with ALTERs in individual artifact definitions (stored procedures, functions and views) in SSDT database projects and 2) does SQLCMD variable substitution. I found your SSDT-DevPack and it looks like QuickDeploy already does half of what I need. I particularly like Quick Deploy To Clipboard - very nice!

Would you be open to adding SQLCMD variable substitution to Quick Deploy? E.g., if there is a SQLCMD variable defined in the project and a stored procedure, function or a view references it, Quick Deploy would substitute it with a default value. Here is an example:

Let's say I have an SSDT database project with a database reference and the following stored procedure:

CREATE PROCEDURE [dbo].[spProcedure1]
AS
    SELECT * FROM [$(SomeOtherDb)].dbo.Table1

If I run the current version of Quick Deploy To Clipboard on this SP, I get

if object_id('[dbo].[spProcedure1]') is null
begin
 execute sp_executeSql N' create procedure [dbo].[spProcedure1] as select 1;';
end
GO
alter PROCEDURE [dbo].[spProcedure1]
AS
    SELECT * FROM [$(SomeOtherDb)].dbo.Table1
GO

What I would like to get is

if object_id('[dbo].[spProcedure1]') is null
begin
 execute sp_executeSql N' create procedure [dbo].[spProcedure1] as select 1;';
end
GO
alter PROCEDURE [dbo].[spProcedure1]
AS
    SELECT * FROM SomeOtherDb.dbo.Table1
GO

(notice the difference in the FROM part of the SELECT statement)

The SQLCMD variable substitution should not be very complicated to implement as the default values for SQLCMD variables are stored in the database project:

They are stored in the .sqlproj file in the following section:

  <ItemGroup>
    <SqlCmdVariable Include="SomeOtherDb">
      <DefaultValue>SomeOtherDb</DefaultValue>
      <Value>$(SqlCmdVar__2)</Value>
    </SqlCmdVariable>
  </ItemGroup>

@GoEddie Do you have any interest in implementing this functionality? If not, or if you don't have the time, would you accept a PR implementing this functionality, if there were one? What requirements would you have for such a PR?

Thanks,
Sergei

It would be nice if default constraints could be automatically named as well. Like DF_TableName_ColumnName

Issue:
When generating a MERGE statement for tables with IDENTITY columns , SET IDENTITY_INSERT is not included in the generated script.

Repro:
Add a table with identity field in MergeUI and click save, generated script doesnt have SET IDENTITY_INSERT

Work Around:
Manually adding the statement

System.ArgumentException: The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG)) at EnvDTE._DTE.get_ActiveDocument() at SSDTDevPack.QueryCosts.Highlighter.CodeCoverageHighlightWordTagger.UpdateWordAdornments() at SSDTDevPack.QueryCosts.Highlighter.HighlightWordTagger.UpdateAtCaretPosition(CaretPosition caretPosition) at SSDTDevPack.QueryCosts.Highlighter.HighlightWordTagger.CaretPositionChanged(Object sender, CaretPositionChangedEventArgs e) at Microsoft.VisualStudio.Text.Utilities.GuardedOperations.RaiseEvent[TArgs](Object sender, EventHandler`1 eventHandlers, TArgs args)