=================================================

My first job out of college was a Network Administrator for a small non-profit organization.  As the only IT guy in the organization, I was responsible for a lot…managing servers, planning upgrades, scheduling downtime, email setup/support, network switches, cabling, wireless network access/security, managing data, backing up data, help desk support, desktop pc support, web site design, managing the phone system, building overhead paging, video surveillance system, report design and generation, unjamming printers and copiers, evaluating new applications, and developing custom applications.  The organization has classrooms spread out across the entire county.  Each classroom had various technologies from a PC, to phones, answering machines, and speaker systems.

I definitely could not specialize in any particular area in this position.  On any given day I could have needed to travel 60 miles round trip to fix a problem in one of the outlying classrooms and return to my office prepare data to submit for a federally mandated report.  When people asked me what I did, I would tell them my job was to fix anything that plugged into a wall.  With a non-existent budget, I had to get creative to make things work in the organization.

Through a series of life choices, I moved on to a new company in a new position that focused more on systems and database administration.  This position is definitely a lot more specialized than where I started.  There are now other support groups that I can refer people to for issues with PC’s, Exchange, networking, phones, etc.

I still think that it is important to keep up on a lot of the basics, especially with SQL server.  People like to blame the database as the problem when often times the solution is not nearly that simple.  Many problems that I encounter on a day-to-day basis are rooted in specialties not directly related to SQL server.  I may not be a specialist in networking, pc repair, or Active Directory administration, but it has been very beneficial to me to have a good working knowledge of these concepts.  There are other people responsible for fixing these things at my current company.  I can usually figure out what the problems is  and direct to the correct support groups fairly quickly with my generalist background.

Carl Demelo shared an awesome way to move your template directory to a different directory on SQL Server Central.  This solution will only work on Windows 7 using the new shell command mklink.

I tried it out to move my template directory to a network drive that gets backed up on a regular basis.  It worked perfect, thanks Carl!

Now for my trick, I have a view that was created to help write more dynamic rolling sql queries and reports in my organization.  I’m not sure who the original author of the script is, as it has been passed around and modified several times.  I thought I would share it here in the hopes that it helps someone else someday.  If you’re the original author or know who is, please let me know so I can give you due credit.

This view defines a bunch of different date parameters compared to the current date, including:

• TODAY_BEGIN
• TODAY_END
• YESTERDAY_BEGIN
• YESTERDAY_END
• DAY_BEFORE_YESTERDAY_BEGIN
• DAY_BEFORE_YESTERDAY_END
• SUNDAY_WEEK_BEGIN
• SUNDAY_WEEK_END
• MONDAY_WEEK_BEGIN
• MONDAY_WEEK_END
• PREVIOUS_SUNDAY_WEEK_BEGIN
• PREVIOUS_SUNDAY_WEEK_END
• PREVIOUS_MONDAY_WEEK_BEGIN
• PREVIOUS_MONDAY_WEEK_END
• MONTH_BEGIN
• MONTH_END
• YESTERDAYS_MONTH_BEGIN
• YESTERDAYS_MONTH_END
• PREVIOUS_MONTH_BEGIN
• PREVIOUS_MONTH_END
• SECOND_PREVIOUS_MONTH_BEGIN
• SECOND_PREVIOUS_MONTH_END
• THIRD_PREVIOUS_MONTH_BEGIN
• THIRD_PREVIOUS_MONTH_END
• FOURTH_PREVIOUS_MONTH_BEGIN
• FOURTH_PREVIOUS_MONTH_END
• TWELTH_PREVIOUS_MONTH_BEGIN
• TWELTH_PREVIOUS_MONTH_END
• PREVIOUS_SIXTH_MONDAY_WEEK_BEGIN
• PREVIOUS_SIXTH_MONDAY_WEEK_END
• PREVIOUS_SIXTH_SUNDAY_WEEK_BEGIN
• PREVIOUS_SIXTH_SUNDAY_WEEK_END
• NEXT_MONTH_BEGIN
• NEXT_MONTH_END

You can take a quick look at the result returned from this view.  After creating the view (script included below), run:

SELECT  *
FROM    vw_date_ranges

With these columns defined, you can easily query a database table looking for rows based off of a date by cross joining this view and the adding the date columns to the where clause.  For example, if you want to see all orders for “This Week”, you can run a query similar to:

SELECT SalesOrderID,
       OrderDate,
       SalesOrderNumber,
       PurchaseOrderNumber,
       CustomerID,
       TotalDue
FROM   Sales.SalesOrderHeader
       CROSS JOIN vw_date_ranges
