PowerBI Report Server Change - January 2019

Yes, it has been a while!  Been busy with life.

However, just fixed an issue on the new release of PowerBI Report Server - January 2019.

We use training videos on the report server to show the users how to use the dashboards; however, after the January release, I was restricted from uploading the file due to the error:

"Uploading or saving files with .mp4 extension is not allowed."

After a bit of digging around, I found this entry in the ConfigurationInfo table in the ReportServer database:  AllowedResourceExtensionsForUpload
I simply added *.mp4 and now I am allowed to upload the videos again!

Hope that helps

peace

SSIS Error: DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005

DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005

I was hit with this VERY generic error writing data to DB2/i in SSIS using MS OLEDB for DB2.
After much searching and troubleshooting, I discovered that this error is thrown whenever you try to insert a NULL into a non nullable column.

Hope this helps someone else save a few hours of work.

peace

SSRS Issue with Microsoft OLE DB for DB2 v3.0

I ran into a perplexing problem on one of my SSRS servers.  This server handled the reporting for internal users, and had a lot of reports that read from our source system, on iSeries DB2 (V6R1), and some reporting from our SQL Server data mart.

The issue was that at random times, SSRS would fully utilize one processor, and a lot of memory.

When looking at the active jobs, it was never the same report, so we tried to find the common thread in each report that was killing the server. We found nothing that was able to stop this from happening. I adjusted the maximum memory setting in SSRS, trying to keep the server from using all memory and causing a restart of the SSRS service. This had no effect as well.

Finally, one of my team members stumbled across the commonality in all of the offending reports: They all used DB2 as the data source.

Well, the first thing I thought of was the Microsoft OLE DB for DB2. We had v3.0 installed on this server. It has been working fine for over two years, but there are regular OS updates to both the iSeries and the SSRS server, one of which could have started this issue.

So I upgraded this server to v4.0, tested the reports to make sure we didn't break anything, and turned the users loose on it.

After two days I can call this a success! The server rarely gets above 10% processor, and very little memory is being used. Reports are faster, and the users are happier!

Update:  The server is running MUCH better, but we have run into an issue with some of the queries causing DB2 errors because the parameters are defined as Text, however, the database field is numeric.  I am still looking for a global way of fixing this, hopefully with the connection string to the DB2.

Update #2: After running v4.0 for 4 months, the problems have definitely gone away.  The only two side effects of this upgrade are:

• A Text type parameter in an SSRS report will throw an error if used on a numeric data type on the DB2. To rectify this, I just did a cast on the numeric field when comparing to the text parameter.
• In v3, field names were limited to a size smaller than on DB2.  If longer field names were used for reports, v3 just truncated the field name and used the limit. v4.0 did away with this limitation, so field names were their full size, causing field name issues. Example:
• AReallyLongFieldNameForTesting on DB2 might show up in v3.0 as AReallyLongFieldNameFo. Once you upgrade to v4.0, the full field name comes in, causing a name mismatch in your reports.

peace

Limitation with YEAR() function in SSIS

I learned something new today while researching an ETL failure:
The YEAR() function in SSIS has a lower limit of 1-1-1753.

Even though the DT_DATE and DT_DBDATE support dates earlier than 1-1-1753, the YEAR() function does not and will fail.

For validation in a derived column transformation, I was using this logic:
YEAR(data_field) < 1900? (DT_DBDATE)"1-1-1900":data_field

And when the date 8-23-0203 came thru, the package failed. The error was non-descript:

An error occurred while evaluating the function.

So, be aware...

peace

SSIS and Max Packet Size

Working to improve my ETL performance, I changed my Max Packet Size on my database server and my ETL server to 32767. However, when I tried to run an SSIS package, I received the following error:

Communication Link Error.
Shared Memory Provider: No process is on the other end of the pipe.

I also received this error when in SQL Server Management Studio, connected to the Integration Services on that server, and tried to browse thru the package store to a package.
After a little research, I determined that SSIS cannot read the packages from the MSDB database if the the Max Packet Size is set larger than 16384.

So at this point, my options are to lower the Max Packet Size on the ETL server, Save my SSIS packages on the hard drive, or move the packages to a package store on a different server, with the Max Packet Size at 16384 or lower.

I am going to test this out and see if it warrants moving the packages to either the file system or another server, or if the 16384 packet size will be sufficient for our ETL processing.


peace

Rebuilding All Indexes on a Table

Sometimes you have a need to rebuild all indexes on a table.  This could be because of an ETL process, or other data loading process that renders the indexes out of date.
I recently experienced this issue on during ETL processing.

I could have scripted out all of the index rebuilds for the table and then added to an Execute SQL task within SSIS. However, if any index should be deleted or renamed, then the Execute SQL task would fail, and the ETL would stop. Also, if we added new indexes, they would not get rebuilt until we added the index to the the Execute SQL task.

