Specifying time zone in SQL DATETIME DEFAULTs

You’ve had your existing application running for years, relying on DEFAULT constraints to set DATETIME fields to the current date using GETDATE(). Everything was great until you migrated to Azure SQL Database – and realized that your SQL Server is now set to UTC, and you can’t change it.

Fortunately, you can change your DEFAULT GETDATE() constraint to save date in your time zone. Just specify the default as shown below, specifying your time zone of choice:

default convert(datetimeoffset, getdate()) at time zone 'Eastern Standard Time'

To prove this, run the following, and note the output will show the time in Eastern Standard Time as opposed to UTC.

create table #test (
     val nvarchar(max),
     dt datetime not null default convert(datetimeoffset, getdate()) at time zone 'Eastern Standard Time'
 insert into #test ( val ) values ('yo')
 select * from #test

Voila! No more time zone issue.

Query a SQL server to find the progress of a database restore

This just came in from a co-worker. Too valuable not to share!

In case you might want to monitor the progress of a database restore on your SQL server, this query shows the progress in percentage, elapsed time, etc…

	r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
	CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
	CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
	CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
	CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
	CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
		CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
		FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

With that, you can really tell how far along a SQL restore is!

Getting Dynamics CRM ObjectTypeCode values via SQL

Lots of tables in Dynamics CRM use the ObjectTypeCode to identify the entity. Sure, we all remember that Account is ObjecTypeCode 1 and Contact is ObjectTypeCode 2… But what about your custom entities, which start at 10,000 and are not guaranteed to be the same in different organizations, even if you have the same solutions installed — how will we remember their ObjecTypeCode values?

You don’t remember them, you query them when they need you. It’s quick and easy if you are on premises and have access to the SQL database. (If you don’t, ask your DBA to create a view with this query and give you rights to it.)

select coalesce(OriginalLocalizedName,name) as DisplayName, Name as SchemaName, ObjectTypeCode
from EntityLogicalView
order by ObjectTypeCode

Much easier than memorizing, and avoids the problems of remembering the wrong number for the wrong organization or deployment. Oops!

How entity relationships can kill performance in Dynamics CRM 2011

Microsoft Dynamics CRM 2011 allows us to specify relationships between entities, and gives us some configuration as to how those relationships work. One of those relationship types is “parental” — which effectively means, “anything that happens to the parent happens to the child.” Therein lies a lot of power — and a lot of risk.

In the environment where I work, we have a lot of entities. Two of those entities are a “Contract” entity (not the out-of-the-box Contract entity, which we renamed to a more appropriate “Service Contract”) and a “Contract Volume” entity. Intuitively, since you can not have Contract Volume without a Contract, we set the relationship type to parental, with Contract being the parent to Contract Volume.

We have thousands of Contract records, and hundreds of thousands of Contract Volume records. The data for these comes from a separate proprietary application. Data is inserted/updated into CRM from this other system using Scribe Insight. The entities are read-only in CRM, so we don’t have to bi-directional synchronization of data. All was well, and we moved data for a couple weeks without issue.

In a recent release, we expanded the functionality of CRM and found the need to customize the owner of the Contract entity to one of three different CRM teams. So, we did what seemed sensible: update the Scribe package to set the owner based on the criteria we came up with. Unfortunately, running this package took down our CRM system within a minute. The database server was overloaded, and SQL blocks were everywhere.

The problem was our relationship. In a parental relationship, any deleting, sharing, and assigning of the parent will affect all of its children, The effect of this is:

  • Deleting a parent will delete all child records.
  • Sharing or un-sharing a parent will perform an identical share or un-share on all child records.
  • Assigning (changing the owner of) a parent will assign (change the owner of) all child records.

Think about the potential impact of this. If a single record has 10,000 child records, and you assign this record to another user, CRM must perform the same operation against all 10,000 child records — as one operation. Add on top of that CRM’s built-in overhead (auditing, principal object access, logging, etc.), and you’ve effectively killed your system, which likely can not keep up with the scope of your request while dealing with other people’s requests.

A better solution for us was to do the following:

  • Change the Parental relationship to a Referential relationships, where there is no cascading, and a delete will simply remove the parent reference, leaving the child an orphan with no parent.
  • Create a cleanup job to delete all the orphaned child records. This can be done via a a Scribe job, a recurring bulk delete job, or manually.

Dynamics CRM has a lot of power, but you have to think about the effect of the choices made, because they very easily can cause unintended consequences — as they did for us recently!

Finding out which tables in a SQL database have no rows

How many times have you looked at a production database and found a table that has no rows in it? Then, when questioning the developers, you are told, “Oh, that table was never used.” (That’s slack undertow.) It happens to me often enough, so I wanted to come up with a way to easily find all the tables in a SQL database that have no rows.

One line of SQL (which actually runs three lines of SQL, once for each table in the database) is all you need:

exec sp_MSforeachtable 'declare @count int; select @count = count(*) from ?; if @count = 0 print ''?'';'

Run that, and the output will list the schema and table name of every table in the current database that has no rows.

Executing native SQL using NHibernate named queries

I’ve been doing a lot of work with NHibernate lately, particularly with named queries. It took a while to get it just right, so I figured it would be helpful to others (and to myself in the future) to note some of the gotchas and how-to steps to get it just right.

What is a named query?

An NHibernate named query is essentially a native SQL statement that can be invoked by NHibernate and can return strongly-typed object data. It allows you to leverage native SQL code — parametrized statements or stored procedures — to perform complex data manipulation and retrieval.

In other words, let’s say you’re writing a baseball game, and you have three objects in your domain model: a Player, a Team, and a DrugTestResult model. You write a stored procedure, spSelectPlayersByLastDrugTestDate, which returns all players who haven’t had a drug test since a given date.