WHERE  Sales.SalesOrderHeader.OrderDate > vw_date_ranges.SUNDAY_WEEK_BEGIN

Or if you want to see all orders for “Last Month”, you can run a query similar to:

SELECT SalesOrderID,
       OrderDate,
       SalesOrderNumber,
       PurchaseOrderNumber,
       CustomerID,
       TotalDue
FROM   Sales.SalesOrderHeader
       CROSS JOIN vw_date_ranges
WHERE  Sales.SalesOrderHeader.OrderDate >= vw_date_ranges.PREVIOUS_MONTH_BEGIN
       AND Sales.SalesOrderHeader.OrderDate < vw_date_ranges.MONTH_BEGIN

Using this method helps me keep my sql query clean, and provides a rolling date range on queries and reports.

Download the script here

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_date_ranges]
(TODAY_BEGIN, TODAY_END, YESTERDAY_BEGIN, YESTERDAY_END, DAY_BEFORE_YESTERDAY_BEGIN,
DAY_BEFORE_YESTERDAY_END, SUNDAY_WEEK_BEGIN, SUNDAY_WEEK_END, MONDAY_WEEK_BEGIN, MONDAY_WEEK_END,
PREVIOUS_SUNDAY_WEEK_BEGIN, PREVIOUS_SUNDAY_WEEK_END, PREVIOUS_MONDAY_WEEK_BEGIN, PREVIOUS_MONDAY_WEEK_END, MONTH_BEGIN,
MONTH_END, YESTERDAYS_MONTH_BEGIN, YESTERDAYS_MONTH_END, PREVIOUS_MONTH_BEGIN, PREVIOUS_MONTH_END,
SECOND_PREVIOUS_MONTH_BEGIN, SECOND_PREVIOUS_MONTH_END, THIRD_PREVIOUS_MONTH_BEGIN, THIRD_PREVIOUS_MONTH_END, FOURTH_PREVIOUS_MONTH_BEGIN,
FOURTH_PREVIOUS_MONTH_END, TWELTH_PREVIOUS_MONTH_BEGIN, TWELTH_PREVIOUS_MONTH_END, PREVIOUS_SIXTH_MONDAY_WEEK_BEGIN, PREVIOUS_SIXTH_MONDAY_WEEK_END,
PREVIOUS_SIXTH_SUNDAY_WEEK_BEGIN, PREVIOUS_SIXTH_SUNDAY_WEEK_END,NEXT_MONTH_BEGIN,NEXT_MONTH_END)
AS
select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)                                TODAY_BEGIN,
dateadd(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate()+ 1 ), 0)) - .000011574        TODAY_END,
DATEADD(dd, DATEDIFF(dd,0,getdate()), -1)                                       YESTERDAY_BEGIN,
dateadd(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate()  ), 0)) - .000011574          YESTERDAY_END,
DATEADD(dd, DATEDIFF(dd,0,getdate()), -2)                                       DAY_BEFORE_YESTERDAY_BEGIN,
dateadd(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate()  ), -1)) - .000011574         DAY_BEFORE_YESTERDAY_END,
-- Relative Dates - Weeks
DATEADD(wk, DATEDIFF(wk,0,getdate()), -1)                        SUNDAY_WEEK_BEGIN,
DATEADD(wk, DATEDIFF(wk,0,getdate()), -1) + 6.999988426          SUNDAY_WEEK_END,
DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)                         MONDAY_WEEK_BEGIN,
DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)  + 7.999988426          MONDAY_WEEK_END,
DATEADD(wk, DATEDIFF(wk,0,getdate()), -8)                        PREVIOUS_SUNDAY_WEEK_BEGIN,
DATEADD(wk, DATEDIFF(wk,0,getdate()), -8) + 6.999988426          PREVIOUS_SUNDAY_WEEK_END,
DATEADD(wk, DATEDIFF(wk,0,getdate()), -7)                        PREVIOUS_MONDAY_WEEK_BEGIN,
DATEADD(wk, DATEDIFF(wk,0,getdate()), -7) + 7.999988426          PREVIOUS_MONDAY_WEEK_END,
-- Relative Dates - Months
dateadd(mm,datediff(mm,0,getdate()),0)                                                    MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0)) - .000011574                   MONTH_END,
dateadd(mm,datediff(mm,0,getdate() - 1),0)                                                YESTERDAYS_MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() - 1 )+1, 0)) - .000011574                YESTERDAYS_MONTH_END,
DATEADD(mm, DATEDIFF(mm,0,getdate()  )-1, 0)                                              PREVIOUS_MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0)) - .000011574                    PREVIOUS_MONTH_END,
DATEADD(mm, DATEDIFF(mm,0,getdate()  )-2, 0)                                              SECOND_PREVIOUS_MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  )-1, 0)) - .000011574                  SECOND_PREVIOUS_MONTH_END,
DATEADD(mm, DATEDIFF(mm,0,getdate()  )-3, 0)                                              THIRD_PREVIOUS_MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  )-2, 0)) - .000011574                  THIRD_PREVIOUS_MONTH_END,
DATEADD(mm, DATEDIFF(mm,0,getdate()  )-4, 0)                                              FOURTH_PREVIOUS_MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  )-3, 0)) - .000011574                  FOURTH_PREVIOUS_MONTH_END,
DATEADD(mm, DATEDIFF(mm,0,getdate()  )-12, 0)                                              TWELTH_PREVIOUS_MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  )-11, 0)) - .000011574                  TWELTH_PREVIOUS_MONTH_END,
-- Added 2/23/09 These will be used in a claim audit report that looks at data from 6 weeks prior.
DATEADD(wk, DATEDIFF(wk,0,getdate()  )-6, 0)  as   PREVIOUS_SIXTH_MONDAY_WEEK_BEGIN,
DATEADD(wk, DATEDIFF(wk,0,getdate()  )-6, 0) + 7.999988426  as PREVIOUS_SIXTH_MONDAY_WEEK_END,
DATEADD(wk, DATEDIFF(wk,0,getdate()  )-6, -1) as   PREVIOUS_SIXTH_SUNDAY_WEEK_BEGIN,
DATEADD(wk, DATEDIFF(wk,0,getdate()  )-6, -1) + 6.999988426  as PREVIOUS_SIXTH_SUNDAY_WEEK_END,
DATEADD(mm, DATEDIFF(mm,0,getdate()  )+1, 0)                                              NEXT_MONTH_BEGIN,
dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  )+2, 0)) + .000011574                    NEXT_MONTH_END

