Handling long running queries on SQL Server can be one of the hardest things to deal with as a DBA. You have dozens of variables that must be considered before you can actually do anything about them. Usually if you ask a number of DBAs how they go about determining if the long runtimes can be resolved, you will get several different variations of troubleshooting. Even if the steps are blurred in sequential order, they will typical contain a few of the following

A while back I wrote about “Why English is really important to us non-English speakers.” and I still believe that is true. But lets not forget that English is our second language and that we sometimes miss out on the details, the words between the lines. Sometimes we even missinterpret the whole thing because we misunderstood a word. After All isn’t that what the movie “Lost in translation” was all about ;-), but that would be guessing on my part since I never watched it completely, however beautiful scarlet may be.

I see a lot of people doing this. Dim con As New System.Data.SqlClient.SqlConnection con.ConnectionString = "..." con.Open() Dim command As New System.Data.SqlClient.SqlCommand command.Connection = con command.CommandText = "update tbl set col = 'something'" command.ExecuteNonQuery() command.Dispose() con.Dispose() I like to use the interfaces instead and use the factory methods that come with it. Something like this. vbnet Dim con As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection con.ConnectionString = "..." con.Open() Dim command As System.

Last week I was asked to create a normalisation routine for the Msp spectra that my application uses. No worry if you don’t know what Msp is. The thing is that it needed to find the maximum value in a collection of values and then do a calculation with that number over the other values in that collection. First the value object. ```vbnet Namespace Model Public Class MspCoordinate Private _A As Decimal Private _WavelengthInNm As Decimal

So Ward Pond and Michelle Ufford started this SQL rap contest. I replied on twitter that I loved their raps and they told me “bring your skillz!” Well I don’t think I have any skillz (in that regard) so here it goes _Who you trying to get crazy with SA? Don’t you know I’m loco………. ashes 2 ashes and dust 2 dust when u fire up ur queries ur RAM goes bust

A couple of days ago onpnt posted the following blogpost: Import directory of XML files into SQL Server 2005 In that post he was using SQLCLR to import a bunch of files. Some of you might not be so familiar with .NET so I am providing a T-SQL way to do something similar You will need to create a directory testxml on the c drive and put a bunch of XML files in there.

This question came up recently so I decided to blog about it If you have your stored procedures in source control and you want to see if they are the same as the one on the server, all you need to do is script out the procs and do a diff right? Let’s see what happens, create this proc Create procedure prTest as set nocount on if exists (select 1 from sys.

The drive to work today was a short 2 hours over the normal 40 minute commute. It turns out there was a semi that drove off a bridge and landed face first on the train tracks below. This caused several problems with traffic this morning. Both north and south bound lanes of the interstate were at a crawl and to top it off, the major railway that brings commuters to and from the greater Chicago area was halted due to the semi kind of being in the way.

This is my first blog, so please go easy one me. So from what I’ve seen a lot of companies tend to need to see hierarchical data in a report, or see data in a hierarchical structure in a report. Usually the best and most simple way to display this data is a drill down report. For the purposes of this blog I’m going to act as if the reader hasn’t used SSRS before but has used Sql Server 2005 and some knowledge in TSQL.

I’m a fan of not allowing connectivity from Excel to my SSAS instances. I just don’t see the active connection ever being a truely manageable and secure connection. It remains though that the users like the way they do things and will fight hand and foot against change. Although it will be a hard task for you to remove the Excel connectivity to SSAS, you can provide them with a viable replacement given report models in reporting services.