Month: July 2012

BI Templates

Templates in BIDS or SSDT are a great time-saving feature for both SSIS and SSRS. They also promote consistency across all developed reports and integration services packages. Finding the correct path to store templates can be a challenge, which is what I hope this blog will illustrate. Below I listed a quick reference where to store your templates.

Templates Paths for SQL Server 2005

%Program Files%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\[Template Type]

Templates Paths for SQL Server 2008 and 2008 R2

%Program Files%\ Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\[Template Type]

Templates Paths for SQL Server 2012

%Program Files%\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\[Template Type]

SSIS = DataTransformationProject
SSRS = ReportProject

NOTE:

  • These paths are based on default installs, you may need to adjust according to your install location.
  • The %Program Files% will be either Program Files or Program Files (x86), depending on if you are using a 64bit architecture.
  • You will need to replace the [Template Type] with DataTransformationProject for SSIS and ReportProject for SSRS

T-SQL Tuesday #32 – A Day in the Life

Thank you to Erin Stellato ( b | t ) for providing this month’s topic, where we were asked to track what we did on Wednesday, July 11th.

Database Developer

Overall I had a pretty normal day in the life of a database developer. My day started around 7:00 am with reviewing tasks and bugs that are assigned to me in TFS (Team Foundation Server), followed by replying to emails. Then getting re-acquainted with where I left myself from the previous night in Management Studio. T-SQL Tuesday

Around 9:00 AM a QA resource presented a bug where a stored procedure was not copying data between tables. What I found was an additional step was added to an insert stored procedure that was attempting to short circuit functionality, when it found a like match. This procedure was working so good, that it only inserted one record… ever… It turned out to be an issue where two columns made up the unique key, and the second column was missing from this check. I added the missing column into the stored procedure to put it back in  business.

Shortly after 10:30 AM, and a quick scrum meeting, I started training another DBA resource on a few stored procedures that I had previously written. This DBA had been tasked with adding  functionality to support new business objectives. Around this same time I worked with an application developer to add functionality in another stored procedure to meet middle tier requirements.

After/during a quick lunch at my desk, I began to review a massive business objective which would eventually output to a Reporting Services Report. This was short lived, when a QA resource discussed an issue with performance of an SSIS Package used to move dimensional data into a Data Mart.

Around 1:00 PM this performance bug was prioritized as critical and I spent the remainder of my day working on a fix. The problem I found in the SSIS package was the shear volume of data coupled with the number calls to an insert stored procedure via an OLE DB Command Transformation. After a couple tweaks to a stored procedure, and a couple new tasks in the SSIS package, performance was significantly improved.

It was now 6:00 PM and time to wrap up my work for today, however the day was not over yet, now it was time to get some dinner and talk SQL Saturday. Rick Krueger ( b | t ) and I will be co-presenting at both SQL Saturday #159 Kansas City and SQL Saturday #161 Iowa City. Rick and I worked on: networking strategies, presentation content, and changing our abstract to get accepted in MN.

After I got home, I decided to review an article that I found on SQL Server Central, which is relevant to our presentations. http://www.mssqltips.com/sqlservertip/2722/sql-server-report-server-2008-r2-execution-log-reports/. Great post from Scott Murray ( b | t )

Kansas City and East Iowa Here We Come…

Rick Krueger ( b | t ) and I are going to Kansas City and East Iowa.
Why you say…
Well let me tell you… We were fortunate enough to have our abstracts selected for both SQL Saturday events, we are also submitting for SQL Saturday #149 in our home state of Minnesota.

At SQL Saturday #159 in Kansas City we were notified that two of our abstracts had been selected. Currently we are scheduled to present Freaky Fast Development Tips in Database Development – Room: Room 4 at 1:00 PM. Then we follow that up with SSRS: Reporting on Reports – Business Intelligence – Room: Room 3 at 2:30.

SQL Saturday #159 Schedule

Then one week later at SQL Saturday #161 in East Iowa we are scheduled to present Freaky Fast Development Tips in Room 3 – Room: 2520B (Conference Room) at 3:30 PM.

SQL Saturday #161 Schedule

If you are going to either event, stop by and say hi, check out our sessions, we love to talk and look forward to meeting you. Hope to see you in Kansas City, East Iowa, and possibly Minnesota

