Merge replication error after SQL 2012 upgrade

After upgrading a server to SQL 2012 that is running as a publisher of a merge replication publication, you may see an error from the subscription agents along the lines of:

The merge process could not perform retention-based metadata cleanup in database 'Grumpy'
Operand type clash: bigint is incompatible with uniqueidentifier

Don't panic - you don't need to drop or delete the replication!

The problem is caused because SQL 2012 has a different data type in the sysmergesubscriptions table for one of the columns, compared to SQL 2008 R2 - but the upgrade does not address this.

This article from Microsoft indicates that you can apply Cumulative update 3 to fix the probem, or an easier fix is to apply the workaround, which just drops and recreates the column in question, but with the correct data type.

if exists (select * from sys.columns sc inner join sys.types st on sc.system_type_id = st.system_type_id 
                where object_id = object_id('dbo.sysmergesubscriptions') 
 and sc.name = 'last_local_recgen' and st.name = 'uniqueidentifier')
        begin
            alter table dbo.sysmergesubscriptions drop column last_local_recgen
            alter table dbo.sysmergesubscriptions add last_local_recgen bigint null
        end

Managment Studio Icons

As businesses slowly migrate from SQL 2008 R2 to SQL 2012 and SQL 2014, you'll probably find you have multiple copies of Management Studio installed on your pc.
There are times when you might want to use different versions of SSMS, depending on the activity and the SQL instance version you are connecting to.

I stick the icon on my desktop, but by doing that, you have no indication of which version of SSMS the icon relates to, until you rename the text.
That works for identifying it when launching it, but what about if you have a 2008 SSMS and 2012 SSMS open, now you have the same icon on your task bar ...

So here are some new icons - click on them to download the ico file (rather than the png image shown below).

Once saved to your machine, right click on your current SSMS icons, choose properties, change icon, and there you go ... custom SSMS icons.

SQL Login Failed: Token-based server access validation failed with an infrastructure error

Something I'd forgotten when I was setting up a new SQL server, was how the UAC (User Account Controls) permissions affect connections to SQL when the windows account in question is part of a group.

I've only experienced this with sysadmin accounts, but the problem can occur in applications or even Management Studio, where users who should have full sysadmin access find they cannot connect at all.

The only clue is that the SQL log contains the lines:

Source: Logon
Message: Error: 18456, Severity: 14, State: 11.

and

Source: Logon
Message: Login failed for user 'DOMAIN\grumpydba'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 1.2.3.4]

Issue is that SQL doesn't seem to apply sysadmin access rights to users / accounts that only exist in SQL groups.

Two quick fixes:

1. If you are opening SQL Management Studio, right click and select "Run as administrator".  This should give you the access you expect.

2. Add the account explicitly as a SQL login, and grant sysadmin.  (Use this if for a system account) This will give the account the relevant permissions and able to connect.

Find that object with sp_find

Personally I don't always trust the SQL depencies report, so I compiled a basic proc that will search the current database for a specific string.

This string could be a proc name, or field name - and this search will return a list of objects in the current database that contain this text string anywhere in their structure (e.g. in a table column, in a view script, in a stored proc).  It also checks the jobs on the server for the string as well - useful if trying to track down where a stored proc is being called from.

Code link: sp_find

Create this in the master database and you can run it in the context of whichever database you are currently connected to.

I find it helpful to map it to a function key in SSMS, then just highlight the text I want to find, and press the relevant key.  Doesn't always work - e.g. if there is a space or a "." in the string - in which case wrap it in single quotes.

If you want to search all databases, you can use this wrapper proc, that cycles through all objects in all databases - so obviously it will take a bit longer.

Code link: sp_find_anydb

Both of them have an @IgnoreCommentedOut parameter, which also attempts to strip out any commented text from proc code (either via the '--' prefix, or contained within /* and */ identifiers).  Again this will slow down the code, so is not applied by default.

Move SQL table to a different schema

Moving a table in SQL from one schema to another is actually very simple, but as it can't be done via Management Studio, remembering the syntax can be a pain.

Logically you think you want to ALTER TABLE to move it to a different schema, but actually you need to ALTER SCHEMA to give it a table.

What you need to do is:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.TableName

Very easy ... although I know I'll forget that again next time I need to do it ... but at least now I know where I can find the right syntax easily!

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!

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:

SELECT @@SERVERNAME AS "SQLInstance" ,

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

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.

Failed to open the explicitly specified database - but what database was it?

A situation where SQL is helpful, but not quite helpful enough, is when it reports that a user is unable to access a certain database.

SQL will now helpfully tell you the user - it will even give you the IP address of the machine they tried to connect from - leaving you short of one minor detail ... which database was it they were trying to access.

This will show up in the error logs as something like:

Error: 18456, Severity: 14, State: 38.

Message
Login failed for user 'Grumpy\DBA'. Reason: Failed to open the explicitly specified database. [CLIENT: 11.22.33.44]

So what was the specified database??

I'm not sure if there is a way to retrospectively find this out, but you can certainly use Profiler if it is something that is still happening.

In profiler, the Eventclass that gives you the magic information is under "Errors and Warnings", and is the "User Error Messages" Event.  That's right - SQL tells the user what database they can't open but won't tell you!

If you also enable the "Security Audit", "Audit Login Failed" events, and add a filter against "LoginName" for the user name in the original message.

What you should see are "Audit Login Failed" messages for the login you care about, that give the same message as in the Error Log, but you should also see a "User Error Message" entry just before it, and in here you get:

Cannot open database "GrumpyDatabase" requested by the login. The login failed.

The ApplicationName field may also help identify what the user is trying to use to access this database, but at least now you are armed with all the relevant details when you challenge them about trying to access your databases.

What reports are being read in SSRS

If you want to check which reports are being run (or not) in SSRS, or details about which users are running reports, you can pull this information from the ReportServer database.

If you need to check you are on the right database, you can check the SSRS machine name and instance the ReportServer database relates to by running:

SELECT
    MachineName,
    InstanceName
FROM Keys
WHERE Client = 1

Now to find out who's being doing what ... and SQL keeps it all nicely for us in the table "ExecutionLog".

You need to join this back to Catalog to get the report names / paths, but you can then easily start to get information on the most frequently run reports, which users are running certain reports, or even which reports are slow to render.

E.g. to get details on how often a report is run:

SELECT c.Name, c.path , count(*) as 'ReportRun'
FROM ExecutionLog e 
JOIN Catalog c on e.ReportID = c.ItemID
GROUP BY c.Path, c.Name

to find out which users are running a certain report:

SELECT userName, COUNT(*) 
FROM ExecutionLog e 
JOIN CATALOG c ON e.ReportID = c.ItemID
WHERE c.Name = 'ReportName'
GROUP BY e.UserName

I'm sure you can find other details to pull out of this table that may prove useful.

NOTE!

By default the retention period for the ExecutionLog table is 60 days.  However this is also adjustable by updating the ConfigurationInfo table for the ExecutionLogDaysKept row.  Set the number of days, or use "0" to keep data indefinitely.

E.g.
UPDATE ConfigurationInfo 
SET VALUE = '-1' 
WHERE Name = 'ExecutionLogDaysKept'

NOTE - Ignore books online which says this should be set to 0 - if you look at the clean up proc in the ReportServer database, namely ExpireExecutionLogEntries, you can see it actually only leaves the data if the value is set to -1, and not 0.

I would also recommend you download the SSRS Reports Dashboard.  This plugs into SSMS and once installed, you just right click on a ReportServer database, and it gives you access to all sorts of info relating to the relevant SSRS instance.