Monday, October 8, 2012

Error Calling Stored Procedure with DATE and TIME datatypes from SSIS

When calling a stored procedure using an OLE DB Command from SSIS to a stored procedure that has a DATE or TIME parameter, you will get the following error:

An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Operand type clash: int is incompatible with date"
The quick fix to this is to change the parameter type in the stored procedure to a varchar(50).
Some blogs recommend changing it to DATETIME, but the range of DATETIME is much smaller than DATE and will truncate.


Wednesday, October 3, 2012

SQL Server Task Status

Have you ever been performing a DBCC SHRINKFILE and wondered how long it was going to take?  The Backup/Restore screen shows percent complete, how about DBCC CHECKDB?

Here's a neat query that can help with that:
Select * from sys.dm_exec_requests
This query returns information about every request that the SQL Server is executing.

One of the columns returned is Percent_Complete.  However, this column is only populated for the following commands:
I have four large databases that I have to shrink when they are copied to the development.  These can take quite a while, so I use this query to monitor status.

Check out the documentation for more details!


Thursday, September 20, 2012

Performance Tuning and Cache

When tuning queries and indexes for performance, it's a good idea to clear the cache when running benchmarks.
For example, when you first run a query, a plan is created, and then data is fetched from the drive.
On the next execute, your query plan won't need to be created, and the results will probably come from the cache, giving a false indication of how long the query will run.

So to clear the cache, execute these two statements:

Then you can test with each execution being treated equally.

Take care on the FreeProcCache, as this is a server level command.   Do not run this on a production box unless you really know what you are doing!


Tuesday, August 28, 2012

SQL Server 2008 R2 and Hyperthreading

So I have read about the debate about hyperthreading on a SQL Server or not.

Well, I decided to put it to the test.

I have an ETL process that runs about 8 hours, moving data from a DB2 source, to a staging area on SQL Server.
Then it is moved into an operational data store, and then finally into a data mart.

The system has four 6-core Xeon processors.

I turned off hyperthreading, and the ETL process decreased by more than an hour!

So there it is.. My totally unscientific, unique results!

Your mileage may vary, but at least give it a try on a Xeon processor.


Wednesday, August 8, 2012

SSRS Reporting in Java

So we are in the final testing stages of our SSRS reporting interface in a Java web front-end.
The results are really fantastic! However, going thru this process has taught me a lot about how SSRS works internally, things that I took for granted when using report manager to render reports. These things will probably help my report design in the future, so I thought I would write them down and pay it forward!

First, a bit about how we implemented this:

In our reports, we force exact pagination by having a caption at the top of the report that reads:  This report contains xxx pages.  Since the report wants this number, SSRS will go ahead and process the report to calculate the page count.  If we had left this out, SSRS would render the first page and not worry about how many subsequent pages were coming.

In the front end, we render page 1 of the report, get the total page count, and then display the first page in a viewer similar to report manager. Note that we are NOT using the report viewer control since this is a Java application. We render all pages to HTML4.0 and display as is on the web page. Since SSRS embeds all of the formatting, the front end is responsible for none of that.

When the user clicks to go to the next page, we again call render with the page number in the device info XML.

In parallel, I created a small C# app that calls a report and renders it much the same way.  I did this so I could make sure I fully understood the process and could assist the Java developers.

We ran into issues when our reports took much longer to render in our front end than it did in report manager or my C# app.  So I started watching stats using the ExecutionLog3 view on the report database.  I paid special attention to TimeDataRetrieval, TimeProcessing, and TimeRendering.
From my app I was seeing the data retrieval occurring on the first render, and some time processing and then some time rendering.  These numbers were all pretty much in-line with what I would have expected. For a 110 page report, I had 3000-3500ms for data, 750-1000ms for processing, and 1000-1500 for rendering. On this first page, the Source field was Live.
On moving to the next page though, the Source field was Session, I was seeing no data retrieval at all, and minimal processing and rendering: 0ms for data, 35-40ms for processing, and 100ms for rendering. Very acceptable numbers for a reporting platform! So this tells me that SSRS processes the dataset all at once, does a bit of pre-rendering to get the page count, and fully renders the first page.

From our front end however, I saw something completely different! Every request had a Source of Live, and each request was pulling data and rendering, sometimes taking up to 15-20 seconds to run the report.

I put WireShark on the SSRS server and started watching network packets. I was able to watch every command that came into the SSRS server, and the response from the server.  It quickly became apparent what was happening.  The front end was not sending the page number for the first Render call, and then calling SetExecutionParameters between each page. The first Render call was causing the server to fully render the report for the first page. The SetExecutionParameters call was invalidating the session and causing the report to be full re-rendered, including data retrieval, for each page. Obviously this was the cause behind the huge difference in execution times!

