none
Failure when trying to call stored proc using WCF-SQL

    Question

  • I have searched for about 2 days and seen nothing that would help me out with this error. It is a bit of a weird issue. I originally had the DB on my local BTS SQL instance and everything worked fine. Obviously I don't want this DB on that server, so I moved it to my dev sql server and now I am getting this error while running the Consume Adapter Service Wizard.

    Configuration of Adapter:

    Uri: mssql://cakeezsql13//UnicaSource?InboundId=MyId

    Binding Properties:

    I have both a PolledDataAvailable and Polling Statement, InboundOperationType is TypedPolling

    Note: If I chose any other type other than TypedPolling; i.e.: Polling or Notification, I am able to pull the WSDL down and generate the files.. however I need to use typed polling for this solution.

    Error:

    An error has occurred while retrieving the properties for the selected operation. Retrieval of Operation Metadata has failed while building WSDL at 'TypedPolling'.

    Stack Trace:

    Microsoft.ServiceModel.Channels.Common.MetadataException: Retrieval of Operation Metadata has failed while building WSDL at 'TypedPolling' ---> System.Data.SqlClient.SqlException: The statement or function must be executed in the context of a user transaction.

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

       at System.Data.SqlClient.SqlDataReader.HasMoreRows()

       at System.Data.SqlClient.SqlDataReader.HasMoreResults()

       at System.Data.SqlClient.SqlDataReader.NextResult()

       at Microsoft.Adapters.Sql.SqlAdapterMetadataResolverHandler.ResolveOperationMetadata(SqlAdapterConnection sqlAdapterConnection, MetadataLookup metadataLookup, String operationId, ISqlAdapterBindingProperties bindingProperties, TimeSpan timeout)

       at Microsoft.Adapters.Sql.ASDKConnectionHandler.Microsoft.ServiceModel.Channels.Common.IMetadataResolverHandler.ResolveOperationMetadata(String operationId, TimeSpan timeout, TypeMetadataCollection& extraTypeMetadataResolved)

       at Microsoft.ServiceModel.Channels.Common.Design.MetadataCache.GetOperationMetadata(String uniqueId, Guid clientId, TimeSpan timeout)

       at Microsoft.ServiceModel.Channels.Common.Design.WsdlBuilder.SearchBrowseNodes(MetadataRetrievalNode[] nodes, WsdlBuilderHelper helper, TimeoutHelper timeoutHelper)

       --- End of inner exception stack trace ---

    Server stack trace:

       at Microsoft.ServiceModel.Channels.Common.Design.AdapterExceptions.ThrowMetadataException(String errorMessage, Object arg, Object source, Exception innerException)

       at Microsoft.ServiceModel.Channels.Common.Design.WsdlBuilder.SearchBrowseNodes(MetadataRetrievalNode[] nodes, WsdlBuilderHelper helper, TimeoutHelper timeoutHelper)

       at Microsoft.ServiceModel.Channels.Common.Design.WsdlBuilder.GenerateOperationSchemas(WsdlBuilderHelper helper, MetadataRetrievalNode[] nodes, TimeSpan timeout)

       at Microsoft.ServiceModel.Channels.Common.Design.WsdlBuilder.GetWsdl(MetadataRetrievalNode[] nodes, Uri uri, TimeSpan timeout)

       at Microsoft.Adapters.Sql.SqlAdapterWsdlRetrieval.Microsoft.ServiceModel.Channels.Common.IWsdlRetrieval.GetWsdl(MetadataRetrievalNode[] nodes, Uri uri, TimeSpan timeout)

       at Microsoft.ServiceModel.Channels.Common.Design.MetadataExchanger.ProcessMetadataGet(Message message, Uri target, TimeSpan timeout, MetadataLookup metadataLookup)

       at Microsoft.ServiceModel.Channels.Common.Design.MetadataExchanger.ProcessMetadataMessage(Message message, Uri target, TimeSpan timeout, MetadataLookup metadataLookup, Message& replyMessage)

       at Microsoft.ServiceModel.Channels.Common.Channels.AdapterRequestChannel.Request(Message message, TimeSpan timeout)

       at System.ServiceModel.Dispatcher.RequestChannelBinder.Request(Message message, TimeSpan timeout)

       at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)

       at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)

       at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

    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 Microsoft.ServiceModel.Channels.IMetadataRetrievalContract.GetMetadata(MetadataRetrievalNode[] nodes)

       at Microsoft.ServiceModel.Channels.Tools.MetadataSearchBrowse.MetadataPanel.GetWsdl(MetadataRetrievalNode[] nodes)

       at Microsoft.ServiceModel.Channels.Tools.MetadataSearchBrowse.MetadataPanel.btnProperties_Click(Object sender, EventArgs e)

    Any help is appreciated. -Chuck

     

     

     


    Charles



    • Edited by ckeezer Tuesday, May 21, 2013 7:31 PM
    Tuesday, May 21, 2013 6:07 PM

