Houston, you have a problem. Quickly backup your Windows Event logs with the following commands – this example will export the Application and System event logs to two seperate files into the C:\logs folder using the built in wevtutil Windows tool.
wevtutil epl Application C:\logs\ExportedApplog.evtx
wevtutil epl System C:\logs\ExportedSyslog.evtx
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.
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))
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
GROUP BY databaseid, databasename
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
You have a SSIS .DTSCONFIG file you want to edit however the xml displays as one long formatted line.
Open the .DTSCONFIG file in Visual Studio, do a Ctrl + K then a Ctrl + D.
This will format the xml nicely without the requirement for third party tools.
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.
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
DECLARE @OutputString varchar(max)
SET @OutputString = (
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/.