Messages are not sent when database name includes periods or other special characters

[rhysgodfrey]

Describe the bug

Description

With version 7.0.7 of NServiceBus.Transport.SqlServer package when a message is moved or created by the endpoint it fails with a "Failed to send messsage" error, if the database name contains periods, for example CompanyName.Project.Async.Transport.

This includes registering the endpoint with ServiceControl and Monitoring, or moving a message to the error queue.

Expected behavior

The endpoint is correctly registered with ServiceControl and Monitoring, and errored messages are moved to the error queue.

Actual behavior

Endpoint is not registered with ServiceControl and Monitoring, and errored messages aren't moved to error queue. Succesful messages are still processed.

Versions

NServiceBus.Transport.SqlServer - 7.0.7

Steps to reproduce

Create an endpoint with a database with a name containing periods for example CompanyName.Project.Async.Transport, upgrade to version 7.0.7 of the transport package, and configure it to register with ServiceControl, Monitoring, or to process a message that will fail.

Relevant log output

**REGISTERING WITH MONITORING**

System.Exception: Failed to send message to [CompanyName.Project.Async.Transport].[dbo].[CompanyName.Project.Monitoring]   ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The object name 'CompanyName.Project.Async.Transport.sys.columns' contains more than the maximum number of prefixes. The maximum is 3.     at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)     at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)     at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)     at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)     at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()     at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()     at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)     at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)     at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, Boolean isInternal, String endMethod)     at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)     at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)     at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderAsyncCallback(IAsyncResult asyncResult)     at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)  --- End of stack trace from previous location ---     at NServiceBus.Transport.SqlServer.SqlCommandExtensions.ExecuteScalarAsyncOrDefault[TScalar](SqlCommand command, String commandName, Action`1 onUnexpectedValueMessage, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Queuing/SqlCommandExtensions.cs:line 16     at NServiceBus.Transport.SqlServer.TableBasedQueue.GetSendCommandText(SqlConnection connection, SqlTransaction transaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Queuing/TableBasedQueue.cs:line 155     at NServiceBus.Transport.SqlServer.TableBasedQueue.SendRawMessage(MessageRow message, SqlConnection connection, SqlTransaction transaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Queuing/TableBasedQueue.cs:line 105  ClientConnectionId:2bf27584-64c6-4377-974c-7faab0ac3efe  Error Number:117,State:1,Class:15     --- End of inner exception stack trace ---     at NServiceBus.Transport.SqlServer.TableBasedQueue.ThrowFailedToSendException(Exception ex) in /_/src/NServiceBus.Transport.SqlServer/Queuing/TableBasedQueue.cs:line 182     at NServiceBus.Transport.SqlServer.TableBasedQueue.SendRawMessage(MessageRow message, SqlConnection connection, SqlTransaction transaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Queuing/TableBasedQueue.cs:line 127     at NServiceBus.Transport.SqlServer.MessageDispatcher.Dispatch(IEnumerable`1 operations, SqlConnection connection, SqlTransaction transaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Sending/MessageDispatcher.cs:line 147     at NServiceBus.Transport.SqlServer.MessageDispatcher.DispatchUsingNewConnectionAndTransaction(IEnumerable`1 operations, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Sending/MessageDispatcher.cs:line 111     at NServiceBus.Transport.SqlServer.MessageDispatcher.DispatchDefault(SortingResult sortedOperations, TransportTransaction transportTransaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Sending/MessageDispatcher.cs:line 97     at NServiceBus.Transport.SqlServer.MessageDispatcher.Dispatch(TransportOperations operations, TransportTransaction transportTransaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Sending/MessageDispatcher.cs:line 34     at NServiceBus.Metrics.ServiceControl.ServiceControlReporting.NServiceBusMetadataReport.RunReportAsync(CancellationToken cancellationToken) in /_/src/NServiceBus.Metrics.ServiceControl/ServiceControlReporting/NServiceBusMetadataReport.cs:line 40



** PROCESSING FAILED MESSAGE **

System.Exception: Failed to send message to [CompanyName.Project.Async.Transport].[dbo].[error]   ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The object name 'CompanyName.Project.Async.Transport.sys.columns' contains more than the maximum number of prefixes. The maximum is 3.     at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)     at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)     at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)     at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)     at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()     at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)     at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)     at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)     at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)     at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)  --- End of stack trace from previous location ---     at NServiceBus.Transport.SqlServer.SqlCommandExtensions.ExecuteScalarAsyncOrDefault[TScalar](SqlCommand command, String commandName, Action`1 onUnexpectedValueMessage, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Queuing/SqlCommandExtensions.cs:line 16     at NServiceBus.Transport.SqlServer.TableBasedQueue.GetSendCommandText(SqlConnection connection, SqlTransaction transaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Queuing/TableBasedQueue.cs:line 155     at NServiceBus.Transport.SqlServer.TableBasedQueue.SendRawMessage(MessageRow message, SqlConnection connection, SqlTransaction transaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Queuing/TableBasedQueue.cs:line 105  ClientConnectionId:19e3a91a-4cd8-4fd4-a73e-c4504d58a7f9  Error Number:117,State:1,Class:15     --- End of inner exception stack trace ---     at NServiceBus.Transport.SqlServer.TableBasedQueue.ThrowFailedToSendException(Exception ex) in /_/src/NServiceBus.Transport.SqlServer/Queuing/TableBasedQueue.cs:line 182     at NServiceBus.Transport.SqlServer.TableBasedQueue.SendRawMessage(MessageRow message, SqlConnection connection, SqlTransaction transaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Queuing/TableBasedQueue.cs:line 127     at NServiceBus.Transport.SqlServer.MessageDispatcher.Dispatch(IEnumerable`1 operations, SqlConnection connection, SqlTransaction transaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Sending/MessageDispatcher.cs:line 147     at NServiceBus.Transport.SqlServer.MessageDispatcher.DispatchUsingNewConnectionAndTransaction(IEnumerable`1 operations, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Sending/MessageDispatcher.cs:line 111     at NServiceBus.Transport.SqlServer.MessageDispatcher.DispatchDefault(SortingResult sortedOperations, TransportTransaction transportTransaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Sending/MessageDispatcher.cs:line 97     at NServiceBus.Transport.SqlServer.MessageDispatcher.Dispatch(TransportOperations operations, TransportTransaction transportTransaction, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Sending/MessageDispatcher.cs:line 34     at NServiceBus.RecoverabilityRoutingConnector.Invoke(IRecoverabilityContext context, Func`2 stage) in /_/src/NServiceBus.Core/Recoverability/RecoverabilityRoutingConnector.cs:line 28     at NServiceBus.RecoverabilityPipelineExecutor`1.Invoke(ErrorContext errorContext, CancellationToken cancellationToken) in /_/src/NServiceBus.Core/Recoverability/RecoverabilityPipelineExecutor.cs:line 52     at NServiceBus.RecoverabilityPipelineExecutor`1.Invoke(ErrorContext errorContext, CancellationToken cancellationToken) in /_/src/NServiceBus.Core/Recoverability/RecoverabilityPipelineExecutor.cs:line 54     at NServiceBus.Transport.SqlServer.ProcessStrategy.HandleError(Exception exception, Message message, TransportTransaction transportTransaction, Int32 processingAttempts, ContextBag context, CancellationToken cancellationToken) in /_/src/NServiceBus.Transport.SqlServer/Receiving/ProcessStrategy.cs:line 63

Additional Information

Possible solutions

The error seems to suggest the problem is with this line, potentially adding square brackets around the database name would fix this? for example FROM [{0}].sys.columns c

[lailabougria]

Hello @rhysgodfrey,

Thank you for raising this with us. We have raised the bug internally. Unfortunately, I can't give you any firm ETA on when this will be taken care of, but we will keep you in the loop on this issue.

[SimonCropp]

we hit the same problem, but with a diff error message

System.Exception : Failed to send message to [ConvertToHtmlIntegrationTest.cs_Doc].[dbo].[DSuiteDocumentConverter]
  ----> Microsoft.Data.SqlClient.SqlException : Could not find server 'ConvertToHtmlIntegrationTest' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

[andreasthell]

We have a hyphen "-" in our and get the following error: SqlException: Incorrect syntax near '-'.
Using version 8.1.5, works with 8.1.4.
Pull request with brackets probably fixes it but test case should include hyphen.

[WilliamBZA]

A fix will be release in 8.1.6. Should be released soon.

[SimonCropp]

"soon" is such a relative term. can we get something more definitive ?

[WilliamBZA]

8.1.6 and 7.0.8 have been released which fixes this.