Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

Or

Can not execute SQL Server RMO PublicationMonitor TransPendingCommandInfo from a .NET 4.0 application.

or

Load LegacyV2Runtime at runtime

There are a couple of solutions to this issue. The simplest one is to add the following to App.config

xml version="1.0"?>
   
     useLegacyV2RuntimeActivationPolicy="true">    
          version="v4.0" sku=".NETFramework,Version=v4.0"/>  
    

That will allow you application to use Mixed mode assemblies (CLR 2 and 4 in this case).

However, I needed to do this programmatically at runtime as the error was occurring in a reusable user control, so I do not have access to the App.config. To do this, I borrowed the following helper class to load legacy V2 runtime :

public static class RuntimePolicyHelper
{
    public static bool LegacyV2RuntimeEnabledSuccessfully { get; private set; }
 
    static RuntimePolicyHelper()
    {
        ICLRRuntimeInfo clrRuntimeInfo =
        (ICLRRuntimeInfo)RuntimeEnvironment.GetRuntimeInterfaceAsObject(
        Guid.Empty,
        typeof(ICLRRuntimeInfo).GUID);
        try
        {
            clrRuntimeInfo.BindAsLegacyV2Runtime();
            LegacyV2RuntimeEnabledSuccessfully = true;
        }
        catch (COMException)
        {
            // This occurs with an HRESULT meaning 
            // "A different runtime was already bound to the legacy CLR version 2 activation policy."
            LegacyV2RuntimeEnabledSuccessfully = false;
        }
    }
 
    [ComImport]
    [InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]
    [Guid("BD39D1D2-BA2F-486A-89B0-B4B0CB466891")]
    private interface ICLRRuntimeInfo
    {
        void xGetVersionString();
        void xGetRuntimeDirectory();
        void xIsLoaded();
        void xIsLoadable();
        void xLoadErrorString();
        void xLoadLibrary();
        void xGetProcAddress();
        void xGetInterface();
        void xSetDefaultStartupFlags();
        void xGetDefaultStartupFlags();
 
        [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime)]
        void BindAsLegacyV2Runtime();
    }
}
 

 To use this, just call and check the LegacyV2RuntimeEnabledSuccessfully before creating your class. e.g.

if (RuntimePolicyHelper.LegacyV2RuntimeEnabledSuccessfully)
{
     DataAccessConnections.Publisher = _publisher;
 
     _replMonitorHelper = new ReplicationMonitorHelper();
} .....

Merge Replication and Geography types Error converting data type varchar to geography

If your table has Geography data types, then with Merge Replication you will get the following error:

Error messages:
The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber. This can occur when there are pending DDL changes waiting to be applied at the Subscriber. Restart the Merge Agent to apply the DDL changes and synchronize the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199398)
Get help: http://help/MSSQL_REPL-2147199398
Error converting data type varchar to geography. (Source: MSSQLServer, Error number: 8114)
Get help: http://help/8114
The process was successfully stopped. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200999)
Get help: http://help/MSSQL_REPL-2147200999

 The fix to this is as follows:

 

sp_changemergearticle 'publicationName','tableArticle','schema_option','0x000000000C034FD1',1,1

go

sp_changemergearticle 'publicationName','tableArticle','stream_blob_columns','false',1,1

go

Replace publicationName and tableArticle as required. You will need to generate a new snapshot and apply.

 

Looking at what these options mean, it's not entirely clear why they correct the issue with Geography types. They concentrate on streaming blobs, so can only assume that internally Geography is dealt with the same as a blob. Interesting...

 

Thank you Microsoft for this solution btw - I would never have guessed it !

Transactional Replication : How to find erroring command and remove it

Had an interesting situation today where a replicated stored procedure call was in error. The issue was that the procedure call had some single quotes in a string parameter. This was giving the following error in Replication Monitor:

Error messages:
Invalid distribution command, state 2: transaction 0x0x000007c000005e36005000000000 command 3{offset : 258 token offset: 44 state: 50) }. (Source: MSSQL_REPL, Error number: MSSQL_REPL21001)
Get help: http://help/MSSQL_REPL21001

