CODEWITHSUNDEEP
sql-serverpersistencenservicebustransport
ErikMuir
ErikMuir

Reputation: 181

NServiceBus SQL Server Transport and SQL Persistence on different databases

I'm trying to run a NServiceBus Host application using SQL Server Transport and SQL Persistence. When I point both the transport and persistence connection strings to the same database everything works fine. However when I point them to separate databases, the program crashes upon receiving its first message, throwing this exception: System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'persistence.MyService_MySaga'.

Here is my endpoint configuration:

var persistenceConnection = @"Data Source=.\SqlExpress;Database=MyServicePersistence;Integrated Security=True;Max Pool Size=100;";
var transportConnection = @"Data Source=.\SqlExpress;Database=MyServiceTransport;Integrated Security=True;Max Pool Size=100;";

var endpointConfiguration = new EndpointConfiguration("MyService");
endpointConfiguration.SendFailedMessagesTo("error");
endpointConfiguration.AuditProcessedMessagesTo("audit");
endpointConfiguration.EnableInstallers();

var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
transport.ConnectionString(transportConnection);
transport.UseSchemaForQueue("error", "dbo");
transport.UseSchemaForQueue("audit", "dbo");
transport.Transactions(TransportTransactionMode.SendsAtomicWithReceive);

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.SqlVariant(SqlVariant.MsSqlServer);
persistence.ConnectionBuilder(() => new SqlConnection(persistenceConnection));
persistence.Schema("persistence");

var subscriptions = persistence.SubscriptionSettings();
subscriptions.CacheFor(TimeSpan.FromMinutes(1));

Potentially noteworthy: I can prevent the exception from being thrown if I change the TransportTransactionMode from SendsAtomicWithReceive to None. However I don't want to do that because it causes all failed messages to go directly to the error queue, bypassing all retries.

Upvotes: 2

Views: 821

Answers (1)

wlabaj
wlabaj

Reputation: 468

Seems like you discovered a bug in our implementation, thanks for raising it.

I created an issue in the SQL persistence repo, you can track it there.

Most likely we'll release the fix in the new upcoming major, hope that works for you. Can you indicate how urgent the problem is?

Upvotes: 1

Related Questions