Query Tuning -System Waits - SQL

This query, from http://www.amazon.co.uk/Inside-Microsoft-SQL-Server-2005/dp/0735623139 obtains either the top 90% or at least 10 waits in the system:


WITH Waits AS
(
SELECT
wait_type,
wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%')
SELECT
W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 90
OR W1.rn <= 10
ORDER BY W1.rn;GO


To reset the statistics prior to undertaking analysis:


DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

String manipulation - removing extraneous detail - SQL

I'm currently working on an Integration Services project where I'm pulling filenames from a directory into a database to check for name consistency against a 'master' table.

The filenames take the form:
Abbey Lane Cemetery 03022 Floor Plan
Abbey Lane Primary 4-11 2001 Floor Plan
Abbeydale Grange Secondary 11-16 4254 Floor Plan
Abbeydale Industrial Hamlet 00433 Floor Plan
Aldine House CYP0048 Floor Plan

To check against the 'master' table, the entries need to be in this format:
Abbey Lane Cemetery
Abbey Lane Primary 4-11
Abbeydale Grange Secondary 11-16
Abbeydale Industrial Hamlet
Aldine House

What I needed to do was strip out the 4fig., 5fig. or CYP#### and the words 'Floor Plan' from every entry.

To strip out the words 'Floor Plan' would be simple enough, but was unsure how best to remove the reference number in each case. I first thought that I could create a selection for all entries with 4fig. and remove those, and repeat for all those with 5fig. and again for CYP####.
After much help from Chris Morris on SQLServerCentral.Com, he devised a query that would reverse the text string, find the position of the third space and return only the site name as required.
I combined the query with my Nums CTE to produce:


DECLARE @n AS BIGINT;
SET @n = 100;
WITH Nums AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Nums WHERE n < @n
)
UPDATE CADList
SET FileNM = d.[Output]
FROM CADlist AS c
CROSS APPLY
(SELECT LEFT(c.FileNM, LEN(c.FileNM) - MAX(x.n)) AS [Output]
FROM (SELECT TOP 3 n
FROM Nums AS n
WHERE SUBSTRING(LTRIM(REVERSE(c.FileNM)), n, 1) = ' '
AND n < LEN(c.FileNM)
ORDER BY n) AS x
) AS d


This can be improved by using the more efficient CTE as posted http://sqlreportingservicescrystalreports.blogspot.com/2009/12/auxilary-cte-of-numbers-sql.html
In my case, I'm only dealing with around 400 rows.

Auxiliary CTE of numbers - SQL

Following on from the previous auxilary CTE of numbers, http://sqlreportingservicescrystalreports.blogspot.com/2009/12/create-common-table-expression-of.html
I realised after reading T-SQL Querying by Itzik Ben-Gan that he's created a much more efficient way of doing it:


DECLARE @n AS BIGINT;
SET @n = 100;
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;





taken from:
http://www.amazon.co.uk/Inside-Microsoft-SQL-Server-2005/dp/0735623139

Create Common Table Expression of numbers - SQL

To create a Common Table Expression (CTE) of numbers for use in queries:


DECLARE @n AS INT;
SET @n = 100;
WITH Nums AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Nums WHERE n < @n
)
SELECT * FROM Nums

Create table code for testing purposes - SQL

To create and populate a test table from existing data for use in forums and for testing purposes:

IF OBJECT_ID('TempDB..#testtable','U') IS NOT NULL
DROP TABLE #testtable
----
CREATE TABLE.........
----

Create test data:

SELECT 'SELECT '
+ QUOTENAME(column1,'''')+','
+ QUOTENAME(column2,'''')+','
+ QUOTENAME(column3,'''')
+ ' UNION ALL' FROM existingdatatable

Run the above select statement and copy the resultant output (remove final UNION) into:

SET IDENTITY_INSERT #testtable ON
----
INSERT INTO #testtable(column1, column2, column3)
SELECT.....
----
SET IDENTITY_INSERT #testtable ON

Find and replace - SQL

UPDATE tablename
SET columnname = Replace(columnname, 'Find value', 'Replace value')

Reporting Services Top Level Access Rights - SSRS

To get a list of the top-level folders and associated user permissions:


SELECT Catalog.Name, Catalog.Path, Users.UserName
FROM Catalog INNER JOIN
Policies ON Catalog.PolicyID = Policies.PolicyID INNER JOIN
PolicyUserRole ON PolicyUserRole.PolicyID = Policies.PolicyID INNER JOIN
Users ON PolicyUserRole.UserID = Users.UserID
WHERE (Catalog.ParentID =
(SELECT ItemID
FROM Catalog
WHERE (ParentID IS NULL)))
ORDER BY Catalog.Path, Users.UserName

this code was obtained from:
http://virtualgenius.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=cat%3DSSRS

Reporting Services Access Rights - SSRS

To get a full breakdown of which users have access rights to reports in Reporting Services:


SELECT Catalog.Path, Catalog.Name, Users.UserName, Catalog.Type
FROM Catalog INNER JOIN
Policies ON Catalog.PolicyID = Policies.PolicyID INNER JOIN
PolicyUserRole ON PolicyUserRole.PolicyID = Policies.PolicyID INNER JOIN
Users ON PolicyUserRole.UserID = Users.UserID
ORDER BY Catalog.Path

this code was obtained from:
http://virtualgenius.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=cat%3DSSRS

Checkboxes - CR

Provide Checkboxes for a boolean field.


If {fldName} = true then Chr(254) else Chr(168)


and set the font to wingdings.

Set Operation EXCEPT - SQL

The following returns customers that have made no orders:


SELECT customerid FROM dbo.Customers
EXCEPT
SELECT customerid FROM dbo.Orders

Self Join - SQL

Self join between two instances of the same table. For example, obtaining employees and their managers.


SELECT E.Name as emp, M.Name as mgr
FROM Employees as E
LEFT OUTER JOIN Employees as M
ON E.ParentID = M.EmployeeID

Logical Query Processing Phases - SQL

Phases involved in the logical processing of a query:

(8) SELECT (9) DISTINCT (11) (top_specification) (select_list)

(1) FROM (left_table)

(3) (join_type) JOIN (right_table)

(2) ON (join_condition)

(4) WHERE (where_condition)

(5) GROUP BY (group_by_list)

(6) WITH (CUBE ROLLUP)

(7) HAVING (having_condition)

(10) ORDER BY (order_by_list)

Obtained from:
http://www.amazon.co.uk/Inside-Microsoft-SQL-Server-2005/dp/0735623139/ref=sr_1_2?ie=UTF8&s=books&qid=1257628926&sr=8-2

Display current month - CR

MonthName(Month(CurrentDate))

Get last day of month - CR

local numbervar thismonth := Month(CurrentDate)+1;
local numbervar thisyear := Year(CurrentDate);
local datevar endday;


endday := Date(thisyear, thismonth, 1)-1; //get the last day of the month

Get first day of month - CR

local numbervar thismonth := Month(CurrentDate);
local numbervar thisyear := Year(CurrentDate);
local datevar startday;


startday := Date(thisyear, thismonth, 1); //get the first day of the month

Display every nth record - CR

Put the following formula in the Suppress section of the Section Expert:

remainder((recordnumber,10,)<>0

This will display every 10th record

Condensing large character string and removing carriage returns - SSRS

***Update ***
Function created:

for condensing a large character string and removing any carriage returns (such as in a comments field).

Function shortcomment(byval commentstring as string, stringlength as integer) as string

if commentstring="" or isnothing(commentstring)
return commentstring
else

commentstring=replace(commentstring, chr(10)," ")
if commentstring.length >stringlengthcommentstring=left(commentstring, stringlength) & "....."
return commentstring
else
return commentstring
end if
end if
end function

to implement:

=code.shortcomment(Fields!comments.Value, 10)

where 10 is the condensed length

Format numbers in formula - CR

ToText({fldName}, 0)

0 indicates the number of decimal places

Condensing large character string and removing carriage returns - SSRS

Useful formula for condensing a large character string and removing any carriage returns (such as in a comments field).


=iif(len(Replace((Fields!Comments.Value),CHR(10)," "))>25, (left(Replace((Fields!Comments.Value),CHR(10)," "),25) & "....."),Fields!Comments.Value)


...next stage will be to create a function...

http://sqlreportingservicescrystalreports.blogspot.com/2009/10/condensing-large-character-string-and_22.html

Newline in textbox - SSRS

To create a linebreak in a textbox:


="This is the first line " + VBCRLF + "This is a newline"

Script to determine SQL Server Reporting Services parameters, path and default values - SQL

--Find all the reports, their parameters and default values
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd --ReportDefinition
)
SELECT
NAME
, PATH
, x.value ('@Name', 'VARCHAR(100)') AS ReportParameterName
, x.value ('DataType[1]', 'VARCHAR(100)') AS DataType
, x.value ('AllowBlank[1]', 'VARCHAR(50)') AS AllowBlank
, x.value ('Prompt[1]', 'VARCHAR(100)') AS Prompt
, x.value ('Hidden[1]', 'VARCHAR(100)') AS Hidden
, x.value ('data(DefaultValue/Values/Value)[1]', 'VARCHAR(100)') AS Value
FROM (
SELECT PATH
, NAME
, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS ReportXML
FROM ReportServer.dbo.Catalog
WHERE CONTENT IS NOT NULL AND TYPE = 2
) A
CROSS APPLY ReportXML.nodes('/Report/ReportParameters/ReportParameter') R(x)
--WHERE NAME = 'Sales_Report'
--Use the where clause above to look for a specific report
ORDER BY NAME

taken from Sankar Reddy
http://www.mssqltips.com/tip.asp?tip=1839

Adding Null Values for Multi-Valued Parameters - SSRS

When we use multi-valued parameters, we would want to have null values as an option as we may want it to be selected by default. The problem is, there is no null value for multi-valued parameters. One possible solution could be to create a view that has a null value. To do this, we can make use of the UNION ALL command present in SQL.

CREATE VIEW viewDomainWithNull
AS SELECT -1 AS [Domain Id], 'NULL' AS [Domain Name]
UNION ALL
SELECT [Domain Id],[Domain Name] FROM Domain

Taken from
http://www.codeproject.com/KB/reporting-services/SSRSaaka3.aspx

Remove Embedded image from a Report Definition - SSRS

I'd developed a report that if it was parred down (all datasets, parameters, images, textboxes removed) would make a good template for future reports.

So, having deleted everything to form my template I noticed that the RDL file was a significant size. On opening up, it was still referencing a number of Embedded Images.

To remove:
Report - Embedded Images... - and delete

SQL Server 2005 Failed to install due to ASP Account not being setup - SQL

Whilst attempting to set up a work computer as a testbed I was going through the process of installing SQL Server 2005 server on XP. I had previously installed all of the components including IIS, .Net framework, etc. In the middle of the install, I got the following error:

SQL Server Setup failed to obtain system account information for the ASPNET account

To proceed, reinstall the .NET Framework, and then run SQL Server Setup again.
The problem is that the user ASPNET was not created at any of the previous installs. To create this user, I did the following (note that framework version may have a different version on your machine)

cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
aspnet_regiis -i

This creates the ASPNET account. After the account is created, SQL Server 2005 installed normally.

This tip was gleaned from:

http://paulsiu.wordpress.com/2007/01/19/sql-server-2005-failed-to-install-due-to-asp-account-not-being-setup/

Format Dates in formula - CR

ToText({fldName}, "dd-MMM-yyyy")

Keep Group Together - SSRS

I believe SSRS lacks the functionality to keep groups of data together.

Say I have a table containin one grouping of data. The report contains enough groups to use more that one 'page' when rendered. The report splits an individual group if it appears at the bottom of the page amd continues with the details on the subsequent page. There seems to be no method of keeping the group together i.e. if the indivual group will not completely fit on the bottom of the page, it will be rendered in its entirety on a fresh page. Thinking about it, if this functionality were available what would happen to a group of data that naturally exceeds one rendered page?

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126895

A work-around that may work....keep a tally of the the number of rows returned and insert a page break after x.....will look into this...

Blank space appears in the file when you export a report to a physical page format file in SQL Server 2005 Reporting Services

Blank space appears in the file when you export a report to a physical page format file in SQL Server 2005 Reporting Services or in SQL Server 2000 Reporting Services
http://support.microsoft.com/kb/938943

Reporting Services Login prompt when deploying solution - SSRS

Just spent 20 minutes fathoming out this issue. I've been transferring a SSRS solution between a 'test' laptop and my work PC, both using local reporting services servers. Whilst deploying an rdl I got a Reporting Services Login prompt, having never seenone before in BIDS I had to do some google searching. Inially I thought it was something to do with IIS security settings but eventually realised that TargetServerURL value was pointing to my other server. So it was simply a case of adjusting this value. Suprised I haven't witnessed this before as I'm sure I've made this mistake in the past

Pagination - SSRS

To remove pagination on the webpage set InteractiveHeight to 0

Date Stamp trigger - SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [trg_test]
ON [dbo].[JobLog]
AFTER UPDATE
AS
BEGIN
IF
UPDATE(JobLogSignOff)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
;
UPDATE JobLog
SET JobLogSignOffTimeStamp = GETDATE()
WHERE
JobLogID in (SELECT JobLogID FROM inserted)
-- Insert statements for trigger here
END
END

No drop trigger - SQL

USE [JobLog]
GO
/****** Object: DdlTrigger [trig_nodrop] Script Date: 07/08/2009 16:59:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [trig_nodrop]
ON DATABASE
FOR
drop_table, alter_table
AS
PRINT
'...you cannot do that, ha ha ha...'
ROLLBACK
;

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trig_nodrop] ON DATABASE

Printed by and time - SSRS

="Printed by " + User!UserID + " on " + Globals!ExecutionTime.ToString()

Execution Time - SSRS

="Execution Time: " +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds <> 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))
)

Page x of y - SSRS

="Page " + Globals!PageNumber.ToString() + " of " + Globals!TotalPages.ToString()

Report Execution - SSRS

--run against ReportServer
USE
ReportServer
GO
SELECT
dbo.Catalog.Name, dbo.Catalog.Path, dbo.ExecutionLog.UserName, dbo.ExecutionLog.TimeStart, dbo.ExecutionLog.TimeEnd,
dbo.ExecutionLog.TimeDataRetrieval, dbo.ExecutionLog.TimeProcessing, dbo.ExecutionLog.TimeRendering, dbo.ExecutionLog.Format,
dbo.ExecutionLog.Parameters, dbo.ExecutionLog.Status
FROM
dbo.Catalog INNER JOIN
dbo.ExecutionLog ON dbo.Catalog.ItemID = dbo.ExecutionLog.ReportID
ORDER BY
dbo.ExecutionLog.TimeStart DESC
-----sum use
USE
ReportServer
GO
SELECT
dbo.ExecutionLog.UserName, COUNT(dbo.ExecutionLog.UserName)
FROM
dbo.Catalog INNER JOIN
dbo.ExecutionLog ON dbo.Catalog.ItemID = dbo.ExecutionLog.ReportID
GROUP BY
dbo.ExecutionLog.UserName
ORDER BY
COUNT(dbo.ExecutionLog.UserName)DESC

A4 Portrait layout - SSRS

For A4 Portrait settings:

Report Properties:
Grid Spacing  0.2cm

InteractiveSize
    Width  21cm
    Height  0cm

Margin all 0.5cm

PageSize
    Width  21cm
    Height  29.7cm

Body Properties:
Size
    Width  20cm
    Height  28.7cm

List of Value Limit - CR

I've just happened upon a major shortcoming of Crystal Reports. A dynamic parameter is only retrieving a limited number of records. On further inspection I found this from Brian Bischof:


'There is an undocumented registry entry that you should know about. By default, CR only reads 1,000 records before creating the list of values to display. You have to add a registry entry to override the default behavior.

HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.0\Crystal Reports\DatabaseOptions\LOV If you are using R2, then change 11.0 to 11.5.

Add a string called MaxRowsetRecords and set a new maximium number of records. Making it 0 gives you unlimited records'

Insert Function - SQL

insert into Establishment ( EstablishmentName, EstablishmentCode)
select Col1, Col2
from test

Bulk Insert Function - SQL

delete from dbo.testlistofestabs;
go
bulk insert testlistofestabs
from 'D:\JOBLOGGING\Establishment import\list of estabs.txt'
with
(
fieldterminator = '\t', --tab
rowterminator = '\n' --new line
)
go

Get current SQL Server Version - SQL

select @@version


For SQL Server 2005:

For SQL Server 2008 R2:

Crystal Reports Processing Engine - CR

If Then Else Functions - CR

if {Element.ElementName} = "True/False"
and {SurveyDataElement.ElementValue}="1"
then "True"
else
if {Element.ElementName} = "True/False"
and {SurveyDataElement.ElementValue}="0"
then "False"

Select Case Function - CR

select ({CADDue_sp;1.DATE DUE}-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 DRAWING STATUS"

Dealing with Nulls - CR

if isnull({ISSUE.DESCRIPTION})
then "There are currently no issues logged against this project"
else ({ISSUE.REFERENCE} & " - " &{ISSUE.DESCRIPTION})

Detailed or Overview report toggled by parameter - CR

Create parameter {?DetailedOverviewReportParam}, two fields with values 1 & 0 and descriptions 'Detailed Report' and 'Overview Report' respectively.

Create formula {DetailedOverview}
shared stringvar fullsuppress := {?DetailedOverviewReportParam}

Put both {?DetailedOverviewReportParam} and {DetailedOverview}in header area, make invisible (to initiate when report is run).

In Section Expert, formula for Suppress (No Drill Down):
shared stringvar fullsuppress;
fullsuppress = '0'

Green bar report - CR

If(RecordNumber Mod 2=0)
then crGreen
else DefaultAttribute


Note: Record Number can be replaced with GroupNumber to alternate Group row colours

Format Dates - SSRS

=Format(Fields!myDate.Value, "M/d/yy") - 2/11/08
=Format(Fields!myDate.Value, "MM/dd/yyyy") - 02/11/2008
=Format(Fields!myDate.Value, "d-MMMM-yy") - 11-December-08
=Format(Fields!myDate.Value, "d-MMM-yyyy") - 11-Dec-2008
=Format(Fields!myDate.Value, "M/d/yyyy H:mm") - 2/11/2008 13:50
=Format(Fields!myDate.Value, "MMM-dd-yyyy") - Feb-11-2008

Switch Function - SSRS

Example use of Switch function (similar to Select Case)

=Switch(Fields!STATUS.Value="CHECKED", chr(252),
Fields!STATUS.Value="DEFERRED", chr(198),
Fields!STATUS.Value="NOT REQD", chr(251))

returns various wingding characters.

chr(252) - Tick
chr(198) - up and right arrow
chr(251) - Cross

Default location of the templates folder - SSRS

To create your own reusable templates, put your RDL files in this location:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject\

URL command sections - SSRS

&rs:Command=Render
&rc:Parameters=false - hide parameters in report
&rc:Toolbar=false - hide toolbar

DateAdd Function - SSRS

=DateAdd(DateInterval.Month, 6, Today())
=DateAdd(”D”, 6, Today())
=DateAdd(”Month”, 1, Today())

Display subreport when null returns - SSRS

create dummy dataset in subreport
select 'abc' as dummy
create text field.........no data available....set visibility to =CountRows("dataset") > 0

Conditional formatting - using a function - SSRS

Another option is to write a function to do this and an example below involves a case statement. In the color property of the textbox, you can reference the function like so:

=Code.GetColour(Fields!SubTotal.Value)

If you had multiple textboxes on a report that required the same colour formatting, this technique would be a bit more reusable.

Public Function GetColour(ByVal InputValue As Double) As String
Select
Case InputValue
Case 1000
GetColour = "Red"
Case 10000
GetColour = "Blue"
Case 30000
GetColour = "Green"
Case Else
GetColour = "Black"
End
Select
End
Function

Green bar report - SSRS

=iif(RowNumber(Nothing) Mod 2, “LightGreen”, “White”)

IsNothing Function - SSRS

=Iif(IsNothing(Fields!ANYFIELD.Value), "The Field Is Null", Fields!ANYFIELD.Value)