There is actually everything you need in that error, the transaction sequence number is the key piece of information.

But out of interested, I listed up the replication errors at the Publisher using sp_helpsubscriptionerrors as follows:

exec sp_helpsubscriptionerrors  @publisher = 'myPublisher', @publisher_db = 'dbName', @publication='publicationName', @subscriber='mySubscriber', @subscriber_db='dbName'

(I've obviously changed the parameters to hide the sensitive bits!)

This command lists up all of the failed transactions for the subscription. In a normal implementation, this may be just one. However if you have multi-threaded your subscription then there may be more than one.

Once the offending command has been identified (and it will be the same as the one reported in Replication Monitor) you can decide what to do. In this case I wanted to delete the command and apply it by hand (minus the single quotes!). There is a nice command which allows this, the important piece of information is the xact_seqno (otherwise known as the transaction sequence number). sp_setsubscriptionxactseqno will remove the command at the subscriber. Yep, not obvious from the name of the command. The crucial bit to note here is you have to run it in the subscriber database, it will not work from the distributor.

exec sp_setsubscriptionxactseqno @publisher = 'myPublisher',@publisher_db ='publicationName', @publication='mySubscriber', @xact_seqno = 0x000007C000005E36005000000000

SQL Server Transactional Replication Subscription Streams not being used

I was recently looking at increasing the performance of Transactional Replication across a very high latency line (plenty of bandwidth). I had exhausted all of the agent parameter tweaks so decided to look at subscription streams on the subscriber. There's a bunch of good articles out there describing this, not least this article from SQL Server Advisory team which describes the relative benefits (and dangers) of using subscription streams.

So I gave it a go. If you set the -VerboseHistoryLevel to 2 you will get a useful statistics report periodically. Upon checking this I was pleased to see 16 separate threads, so my set up was right. However, when running tests only the first thread was ever used. Looking at the BOL it says :

Note

Subscriptionstreams do not work for articles configured to deliver Transact-SQL. To use subscriptionstreams, configure articles to deliver stored procedure calls instead. Fine, I'm replicating Stored Procedures calls (rather than table articles) so this should work. Well no, my bad, what that note means is the Standard procedures i.e. SCALL, XCALL, MCALL routines. Not replicated Stored Procedures articles. Simple misunderstanding that made all the difference. Changed replication to be Table Article based, and behold 16 threads of goodness and over 10 times increase of performance.

SQL 2012 Full Restore to a new database - error Database in use

A colleague decided to use the new(ish) SQL 2012 Management Studio to restore a database to a new named database. Something that is simple in previous versions, and very useful when you don't want to overwrite an existing database. However, whenever he tried it always failed with Database in use error. Weird.

So I had a look. The user interface has changed a little, maybe to make it less confusing, however it does pretty much the same thing. After selecting the Device radio button, selecting the backup file, you now have a separate Database in the Destination section. So far so good.

Off to the Files page. This is a little clearer than before, quite like the Relocate all files to folder option. Here is where you rename the Restore As path just as before.

Next is the Options page - this is new and has a few more options. Very welcoming is the Close existing connections to destination database, however that also scares me a bit as I can see people using that to force a restore through when the error lies elsewhere. Not least because my colleague suggested using this to overcome the error. Luckily I said no !

By default the Tail-Log backup option is checked.

So if you now click OK, you get a nice progress bar at the top and the error.

Curious - this is a restore to a new database, no one is on it (I did an sp_who2 just to be sure).

The Solution

The Tail-Log backup option is interesting. On a whim, I unchecked this (it's a full backup full restore no logs to a new database - why do I want to take a Tail-Log backup ?) and it worked. In fact it is the sub option - Leave source database in restoring state - that is the issue. Just uncheck that option and it will all work.


I can see the usefulness of the Tail-Log backup option in a disaster recovery situation. Very cool option in the UI. However, I'm not sure why the Database in use error occurs and I think it is very dangerous to have to check to Server connections option. Not a good habit to get into.

Anyhow, a solution (of sorts).