Category: SQL Server

SSRS – Useful Header / Footer Expressions

SQL Server Reporting Services is an excellent tool for controlling the output of reports and having reports created dynamically and even emailed to users. On all reports, I generally try to add a header and footer that will allow the users to quickly see the details of the report.

Generally, it’s a great idea to include the parameters used to generate the report especially if there’s several options that could produce dramatically different results.

Header
I always like to include all of the parameters used to generate the report in the header and include the name of the report as well. In most cases, the reports I create are exported to PDF and sometimes printed for management or sometimes emailed to clients.

And of course, it’s always a great idea to add some branding to the header like including your company’s tag line and logo.

Footer
I always include the creation time of the report, along with the page number and total number of pages in the footer of the report.

SQL Server – Get Only the Date

Very often, I need the date of some data and do not like to convert the date to a varchar and then proceed to format it or convert it back to a date.

One of the official Microsoft sanctioned ways is:
SELECT DATEADD(dd,0,DATEDIFF(dd,0,GETDATE()))

SSRS Doesn’t Respond

Every few months, especially after a windows update, I find that SQL Server Reporting Services’ ReportManager just won’t load. There’s no errors, there’s nothing in the System Event Log or any reason that I can see for the reportmanager never loading.

I usually end up out of frustration stopping the reporting services, stopping IIS, and then usually starting IIS and then finally starting Reporting Services again. Today, when ReportManager wouldn’t load I went to restart IIS and got an error about the w3svc not being able to stop.

Eventually, I figured out I could run the following command and eventually get the World Wide Web Service to stop. I then could start IIS again and then start Reporting Services back up.

Taskkill /F /FI "SERVICES eq w3svc"

Hope this helps.

Get List of Tables and Creation Date from SQL Server

Getting information from the SQL Server system tables is relatively easy and can be a great way of checking whether you completed some task or whatever.

I use the following query or some variant to see when I’ve done something or what I named it or whatever.
SELECT name, create_date FROM sys.tables order by create_date

SSSRS Number Stored As Text

Today, while changing a report in SQL Server Reporting Services I noticed that my report when exported to excel was showing the dreaded “Number stored as text.”

When a value was 0, I was replacing with a blank string. By changing it to return Nothing. I was able to get rid of the dreaded Number Stored as Text message.

Hope this helps!

SSRS 2005 Report Manager Browser Fix

As anyone that works with Microsoft products (especially older ones) knows, it can be extremely difficult to get one of their products to work well across different browsers.

In SSRS 2005’s Report Manager the reports appear to look scrunched because the size hasn’t been properly set on an iframe. Unfortunately, we have to fix part of the core’s CSS if we want the reports to properly render in the Report Manager.

We need to edit the following file:
C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportManagerStylesReportingServices.css

Add the following code to the end of the file:

.DocMapAndReportFrame
{
min-height: 500px;
min-width: 500px;
}

SQL University & #SQLHelp On Twitter

In the last three months, I can say that I have learned more about SQL Server than ever before because of the SQL University put together by Jorge Segarra and many other very vocal members of the SQL Server community. SQL University started with very simple introductory blog posts, and has now moved onto much more advanced topics like high availability, back ups, career development and even a little bit about business intelligence.

I’ve really benefit from all of the work by the volunteers and can’t wait to eventually contribute my own posts, discussion and feedback to the authors and community at large. Today, I had a very large problem when updating SQL Server 2005 with no updates whatsoever to SQL Server 2005 SP4 and was able to get some very valuable help and feedback from posting on twitter with the #SQLHelp hashtag.

I can’t help but say how much I appreciate all the help, and valuable learning that has been provided! Keep up the great work!