none
MQ giving SQL Errors? RRS feed

  • Question

  • I'm really confused by this message on our QA machine.   The Receive Location is for an MQ-Series queue.
    Why is the WCF-SQL adapter involved in the error found in application EventLog (see in italics below)? 

    I have rebooted BizTalk server and MQ server.
    Not sure what to do to fix.  Everything was working together for months and production is working fine with same code and same bindings except for appropriate server-name changes.

    Once the message is received and mapped, it does to to a WCF-SQL port and calls a stored-proc, but if that is erroring, then it should be the SendPort being suspended, not the Receive Location.

    Monitoring in "Websphere MQ Explorer", I see files being read from one queue, and routed to another queue by BizTalk.  It only seems to be the second queue that is giving errors (based on the receive location name in the message below).  MQ-Explorer shows the "Receive_XYZ_Events_Normal_MQ" queue at 204 "current queue depth", then it goes up to sometimes 230 or so, then always back to 204.  I tried the "clear queue" function, which says it cleared, but it still shows "current queue depth" as 204.

    There are hundreds if not 1000s of the error below in the Windows App Event Log, yet only 183 suspended message in BizTalk.  I would expect if there were 1000 event log messages saying things were suspended, then there should be 1000 message in the suspended query.

    Thanks,
    Neal Walters

    A message received by adapter "WCF-SQL" on receive location "Receive_XYZ_Events_Normal_MQ"
    with URI "MQS://MyMQServer/MyQManager/MyQueueName" is suspended.
     Error details: Microsoft.ServiceModel.Channels.Common.ConnectionException:
     A network-related or instance-specific error occurred while establishing a connection to SQL Server.
     The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
     (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
     ---> System.Data.SqlClient.SqlException:
     A network-related or instance-specific error occurred while establishing a connection to SQL Server.
     The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
     (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.Adapters.Sql.SqlAdapterConnection.OpenConnection()
       --- End of inner exception stack trace ---

    Server stack trace:
       at Microsoft.Adapters.Sql.SqlAdapterConnection.OpenConnection()
       at Microsoft.Adapters.Sql.ASDKConnection.Open(TimeSpan timeout)
       at Microsoft.ServiceModel.Channels.Common.Design.ConnectionPool.GetConnection(Guid clientId, TimeSpan timeout)
       at Microsoft.ServiceModel.Channels.Common.Design.ConnectionPool.GetConnectionHandler[TConnectionHandler](Guid clientId, TimeSpan timeout, MetadataLookup metadataLookup, String& connectionId)
       at Microsoft.ServiceModel.Channels.Common.Channels.AdapterRequestChannel.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
       at System.ServiceModel.Channels.ServiceChannel.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

    Exception rethrown at [0]:
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at System.ServiceModel.ICommunicationObject.Open()
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.GetChannel[TChannel](IBaseMessage bizTalkMessage, ChannelFactory`1& cachedFactory)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.SendMessage(IBaseMessage bizTalkMessage)
     MessageId:  {B6D6EF8A-D1A3-427E-BC3C-532E7C56BB6A}
     InstanceID: {0B60B44B-CBBF-415D-96EE-FCF83E70A141}

    Thursday, July 12, 2012 12:46 AM

All replies

  • Hi Neal,

    Is the SQL adapter subscribing to the receive port? or is there an orchestration in the middle?

    I think the issue is due to some security permissions required  on the SQL server. you said there were some server name changes , can you give more details on what changed?


    Regards, Mazin - MCTS BizTalk Server 2006

    Thursday, July 12, 2012 12:37 PM
  • Yes, the SQL adapter subscribes based on a filter value on BTS.MessageType; there are no orchestrations in this process.

    No server names changes; I was referring to the process of substituting a prod server for a test server in the binding files (using BizTalk Deployment Framework from CodePlex). 

    Yesterday, in one attempt to correct the issue, I also set up two separate HostInstances, one called ReceiveMQ and one called SendMQ, and I put all the MQ port/locations into these hosts.  This was recommended a few weeks ago when we had a totally different issue.

    Neal

    Thursday, July 12, 2012 1:45 PM
  • I think you verified these below things already if not would you check again from your end.

    • Make sure your DTC is having the Allow Remote Administration must me enabled and verify one more time as per the MSFT suggestion on DTC.
    • Make sure you enable named pipes in the SQL Configuration tool on the SQL Server.
    • Make sure you applied correct configuration details on WCF-SQL settings.

    Thanks, Raja MCTS BizTalk Server 2010, MCC If this answers your question please mark it accordingly

    Thursday, July 12, 2012 7:56 PM
  • Raja,

        That begs the question - why a SQL error on an MQ Receive?

    Neal

    Thursday, July 12, 2012 9:54 PM
  • Could it be that the BizTalk server lost connection to the MessageBox and that's what that error was? Do you see errors in the event log that have similiar error for other receive locations? What happens when you try resuming those suspended instaces?
    Friday, July 13, 2012 6:36 PM
  • Could you able to regenerate the same error message if you stop the WCF-SQL port?

    Thanks, Raja MCTS BizTalk Server 2010, MCC If this answers your question please mark it accordingly

    Friday, July 13, 2012 7:18 PM
  • Hi Anil,

         We were suspicious of the MessageBox on Wednesday; about a month ago, we had put had the .ldf on a USB drive because we needed additional space.  So yesterday, we decided to convert that machine to Virtual, to get us to the SAN where we could forget about the USB drive. But even in that case, I don't think BizTalk uses WCF-SQL adapter to talk to its own databases.  The message still sounds very contradictory.

    On Wednesday, no flight data was being stored in application SQL database; today, most of it seems to be, but still getting a large number of the error reported above.  My goal of course is to make sure none of the data from the MQ is lost, and that all of it gets into SQL. I opened a ticket with Microsoft, and just sent them a diagnostic.

    Neal

    Friday, July 13, 2012 7:22 PM