goeddie / ssdt-devpack Goto Github PK
View Code? Open in Web Editor NEWLicense: MIT License
License: MIT License
It would be nice if default constraints could be automatically named as well. Like DF_TableName_ColumnName
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.
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?
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?
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
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.
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
Would love to use SSDT-DevPack in VS 2017, but VS 2017 completely ignores it while VS 2015 sees it fine.
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)
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.