SQL Transport/Persistence issue


(Brian Robichaud) #1

Hey all,
I am working on deploying my first saga that leverages sql persistence/transport. I think I have a permission issue but I am finding it hard to pinpoint.

I have a custom schema where all of my queue data is being store and I have my error and audit tables under the dbo schema. is that approach supported? I was thinking because other services my use the same database for persistence and share the same error queue.

Anyways when an error occurs I get a FATAL error shown here:
FATAL NServiceBus [(null)] - Failed to execute reverability actions for message 45efba27-98ed-42b8-aa0e-d374dfdbf1f9 - CriticalError.RaiseSystem.NullReferenceException: Object reference not set to an instance of an object. at CFN.Messages.B2B.SubscribeToNotifications.Log(FailedMessage failed) at NServiceBus.Recoverability.<>c__DisplayClass4_0.b__1(MessageFaulted e) at System.Linq.Enumerable.WhereSelectListIterator2.MoveNext() at System.Threading.Tasks.Task.WhenAll(IEnumerable1 tasks) at NServiceBus.RecoverabilityExecutor.d__3.MoveNext() — End of stack trace from previous location where exception was thrown — at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at NServiceBus.Transport.SQLServer.ReceiveStrategy.d__15.MoveNext()

here is what I have in my endpoint config:

   public void Customize(EndpointConfiguration endpointConfiguration)
        #region DIConfig
        var builder = new ContainerBuilder();

        IEndpointInstance endpoint = null;
        builder.Register(x => endpoint)

        builder.RegisterInstance(new CDACFactory());
        builder.RegisterInstance(new QueueFactory());
        builder.RegisterInstance(new CaseNotesClient(INFO_SVC_URL));
        builder.RegisterInstance(new CaseStatusClient(INFO_SVC_URL));
        builder.RegisterInstance(new CasePriorityClient(INFO_SVC_URL));

        var container = builder.Build();

            customizations: customizations =>

        #region sqlServerConfig

        var connection = SQL_CONN;

        var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
        transport.UseSchemaForQueue("error", "dbo");
        transport.UseSchemaForQueue("audit", "dbo");


        var routing = transport.Routing();

        var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
        var dialect = persistence.SqlDialect<SqlDialect.MsSqlServer>();
            connectionBuilder: () =>
                return new SqlConnection(connection);
        var subscriptions = persistence.SubscriptionSettings();


        SqlHelper.CreateSchema(connection, "CUSTOMSCHEMA");


        SubscribeToNotifications.Subscribe(endpointConfiguration, endpoint);

(Andreas Öhlund) #2

Looking at the stacktrace it seems like there is a nullref in CFN.Messages.B2B.SubscribeToNotifications.Log(...) I assume that is your own code doing some logging. Can you share the code for the .Log() method?

(Brian Robichaud) #3

Yeah, I ended up finding a null reference in my SubscribeToNotifications.Subscribe method that is being called on the last line of the endpoint config…

(Brian Robichaud) #4

This is the code for that class… I am clearly not referencing the endpoint correctly… Do you know what I am doing wrong here? The null reference was on the _endpoint.Send() call.

public static class SubscribeToNotifications
    private static string _mailTo = ConfigurationManager.AppSettings["MailTo"];
    private static string _mailFrom = ConfigurationManager.AppSettings["MailFrom"];
    private static string _mailBusDestination = ConfigurationManager.AppSettings["MailBusDestination"];
    private static string _mailSubject = ConfigurationManager.AppSettings["MailSubject"];
    private static string _mailBody = ConfigurationManager.AppSettings["MailBody"];
    private static string _mailPriority = ConfigurationManager.AppSettings["MailPriority"];
    static ILog log = LogManager.GetLogger(typeof(SubscribeToNotifications));
    private static IEndpointInstance _endpoint;

    public static void Subscribe(EndpointConfiguration endpointConfiguration, IEndpointInstance endpoint)
        var errors = endpointConfiguration.Notifications.Errors;
        errors.MessageSentToErrorQueue += (sender, retry) => Log(retry);
        _endpoint = endpoint;

    static string GetMessageString(byte[] body)
        return Encoding.UTF8.GetString(body);

    static void Log(FailedMessage failed)
        log.InfoFormat("failedMessage = {0}", JsonConvert.SerializeObject(failed));

        _mailBody = string.Format(_mailBody + "\r\n messageID: \r\n {0} \r\n Exception Details: {1} \r\n Stack Trace: {2} \r\n",

        EmailMessage emailMessage = new EmailMessage { To = _mailTo, From = _mailFrom, Body = _mailBody, Priority = int.Parse(_mailPriority), Subject = _mailSubject };

        // send to email endpoint
        var sendOptions = new SendOptions();
        _endpoint.Send(emailMessage, sendOptions);


(Andreas Öhlund) #5

Hi Brian, technically that should work, can you share the code that sets up the endpoint and makes the call to SubscribeToNotifications.Subscribe(endpoint)?

That said what you are doing is a bit dangerous, should the failure be due to some issue with the transport, quotas etc, then the send would fail and potentially bringing the endpoint down. Can you share some details on why you need to send that email? do you want to send it for all failures?

A few options:

(Brian Robichaud) #6

Yeah it is to notify when a failure occurs. I like the first option you listed and will move toward that approach now. Thanks!

(Brian Robichaud) #7

Hey Andrea.
I am looking to implement the first option however, my ServiceControl instance is on a cluster so I am having a hard time setting up the routing.

I downloaded the sample and where it is registering the publisher, I am not sure where to specify the cluster name:


was trying:

that throw a runtime error. Do you know how I should be specifying the cluster in this scenario?

(Ramon Smits) #8

You are using SQL Transport. which acts like a broker. If you look at your tables you should see a table dbo.Particular.ServiceControl if you use the default service control name. Your original snippet should work if you use the default queue


Maybe you are using a different schema? In that case you need to add a schema mapping in your endpoint transport configuration.

(Brian Robichaud) #9

I actually have multiple transports… I grabbed the sample and was going to try connecting my MSMQ clustered service control instance.

(Ramon Smits) #10

Ok, that was not mentioned in your post. The title also explicitly states SQL Transport.

So in the code where you use .RegisterPublisher(...) you configured the MSMQ transport.
For MSMQ you can indeed add the destination as a postfix with the @ sign. Can you share the exception that you are getting? Please share the full exception details with the stack trace so that also nested exceptions are shown.