Category Archives: Reporting/BI

Simple Index Usage Query

Yes, another index usage query. I adapted this a tiny bit to add a time element to it. DMVs will flush out any information when the services are restarted. I omitted system usage information to gear it more toward user created objects. Also, added the users statistics together to get a better Idea of total usage. It gives the user a good starting point to see utilization of current indexing. Conversely, Microsoft provides a “Missing indexes” DMV named sys.dm_db_missing_index_details. Don’t have blind faith in the suggestions. They are based on specific activity since last restart.
Read more »

XQUERY – Examples and Scripts

There are tons of thing in this world that I really don’t understand. SQL Server XQuery is just one of those just out of reach.  Poking around trying to answer a question for a developer, I found the following XML script examples. Teh writer provides excellent explanations and examples. Immediately found the right path(pun intended) to go down.  I so wished my brain worked this way!

XQuery Labs – A Collection of XQuery Sample Scripts

Jun 26 2008 3:41PM by Jacob Sebastian

Read more »

Scripting User Database Rights

All DBA’s will be tasked at time to refresh a copy of production to a development, reporting or promote as adatbase to a UAT or QA environment. Many times it is important to retain the existing users rights on the target instance.
The script uses the sys.database_role_members, sys.database_principals, sys.database_permissions, sys.sysobjects and sys.sysusers tables.

Some things to note:

  • Script does not remove or reduce rights if the user already exists.
  • Create user statements that test if users already exits.
  • Add users to existing database roles.
  • Scripts object level grants.

Read more »

T-SQL Split up an Array

I work with data from a system that stores values as arrays or extents. In 2008, I created a CLR function to expose the C# split command. I tried several iteration with loops in T-SQL, etc. overtime, I have noticed the CLR as being a bit of a memory hog. 

I found this possible solution : Split a string in TSQL (without looping)

Need to change it a bit .. 

1. Reduce the numbers table to MAX Extents position ( 80). Still populated it with 1-120.. just in case.
2. Needed it to return as an INLINE Scalar Value and not a Table Value Function. 

Here is What I Came up with from the original  Script:

/*
Adapted from Split a string in TSQL (without looping)
http://blog.magenic.com/blogs/whitneyw/archive/2008/10/08/Split-a-string-in-TSQL-_2800_without-looping_2900_.aspx
(Link no longer valid)
JCD Changes 5/2010
1. Only have need for 80 position Arrays..
So, I limited my Numbers Table to 120
2. Added a @pos INT to evaluate only one position
3. Need it to act as a SCALAR Function
and not a a Table value Funtion

*/
— ORIGINAL COMMENT
— Create and populate an auxiliary
— table of numbers

IF OBJECT_ID(‘dbo.Numbers’) IS NOT NULL
BEGIN
DROP TABLE dbo.Numbers;
END
GO
CREATE TABLE dbo.Numbers
(
number INT NOT NULL
CONSTRAINT PK_Numbers
PRIMARY KEY CLUSTERED
);

SET NOCOUNT ON;

DECLARE
@max AS INT,
@rc AS INT;

SET @max = 120; — you may need to set this much higher
SET @rc = 1;

BEGIN TRAN;
INSERT INTO dbo.Numbers(number) VALUES(1);

WHILE @rc * 2 <= @max BEGIN INSERT INTO dbo.Numbers(number) SELECT number + @rc FROM dbo.Numbers; SET @rc = @rc * 2; END INSERT INTO dbo.Numbers(number) SELECT number + @rc FROM dbo.Numbers WHERE number + @rc <= @max; COMMIT TRAN; GO /* --ORIGINAL COMMENT Next, we create the inline function that will do the heavy lifting for us. I've added an input for separator as hard coding the comma will almost always guarantee some other delimiter will be needed. Turned it into a scalar function */ IF OBJECT_ID('dbo.SplitString') IS NOT NULL DROP FUNCTION dbo.SplitString; GO CREATE FUNCTION dbo.SplitString(@arr AS VARCHAR(MAX), @sep AS CHAR(1),@pos AS INT ) RETURNS VARCHAR(60) AS BEGIN BEGIN -- SET Value DECLARE @result VARCHAR(60) -- Changed to put return one value to a variable SELECT @result = RTRIM(LTRIM(SUBSTRING(@arr, number, CHARINDEX(@sep, @arr + @sep, number) - number))) FROM dbo.Numbers WHERE number <= LEN(@arr) + 1 AND SUBSTRING(@sep + @arr, number, 1) = @sep AND ((number - 1) - LEN(REPLACE(LEFT(@arr, number - 1), @sep, '')) + 1) = @pos; END RETURN RTRIM(@Result) END GO /* --ORIGINAL COMMENT Finally, we call our function with a string of values. If we needed to then utilize this for filtering we could use the CROSS APPLY operator introduced in SQL 2005. */ DECLARE @arr varchar(MAX), @sep char(1), @pos INT; SELECT @arr = '1, 2, Dan, 4, Blah, %, 7, "We the people...", 9,"A string walks into a RBAR and says...", B, C' ,@sep = ',' -- Set the delimiter ,@pos = 6 -- Set the position to return -- Return Single Value SELECT dbo.SplitString(@arr, @sep, @pos) as SplitValue; SELECT dbo.SplitString(@arr, @sep, 8) as SplitValue; SELECT dbo.SplitString(@arr, @sep, 10) as SplitValue; GO

