SQL Server

SQLBits IV

I travelled down to Manchester last weekend to go to the SQLBits conference, something that I'd heard a lot of good things about. I'm very glad I went, as it was an extremely useful and informative event. And free! 🙂 With 28 sessions being given by 28 speakers, there was plenty of choice on what to go to…possibly too much as I missed out on a few that I would have really liked to have seen.

SDS Q&A

Following up from yesterday's post about SDS, there is now a Q&A about the announced changes on the SDS Team Blog: SDS Q&A

“Full” SQL Server in the cloud

Now this is a bit interesting! On Tuesday, the SQL Data Services team announced that SDS would offer full relational database capabilities. This has really perked my interest now, as I was never fully comfortable with the ACE (Authority, Container, Entity) data model. Now that SDS is going to support Stored Procedures, Views, Indexes and even Triggers, I think this is a product to get excited about. Of course, the announcement means I still have more questions than answers!

Excel connections in SSIS 64-bit

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.

SSRS 2008: Using the TOP filter in a chart

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.

Quick tip: Search for a term in a Stored Procedure

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.

Running scripts on multiple servers in SSMS 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).

Column headings do not repeat in SSRS 2008

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.

Formatting Dates in SSRS

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.

New SQL Server blog

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.