Flexible update trigger - take the column value when provided, otherwise have the trigger provide a value

We have a small, departmental database with some tables that will sometimes be updated via a batch process, and sometimes via a people inputting the data directly in a datasheet. Here's a neat trick to allow us to have auditing fields (UpdateDate , LastUpdatedBy) that will either reflect the value provided by the update statement, OR provide a default value, if no value is provided in the update statement.  The trigger does this using the Update() function, which has apparently been around for a while, but I'd never used it before.

Here's the trigger:

 IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID('dbo.trg_Update_SLAMetric'))  
 DROP TRIGGER dbo.trg_Update_SLAMetric  
 go  
 CREATE TRIGGER trg_Update_SLAMetric  
 ON dbo.SLAMetric  
 AFTER UPDATE  
 AS  
 -- Update UpdateDate , LastUpdatedBy  
 UPDATE SLAMetric  
 SET   
   UpdateDate = GETDATE()  
   ,LastUpdatedBy =   
     case   
       When UPDATE(LastUpdatedBy) then Inserted.LastUpdatedBy  
       else SUSER_NAME()  
     end  
 from dbo.SLAMetric SLAMetric  
   join Inserted  
     on Inserted.SLAMetricID = SLAMetric.SLAMetricID  

It correctly updates the LastUpdatedBy field in both situations - updating the table and including the LastUpdatedBy field, and also just updating other fields (in which case it's just set to SUSER_NAME().

 update slametric set MetricNumerator = 0  
 select * from slametric  
 update slametric set LastUpdatedBy = 'test'  
 select * from slametric  

Microsoft Azure error - "Cannot open database "master" requested by the login"

I've been busy setting up a database for a TSQL programming class that I'll be teaching in a few weeks. I've run into a few things that made me scratch my head. Here's one of them.

I created a sample database, a new login, and a user to go with the login. Then I gave the user read only access to the database, via sp_addrolemember.

When I used that login to connect via SSMS (File, Connect Object Explorer, enter the new login, also click on the Options button to specify the correct database), it appears to connect fine, and I see the connection.

However, when I expand the databases tab, and try to right-click the sample database to get the New Query option, I get the message "Cannot open database "master" requested by the login. The login failed.". So even though I was trying to open a query in the sample database, it tried accessing the master database.

Workaround:
This is a strange one, but the workaround is to right click on the connection object, and not the database. This will allow you to open up a new query, and will automatically put you in the database you specified in the connection options.

Doing this involves a bit of rethinking, because most developers who have worked extensively with SSMS are accustomed to right clicking on a database and selecting New Query to open a connection. But this won't work with Azure, unless you're also a user in the Master database.

Hopefully this write-up will save a few people some time when they go searching for details on this issue. Feel free to comment with more information.

Error "The database entered is not valid" when trying to set a default database, connecting to SQL Server Azure

Are you getting the error "The database entered is not valid" when trying to set a default database in the ODBC Data Source Administrator, DSN configuration? I did too, and it took me a while to figure out the root cause.

It turns out I was using the SQL Server driver (version 6.01.7601.17514) , when I should have been using the SQL Server Native Client 11.0 driver (2011.110.3000.00)

There are apparently a few other causes of this error as well, but switching to the more recent driver took care of the issue for me.

Review - Microstrategy Analytics Express

I'm doing a review of Microstrategy Analytics Express, and thought it might be interesting to jot down a few things I run into as I'm working my way through it. I'm connecting with a SQL Server Azure 11 database, onto which I've loaded the AdventureWorks 2012 sample database.

A few notes, in no particular order:

- I'm getting the below error a lot when I try to view the available tables:

TypeError - Cannot read property 'n' of undefined

I've narrowed it down, and it appears to happen whenever the table I'm trying to view has a column with a data type of HierarchyID, or any user-defined data type. Which, unfortunately, is about 90 percent of the tables in the AdventureWorks sample database. This is quite problematic. So far I haven't found a workaround.

- In the manual, there's a distinction made between the "Quick, easy-to-use dashboard", and the "Customizable, detailed dashboard". These phrases are repeated over and over again. For the sake of clarity, they really should have figured out a name for these 2 types of dashboards - for instance, Lite Dashboard and Superuser Dashboard. It's a little confusing to read these phrases again and again when two types of objects like this would be given different names.

- It looks like for text files, only comma delimited files are supported. That's a little surprising - most products have supported multiple delimiters for decades.

- It's not possible to edit a the underlying source of a dataset in Analytics Express. A dataset is an imported (not linked) set of data. Once you've imported, it you can't edit the underlying SQL in any way. You can, however, do things like put an expression on one of the fields. You also can't name a dataset. It's automatically given a name which is the name of the underlying table or tables with the date/time appended to it.

- When creating a new dataset, the field names are interpreted for you - Microstrategy tries to guess whether it's a metric, or an attribute. Frequently, however, it guesses wrong, and I haven't found a way to correct it. For instance, a column name of BusinessEntityId is determined to be a metric, and there's no way to reclassify it.

- When creating a new dataset, once you've chosen your fields, you'd expect them to actually show up when you click "Continue". But it doesn't. The fields that show up are the fields from the first dataset you've ever created. I was scratching my head over this, wondering what incorrect button I clicked on, or option I picked incorrectly. But I've redone it a few times now, and this is really what happens. If you actually want to see the resultset from the dataset you just created, you have to close down the grid that shows up by default, and open up the one you just created. It's confusing.

I'm looking forward to trying out the Desktop Developer option, which I'm assuming doesn't have some of the limitations of the online Analytics Express.

Troubleshooting slow SQL Server stored procedures - using the Modulo function to improve productivity

 Recently I needed to create a stored procedure to process and cleanse a huge set of call center data on a daily basis - more than half a million calls a day. And because data could be backed up and unavailable at times, sometimes a run of multiple days would be necessary, so many millions of rows. 

While I was still working out the logic of the cleansing, my procedure was still pretty slow. Even one day's run took 10 minutes. This meant that every time I needed to tweak the logic, it would take at least 10 minutes to examine the results! 

That kind of work pattern gets old quickly. The problem with having to take a 10 minute break all the time is that task switching is a pain and makes you forget things. It's easier to sustain focus if you don't have to spend 10 minutes out of every 20 working on something else.

So, how to speed up the procedure and get rid of all those inefficient breaks?  For this stage of my work, it would have been fine to work with a much smaller subset of data, as long as I got a good sampling of records. This little hack allowed me to get this subset of data. My first step was creating a temporary table to bring in the records to be processed. So while I was still working out the logic, I added a where clause like this to get a much smaller subset of data:

 where   
   convert(date, CallEnd) in (select CallDate from @DatesToFetch)  
   -- Just get 1 out of 100 records for now  
   and CustomerCallD % 100 = 0   

This uses the Modulo function (%), which gives the remainder of the division of one number by another. In the above example, the CustomerCallD was an identity value increasing by 1 each time, so it would get every CustomerCallID ending in 00 (where the remainder of CustomerCallID divided by 100 was 0). This was a great way to cut back dramatically on the time required to run the procedure, yet still get a great sampling of records. Then when you're ready to run the full set of data, just comment out the filter.

Microsoft SQL Server online documentation is improving

Previously, when I was looking for quick information concerning syntax or other SQL Server details, I avoided the Microsoft SQL Server online documentation. Why? Because it was far too difficult to extract the information you needed from the gobs and gobs of text, with every possible variation of every single parameter.

Check out this page from the SQL Server 2008 official documentation, and see how long it takes you to get a quick example of how to to create a unique constraint on a table.

Did you notice that there's 764 lines on the page? And all the examples are at the bottom. The specific example I was looking for was on line 508, in a list of 14 examples.

That's why I usually went straight to the third party sites such as StackOverflow and w3schools. They have single topic short pages, with examples front and center - you can get what you need in a few seconds.

But I was recently reviewing the online documentation, specifically for SQL Server 2014, and it's improved tremendously. Take a look at this page, on creating a check constraint. It's short, to the point, and the examples are not difficult to find. Honestly, I'd still go to the third party sites before I'd go to the official documentation, just because they're still more concise and to-the-point. But now the official documentation at least has a chance.


************************
EDIT 11/11/2014
************************
So, Microsoft documentation has been slowly improving, which is great. But I've recently been playing with Redshift, the Amazon AWS data warehouse product. Now there's some well done documentation. I had been reading up on a new feature that they had - IF NOT EXISTS for table creation and dropping. Since I was working through getting a Redshift cluster set up, I decided to try the new feature, looking at this page of the documentation.

But the feature didn't work. Since every page of the online documentation has a Feedback button, I sent some feedback saying that the feature didn't work. Within 2 hours, I got a friendly email back, saying that I had discovered an "undocumented feature", and that they were working on fixing it. Wow!  Very impressive.

How to search for a string in your SQL Server database code

*****************************************************************
EDIT May 27, 2014: Forget this!  Really, just forget it and use the Red Gate SQL Search tool.  I just downloaded it and have been using it all morning. It's free, functional, searches EVERYTHING at the same time (job steps, tables, columns, etc), and has a very usable interface. This will replace ALL of my queries against system objects.

One tip - if you need to search different servers, you may find it difficult to switch database servers within the Red Gate SQL Search tool. The drop down doesn't appear to work. I've found that the only way to do that is to close the tool, go to the server you want in Object Explorer, and then open the tool again.  You will now be searching the correct server.
*****************************************************************

As anyone who's been a developer for any length of time knows, what you're doing most frequently is NOT working with a fresh slate on a brand new project. No, it's less fun but much more common that you'll be working on enhancing, fixing, modifying, or otherwise updating code that's already out there. And this is another thing you figure out very soon in your career - documentation for the average database product that's internal to a business is almost always either non-existent, or inaccurate.

So, a very important skill for the developer is the ability to research, and figure out what's out there already.

I like to use the following query to search for code. It will return all the code (including views, stored procedures, functions, etc) that contain the string (table name, view name, stored procedure name, etc) you're searching for. There are add-ons that have similar functionality, but I'm not always able to install an add-on.

A huge bonus with this query is the XML field that contains the definition. When you click on it, you can see the actual text of the definition in a new window - no sp_helptext needed!

 SELECT  
   Type =  
     case  
       when OBJECTPROPERTY(object_id, 'IsProcedure') = 1 then 'Procedure'  
       when OBJECTPROPERTY(object_id, 'IsView') = 1 then 'View'  
       when OBJECTPROPERTY(object_id, 'IsTableFunction') = 1 then 'TableFunction'  
       when OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1 then 'ScalarFunction'  
       else 'other'  
     end  
   ,Name = OBJECT_NAME(object_id)   
   ,Definition = CAST((SELECT definition AS [processing-instruction(x)] FOR XML PATH('')) AS XML)   
 FROM sys.sql_modules  
 WHERE  
   Object_definition(object_id) LIKE '%hier%'  
 order by 2, 1 

This is the output you'll get. Try clicking on the definition field to open it up in a new window.