November 11, 2012

SSRS: Formatting Numbers and Dates

Use the following subset of Visual Basic format strings to format numbers and dates in SSRS data regions using the Properties dialog box. 

SSRS Numeric formats:

















SSRS Date and Time formats:





11/11/2012 11:30.000 AM

11/11/2012 11:30:00 AM


11/11/2012 11:30.000 AM

Sunday, November 11, 2012


11/11/2012 11:30.000 AM                 

11:30 AM


11/11/2012 11:30.000 AM

11:30:00 AM


11/11/2012 11:30.000 AM

Sunday, November 11, 2012 11:30:00 AM


11/11/2012 11:30.000 AM

Sunday, November 11, 2012 11:30 AM


11/11/2012 11:30.000 AM

11/11/2012 11:30 AM


11/11/2012 11:30.000 AM

11/11/2012 11:30:00 AM

M or m

11/11/2012 11:30.000 AM

November 11

Y or y

11/11/2012 11:30.000 AM

November, 2012


11/11/2012 11:30.000 AM


September 8, 2012

export to Excel to cell range from SQL Server

This T-SQL script exports data from SQL Server to a specific range of cells in an Excel file:

Now verify your work using SQL Server Management Studio and run this script to select rows falling within a specific cell range in the Excel File:

September 2, 2012

Windows on Mac with Apple's Boot Camp

I'm using Apple's Boot Camp to run Windows on my Mac at native speed without the performance penalties  that come with installing a "virtual machine". Keyboard shortcut and function key mappings required minor  acclimation but Apple conveniently provides a Windows On-Screen keyboard that is accessible via the Start menu:
  1.  click Start menu
  2.  click All Programs 
  3.  click Accessories
  4.  click Accessibility
  5.  click On-Screen Keyboard 

Mac            On-Screen Keyboard
option          Alt
command     windows start menu
control         Ctrl

Screen shots
Using the On-Screen Keyboard, PrtScn takes a screenshot of the entire desktop whereas Alt+PrtScn takes a screenshot of the active window only. Since you can only use the mouse to click one key of the On-Screen Keyboard, you will need to simultaneously press the "option" key on the Mac while you click the PrtScn key with the mouse. Move the On-Screen Keyboard out of the active window so it won't appear in the screenshot.

Don't want to use the  the Windows On-Screen Keyboard for screen shots? Use the Mac keyboard  as follows:

print screen: fn + F11
print active window only: option + fn + F11

August 27, 2012

Sorting with Reporting Services

SQL Server Reporting Services provides the following mechanisms for implementing report sorting:

Method #1: Query-level static sort

In Design View, implement a default sort with the ORDER BY clause. It is good practice to add a default sort with an ORDER BY clause to the dataset Query in the Report Data pane. Steps:
  1. navigate to the Report Data pane
  2. click the dataset you are using for the report
  3. click Query
  4. add an ORDER BY clause to the query

Method #2: Table-level static sort

In Design View, define static sort columns. This approach usually results in requests from the end user for multiple reports that use different sort criteria. Steps:
  1. select the report
  2. click the square in the top left corner
  3. click Tablix Properties
  4. click Sorting
  5. click Add
  6. choose a sort column from the dropdown listbox
  7. choose the sort order 

Method #3: column-level interactive sort 

In Design View, define interactive sort columns. Depending on the number of interactive columns defined and the number of report users, this approach may result in performance issues. If you are using this method to implement sorting, it is good practice to add a default sort with an ORDER BY clause to the dataset Query in the Report Data pane. Interactive sorting overrides the default sort order that was implemented using the ORDER BY clause (method #1 above) or inside the report at the Table level (method #2 above). Steps:
  1. right-click the column you want to sort 
  2. click Text Box Properties
  3. click Interactive Sort
  4. click Enable if it is disabled
  5. choose a sort column from the dropdown listbox
  6. In Preview, you will now see an up-down arrow in the column heading that the report user can click to toggle between ascending or descending sort order

July 14, 2012

String search

This function searches for a string and returns the number of occurrences it finds.

(@SearchString VARCHAR(4000),
@SearchFor VARCHAR(200))
@Position INT,
@Counter  INT

SET @Position = 0
SET @Counter  = 0

WHILE (CHARINDEX(@SearchFor,@SearchString) > 0)
SELECT @Counter = (@Counter + 1),
@Position = (CHARINDEX(@SearchFor,@SearchString) + 1)
SELECT @SearchString =

RETURN @Counter

June 10, 2012

Export data to Excel file

Here's a T-SQL query that uses OPENROWSET to export data from SQL Server to an Excel file:

You may need to grant permissions to the temp directory used by the service account running SQL Server or the Windows directory the export file is being written to from the cmd line as follows:

> icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant MSSQLSERVER:(R,W)
> icacls C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp /grant MSSQLSERVER:(R,W)
> icacls C:\Temp /grant MSSQLSERVER:(R,W)
> icacls C:\Temp /grant "NT Service":(R,W)
> icacls C:\Temp /grant "Local Service":(R,W)

(substitute the system volume and directory names as appropriate)

May 11, 2012

SSIS: Excel values import as NULL

SSIS Imports Values as NULL
When using SSIS to import data from an Excel Spreadsheet to SQL Server, some values in an Excel column might be imported as Null when the underlying value is not Null.
I had this occur with a zipcode column that contained intermixed numeric and text datatypes.
Excel ISAM driver limitation
According to Microsoft, this is caused by a limitation of the Excel ISAM driver. It determines the datatype of an Excel column by sampling the values in the first few rows of the column and then chooses the datatype for that column. Cells in the column that do not conform to the chosen datatype default to Null.
Updating the server registry key
I found that adding the option IMEX=1; to the Excel connection string does not always resolve this problem.
I had to edit the server registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet4\Engines\Excel and modify the registry setting from TypeGuessRows=8 to a larger value in order to ensure the Excel ISAM driver sets the column datatype to text.
T-SQL code
Use this T-SQL code once you've updated the server registry key: INSERT INTO [yourTable] SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:yourDirectoryyourFile.xls;IMEX=1;HDR=YES', 'SELECT * FROM [YourSheet$]')
IMEX options:
  • 0 = Export mode
  • 1 = Import mode
  • 2 = Linked mode (full update capabilities)