Freaky Fast Database Development Tips

Join Rick and Dave on this most excellent adventure as they teach you how to get more work done in less time. Leave your checkbook at home, because this is all about maximum productivity with minimum budget. Leave with Visual Studio and SQL Server Management Studio shortcut keys, extensions, macros, and add-ins. We guarantee you will see something you haven’t seen before, or your money back. Co-Presenter: Rick Krueger (@DataOgre)

SSRS: Reporting on Reports

Business Intelligence gets a lot of press these days, but do you often wonder to yourself as you are writing a report, ‘Who is going to use this’? Learn about the rich information that SSRS is already capturing for you about your reports: who is running them and how often, how long do they take to execute, how much time is spent in data retrieval vs rendering, which parameters were passed in, and more… Co-Presenter: Rick Krueger (@DataOgre)

Inspired by David Klees ( b | t ) blog post Speaking in Kansas City and Iowa City! whom will also be presenting at both events with Andy Galbraith ( b | t ).

View table data with Query Shortcuts Part – 2

In Part 1 of the Query Shortcuts, I demonstrated a quick SELECT * FROM keyboard shortcut. The problem with this approach is if your table is not in the default schema, you have to select the schema and table name. In this post I am going to demonstrate how you can use a custom stored procedure in the Query Shortcuts to return table data without selecting the schema.

First let’s look at the stored procedure.

Stored Procedure:

The idea behind the stored procedure is to find the schema for the table name that is passed in. If the same name is used in multiple schemas, the stored procedure will output both tables and all their data.

CREATE PROCEDURE [sp_GetTableData] (
   @TableName varchar(200))
AS 
   -- Remove any square brackets, we only want the table name
   SET @TableName = REPLACE(REPLACE(@TableName, '[', ''), ']', '')
 
   DECLARE @SelectFrom nvarchar(max);
   -- If temp table exists remove it
   IF OBJECT_ID('tempdb..#TableNames') IS NOT NULL 
      DROP TABLE #TableNames;
 
   -- Get a list of all tables that match in all schemas
   SELECT   TableName = s.NAME + '.' + o.name
   INTO     #TableNames
   FROM     sys.objects o
   JOIN     sys.schemas s ON o.schema_id = s.schema_id
   WHERE    o.name = @TableName
            AND o.type = 'U'
 
   -- Loop through all tables and output table results
   WHILE ( SELECT COUNT (1) FROM #TableNames ) >= 1 
      BEGIN
         SELECT TOP 1 @SelectFrom = N'SELECT * FROM ' + TableName
         FROM     #TableNames T
 
         EXECUTE sp_executesql @SelectFrom;
 
         DELETE TOP (1) FROM #TableNames
      END
 
   -- clean up
   DROP TABLE #TableNames

To use this query shortcut in all databases in the current server instance you can store this stored procedure in the Master database. Typically I would not recommend using the prefix “sp_”, however in this instance we need the procedure to behave as a system stored procedure. Also by using the prefix “sp_” SQL Server will look in the master database for the procedure before looking in the current database.

Query Shortcut Setup:

In order to setup a query short cut you will need to navigate to the SSMS Options Dialog:
(SQL 2012)

  1. Open SSMS navigate to Tools –> Options…
  2. After the dialog opens navigate to Environment –> Keyboard –> Query Shortcuts. This will open the Options dialog window shown below.
  3. Enter “sp_GetTableData ” in any of the open text fields under Stored Procedure.
  4. Click [OK]

NOTE: The new query shortcut will be available in all SSMS windows opened after the query shortcut is saved.

Query Shortcut for Stored Procedure sp_GetTableData

Now open any query, highlight any table name and press Ctrl+F1. Voila you have the contents of the highlighted table. Below we are looking at a snippet of the [dbo].[uspGetEmployeeManagers] procedure in the adventure works database.

Output After Pressing Ctrl+F1

Notice that this time the schema is not selected as a part of the table name. This gives us the ability to double click to highlight the table, and run our shortcut. This feature is handy when learning/debugging a stored procedure, function, view or any other db object.

A downfall with using the stored procedure version is the stored procedure has to be setup on each instance of SQL Server that you connect to in SSMS. This is because the stored procedure lives in the instance where as the Query Shortcut lives in SSMS. Part 1 does not have this same limitation.