Application roles and connection pooling (Development | .NET | SQL)

One would have thought this to be a well known fact but since one of our developers stumbled upon it yesterday I thought it might be useful to someone else.

As soon as a pooled connection with enabled application role gets reused an exception gets thrown and its description is not really helpful if you’re not aware of the problem: General Network Error. This happens because the security context of the connection doesn’t get properly reset when it is closed. To work around the problem you can either avoid using application roles or disable connection pooling (by adding Pooling=False to the connection string). You can find more details in this knowledge base article.

It might be worth mentioning that the above is completely accurate only for SQL Server 2000. SQL Server 2005 comes with a new stored procedure sp_unsetapprole which can be used to reset the security context. I haven’t tried it but by calling it before closing the connection you should be able to make application roles work together with connection pooling.

10/20/2006 3:09:17 PM (Central Europe Standard Time, UTC+01:00)    Comments [1]

Splitting and joining date and time values (Development | SQL)

Today I’ve been evaluating database designs made by this year’s candidates for work at our company. One of the issues I’ve been encountering all the time was the handling of date and time values. The SQL Server’s common data type datetime for both values has been the source of quite some confusion, especially since the values required to be separate at some occasions and joined at others.

I suppose the decision on whether to store them separate or joined depends on how the values are going to be used. In most cases the extra storage space required by using separate columns for both values isn’t worth it but in some usage scenarios the resulting reduced query complexity and improved indexing usage can justify it.

Either way sooner or later the need for separating or joining the date and time parts will arise. The following query demonstrates both operations in what I believe is the most efficient way:

SELECT
   dateOnly = CONVERT(datetime, FLOOR(CONVERT(float, dateTimeColumn))),
   timeOnly = dateTimeColumn - CONVERT(datetime, FLOOR(CONVERT(float, dateTimeColumn))),
   dateAndTime = dateOnlyColumn + timeOnlyColumn 
FROM DateTable

4/18/2006 10:38:07 PM (Central Europe Standard Time, UTC+01:00)    Comments [1]

Export SQL Server Agent job history (Development | SQL)

The following query is a good starting point if you want to export the SQL Server Agent job history to a file and you’re still using Enterprise Manager from SQL Server 2000. In SQL Server Management Studio from SQL Server 2005 there’s already a command available to do this in the Log File Viewer window accessible from the View History command on the selected job.

USE msdb
SELECT
   J.name, 
   S.step_id,
   S.step_name, 
   H.message, 
   run_status = CASE H.run_status
      WHEN 0 THEN 'Failed'
      WHEN 1 THEN 'Succeeded'
      WHEN 2 THEN 'Retry'
      WHEN 3 THEN 'Canceled'
      WHEN 4 THEN 'In progress'
   END, 
   H.run_date, 
   H.run_time, 
   H.run_duration
FROM sysjobhistory H
   INNER JOIN sysjobsteps S ON H.step_id = S.step_id AND H.job_id = S.job_id
   INNER JOIN sysjobs J ON J.job_id = H.job_id
ORDER BY H.job_id, H.run_date, H.run_time, S.step_id

3/22/2006 11:42:26 PM (Central Europe Standard Time, UTC+01:00)    Comments [0]