An account becomes orphaned when there is a user account with security rights in the database that is not linked to a user account that can log in to the server.  This happens because SQL logins with the same username have different SID’s on different servers.  It is not a problem for Active Directory logins because the SID is stored in Active Directory.

Once an account becomes orphaned, it is a pretty easy fix:

USE <database name>
ALTER USER <username> WITH LOGIN = <username>

But it’s still a pain to remember to run this code to reset the logins.  I usually forget, and the scratch my head for a minute to figure out why the application won’t connect to the development database anymore.

Fix it once and for all

Microsoft has a handy script to fix orphaned users (link).  Download and run the script on your production server to create two stored procedures: sp_hexadecimal and sp_help_revlogin.

Then run:

EXEC sp_help_revlogin

Run the output from this procedure against your development server to create the appropriate logins with the correct username/password and SID.

Ever since I ran this, I have not had any more orphaned users when restoring databases back to dev.

One of the main vendor-built applications that I support has views built on top of tables.  Unfortunately that’s not the end of the story.  These views are built on top of views, which join together other views, which link back to the database tables.  In all the training materials provided by the vendor, they say to *always* use the views when writing a report on the data and never directly query the table.

Using the vendor provided views generally works out OK and performs reasonably well.  One particular report I wrote kept bugging me due to how long it took to run (several minutes each time). compared to how much data was actually returned.  So I started looking at the execution times and the query plans.  Note, I took the screenshots below using the fantastic free tool from SQL Sentry, SQL Sentry Plan Explorer.

This report needed to join 5 tables to get the data that I needed.  Using the vendor provided views, here is the join diagram I started out with:

You can see that the vendor views are joining together a lot more hidden tables (table & field names blurred to protect the innocent) than the 5 I actually need.  The nested views are even hitting the same tables more than once.

Here is the original query plan:

You can’t see it in the screen shot, but one of the thick lines in the middle is representing 23 million rows!

OK, time to pull out the detective hat.  I decided to rewrite the query using only the base tables.  I had to do a bit of extra work with things like UTC datetime vs. local time which the views converted.  After the rewrite, the join diagram looked like this:

Here is the final query plan:

The highest number of rows coming through is about 11,000.  A far cry from the 23 million rows in the original query! The execution plan also looks a lot leaner than what I started with as well.

The original query was running with an average CPU time = 26062 ms, and an average elapsed time = 26424 ms.
My rewritten query is now running with an average CPU time = 0 ms, and an average elapsed time = 266 ms.

