SQLPackage and SSDT dropping and recreating items where they already exist and no changes were made

So I have been struggling for hours trying to figure out why my SQLPackage.exe task thinks it needs drop my Certificates even though I just deployed them and haven’t made any changes. Look at the example below, this is not the SSDT project or output it is only used for illustrating the problem and solution:

create certificate [YourCertificateName]
authorization [dbo]
with 
  subject = N'Some description about your encryption certificate'
  , start_date  = N'01/01/2012 00:00:00'
  , expiry_date = N'12/31/2050 00:00:00'
active for begin_dialog = on;

If you run this from SSDT or SQLPackage where you have the above Certificate scripted as part of a Database Project and deploy this more than once you will notice that it is dropping and recreating the Certificate every time. This happens without you making any changes and the object already existing. My colleague Simon Sabin point out the following “The reason for this is that the dates in the above script aren’t ISO Standard.” This means that if you run a Schema Comparison you will notice the Certificate on the server looks like this:

create certificate [YourCertificateName]
authorization [dbo]
with 
  subject = N'Some description about your encryption certificate'
  , start_date  = N'2012-01-01T00:00:00'
  , expiry_date = N'2050-12-31T00:00:00'
active for begin_dialog = on;

When a date is stored as a property or into a Date column it is converted to ISO standards and thus when you script an object using a different format they will look different even though they are the same and cause the object to be dropped and recreated.

Advertisements

T-SQL Code Documentor

I recently resigned and was tasked to document all the important stored procedure and triggers. Yes I know this should have been done at the time but we have never had time, or should I rather say it’s never been priority.

I was deliberating over the different methods possible to document the T-SQL code. Red-Gate has SQL Doc but that only updates extended properties. I thought I could use Word and document what every Stored Proc or Trigger does but that document will be dead in the water. When making changes some of this come to mind… “Oh I need to update the documentation”… “Where is the documentation saved again?”… “Aargh!!! The other guy did not document his changes”. The last two can be mitigated by using source control or versioned docs like Google Docs or Sharepoint etc. and a bit of discipline.

The worst part is, how do you tie sections of the code to sections of the document?

Then I thought I can document the code by adding standard T-SQL comments. This makes sense as the documentation then exists inside the code at the exact point where it matters or explains something; this is sometimes referred to as “Living Documentation”.Read More »

Generate SQL Update Statement And Stored Procedure From Table Name

Today I had to write a few simple update stored procedures and a couple of update statements. This got me thinking, seeing that I’m a SQL / .Net developer, I create loads of these every month.

I don’t like doing repetitive tasks so why not spend a  little time and write a script that generate these for me? So here it goes, here are two scripts and they are available as is. I know there are some data types that might not work or get excluded or throw exception etc etc etc…for the bulk of my work this will work a charm and in any case I need to check before I compile them.

Note: Your database context needs to be the same for the stored procedure/update statement as well as the table in question.

Execution for Update Statement

EXEC [GenerateUpdateStatementForTable] @TableName = 'Offices'

Read More »

Script All Indexes SQL Server 2005/2008

I needed a script to script out all the indexes with their included columns. All the scripts I could find on the inernet only scripted the normal columns, so I created my own script to script all columns and included columns.

At the moment this script does not support Fill Factor, Filters, With Options or File Groups.

If and when I make any changes I will update this script. If you like you can make changes and send them to me and I’ll update accordingly

ALTER PROC ScriptOutIndexes
AS 

 SET NOCOUNT ON 

 SELECT
 OBJECT_NAME([ixs].[object_id]) 'table_name'
 ,[ixs].[type] 'index_type'
 ,[ixs].[type_desc]
 ,[ixs].[name] 'index_name'
 ,[ixs].[is_unique]
 ,[ixs].[fill_factor]
 ,[ix_col].[key_ordinal]
 ,[col].[name] 'column_name'
 ,[ix_col].[is_descending_key]
 ,[is_included_column]

Read More »