This is a new one on me…when running SSIS in a 64-bit environment, Excel connections (and Access as well I presume) do not work, as the OLE DB Provider for Jet is not supported. At a current client, they are moving the server platform from Windows 2003 Server to Windows 2008 Server 64-bit. I had developed some ETL packages for them, pulling data from Excel and CSV sources. The packages get executed when the files have been unzipped and downloaded to a specified folder.

I found an interesting forum post over at SQLTeam this morning, and decided to have a go at coming up with a solution. Filters are quite commonly used in Tablix reports to show only the top n rows from a dataset. This is easily done by using the Top N operator in the Filters tab of the Tablix, and choosing the column in the Expression drop down. In the forum post however, the original poster was wanting to show the top n values in a chart.

Need to find out if a term is referenced in any stored procedures in your database? Use the following T-SQL statement… select name, definition from sys.procedures p inner join sys.sql_modules m on p.object_id = m.object_id where m.definition like '%search term%'; This will work in SQL Server 2005 and 2008.

SQL Server Management Studio 2008 has a new feature, the ability to run a script on multiple servers at once. To set this up, the servers need to be registered to a Server Group in the Registered Servers window (see image). Right-clicking on the Server Group and selecting New Query creates a query window which is connected to the servers in the group (as long as the connection information is correct and the passwords, if using SQL Server Authentication, are saved).

I've just stumbled upon an odd issue with the Tablix control in SSRS 2008. I deployed a report on a server a few weeks ago and it is now reporting on enough data to make it span multiple pages. I wanted the column headings to appear on all pages, not just the first. However, when I set the RepeatColumnHeaders property on the Tablix to be true, the headings still did not appear on the next page.

In Reporting Services, formatting dates can become a headache for the developer, often due to differing regional settings on servers, development boxes etc. Therefore I've found it much more useful to force the report to use a particular format, rather than allow the server settings to decide for me. Previously I would have used FormatDateTime in the expression, as shown here: =FormatDateTime(Fields!DateStamp.Value, DateFormat.ShortDate) Depending on the regional settings, this could return either 12/3/2008 or 3/12/2008.

I'll be posting any SQL Server related stuff in this new blog. I may even try to make it vaguely interesting! And I promise not to post my daily “I hate SSIS!"/“I love SSIS!” thoughts here. 🙂 Don't expect any content in the next two weeks, as I'll not be working and I hope to have a wee break from databases for the duration. But let's make the intro post useful; go get the new SQL Server Cribsheet from Red Gate.