So I wrote a stored procedure to rebuild all of the indexes on the a table, or, just one index on a table.

I wanted to share that procedure with you, since I think this may be a common scenario.

-- =============================================
-- spwRebuildTableIndexes
--
-- Author:        Bob Pearson
-- Create date: 2013-03-14
-- Description:    Rebuilds specified index or all indexes on a table
--
-- Updates
-- Date            Who        What
-- --------        ----    ----------------------------
--
-- =============================================
ALTER PROCEDURE [dbo].[spwRebuildTableIndexes]
    @TableName varchar(100),
    @IndexName varchar(100) = null

AS
BEGIN

    Set NOCOUNT ON;

    Declare @SQL varchar(max) = '';
   
    if @IndexName is not null
    BEGIN
        Select @SQL = 'Alter Index ' + @IndexName + ' on ' + @TableName + ' REBUILD;';
        Execute (@SQL);
        print @TableName + '.' + @IndexName + ' Rebuilt...'
        Return;
    END

    Declare cur cursor for

        Select    i.name
        from    sys.indexes i
        inner join
                sys.objects o
        on        i.object_id = o.object_id
        where    o.name = @TableName and o.type = 'U'

    for read only;

    Open Cur;

    Fetch Next from Cur into @IndexName;

    While @@FETCH_STATUS = 0
    BEGIN       
        Select @SQL = 'Alter Index ' + @IndexName + ' on ' + @TableName + ' REBUILD;';
        Execute (@SQL);
       
        print @TableName + '.' + @IndexName + ' Rebuilt...'
       
        Fetch Next from Cur into @IndexName;
    END
   
    Close Cur;
    Deallocate Cur;
END

peace

Quick T-SQL Tip #2: Identity Columns Seed Value

This post really deals with high volume tables with an identity column, but the idea is something that everyone should at least be familiar with.

Quite often I see table creation scripts that start like this:

Create Table ATable
(
ID int not null identity(1,1)
...

And while that is perfectly legal and will work with no issues, if this table is going to be a high volume table, you should consider using the entire Int, not just half of one.

What??

The Int data type spans -2,147,483,648 thru 2,147,483,647.  If your seed is 1, you lose 2,147,483,649 values that could be used.

I have seen production situations where they have run out of identity values because the seed was 1.
So, next time you create an Identity, ask yourself: "Two billion, or four billion?"

and then 

Create Table ATable
(
ID int not null identity(-2147483648,1)
...

peace

Quick T-SQL Tip #1 - Formatting SQL

I am going to start a series of posts for quick T-SQL tips.  In my current position, I support a group of business report developers that are not too familiar with T-SQL.  This will help them learn some of the tips and tricks that I use frequently.

So, tip #1, Formatting SQL.

This tip does not just apply to T-SQL, but any kind of code in general.
I am sure most other developers have been here:  You inherit some code and the first hour is spent trying to figure out what it's doing!

Well, here is my basic formatting.  My goal is to let anyone look at it and understand it quickly!


Select tab Field1 As Field1Name,
tab tab Field2 as Field2Name
From tab Table1 T1
Inner join
tab tab Table2 T2
on tab tab t1.Field = T2.Field
Where tab T1.Field = 'AA'
Order by
tab tab T2.Field1 ASC

Doesn't look that pretty there, but in SQL Management Studio, it works like a charm!  :)

My goal is to have enough whitespace that the SQL does not look cluttered.
No matter how you format, always keep the next developer in mind!!

peace

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.

peace

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:

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION

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!

peace

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:

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

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!

peace

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.

peace

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…

peace

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. (http://msdn.microsoft.com/en-us/library/reportservice2005.reportingservice2005.listsubscriptions)

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.

peace
BobP

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:  http://connect.microsoft.com/SQLServer/feedback/details/750927/blank-parameter-labels-cause-error-in-subscriptions

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

Has anyone else ran into this issue?

BobP

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.

https://connect.microsoft.com/SQLServer/feedback/details/740975/ssrs-sends-all-strings-to-sql-server-as-unicode

peace

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.

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'

peace

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... :-)

peace

Multi Row Inserts in T-SQL

A new feature in SQL Server 2008 is the ability to insert multiple rows with one insert statement!

In previous versions, anytime you wanted to insert multiple rows into a table with a T-SQL script, you would have done something like this:

Insert into MyTable (Field1, Field2)
Values ('ABC',123)
Insert into MyTable (Field1, Field2)
Values ('DEF',456)

However, with SQL Server 2008, you can now do this:

Insert into MyTable (Field1, Field2)
Values ('ABC',123),('DEF',456)

Working with a growing data mart, I do a lot of scripted inserts, so this definitely makes my life a little simpler!

peace