Looking at the actual execution plan, SSMS is prompting me that there is a missing index for my new query, and there is still a Clustered Index Scan that accounts for 87% of the query.   I may look into that more at a later date, but for now I am very happy running a query in a couple of seconds that used to take several minutes. I don’t think it’s really worth the extra effort to try to shave another 100 ms off of a query that completes in under 300 ms.

The vendor supplied nested views were each joining together multiple views and tables, which was causing a lot of extra and unnecessary bloat in my query.  An hour of work and cutting out all the bloat made a huge difference in this particular report’s run time.

And the users rejoice at how fast their report now runs!

Create the template file

1. First right-click on the root node in Template Explorer labeled “SQL Server Templates”
2. Select New, and then Folder
3. Name your folder.  I generally start my folder names with a period so that they get sorted at the top of the list.  for example: “.Admin”
4. Once you create your folder, Right click on that folder name.
5. Select New, and then Template
6. Name your template whatever makes sense to you.

Edit the template file

Now that your template is created, the next logical step is to double-click it to open it.  If you do that, you will see a blank query open up in the main window.  This is not exactly what you want.  Instead, right-click on your template and then click Edit.

Now when the blank query opens in the main window, it should have the template name in the query tab at the top.  By going this route, when you save the query your script will get saved into the template file.

The first thing I usually do is start with a quick header.  I will put some general information about what the template is for, the source if I copied it from a blog post somewhere, and the shortcut key to specify values for the template because I can never seem to remember them.

--------------------------------
-- Template Header
-- It's here so I remember what I am using this piece of script for
-- Created by Matt Nelson, 9/15/2011
--
-- Press CTRL + SHIFT + M to fill in variables (because I can never remember the key combination)
--------------------------------

I can add my script now that my header is in place.  There is a special syntax that you can use to use the CTRL+SHIFT+M shortcut to fill in blanks.  I am going to steal Microsoft’s Backup Database template here to show you the syntax

BACKUP DATABASE sysname, Database_Name>
TO  DISK = N'sysname, Database_Name>.bak'
WITH
NOFORMAT,
COMPRESSION,
NOINIT,
NAME = N'sysname, Database_Name>-Full Database Backup',
SKIP,
STATS = 10;
GO

When you want to make a variable placeholder in your script you will put a 3 part variable enclosed between the greater than and less than symbols: <Parameter,Type,Value>  like: sysname, Database_Name> .

• Parameter is basically the variable name.
• Type is a placeholder for the of variable.  There are no constraints around the type like there are with a database table column.  I generally leave this blank.
• Value can also be blank, or you can make it a sample for yourself.

Once you get your script all set up, don’t forget to save it.  The next time you want to run it, all you have to do is double-click on it in Template Explorer.

I am not going to show any code this month, and I am also going to veer off the topic of SQL server slightly.

My company does a lot of reporting though Cognos.  The nice thing about Cognos is that it is platform independent.  Once a connection is made to an Oracle, MSSQL, or even MySQL database, the presentation looks the same to a report developer.  The end user goes to the URL for cognos and runs their report.  They may not even know what application or technical backend is supplying the data.

We have LOTS of reports that get run across the enterprise.  We also probably have a couple hundred report developers that write reports with varying degrees of frequency.  I’m sure you can imagine that without any standards no two reports would look similar and end users would not know how to use or interpret the information that they get out of a report.  Hence why data presentation is so important.

There are several standards that have to be met in our environment in order for a report to be published for end users.  While I don’t think it would be appropriate for me to show you any screenshots, I can summarize some of the general rules here.

1. All reports must use the corporate header and footer.  Information included must contain the business unit for the report, Title of the report, corporate logo, confidentiality statement, page numbers, and telling how to best view the report (ie: html, pdf, Excel, etc.)
2. The last page of the report must be the standard documentation page.  This page has a table with specific information recorded about the report including:
• Report Purpose: A general description of what the report is and how it should be used.
  
• Data Source: name of the server, database, or application where the data in the report came from.
• Author: who wrote the report, and how to contact them with questions.
• Security: Security requirements of the report.  Does it contain confidential information? Who or what security groups are allowed to run the report.
• Business Logic: List of information that may be critical to interpreting the report.  For example, “Annual sales figures are calculated based on the fiscal year of July 1 to June 30 in this report”.  Also list any abbreviations that everyone may not know.  For example, “AHG = Average Height by Gender (I made that up)”.
• Assumptions: What assumptions, if any, were made while writing the report.   I have also seen some report authors insert any user defined variables here.  This way when someone runs a report and they think that it is wrong, the report author can see exactly what values the user entered into the prompt boxes as a point to start troubleshooting.

