Friday, 27 September 2013

SSRS Reports blank in Windows 8

Further to a previous post about reports being blank on Chrome (Report doesn't show on Chrome), I've recently come across a similar issue but on Internet Explorer.

The combination that causes the problem though is Windows 8 and IE 10 - you still get the parameter boxes, and navigation buttons, but the rest of the report is completely blank, and running or refreshing the report doesn't even give the spinning "Loading" window.

Turns out this is actually very easy to fix - you just need to view the reports in compatibility mode.

Two ways to do this - one to check if this does fix it, and one to set it permanently.

Check mode
Hit F12 - brings up a dev tools window in bottom of browser.
On the bar should be "Browser Mode: IE10"
Click on this and select "Internet Explorer 10 Compatibility Mode"
This should automatically refresh the report screen - and hopefully the report will now display.

Set permanently
Open IE and open one of the reports that doesn't display
To set the compatibility mode for the site, firstly ignore the Microsoft guff about seeing a little torn-page icon in the address bar - never seen it myself, although feel free to click that first if it's there for you
Hit ALT - this opens a hidden menu bar at the top of the browser window (with File / Edit / Tools / etc.)
Select "Tools", then select "Compatibility View Settings"
You site address should now be shown next to a "Add this website" comment - so hit "Add" and then "Close"
Refresh the page and you should see your report magically appear :)

Of course, if you are using Chrome, you are still in trouble!

Thursday, 29 August 2013

SQL Service Pack Versions

I know this information is available in multiple other places, but I can never find it when I need it, so I'm putting it here for my benefit ... and for anyone who stumbles across this!

First up, it is possible to determine the service pack level directly from SQL using the SERVERPROPERTY function with the 'productlevel' key, for example:

SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS "Server", 
SERVERPROPERTY ('ProductVersion') AS "Build", 
SERVERPROPERTY ('edition') AS "Edition", 
SERVERPROPERTY ('productlevel') AS "Patch" 

would return results similar to

SQLInstance Server Build Edition Patch
GRUMPY\DBA GRUMPYCLUSTER1 10.50.4000.0 Enterprise Edition (64-bit) SP2

Loop through all your servers and you can quickly keep track of which servers are at what build, and what needs patching.

However, if you just want to check what patch level a certain build number equates to (since you can see that as soon as you connect in SSMS), here are the major releases and their version numbers:

SQL 2000 Shiloh
8.00.194 RTM
8.00.384 SP1
8.00.534 SP2
8.00.760 SP3
8.00.2039 SP4
SQL 2005 Yukon
9.00.1399 RTM
9.00.2047 SP1
9.00.3042 SP2
9.00.4035 SP3
9.00.5000 SP4
SQL 2008 Katmai
10.00.1600 RTM
10.00.2531 SP1
10.00.4000 SP2
10.00.5500 SP3
10.00.6000 SP4
SQL 2008 R2 Kilimanjaro
10.50.1600 RTM
10.50.2500 SP1
10.50.4000 SP2
10.50.6000 SP3
SQL 2012 Denali
11.00.2100 RTM
11.00.3000 SP1
11.00.5058 SP2
11.00.6020 SP3
SQL 2014 Hekaton
12.00.2000 RTM
12.00.4050 SP1 (original)
12.00.4100 SP1 (re-released)
SQL 2016
13.00.200 CTP

Wednesday, 13 February 2013

Copy SSMS Templates

If you use Templates in SQL Management studio to store your own code snippets, or team code templates, you may be reluctant to upgrade your machine or version of SSMS for fearing of losing access to them.
While there isn't an export / import function within management studio, you can manually copy over your template files for your new SSMS.
SQL stores these under the hidden “AppData” folder in the Users directory, so you may need to turn on "display hidden files/folders" to see them.
for SQL 2008 you can find the templates under:
C:\Users\GRUMPY.DBA\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql
Note that SQL 2008 copies all the default templates here as well, but you should be able to find your own custom templates in here (especially as you are probably already using a '-' as the first character of your folders to force it to appear at the top of the Template Explorer already aren't you?!).
Now just copy them to the same location on your new machine.
Alternatively, if you have installed a funky copy of SSMS for SQL 2012, you need this location (as keeping it the same is too simple for Microsods):
C:\Users\GRUMPY.DBA\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates\Sql
Again you'll find a copy of all the RTM templates visible in SSMS 2012 (this time via the seemingly pointlessly renamed "Template Browser"), and you can just copy your custom templates / folders into here. 
Note you need to restart SSMS to get it to pick up the new folders, but it's definitely quicker than creating them all by hand one at a time.