D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.



My alternative to scraps of paper lying about.



Others may find bits of it useful!








Monday, 25 January 2010

CTE maximum recursion error- SQL

I was running a CTE an got the following Error message:

'The statement terminated. The maximum recursion 100 has been exhausted before statement completion.'

The statement would undertake over 100 recursions hence the message. To rectify, I added the following query hint:


OPTION (MAXRECURSION 500);

Overriding the default of 100 for this particular statement.

Saturday, 16 January 2010

Syntax for checking objects existence - SQL

SET NOCOUNT ON;
USE
database;
GO
IF OBJECT_ID('dbo.table) IS NOT NULL
DROP TABLE dbo.table
GO
CREATE TABLE .....

Friday, 15 January 2010

Example Select Case to get time period bandings - CR

Example Select Case to get time period bandings:

select ({fldName}-currentdate)
case upfrom_ 365 : "12 months"
case 271 to_ 365 : "9 months"
case 181 to_ 271 : "6 months"
case 91 to_ 181 : "3 months"
case 31 to_ 91 : "1 month"
case 0 to_ 31 : "IMMINENT"
case -90 to_ 0 : "OVERDUE - by up to 3 Months"
case -180 to_ -90 : "OVERDUE - by up to 6 Months"
case -365 to_ -180 : "OVERDUE - by up to 12 Months"
case -545 to_ -365 : "OVERDUE - by up to 18 Months"
case -730 to_ -545 : "OVERDUE - by up to 24 Months"
default: "CHECK STATUS"