Tag Archives: query - Page 2

SSRS Active Session Information

SQL Server Reporting Services (SSRS) showed up to the party in SQL Server 2000.  A really awesome tool for data delivery.  Most SQL Server environments heavily leverage this powerful tool.  Microsoft has baked in some excellent views and tables for report rendering information (dbo.ExecutionLog, dbo.ExecutionLog2 and dbo.ExecutionLog3).  SSRS also supplies a text log with additional details for errors and warnings.  All are  useful when trouble shooting a failure or identifying report execution trends, but what happens if you want to know what is running, now?

Recently, I needed to  identify an active report that was creating a considerable amount of blocking.  We were able to identify a reporting login , verify the  blocking originated from SSRS. I could grab the query being executed, but needed to find the report and user or maybe subscription causing the blocking.  As I mentioned, for the most part the ReportServer database gives us historical information.  I always based most of my queries on the ReportServer database.  In a brief moment of clarity, I realized that maybe just maybe this ReportServerTempDB database might just store ‘temporary’ data about active sessions. Welcome back to the show, Jack!
Read more »

SQL Query to script DTUTIL.exe SSIS Export Commands

There are several reasons why DBAs will need to export/import SSIS packages for upgrades, development environments and backups.  Many use SSMS to do imports and exports of the dtsx packages. It isgreat unless you need to export several or even all the packages. Microsoft supplies a command line tool dtutil.exe to import, export, delete, update and verify SSIS packages.

I wrote the script below to query msdb and script out the dtutil commands. I save the output to text or file. It can be executed windows batch file(.cmd or .bat).  If your environment uses configuration files(.dtsconfig), make sure you grab a copy of those, too.

USE msdb
-- Scripts  DTUTIL commands 
-- Results to Text 
-- Script out commands to execute on target server
-- Output to be used as a windows batch file
-- Tested: SQL 2008 R2  and SQL 2012
-- REMOVE  " ---- " before saving as a .cmd or .bat file
-- JCD 01/29/2013

DECLARE @folderlocation VARCHAR(300)

-- Set location of folder to store 
SET @folderlocation = 'S:\SSISPackages' 

--- Do not alter below ---

-- Add Source Servername to folder structure (ex.C:\SSISExport\MyServer\)
SET @folderlocation = @folderlocation + '\'+CONVERT(VARCHAR(50),ServerProperty('SERVERNAME'))+'\'

--- Create Folder Structure
SELECT  'MKDIR "'+@folderlocation+'"'
SELECT 'MKDIR "'+ @folderlocation+RTRIM(foldername)+'"'
from [msdb].[dbo].[sysssispackagefolders]

-- Create DTUtil Commands
select 'DTUtil /SQL ' + 
'"\' + CASE WHEN DATALENGTH(RTRIM(F.foldername)) > 0 THEN RTRIM(F.foldername)+'\' ELSE '' END +
P.name + '"' +  ' /SourceS "'+CONVERT(VARCHAR(50),ServerProperty('SERVERNAME'))+'" /Encrypt  FILE;"'+@folderlocation+ 
CASE WHEN DATALENGTH(RTRIM(F.foldername)) > 0 THEN RTRIM(F.foldername)+'\' ELSE '' END + P.name + '.dtsx";0 /Q'
from [msdb].[dbo].[sysssispackages] P inner join [msdb].[dbo].[sysssispackagefolders] F 
on P.folderid = F.folderid
ORDER BY F.foldername


SQLPerformance.com from SQLSentry

I was pretty excited to see the launch of a new website from SQLSentrySQLPerformance is going to be a great resource for the SQL Server community.  Congrats to SQL Server MVPs Aaron Bertrand and Kevin Kline on the launch of the site.

Read Aaron Bertrand’s announcement  here.

Check out the free tool SQL Sentry Plan Explorer v1.3 . We use it a lot to review queryplans.

SQL Script to Review Backup and Restore Processes

A coworker shared this awesome script to monitor backup and restore operation progress. I am not sure of the origin, but is definitely has become an essential member of my SQL scripts folder.

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 »


One important tool in managing disk space is to make sure the system accurately reports space used by tables.  DBCC UPDATEUSAGE will update the values returned by sp_spaceused.

The output of sp_spaceused “displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.”  One thing to note when reviewing the output is you may see the the reserved size is disportionately larger than the data and index space used. The reserved space should always be greater, but in one case I did see > 20 GB of difference. The space reported by system can get out of whack (technical term) from repeated use of TRUNCATE statements and frequent DDL statements.

Excellent article on DBCC UPDATEUSAGE by Armando Prato.

Google search for DBCC UPDATEUSAGE.

Read more »

Query Plans and active query information

Quick script to see active processes and to grab the query plan. It leverages some DMVs to get the query text and plan. Only can be used on SQL 2005 and above. It is a great and light wight way to see what is going on with the server and performance tuning.

Read more »

Disk Space – Get table sizes

Below is a script  I use to determine table size.  Basically, it walks through the user databases and for each table.  The information is converted in GB. It’s a pretty simple use of MS sp_spaceused with sp_msforeachtable. More info on sp_spaceused.

Full script, below. Download script here.

Read more »

T-SQL Using Parameter Table like a Cursor

The Google gods seem to like a very old page of mine about using Cursors in T-SQL. So, from time to time I get commnets via email.  Thought I  would share an alternative to using cursors.

Read more »

SSRS – Format() and FormateDateTime()

Found this reference … JUST IN TIME!

Thanks Thavash This is by far my most visited page.


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.