Archive for the 'RDBMS' Category

Display Job History

We have a service that polls a myriad of things several times an hour to send pages and alerts to the appropriate people based on a specific set of criteria. One of the things that it polls is the SQL job history, which can be helpful to be proactive in catching long running jobs or even if a job gets disabled.

The time that a job is scheduled to run and it’s run duration is saved as an integer in hhmmss format, so converting this to the datetime datatype requires some manipulation. You can find a lot of ways to do this using string functions, which is the way we had completed the conversion … until is started erroring.

The statement below will convert the Integer value to the datetime datatype using mathmatical functions instead of string functions, which is less prone to error.

The first thing that we will do is extract the hours, minutes and seconds into their separate parts using the modulo operator. Once we have that we will convert these values into seconds (the smallest interval we are working with) and add them together. Once we have the offset value in seconds from midnight we use the dateadd function and add the seconds to the run date. Viola! And it’s even a little faster.

SELECT
    DATEADD(ss,(((Run_Time - Run_Time % 10000) / 1e4) * 36e2 + 
                ((Run_Time % 10000 - Run_Time % 100) / 1e2) * 6e1 + 
                  Run_Time % 100),CAST(CAST(Run_Date AS char(8)) AS datetime)) StartTime,
    DATEADD(ss,(((Run_Time - Run_Time % 10000) / 1e4) * 36e2 + 
                ((Run_Time % 10000 - Run_Time % 100) / 1e2) * 6e1 + 
                  Run_Time % 100 + 
                ((Run_Duration - Run_Duration % 10000) / 1e4) * 36e2 + 
                ((Run_Duration % 10000 - Run_Duration % 100) / 1e2) * 6e1 + 
                  Run_Duration % 100),CAST(CAST(Run_Date AS char(8)) AS datetime)) EndTime,
    j.name JobName,
    jh.step_name StepName
FROM
    msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j
    ON jh.job_id = j.job_id
WHERE
    jh.step_id > 0
ORDER BY
    j.job_id,
    jh.step_id ;

Snippets for SQL Server Management Studio (SSMS)

How many times have you had to open an existing SQL object and copy the header information to add to a new procedure, view or function? How many times have you done this and forgot to change the information in the header? Do you like standards? If you answered “Yes!” to any of these questions then templates are for you! Microsoft SQL Server Management Studio comes with a plethora of built-in templates, but this little gem of a feature also allows you to create your own!

To open template explorer, select View -> Template Explore or press Ctrl+Alt+T. This will open a side bar (by default, but you can change it) with the templates installed by default. To add your own templates, right-click on “SQL Server Templates” and then “New” from the context menu. This will give you the option to create a new folder or template. I prefer to keep my user-created templates in their own folder, since that makes it easier to transfer templates between machines.

Let’s create a folder called “CodeSnippets” as shown in the screen shot below.

Template Context Menu (Folder)

Template Context Menu (Folder)

Now that we have our folder created, let’s create some templates. We will create a basic header template and a stored procedure template. To create our templates, Right click on the newly create folder and select New -> Template from the context menu.

Template Context Menu (Template)

Template Context Menu (Template)

Now that we have the blank templates created, let’s add some content. Right click on the template and select “Edit” from the context menu.

Template Context Meny (Edit)

Template Context Meny (Edit)

From here you can write or paste your content into the template.After creating your templates, usage is as simple as dragging and dropping your template into the proper location in the query window.

I mentioned earlier about transferring templates between machines. To move your templates between machines browse to

C:\Documents and Settings\[User Profile]\Application Data\Microsoft\Microsoft SQL Server\[90 for SQL 2005 or 100 for SQL 2008]\Tools\Shell\Templates\Sql\[Your Snippet Folder Name]

for Windows XP and Windows Server 2003 or

C:\Users\[User Profile]\AppData\Roaming\Microsoft\Microsoft SQL Server\[90 for SQL 2005 or 100 for SQL 2008]\Tools\Shell\Templates\Sql\[Your Snippet Folder Name]

for Windows Vista, Windows 7 and Windows Server 2008. (Please replace the items in [] with your specific value for your environment) and copy the data to the appropriate path on the source machine.

I hope you find this as useful as I do! Happy coding!

~Ron

Using SSIS to Export a Fixed Width Flat File