Answers

  • Found the Error!!!

    The error is in the stored proc that was created. My DBA put some row locking statements that were causing the issue.. So don't use this code

    DECLARE @result int, @msg nvarchar(1000)
    			EXEC @result = sp_getapplock @Resource = 'MessageQueue$Pop', @LockMode = 'Exclusive'
    			IF @result < 0
    			 BEGIN 
    				SET @msg = N'Failed to get the application lock. Reason: ' + 
    								CASE @result
    									WHEN -1 THEN 'The lock request timed out.'
    									WHEN -2 THEN 'The lock request was canceled.'
    									WHEN -3 THEN 'The lock request was chosen as a deadlock victim.'
    									ELSE 'Indicates a parameter validation or other call error.'
    								END
     				RAISERROR (@msg,16,1);
    			 END


    Charles

    • Marked as answer by ckeezer Friday, May 24, 2013 2:21 PM
    Friday, May 24, 2013 2:20 PM

All replies

  • If the solution works locally but not remotely, it usually has to do with MSDTC. Can you verify the following:

    -Does it work if you set "UseAmbientTransactions" to false in the WCF bindings?

    -Is MSDTC configured correctly between the two servers? (BizTalk and the Source Data SQL)

    -Do you poll from an SP? An if so, are there any local transactions being executed inside the SP?

    -Also since you have a local SQL Server on the BizTalk Server, I assume that you have SQL Server Management Studio installed. Can you open it on the BizTalk Server, connect to the remote SQL Server that you are trying to poll from and execute the same SP/SQL that is stated in PolledDataAvailable and PollingStatement and verify that this works.

    Morten la Cour



    • Edited by la Cour Wednesday, May 22, 2013 4:50 AM
    Wednesday, May 22, 2013 4:40 AM
  • la Cour,

    Thank you for the response. Here are the answers to your questions;

    1 - No it does not work if I set UseAmbientTransactions to false. The same error is produced.

    2 - Using DTCPing.exe I am able to successfully ping both BTS and remote SQL servers.

    3 - Yes I poll from a SP. Yes there are local transactions.

    4 - Yes I am able to connect to the remote server from the BTS' SQL MS, and I can execute the STP's.

    In addition, I commented out the local Begin and Commit Transaction in the STP, and still get the same error.

    Also, I just recreated the local DB, and receive location and I am able to poll the data. I also have a second receive location that is pointed to the remote server, polling fails every time. The ports are setup identical, minus the server name in the URI property. I also tried turning off the Ambient feature, and that had no effect.

    Just for grins I added the user running the Host Instance as a db_owner for the DB in question. Still get error.


    Charles





    • Edited by ckeezer Thursday, May 23, 2013 6:27 PM
    Wednesday, May 22, 2013 12:55 PM
  • Here is the DTCPing log file. Please note, I have removed all IP Address, ServerA is my BizTalk Server and ServerB is my remote SQL Server.

    IP Configure Information
    	Host Name . . . . . . . . . : ServerA
    	DNS Servers . . . . . . . . : XX.XXX.XX.X
                                             XX.XXX.XX.X
                                              XX.XXX.XX.X
    	Node Type . . . . . . . . . : Hybrid
    	NetBIOS Scope ID. . . . . . : 
    	IP Routing Enabled. . . . . : no
    	WINS Proxy Enabled. . . . . : no
    	NetBIOS Resolution Uses DNS : no
    
    Ethernet adapter {}:
    
    	Description . . . . . . . . : Intel(R) PRO/1000 MT Network Connection
    	Physical Address. . . . . . : 
    	DHCP Enabled. . . . . . . . : yes
    	IP Address. . . . . . . . . : 
    	Subnet Mask . . . . . . . . : 
    	Default Gateway . . . . . . : 
    	DHCP Server . . . . . . . . : 
    	Primary WINS Server . . . . : 
    	Secondary WINS Server . . . : 
    	Lease Obtained. . . . . . . : Thu May 23 12:41:09 2013
    	Lease Expires . . . . . . . : Fri May 31 12:41:09 2013
    
    ++++++++++++lmhosts.sam++++++++++++
    
    ++++++++++++hosts      ++++++++++++
    	CrmServer
    05-23, 11:23:40.538-->Error(0x424) at clutil.cpp @256
    05-23, 11:23:40.538-->-->OpenCluster
    05-23, 11:23:40.538-->-->1060(The specified service does not exist as an installed service.)
    ++++++++++++++++++++++++++++++++++++++++++++++
         DTCping 1.9 Report for ServerA
    ++++++++++++++++++++++++++++++++++++++++++++++
    RPC server is ready
    ++++++++++++Validating Remote Computer Name++++++++++++
    05-23, 11:24:19.468-->Start DTC connection test
    Name Resolution:
    	ServerB-->XX.XXX.XX.XXX-->ServerB.Domain
    05-23, 11:24:19.588-->Start RPC test (ServerA-->ServerB)
    RPC test is successful
    	Partners CID: 3EFA0B0A-B24B-4FA2-A62B-5F96CFBD64EE
    ++++++++++++RPC test completed+++++++++++++++
    05-23, 11:24:38.191-->RPC server:ServerA received following information:
    	Network Name: ServerA
    	Source  Port: 14718
    	Partner LOG: ServerB134032.log
    	Partner CID: 3EFA0B0A-B24B-4FA2-A62B-5F96CFBD64EE
    ++++++++++++Start Reverse Bind Test+++++++++++++
    Received Bind call from ServerB
    	Network Name: ServerA
    	Source  Port: 14718
    	Hosting Machine:ServerA
    05-23, 11:24:38.377-->Trying to Reverse Bind to ServerB...
    	Test Guid:3EFA0B0A-B24B-4FA2-A62B-5F96CFBD64EE
    Name Resolution:
    	ServerB-->XX.XXX.XX.XXX-->ServerB.Domain
    Reverse Binding success: ServerA-->ServerB
    ++++++++++++Reverse Bind Test ENDED++++++++++
    05-23, 11:24:38.388-->Called POKE from Partner:ServerB
    	Network Name: ServerA
    	Source  Port: 14718
    	Hosting Machine:ServerA
    ++++++++++++Start DTC Binding Test +++++++++++++
    Trying Bind to ServerB
    05-23, 11:24:38.391-->ServerA Initiating DTC Binding Test....
    	Test Guid:0070D0FA-358C-43FE-A18B-0807298DFD21
    Received reverse bind call from ServerB
    	Network Name: ServerA
    	Source  Port: 14718
    	Hosting Machine:ServerA
    Binding success: ServerA-->ServerB
    ++++++++++++DTC Binding Test END+++++++++++++


    Charles



    • Edited by ckeezer Thursday, May 23, 2013 3:37 PM
    Thursday, May 23, 2013 3:36 PM
  • Please look into the user rights on sql box .the user should have read wright permission

    Abhi.

    Thursday, May 23, 2013 7:23 PM
  • Do you mean at the DB level, or at a NTFS level?

    The users that the host instance is running as is a DB_Owner for the DB in question. I have also given that user execute permissions on the stored proc.


    Charles

    Thursday, May 23, 2013 7:32 PM
  • Found the Error!!!

    The error is in the stored proc that was created. My DBA put some row locking statements that were causing the issue.. So don't use this code

    DECLARE @result int, @msg nvarchar(1000)
    			EXEC @result = sp_getapplock @Resource = 'MessageQueue$Pop', @LockMode = 'Exclusive'
    			IF @result < 0
    			 BEGIN 
    				SET @msg = N'Failed to get the application lock. Reason: ' + 
    								CASE @result
    									WHEN -1 THEN 'The lock request timed out.'
    									WHEN -2 THEN 'The lock request was canceled.'
    									WHEN -3 THEN 'The lock request was chosen as a deadlock victim.'
    									ELSE 'Indicates a parameter validation or other call error.'
    								END
     				RAISERROR (@msg,16,1);
    			 END


    Charles

    • Marked as answer by ckeezer Friday, May 24, 2013 2:21 PM
    Friday, May 24, 2013 2:20 PM