Updating Default Values for Columns

To make life a little simpler, a default value can be added to a column so if the information is not passed with the row data, SQL Server can plug in some information for you.  A common scenario is to add a default DateTime of the current system time.  This image show what that looks like when you design the table in SSMS.

DefaultBinding

What happens if you wanted to change the default value option for those tables?  For example, we are preparing to move to Azure SQL and our default options need to be GETUTCDATE() instead of GETDATE().  I put together this script to help me identify all the constraints in the database that need to be updated. This code has the new lines so you should be able to run the output. Don’t forget to update your data before you made the change. You can find that example here.

[sourcecode language=”SQL”]
SELECT
‘ALTER TABLE ‘ + t.name + ‘ DROP CONSTRAINT ‘ + dc.name + ‘
GO
‘ + ‘ALTER TABLE ‘ + t.name + ‘ ADD CONSTRAINT ‘ + dc.name + ‘ DEFAULT GETUTCDATE() FOR ‘ + c.name + ‘
GO’
FROM sys.default_constraints dc
INNER JOIN sys.tables t ON dc.parent_object_id = t.object_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE dc.parent_column_id = c.column_id
AND dc.definition = ‘(getdate())’
[/sourcecode]

Converting to UTC Time

In preparation for a migration to Azure SQL Database, we found we had to adjust our date formats to UTC time as this is the way dates are stored in azure because your databases could be all over the world.  For new systems, this is very useful; however, what about migrating data to Azure?  I have tables with dates based on a GETDATE() default and this time will be the time zone for the server where the record was created.  I wanted to convert my dates to UTC time and came up with this script.  It is based on servers in the EASTERN time zone.  As the date for daylight savings time changes each year, I used timeanddate.com for the historical data.

Suppose I have a table named Customers with a column called CreationDate and I wanted to update my times to UTC. I would apply this logic. As always, test before implementation. I put an ELSE statement at the end so I could check to see if any dates fell outside my range. This only goes back to 2009. You would have to consult timeanddate.com for more dates if you have older data.

[sourcecode language=”sql”]
UPDATE Customers
SET CreationDate = CASE
WHEN YEAR(CreationDate) = 2009 AND CreationDate BETWEEN ‘2009-03-08 02:00:00.0000000’ AND ‘2010-11-01 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2009 AND CreationDate NOT BETWEEN ‘2009-03-08 02:00:00.0000000’ AND ‘2010-11-01 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2010 AND CreationDate BETWEEN ‘2010-03-14 02:00:00.0000000’ AND ‘2010-11-07 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2010 AND CreationDate NOT BETWEEN ‘2010-03-14 02:00:00.0000000’ AND ‘2010-11-07 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2011 AND CreationDate BETWEEN ‘2011-03-13 02:00:00.0000000’ AND ‘2011-11-06 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2011 AND CreationDate NOT BETWEEN ‘2011-03-13 02:00:00.0000000’ AND ‘2011-11-06 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2012 AND CreationDate BETWEEN ‘2012-03-11 02:00:00.0000000’ AND ‘2012-11-04 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2012 AND CreationDate NOT BETWEEN ‘2012-03-11 02:00:00.0000000’ AND ‘2012-11-04 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2013 AND CreationDate BETWEEN ‘2013-03-10 02:00:00.0000000’ AND ‘2013-11-04 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2013 AND CreationDate NOT BETWEEN ‘2013-03-10 02:00:00.0000000’ AND ‘2013-11-04 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2014 AND CreationDate BETWEEN ‘2014-03-09 02:00:00.0000000’ AND ‘2014-11-02 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2014 AND CreationDate NOT BETWEEN ‘2014-03-14 02:00:00.0000000’ AND ‘2014-11-02 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2015 AND CreationDate BETWEEN ‘2015-03-08 02:00:00.0000000’ AND ‘2015-11-01 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2015 AND CreationDate NOT BETWEEN ‘2015-03-08 02:00:00.0000000’ AND ‘2015-11-01 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
WHEN YEAR(CreationDate) = 2016 AND CreationDate BETWEEN ‘2016-03-13 02:00:00.0000000’ AND ‘2016-11-06 02:00:00.0000000’ THEN DATEADD(hh,6,CreationDate)
WHEN YEAR(CreationDate) = 2016 AND CreationDate NOT BETWEEN ‘2016-03-13 02:00:00.0000000’ AND ‘2016-11-06 02:00:00.0000000’ THEN DATEADD(hh,5,CreationDate)
ELSE ‘2046-01-01 11:07:20.6500000’
END
FROM Customers
[/sourcecode]

Granting SSIS Package Administration Access to Developers

In many instances there is a development team or even a warehouse team that needs to run SSIS packages and you want to store those packages in SQL Server and run them through the SQL Agent Service.  You may not; however, want to grant those users/group sysadmin rights to the machine.  The following describes my experience in setting this up.

SSIS_DB1First, you must add the user/group to the DCOM Configuration for “Microsoft SQL Server Integration Services 11.0” (SQL 2012), which can be found in the Component Services MMC.  Right click and choose properties.

 

 

SSIS_DB2You can now add the groups to two security sections–“Launch and Activation Permissions” and “Access Permissions”.  It was my experience I had to have them in both groups.  I granted full rights in each group.

The final component is to add each user/group to the local group “Distributed COM Users”.  It seems like it won’t even authenticate a user for the groups you set earlier unless they are in the local group.  This will allow users to connect the Integration Services Instance.SSIS_DB3

At this point you should restart the Integration Service services.

If you haven’t already, you can go ahead and create your Catalog Database.  We won’t go over those steps here–perhaps in another post.

The last step is to allow them to deploy and manage the packages on the servers.  I add the group to the SSISDB and grant the role of ssis_admin.  Now your teams should be able to function completely within the SSIS parameters and not have permissions to drop database objects or modify security settings.

SSIS_DB4

The Local Node is Not Able to Communicate With the WSFC Cluster

I was setting up an availability group in SQL Server 2012 on a pair of servers and came across the following error.  “the local node is not able to communicate with the wsfc cluster”  Interestingly enough, I had already installed an availability group on these two servers in another domain.  I was changing domains (long story) and had decommissioned the cluster and wanted to set it back up.  I knew I had already done the following

  • Installed the latest Service Pack (2)
  • Validated the cluster

The fix was simply to remove the Availability Group option, re-apply it and then restart the SQL Server Service.  After that, I was off and running.

LocalNodeCannotCommunicate