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!








Sunday, 19 July 2015

D J Horton Consulting Ltd

Not been posting much on here lately as I've been preoccupied setting up my business:
D J Horton Consulting Ltd.
http://uk.linkedin.com/in/domhorton

Offering IT services in SQL Server, T-SQL, SQL Server Reporting Services, SQL Server Integration Services, Salesforce data migrations, administration & development, Informatica Cloud Services, and AutoCAD!

Monday, 20 April 2015

combine multiple rows into one

LEFT OUTER JOIN (

SELECT ref, STUFF

((SELECT ' ' +

com_text

FROM comm

WHERE ref = q1.ref





ORDER BY comseq ASC

FOR XML PATH('')), 1, 1, '') [Comments]

FROM comm AS q1

GROUP BY ref

) AS Comm ON Comm.ref = x.ref

Tuesday, 6 May 2014

Green bar for a group header - SSRS

=

IIF(RunningValue(Fields!xxxx.Value,
CountDistinct, Nothing) mod 2, "WhiteSmoke", "White")

Tuesday, 24 December 2013

List all Stored Procedures within a db - SQL

SELECT
*

FROM
TFSheffieldNew.INFORMATION_SCHEMA.ROUTINES

WHERE
(ROUTINE_TYPE = 'PROCEDURE')

Monday, 28 October 2013

Auxiliary Numbers Table - Populated by CTE (Based on Itzik Ben-Gan)

USE [Auxiliary]
GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE
  [dbo].[Nums]
([n] [int] NOT NULL, PRIMARY KEY CLUSTERED
 ([n]  ASC ))
GO
;WITH x00(n) AS (SELECT 1 UNION ALL SELECT 1),
x02 (n) AS (SELECT 1 FROM x00 a, x00 b),
x04 (n) AS (SELECT 1 FROM x02 a, x02 b),
x08 (n) AS (SELECT 1 FROM x04 a, x04 b),
x16 (n) AS (SELECT 1 FROM x08 a, x08 b),
x32 (n) AS (SELECT 1 FROM x16 a, x16 b),
cTally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) FROM x32)
 
INSERT INTO Nums(n)
SELECT * from cTally
WHERE n <= 1000000;
GO



 Based on the CTE used here:
http://sqlreportingservicescrystalreports.blogspot.co.uk/2009/12/auxilary-cte-of-numbers-sql.html