The documentation page on a report becomes a valuable tool for anyone trying to understand a report and also very useful for someone who is troubleshooting a report.  Of course it is also helpful when an end user does not delete the documentation page…..

I kept running into error messages while testing the Cognos reports that hit the MySQL database:

• RQP-DEF-0177 An error occurred while performing operation ‘sqlOpenResult’ status=’-28′.
• UDA-SQL-0114 The cursor supplied to the operation “sqlOpenResult” is inactive.
• UDA-SQL-0283 Metadata describing <column name> does not match results from the database.

I then opened up Cognos Framework Manager to look at the package.  The weird thing is that I was able to run a Test Sample and see data return in Framework Manager.  However the same data item was throwing errors in Report Studio.  When I looked at the data items in Framework Manager, they had a data type of nVarChar.

Time to bring in the big guns

I started working with our Cognos support group to figure out what was going on.  They were able to edit the definition of one of the data queries in Framework Manager.  When the updated the data item, the data type of the text columns changed from “nVarChar” to “Character Length 16″.  Once they made the change and updated the package, the reports started running again in Report Studio.

Woohoo, problem solved!

Oh wait, I spoke too soon…I went back in to Framework Manager to repeat our support group’s steps against other query items to update the whole package.  Unfortunately when I updated any of the data queries, the data type kept changing back to “nVarChar” and the reports threw errors again.  I even tried to re-update the data item that our support group fixed.  When I made my edit, the reports broke again.

The Solution

Long story short, and after 2 weeks and plenty of headaches….I had a different version of the MySQL ODBC driver installed on my PC from what our Cognos support group did.  I changed the driver version from 5.1 to 3.51, and finally it worked!  I figured that the newer driver would be better, but apparently Cognos 10 Framework Manager has a problem with the MySQL ODBC 5.1 driver, and needs the 3.51 driver instead.

With the older driver installed on my system, I was able to update the Framework Manager package and then pull back data error free in Report Studio.

One other thing of note: after I update the query subject in the Physical Layer in Framework Manager, the links broke in the Presentation layer.  I had to go back into the Presentation Layer to update the links there as well.

While I still have a collection of scripts in all 3 places for various purposes, I have been moving more and more towards using template explorer.
The biggest reason for my change  is because whenever I need to run a script against a database server I am already opening up SSMS.  It is then easier to open up template explorer than it is to then click on File->Open, and then browse for a specific script.

Lets get started

1. Open SSMS
2. Open the Template explorer by either using the key combination CTRL + ALT + T  (my preferred method), or clicking on View->Template Explorer.
3. The template explorer will then open on the right hand dock in SSMS (kinda like how it shows up in my screen shot here!).

From here you can expand any of the categories to see a list of applicable scripts.  Microsoft gives us a decent collection of templates to get us started and you can add your own scripts to the collection (covered in a future blog post).  Find a script template that you want to use and double click it.  SSMS will open that template up as a new query in the main window.  WARNING: Make sure that the script window is connected to the appropriate server\instance before executing any scripts. (not that I have ever done that….)

For this example, I expanded the Backup category, and then double clicked on “Backup Database”

Now wait a minute, there’s a lot of funky stuff there.  “BACKUP DATABASE” won’t run.

Here comes the magic of Template Explorer.  Press CTRL + SHIFT + M on your keyboard.  Behold the Specify Values prompt box.

Fill in the database name, and the file location where you want the backup saved to and press OK.  Like Magic, the template place markers are removed from the script and replaced with the values that you specified a moment ago.  The script is now ready to run as long as you double check to make sure you are running it against the appropriate server\instance.

.

A couple gotchas

• All of the Template Explorer scripts are saved on my PC in:
  C:\Users\<user profile>\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql
  This is running SSMS 2008 R2 on Windows 7.
• This save path for the templates does not get backed up on my local PC.  It also does not travel if I use SSMS on another PC.
• So far my solution has been to use a Remote Desktop Connection back to my main work PC if I am using another PC in the office.
• Every now and then I will manually copy the directory up to a network location that does get backed up so that I can get my templates back if my PC dies.  Does anyone have a better solution?
• If you delete a template or folder that Microsoft provided, it will be recreated whenever you restart SSMS.  I found that it is best to leave the Microsoft provided templates alone and add my own.

Coming soon in a future post, making your own template. [EDIT: here is part 2]