question

Blackhawk-17 avatar image
Blackhawk-17 asked

NULL values in COMPUTE Clause

When we run a query and add a COMPUTE clause it runs fine from SSMS.

Run as a Stored Procedure via SQL Agent it fails. This is in the Agent log:

2010-06-04 13:32:20 - ! [LOG] Step 1 of job 'test' (0x4F1D4A3F03FA3148999EE07499A7D7EF) has caused an exception in the TSQL subsystem and has been terminated

This is in the Job History:

Message Executed as user: XXX\SQL_Agent_Service. An exception (0xc0000005) occurred in the TSQL subsystem. The step failed.

There are NULLs in the data. This is on SP3, the behaviour was not observed on SP2. If we comment out the COMPUTE clause it runs fine.

Also noticed is that the output file is locked open and we can't delete it. The Agent is able to overwrite it though. If the job isn't terminated by SQL then the file can be deleted as normal.

The Agent Service account has full control where the file is being created.

Any thoughts out there?

sql-server-200564-bit
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
+1 - nice find. Can you get around it by COALESCEing before the COMPUTE?
0 Likes 0 ·
Blackhawk-17 avatar image Blackhawk-17 commented ·
Looking into it deeper it appears that SUM is returning the NULL. T-SQL won't accept an ISNULL after the COMPUTE though :(
0 Likes 0 ·

2 Answers

·
Matt Whitfield avatar image
Matt Whitfield answered

This one is pretty clearly a bug. I can't see any related KB articles that seem to tie up from the CU packages for SP3, but I haven't had a long look.

It's worth raising this one on Connect...

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered

My recommendation is to not use the COMPUTE clause to begin with. Rather, use GROUP BY WITH ROLLUP or GROUP BY WITH CUBE instead. Please see Books Online for more information.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.