Category Archives: SQL

SQL Triggers

By | | ,
Leave a comment

A fact which seems buried to me is that an after trigger actually does still have access to both the inserted and deleted virtual tables.

So if you have been struggling to re-implement a simple insert, update, or delete because you feel you must use an instead of trigger in order to access the values in inserted or deleted, relax.  You’re doing it wrong.

I can beef this up with some code, and I suppose I will, but the code is not the issue–it’s the concept, and once I learned to rely on the inserted and deleted tables persisting until my trigger itself goes out of scope, my life got much simpler.

Continue reading →

Been Busy up in Heah!

By | | , , ,
Leave a comment

Man, I’m tired.  I’ve been clipping along at work, back to wa-a-ay long days on a project I like a lot, and whacked a test on Tuesday.  Only missed one question on the ITIL V3 Foundation exam.  I resisted the urge to stomp and glower while muttering and swearing about having missed a question, as the other guy finished and failed, and the third guy was visibly agitated as he kept plugging away.  So I’m good for ITIL something or other.  Thanks Knikki!

I also took the ITIL Service Operations class offered by HP a few weeks ago, and have the test for that coming up soon.  The place I work is implementing improvements along the ITIL lines, and running us through these courses.  So it feels darned … Continue reading →

SQL Tip of the Day

By | | , , ,
Leave a comment

COLLATE DATABASE_DEFAULT

This is a work-around, and a dangerous one, but it works in a pinch. If you are getting collation errors such as “Cannot resolve collation conflict for equal to operation” bubbling up from SQL Server through your application, you are likely trying to JOIN two datasets of incompatible collations.

The quick and dirty workaround is to COLLATE on the fly just as you would CAST a variable for a read but not a write. So you’ll do it in a SELECT statement, which I am happy to report works just fine.

Sample problem:

SELECT Trusty.Age, Shifty.Height FROM MyGoodDataSource AS Trusty INNER JOIN SketchyDataSource AS Shifty ON Trusty.LastName = Shifty.LastName

ERROR! “Cannot resolve collation conflict for equal to operation”

So we’re going to CHOOSE TO INTERPRET THE … Continue reading →

SQL Query of the Day — Group By Year

By | | ,
Leave a comment

This fellow has a blog, and it’s good: Grouping datetime by date in T-SQL « SQL Disco.  Many times in SQL you need to group by year, or by date, and what you have to work with is a big nasty DATETIME field.  I needed to find out how many updates had been done each year, and I had gnat’s ass quality timestamps, down to the second.

I know what you’re thinking, but if you use CONVERT(dtgMyDate), you’re doing it wrong, as our friend at SQL DISCO points out.  In his example, he groups by day; in this one, I have modified it to group by year:

SELECT DATEADD(yyyy,(DATEDIFF(yyyy,0,dtgMyDate)),0),
COUNT(*)
FROM Table1 GROUP BY DATEADD(yyyy,(DATEDIFF(yyyy,0,dtgMyDate)),0)

You see what he does there? Instead of costly CONVERT, … Continue reading →