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.

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 »

Go Directly To User Detail on SFDC

My wife works as a Salesforce Administrator. When Chatter launched she very quickly became annoyed with the new Chatter profile page that appeared when you clicked on a user’s name.

She wanted to go to the User Details page instead as this is where all the Admin gets done. She realised that to go to the User Details page all you needed to do was append noredirect=1 to the end of the Url.

I decided to develop a Chrome Extension to do this automatically for all User Profile links (i.e. Id’s that Start with 005).

https://chrome.google.com/webstore/detail/go-directly-to-user-detai/jpdmkhlcnjjndfpgljcpmllddeidkdjc?utm_source=eugeneniemand.com

Please feel free to download. If you have any feedback, bugs or suggestions please leave them on the Chrome Web Store.

 

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

How often have you received the error below?

Error

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Ovott” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Very helpful, I know… Please excuse the sarcasm. I spent days trying to fix this error as I just installed some database drivers before building a new package. At first I thought it was the drivers, so I removed them. Long story short It cost me Repairing/uninstalling and reinstalling SQL Server 2008 R2 from different images around 9 times before I could get SSIS to open packages again, but this is a whole other story.

So after getting SSIS working again I thought, hey everything is back to normal. Tried executing the package and to my surprise I was still getting this error.

Problem

I have to mention that this error only occurs when you execute the package, when you do a preview everything works which makes it even more confusing, but this makes sense as configurations only apply when you execute the package.

The error was due to using Package Configurations forRead More »