Disable your Reporting Services Preview Cache in Development

You have made changes to your backend reporting database, rerun your Report Preview but the values havn’t changed ! One of best recommendations I can make to SQL 2012 Reporting Services developers is to disable preview caching on the development machine.   Of course you won’t want to change this in production, however in your dev environment, this will save you from the confusion and additional time spent forcing a refresh on reports.

To disable the Reporting Services Designer Preview cache go to C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies and open the RSReportDesigner.Config file for edit.  Now locate the CacheDataForPreview tag and change the value to False.  Save the file.

For SQL Server 2008 Business Intelligence Studio users, you will find the file in the C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies path.

How to select the database id with sysfiles using sp_msforeachdb

The following example demonstrates how to gather the database id along with sysfiles metadata using the procedure sp_msforeachdb.   This could be useful to collect both a detail and a summary view of database file footprint.


CREATE TABLE #sysfilesplusdatabaseid
(databaseid int, databasename sysname, fileid int, groupid int, size int, maxsize int, growth int, status int, perf int, name sysname, filename varchar(500))

INSERT #sysfilesplusdatabaseid
EXEC sp_msforeachdb @Command1 = 'SELECT DB_ID(''?'') as databaseid, ''?'' as databasename, * From ?..sysfiles'

This will allow you build a view of the total database size by doing a sum of the file sizes grouped by the database.

SELECT databaseid, databasename, SUM((size*8)/1024) as RawSizeMb
FROM #sysfilesplusdatabaseid
GROUP BY databaseid, databasename

Convert Incoming Flat File Date to DateTime in SSIS

You need to convert an incoming string formatted date from a flat file into a date datatype in SSIS.   The following example demonstrates how to convert a flat file date in the format “DD/MM/YYYY” into the ISO format YYYY-MM-DD, where it can be converted to a Datetime data type by DT_DBTIMESTAMP. Nulls and partially populated strings (e.g. a date without leading zeros formatted) are also handled by converting to null dates.

 (ISNULL([your date string])) ? NULL(DT_DBTIMESTAMP) : (LEN([date string]) < 10) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([your date string], 7, 4) + "-" SUBSTRING([your date string], 4, 2) + "-" + SUBSTRING([your date string], 1, 2))

Excellent reference here …http://toddmcdermid.blogspot.com.au/2008/11/converting-strings-to-dates-in-derived.html

Install Sharepoint 2010 on a Windows desktop

After living in virtual machines more recently someone cool reminded me about this trick to enable Sharepoint 2010 install on a Windows desktop.

The full McCoy is here … http://msdn.microsoft.com/en-us/library/ee554869.aspx but essentially extract the sharepoint install pack to your local file system, locate the config.xml file in the ..\files\Setup\ folder of the Sharepoint install.  Add the following line <Setting Id=”AllowWindowsClientInstall” Value=”True”/> before the end of the </configuration> tag.

Just make sure your Windows desktop has plenty of 64 bit thrust and loads of RAM.

TSQL to Convert Upper Case to Sentence Case

This is a quick snippet showing how to convert a string to sentence case (first letter capitals and the rest lower case).

CREATE FUNCTION dbo.ConvertStringtoSentenceCase
(@InputString Varchar(max))
RETURNS varchar(max)
as
BEGIN

DECLARE @OutputString varchar(max)

SET @OutputString =  (
upper(substring(@InputString,1,1)) +
	lower(substring(@InputString,2,LEN(@InputString)))
	)

 RETURN @OutputString
END
GO

Execute function like this …

SELECT dbo.ConvertStringtoSentenceCase('Convert This String to Sentence CASE')

A good post here from PinalDave showing how to convert to Title case http://blog.sqlauthority.com/2007/02/01/sql-server-udf-function-to-convert-text-string-to-title-case-proper-case/.