To begin, open Business Intelligence Development Studio (or Visual Studio, if you have that installed) and under Business Intelligence projects, create a new Integration Services Package. You will also need a directory at c:\export to contain the output flat file.
Start by adding a new database connection on the Connection Manager tab (this is usually at the bottom of the SSIS Package Designed). For the purposes of this tutorial I will be using the AdventureWorks database my local machine. This connection will be the source of the data for the flat file.
Database Connection

Database Connection

After you create the database connection, go ahead and create the flat file destination connection. I used the c:\export directory and named my export file Export.txt, but anything will work. On the format drop down, select “Fixed width.”

Data Destination

Data Destination

Please ignore the “Column” sub-menu on the right for now. Select “Advanced” from the sub-menu on the right. Now we will create all of our columns. For this tutorial I created a mail merge type export using First Name, Last Name, Address, City, State and Zip. Add one column to handle the line feed/carriage return. Without this column the package output will concatenated into on one row.

Data Destination Columns

Data Destination Columns

On the Control Flow tab of the SSIS Package, add a data flow task. After adding the task, select the Data Flow tab. Add an OLE DB Source. On the OLE DB Connection Manager, the database connection we created earlier should already be selected. If it is not, select the connection we created at the beginning of the tutorial. The T-SQL used to select the data is below. Please note that there is no data formatting in the T-SQL, only the inclusion the column to handle the line feed/carriage return.

ANSI decimal character 10 is line feed, CHAR(10) in T-SQL, and ANSI decimal character 13 is a carriage return, CHAR(13) in T-SQL. Sometimes these characters are used in conjunction, depending on the format, but for our purposes we must use only one or the other.

SELECT
    pc.LastName,
    pc.FirstName,
    pa.AddressLine1,
    pa.City,
    psp.StateProvinceCode,
    pa.PostalCode,
    CHAR(10) CRLF
FROM
    Person.Contact pc
INNER JOIN HumanResources.Employee hre
    ON pc.ContactID = hre.ContactID
INNER JOIN HumanResources.EmployeeAddress hrea
    ON hre.EmployeeID = hrea.EmployeeID
INNER JOIN Person.Address pa
    ON hrea.AddressID = pa.AddressID
INNER JOIN Person.StateProvince psp
    ON pa.StateProvinceID = psp.StateProvinceID
WHERE
    psp.CountryRegionCode = 'US' ;
Data Source

Data Source

One of the issues that you will face with any fixed width export are columns whose width is greater than the fixed width destination. We are going to handle this with a Data Conversion task. Be sure to give the converted column a friendly name, other than “Copy of ” and the column and set the length of the converted field to the same length of the destination fixed width column.

Data Conversion

Data Conversion

To make sure that the package succeeds if the width of the origin column is greater than the destination column, we will set the conversion to ignore all truncation errors. For these purposes it is important not to confuse width and length. A column with a data type of nchar(4) has a width of 4 but may have a length of 0 to 4. The data length may be less than or equal to destination, but if the width is greater it may prove problematic.

Error Handling

Error Handling

After adding the data conversions and error handling, let’s map the converted columns to our flat file destination columns.

Flat File Mapping

Flat File Mapping

That’s it. Run the package and you will see the fruits of your labor.
~Ron

UPDATE: The zipped solution can be downloaded from here. WordPress does not allow files of this type, so it is available via Dropbox.

Database Versioning

We all know the importance of incrementing version numbers with software releases. But what about the database? Database objects are just as important to a web application as any front end page, code behind, JavaScript or web service.

Most companies I have worked for have very strict control over source code versions. Many have stricter policies for database access. But where are the controls for database design?

There are some very excellent pieces written on database versioning at OdeToCode.com.

  1. Three rules for database work
  2. The Baseline
  3. Change Scripts
  4. Views, Stored Procedures and the Like
  5. Branching and Merging

After reading through these articles I can definitely see the merit of database source control and versioning, and I hope you can as well. But how do you implement these features? No one likes change.

First decide the scope of control that will be implements and the access needed for each role on your team(s). (WARNING: Please don’t read one article and take that as gospel.) Once that is decided, create a plan of action and a timeline to implement the new policies. Please keep operational tempo in mind as no once wants to change processes right before an impending release.

Do your research and come to a consensus on what will work well in your environment. What works well in one environment may not work well in another.

 

~Ron