So I worked with the Java developer and was able to remove any extraneous SSRS calls, and now the performance is amazing!

Our report template it pretty complex, we have a lot of background features that require processing, so to have execution times in the low 100ms range is great!

Speaking of our report template, we have a parameter called Date Range.  A user can select a value in this drop down and select ranges such as last month, last year, etc. When the user selects one of these, we call the SQL Server to return a start and end date based on the selection. In report manager, once you select one date range, and then try to select a second, the start and end dates will not update.  This is a “feature” according to Microsoft. Since we wrote our own front end, we were able to work around this. So you can select Last 2 Years and the start and end dates will populate, and then you can change to rolling 13 months and the start and end dates will update accordingly!

That’s all for now… I welcome any and all questions…


Monday, July 16, 2012

SSRS Create Subscription Issue Using Web Services

Our implementation of SSRS into our Java web site is going very well! However, we ran into an issue trying to set up subscriptions thru the web services.

When trying to set the message body text (the Comment field), we got the error:
One of the extension parameters is not valid for the following reason: you do not have permission to modify the value for the "Comment" 
The users all have the browser role, which allows Manage Individual Subscriptions, but this is not enough. There is a restriction in that setting that disallows the user from setting the comment field.
What I had to do was give the browser role the ability to Manage All Subscriptions. The major caveat with this setting is that if the application calls ListSubscriptions(null, null), the user will see all subscriptions in the system. We had to setup code on the front end to ALWAYS pass the UserID to the ListSubscription method. (

Also, note that for the My Reports folder, the browser security role is not used, its the My Reports role. You will need to give the My Reports role the Manage All Subscriptions task as well.


Tuesday, June 26, 2012

SSRS Subscriptions and Blank Labels

Ran into an interesting issue with SSRS subscriptions. Actually, I am very surprised I have not run into this yet.

I am running SQL Server 2008r2 SP1.

If you have a report with a multi select parameter that allows blanks, a blank label will either prevent you from setting up the subscription, or cause the subscription to fail when it is run.

The Javascript error is:
Message: 'firstChild' is null or not an object
Line: 640
Char: 13
Code: 0
URI: http://Server/InternalReporting/js/ReportingServices.js
and the subscription error is:
Failure sending mail: Default value or value provided for the report parameter 'ReportParameter1' is not a valid value. Mail will not be resent.

I have opened an issue on Connect:

What I have done in the meantime is to replace any blank labels with a holder character ("-").

Has anyone else ran into this issue?


Monday, May 7, 2012

SSRS Parameters are Forced Unicode

While doing some performance tuning on a report query today, I ran into the issue of SSRS sending all strings to a SQL Server database as unicode. Here is a snippet of the query I was working with:
or tbl.Field in (N''0100'',N''1002'',N''1201'',N''1202''....
This snippet came right out of profiler with no editing...

The N in front of each string forces SQL Server to treat it as unicode, as an NVARCHAR string. The issue with this is that my tbl.Field column is a varchar. So no indexes would ever be used for this query, it was always a clustered index scan.

My workaround was to add an nvarchar column, using a trigger to popuate it with the nvarchar version of the varchar field. Messy, but once I did that, and indexed it, the query performed fine.

I have opened up a Connect suggestion, so please visit that and vote for it. This situation should not exist.


Monday, April 16, 2012

SSRS Reporting in a Java Web Site

For the last 6 months or so, I have been working on a project with our web application development team to integrate SSRS reporting into our client facing Java web site.

When I first proposed this project, I knew that in theory this should work fine. But, it did take a bit of research and a lot of work to bring it to fruition.

We are moving into the testing phase this week, and I am happy to say that it works very well! The Java developer working on this is doing a great job of translating all of my Microsoft talk to java :)

We are using the Web Services provided by SSRS with a custom security extension that authenticates against our web site security.

Since we are only rendering one page at a time, rendering is pretty quick, taking less than a second a page to render.

Once we go thru testing and release, I am going to write more on this, including some details.

Wednesday, April 11, 2012

Retrieve Table List in DB2/400 via SQL Statement

Again, mainly to remind myself, the query to retrieve a list of tables in DB2/400 is:

select * from qsys2.systables where table_type='T';

If you want a list of views, table_type would = 'V'


Thursday, March 15, 2012

Duration Change in SQL Profiler 2005

Beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds. - Microsoft Books Online

This is just a helpful tip so you don't waste time following a non-issue... :-)