SQLServerCentral – Report Server Diagnostic Reports

FROM http://www.sqlservercentral.com/articles/SSRS/69257/

Michael Davis has published this article and RDL files
on SQLServerCentral.com.  Just happened to read the email, today.

SSRS is a powerful tool, but the ReportServer tool lacks some administrative reporting.  Micahel Dais has done a great job filling in that gap!

Super Easy to deploy and gives the end user a better glimpse
into what’s happening on their SSRS installation. While it is
written for a SQL 2005 , some posters announced they had no
problems working against SQL 2008 installations.

Thanks Michael!

Read more »

Simple Talk Blog – Top 10 SSRS Challenges and Solutions

Ryan Duclos wrote a great article on 10 Common Issues with SSRS. Download his Code, too.

FROM  http://www.simple-talk.com/sql/reporting-services/ten-common-sql-server-reporting-services-challenges-and-solutions/

Read more »

Except and Intersect operators in SQL Server 2005/2008 (from decipherinfosys)

Good Explanantion and usage examples of the Except and Intersect  Operators in SQL 2005 ( 2008).

From decipherinfosys
In SQL Server 2005, MSFT introduced two new operators in their T-SQL language: Except and Intersect. We had briefly touched upon these while discussing the MERGE command in Oracle – you can read more on that post here. EXCEPT returns the distinct data value from the left query (query on left side of the operand) which does not exist in the right query (query on the right side of the operand). INTERSECT returns data value which is common in both queries. In Oracle, the equivalent of the EXCEPT operator is MINUS and INTERSECT is same as INTERSECT. In SQL 2005, using EXCEPT and INTERSECT, one can also simulate the MERGE command in Oracle (see the blog post that is mentioned above).

Read Full Post Here

SSRS – Format() and FormateDateTime()

Found this reference … JUST IN TIME!

Thanks Thavash This is by far my most visited page.

http://thavash.spaces.live.com/blog/cns!CF6232111374DFD2!155.entry

April 10

Working with Dates in Reporting Services

As with any other technology ( eg. SQL , C# ), you always find people running into problems with processing of dates. I was asked today , “How to I format dates when using the Date Picker and sending it through to a stored procedure” ? Let’s have a look ….
1) The FormatDateTime command
This is pretty easy to use, but maybe a bit limiting. You can specify 1 of 4 formats using the command arguments. Let’s say we have selected a date such as 10th April 2007 , our results will be as follows :
Command Result
FormatDateTime(Parameters!Date.Value,1) Tuesday, April 10, 2007
FormatDateTime(Parameters!Date.Value,2) 4/10/2007
FormatDateTime(Parameters!Date.Value,3) 12:00:00 AM
FormatDateTime(Parameters!Date.Value,4) 00:00

…but the better way to do it would be to use …

2) The Format command and specify the exact format you require. For example…

Command Result
Format(Parameters!Date.Value,”dd-MM-yyyy”) 10-04-2007
Format(Parameters!Date.Value,”dd/MM/yyyy”) 10/04/2007
Format(Parameters!Date.Value,”MMM-dd-yyyy”) Apr-10-2007
Format(Parameters!Date.Value,”MMM-dd-yy”) Apr-10-07

So 3 M’s give you “Apr” ….anyway this is quite useful if you’re looking for Day/Month/Year , since the system will default to MM/DD/YYYY.

Using this you should be able to display the date format you want , or send through a particular format to a Stored Proc.

EDITED 22/08/2007 : If the Format Command doesn’t work , try converting the value to a date , eg.

Format(Cdate(Parameters!Date.Value),”dd-MM-yyyy”)

Jeff’s SQL Server Blog – Proper Case UDF

Using My Favorite Tech Service –http://www.Google.Com.

I Found this Posting for a Proper case T-Sql User Defined Function on Jeff’s SQL Server Blog — Go to the post as it has an port to MySQL and a couple of other ‘ADVANCED’ examples in the comments.

From the original Blog :

I posted this one a long time ago and needed to use it today, so I thought I’d post it up here as well in case anyone finds it useful.  This simply attempts to capitalize the first letter of each word for the string passed in.  Use it to help clean up some pre-existing data, but don’t use it as a way of presenting your data that is stored improperly since it isn’t exact.�
Read more »

T-SQL: Adding Leading Zeros with a Simple Scalar Function

We were creating a extract at work from T-SQL and new that the we would need to export data with leading zeros. The data was not stored in the database with those leading zero values. The solution, create a simple scalar function to dynamically add the zeros that could accept two parameters ( text , and total length).

The Function is rather simple takes the total length value passed by the user and then subtracts the Length of the the text value.

What would make this better?

  • Would have a default value for total length
  • Error checking to make sure the text supplied is not longer than the total length

Read more »