The Heap™ – Consultancy ©®

4:40 PM

I've found a major bug in the most distributed audit trigger code for SQL server. I've seen this code in many places but one(!) table (on my local machine) wasn't writing any changes to the audit table. So I've investigated ( for a few hours) and found that the bits operation with COLUMNS_UPDATED() wasn't right. In fact MSDN says that we should not rely on ORDINAL NUMBER of a column but the COLUMN_ID.

And here you can see the problem. I have no idea why there is no columnId 2.

Maybe a deleted column ?

The problem was that the bit operations failed since there was no column 2

Anywasy this is the fixed version jsbin.com/lafayiluri/1/edit?html,output

Now how do I tell the world to change the most common audit trigger ? well I guess i'll say it here only.

And finally for the visual comparison :

That's all.

Max Vernon

5:15 PM

@RoyiNamir write up a question on the main site, then provide an answer showing what you've found.

5:55 PM

It will be closed within 2 seconds as a duplicate

Andriy M

@RoyiNamir Duplicate of what?

Andriy M

6:11 PM

@RoyiNamir Perhaps it would be even better to replace INFORMATION_SCHEMA.COLUMNS with sys.columns. That way you wouldn't need to use COLUMN_PROPERTY and instead could use the column_id directly from the system catalogue.

6:45 PM

I guess you're right

7:33 PM

@RoyiNamir Do the results change if you replace information_schema tables with the appropriate sys tables?

I remember Aaron Bertrand mentioning here and in many of his answers that INFORMATION_SCHEMA should not be used in SQL Server.

7:46 PM

@ypercubeᵀᴹ I see but I don't see any tablename in that table. ( I need to the WHERE clause)

@RoyiNamir which table?

I think the object_id refers to the table but I have to check.

I didn't say it was trivial ;)

You'll need to join to sys.objects or to sys.tables

or use object_name() function

A: Why DB2 cannot allow creation of database with keyword "BACKUP" in its name?

8:21 PM

Why, oh why is my avatar image changed?

Initially when I had tried with following command, it didn't work. > db2 create database BACKUP or > db2 create database "BACKUP" This shows an error as: "SQL1001N "BACKUP_TEST" is not a valid database name. SQLSTATE=2E000" But after answer from Ian Bjorhovde, I tried with below syn...

This seems like a bug.

Different avatar appears in posts I've edited than my profile

Oh no. It's changed in my answers, too

A: Query multiple tables with foreign key filled or is null

This is what outer joins do. Use a LEFT (outer) JOIN: select t1.id, t1.field1, t2.field2 from table1 as t1 left join table2 as t2 on t1.id_table2 = t2.id ; If you want the empty string (and not NULL) in the result when there is no match, replace t2....

@RoyiNamir Ask a question and self answer? It's bound to get some upvote love :)

Yep. What TomV says ^^^

You could also comment on that simple-talk page with a bug report.

But I'd prefer you post here

Then we can go and comment / tweet / bitch everywhere ;)

And @RoyiNamir would still be blamed for pointing it out initially :)

8:38 PM

Fame in eternity

Oh @RoyiNamir I still owe you one because of the WebAPI help, it's now in production and working perfectly, thanks again

8:49 PM

@TomV Sure any time :-)

Jack Douglas

9:03 PM

@EvanCarroll doesn't backend_xmin just help us find which tuples are being retained due to being needed by a replica (rather than by a local transaction)?

9:13 PM

@JackDouglas @TomV what picture/avatar do you see in my answers?

the same old one, as in this room?

@ypercubeᵀᴹ The usual yes

Jack Douglas

@ypercubeᵀᴹ I see a generic avatar

not the one you have in here