Now that you have a domain model and a SQL statement, how do you execute it through NHibernate?

Mapping your named queries

NHibernate needs to know about your named queries in order to execute them. Much like you tell NHibernate about your domain model using XML mapping files (*.hbm.xml), you tell NHibernate about your named queries using those same files.

Since the named query mappings do not relate directly to your domain model mappings, you can create a separate mappings file just for your named queries. A sample mappings file for our spSelectPlayersByLastDrugTestDate could look like the following.

< ?xml version="1.0" encoding="utf-8"?>
<hibernate -mapping xmlns="urn:nhibernate-mapping-2.2">
<sql -query name="FindPlayerByLastDrugTest">
<query -param name="LastDrugTestDate" />
<return class="Player">
<return -property column="PlayerID" name="PlayerID" />
<return -property column="PlayerName" name="Name" />
<return -property column="TeamID" name="Team" />
<return -property column="LastDrugTestID" name="LastDrugTestResult" />
exec spSelectPlayersByLastDrugTestDate @LastDrugTest=:LastDrugTestDate

The named query part of the mapping is in the <sql-query> section. I’ll describe its most important sections below.

  • The sql-query tag defines the named query. The name attribute is the name that NHibernate (and, in turn, your code) will use to reference the query. In our example, we are stating that this named query is called FindPlayerByLastDrugTest.
  • The query-param tag defines a parameter in your query. The name attribute reflects the name by which you are referring to that parameter, not the parameter name in the actual SQL statement. The type is the data type of that parameter. In our example, we have one parameter, LastDrugTestDate, which is a DateTime.
  • The return tag defines the type which is returned by the named query. In our example, the return type is a class, Player. Our SQL statement must therefore return fields which map to the Player object. Note that named queries do not have to return domain model objects; they can return ordinals, other types, or nothing at all. (As a result, the return tag is optional.)
  • The many return-property tags tells NHibernate how to map a column in the query results (the column attribute) to a property name in the return type. NHibernate gets rather crafty here. For normal properties (PlayerID and Name in our example) the mappings are simple column-name-to-property-name. Note, however, the TeamID mapping, where we map a TeamID in our result set (which could be an integer, the primary key for a Team domain object) to a property named Team. In our domain model, the Player.Team property is not an integer, it is a Team. NHibernate, because it knows how a Player relates to a Team, will auto-map the player to the team. This nuance is important in getting your named queries to work with your domain model: you map foreign key values in your result set to objects in your data model. (A similar thing happens for the LastDrugTestResult property.)
  • Finally, after we close our return tag, we provide our actual SQL statement. This is a standard parametrized SQL statement with a twist. To inject your query-param values into the SQL statement, you specify :Name — that is, a colon followed by the name specified for the query-param.

Got all that? Good. We’re almost done!

Running your named queries

Finally, the easy part — running the named query! Named queries are run against the NHibernate ISession. Various methods are available to set parameters using a fluent interface.

Running our FindPlayerByLastDrugTest query could look like this:

// get your NHibernate session however you normally would
//ISession session = ...

// create an IQuery based on your named query, specifying parameters
IQuery namedQuery = session.GetNamedQuery("FindPlayerByLastDrugTest")
.SetDateTime(new DateTime(2003, 1, 1));

// execute the query
IList list = query.List();

// if you were running a query that returned a single, scalar value, you could say...
var result = query.UniqueResult();

Note that there are generic versions of List() and UniqueResult(), but I had problems getting them to work. Not sure why (I didn’t dig that far), but the above queries will do the same (though they require some type-casting).

Named queries do a lot more. The following links may prove useful in your learning.

If you find other great resources, be sure to share!

Scripting the deletion of objects in a SQL database (second version)

A few weeks ago, I wrote a post about deleting all tables and constraints in a database. It’s time to take that one step further!

The following SQL code will delete all stored procedures, user-defined functions (UDFs), views, table constraints, and tables (in that order) from a given SQL database. As with the original, you can specify an object name previx by changing the set @tblname = '' as needed.

This is helpful in two situations:

  • When you want to delete all objects in a database without dropping/recreating it, in which case you’d specify '%' as the @tblname.
  • When you want to delete all objects in a database whose name starts with a certain sequence of characters. For example, if you want to delete all the ASP.Net membership objects in a database, you can specify '%aspnet_%' as the @tblname, since its SQL objects start with aspnet_ or vw_aspnet_.

Note that you should be very careful any time you use scripts like this, as data loss can happen where you least expect it. I use this script for clearing out development databases, but would never use it for production databases.

Anyway, on to the script.
Continue reading

Remove anonymous users from ASP.Net Membership tables

If you use the ASP.Net membership tools, have <anonymousIdentification enabled="true" /> specified in your Web.config, and get lots of anonymous visitors, it’s only a matter of time before your database grows. What’s filling it up is the countless user records for your anonymous users.

If you don’t need to track user and profile information for an anonymous user once they leave the site, you can delete the unneeded data by running a SQL script. The following script will delete from your membership tables all anonymous users whose last activity was more than 7 days ago.

delete from aspnet_profile
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7

delete from aspnet_usersinroles
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7

delete from aspnet_membership
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7

delete from aspnet_personalizationperuser
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7

delete from aspnet_users
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7

Doing this may be very important to those using a shared hosting plan that has limited SQL disk space, as those anonymous users can quickly eat up disk space. Since each row in aspnet_users takes up just over 1kb, having 1,000 anonymous users will eat up 1MB — something that adds up quick if you only have 250MB of SQL disk storage.