Create a Date Table in Oracle

Occasionally you may be working with non Microsoft data sources such as Oracle over front end tools like Power Pivot or Reporting Services. Some options for a date table include creating a linked table in Power Pivot or generating a table in the data source. Generate a date table quickly using your Oracle data source […]

SQL Server Data Compare

To download the admin install go to http://msdn.microsoft.com/en-us/data/hh297027, download the ssdtsetup executeable for SQL Server 2014, and also vs2010, vs2012, vs2013.  From the command prompt run ssdtsetup /layout <destination> where <destination> is the path of the USB stick / network / local drive you want to install to. This may take a while to download the […]

SSIS: Execution History Catalog Dashboard To Go

The SSISDB contains a wealth of statistical data for your SSIS package executions and messages.   In a package failure scenario, trawling through SQL Management Studio to get detailed reports becomes quite tedious.  For a very quick and easy dashboard looking at the most recent events try a simple Reporting Services report with two tables […]

TSQL : Convert a String to Pad Leading Zeros

Uncle Julio imported some new product codes into a database for the expanding Fajita business. However the leading zeros have been removed somewhere during the import process. The point of sale machines requires a 4 character product code so he turned to his close friend and I.T. expert Jose, to pad leading zeros in front […]

TSQL : Convert DateTime String to Datetime

You have imported data from a text file into your database which included datetime values formatted as strings. An example is the following string ‘2013-09-12 01:03:52’ which represents the date 12th September 2013 at 3 minutes past 1am on the 52nd second. The following snippet example show how to convert this string to a datetime […]

TSQL : Convert a Range to Rows in a View

You have a table that contains rows identifying start and end values for a range and you want to convert this to rows i.e. one row for every number between the start of the range and the end of the range. You also need to do it in a view. An example is : Id […]