none
Cannot polling data from Database RRS feed

  • Question

  • Hi Pro,

    I want to polling data from database, I'm using TypePolling type and WCF-Custom adapter But I have issue that BizTalk cannot get data even thought have not any error return.
    I also tried something:
    - I try to run both PolledDataAvailableStatement and PollingStatement query in the SQL server and returned data.
    - I can see both query execute in the SQL profiler but infact it may not execute because in my PollingStatement query have insert a row into the log table, I cannot see any record after BizTalk polling. 
    - I'm using the same user that I config on HostIntance and runing via SQL server.
    - I generated the tables/store procedures and data to script and create another one on local database server and it work fine.
    - I configured tracking log on BizTalk config file and can see the receive location already polling data with the correct interval time.

    Can anyone help please.
    Thanks in advance.


    Monday, May 23, 2016 6:53 AM

Answers

  • I think its Windows Firewall on the BizTalk Server which not allowing Outbound DTC connections which is blocking MSDTC from participating in Transactions with SQL Server.  You can modify the MSDTC Firewall rules in the Control Panel.

    Also check the most common errors that block MSDTC connectivity in a network are:
     1. MSDTC Security settings are not configured properly: http://blogs.msdn.com/florinlazar/archive/2004/06/18/159127.aspx
     2. MSDTC is not in the exception list in the firewall. Refer: https://technet.microsoft.com/en-us/library/cc725913(v=ws.10).aspx
     3. The two machines involved in the transaction cannot see each other by the NetBIOS name. Try ping-ing the machines by name. If that fails, MSDTC will fail. You can solve this by updating your system32\drivers\etc\hosts file.
     4. If the machines were imaged improperly, the CID for MSDTC under HKEY_CLASSES_ROOT\CID may be the same on the two machines, while they need to be unique. The KB 306843 mentioned above gives the instructions to correct this issue.

    Ensure you have covered all of the options available https://msdn.microsoft.com/en-us/library/aa561924



    Rachit Sikroria (Microsoft Azure MVP)

    • Proposed as answer by Angie Xu Monday, May 30, 2016 5:57 AM
    • Marked as answer by Angie Xu Monday, May 30, 2016 5:57 AM
    Tuesday, May 24, 2016 7:15 PM
    Moderator

All replies

  • Hi Daniel

    1)Is DTC configured fine on SQL Server and BizTalk Server? Refer this.

    2)Also check if UseAmbientTransaction is set to True in the adapter config-

    https://mariuszgorzoch.wordpress.com/2012/01/25/useambienttransaction-true/

    3)EnableBizTalkCompatibilityMode should be True in adapter config

    4)What is your PolledDataAvailableStatement returning?
    It should be a positive number. Eg:
    SELECT COUNT(*) from <table_name>

    https://msdn.microsoft.com/en-us/library/dd787981.aspx


    Thanks Arindam




    Monday, May 23, 2016 7:02 AM
    Moderator
  •  In the Receive Location bindings Properties of the Biztalk Administration Console, you have to set the "UseAmbientTransaction"  to False and "EnableBizTalkCompatibilityMode" to True.

    Rachit Sikroria (Microsoft Azure MVP)


    Monday, May 23, 2016 7:06 AM
    Moderator
  • Thanks Arindam,

    2)Also check if UseAmbientTransaction is set to True in the adapter config-

    --> I already set it to true

    3)EnableBizTalkCompatibilityMode should be True in adapter config

    --> I also set it to true

    4)What is your PolledDataAvailableStatement returning?
    It should be a positive number. Eg:
    SELECT COUNT(*) from <table_name>

    --> yes, the return of this is the number ( actual number in my result is 69)

    Monday, May 23, 2016 7:13 AM
  • Hi Daniel

    Can you please check on DTC settings as well on BizTalk and the SQL Server(where you are polling from)-

    1)Is DTC configured fine on SQL Server and BizTalk Server? Refer this.


    Thanks Arindam

    Monday, May 23, 2016 7:51 AM
    Moderator
  •  In the Receive Location bindings Properties of the Biztalk Administration Console, you have to set the "UseAmbientTransaction"  to False and "EnableBizTalkCompatibilityMode" to True.

    Rachit Sikroria (Microsoft Azure MVP)


    Thanks so much Rachit,

    It can polling data but without the PollDataAvailableStatement.


    Monday, May 23, 2016 8:12 AM
  • Hi Daniel

    Because you are also doing an insert in your PollingStatement as you have described, you should not disable UseAmbientTransaction-

    I can see both query execute in the SQL profiler but infact it may not execute because in my PollingStatement query have insert a row into the log table,

    You should really make sure that MSDTC communication works between the SQL Server you are polling and BizTalk Server, using the  link I shared earlier(make sure DTC security settings on both machines are as described in the link). This would ensure that you get transactional consistency that you need - otherwise, in error scenarios you can get unexpected results like records have not been polled but the insert worked and vice-versa. Turning off UseAmbientTransaction is probably fine in SELECT only operations. But, you also are doing INSERT/UPDATE via the PollingStatement.


    Thanks Arindam






    Monday, May 23, 2016 8:22 AM
    Moderator
  • Hi Daniel

    Because you are also doing an insert in your PollingStatement as you have described, you should not disable UseAmbientTransaction-

    I can see both query execute in the SQL profiler but infact it may not execute because in my PollingStatement query have insert a row into the log table,

    You should really make sure that MSDTC communication works between the SQL Server you are polling and BizTalk Server, using the  link I shared earlier(make sure DTC security settings on both machines are as described in the link). This would ensure that you get transactional consistency that you need - otherwise, in error scenarios you can get unexpected results like records have not been polled but the insert worked and vice-versa. Turning off UseAmbientTransaction is probably fine in SELECT only operations. But, you also are doing INSERT/UPDATE via the PollingStatement.


    Thanks Arindam






    Thanks for your suggetion Arindam, in fact I created the store procedure and calling in the PollingStatement .

    Inside the store procedure I have select value, insert into log table and update table value that I'm using the SQL transaction and try/catch block. 

    Monday, May 23, 2016 1:01 PM
  • Hi Arindam,

    I just enabled the MSDTC in both BizTalk server and SQL server but still not polling data.

    Tuesday, May 24, 2016 3:40 AM
  • I also tried something:
    - I try to run both PolledDataAvailableStatement and PollingStatement query in the SQL server and returned data.
    - I can see both query execute in the SQL profiler but infact it may not execute because in my PollingStatement query have insert a row into the log table, I cannot see any record after BizTalk polling. 
    - I'm using the same user that I config on HostIntance and runing via SQL server.
    - I generated the tables/store procedures and data to script and create another one on local database server and it work fine.
    - I configured tracking log on BizTalk config file and can see the receive location already polling data with the correct interval time.

    I believe the issue come from SQL server environment, because it work fine with the same table/store procedure in another database server. But still not found the root cause yet.

    Tuesday, May 24, 2016 5:42 AM
  • 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.

    Can you try running DTCTester.

    http://support.microsoft.com/kb/293799

    Refer: http://social.technet.microsoft.com/wiki/contents/articles/2031.biztalk-server-troubleshooting-problems-with-msdtc.aspx

    Use the DTCTester utility to verify transaction support between two computers if SQL Server is installed on one of the computers. The DTCTester utility uses ODBC to verify transaction support against a SQL Server database. For more information about DTCTester see How to Use DTCTester Tool  .

    Use DTCPing to verify transaction support between two computers if SQL Server is not installed on either computer. The DTCPing tool must be run on both the client and server computer and is a good alternative to the DTCTester utility when SQL Server is not installed on either computer. For more information about DTCPing, see How to troubleshoot MS DTC firewall issues  .

    The problem could be that the SQL Server machine could not resolve the name of the machine that is making the call via BizTalk.


    Rachit Sikroria (Microsoft Azure MVP)

    Tuesday, May 24, 2016 6:34 AM
    Moderator
  • I just try using DTCTest tool and get the error:

    tablename= #dtc714
    Creating Temp Table for Testing: #dtc714
    Warning: No Columns in Result Set From Executing: 'create table #dtc714 (ival in
    t)'
    Initializing DTC
    Beginning DTC Transaction
    Enlisting Connection in Transaction
    Error:
    SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server Driver]
    Distributed transaction error'
    Error:
    SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid cursor state
    Typical Errors in DTC Output When
    a.  Firewall Has Ports Closed
    -OR-
    b.  Bad WINS/DNS entries
    -OR-
    c.  Misconfigured network
    -OR-
    d.  Misconfigured SQL Server machine that has multiple netcards.
    Aborting DTC Transaction
    Releasing DTC Interface Pointers
    Successfully Released pTransaction Pointer.
    I'm checking this issue.

    Tuesday, May 24, 2016 7:32 AM
  • Hi Daniel,

    Read: https://support.microsoft.com/en-us/kb/293799

    Typical Causes Where the DTCTester Tool Fails

    • Firewall has incorrect ports closed.
    • There are bad WINS/DNS entries.
    • Misconfigured SQL Server cluster or dual network adapter.
    • Other misconfigured network setting.

    Suggested Ports to Open for DTC

    port(s) port's purpose
    Open 135 RPC EPM (end point mapper)
    Open 1433 TDS SQL traffic when using TCP/IP
    Open 1434 SQL 2000 Integrated Security
    Open 5100-5200 MSDTC [Dynamically assigned a port by the EPM]


    Rachit Sikroria (Microsoft Azure MVP)

    Tuesday, May 24, 2016 7:35 AM
    Moderator
  • I just try check port via powershell Test-NetConnection <Ip> -Port <PortNo>.

    Just ports 135, 1433 is open, the rest is not.

    Also try in the database server which work fine and the open ports same as above server (Just ports 135, 1433 is open, the rest is not)


    Tuesday, May 24, 2016 7:57 AM
  • I think its Windows Firewall on the BizTalk Server which not allowing Outbound DTC connections which is blocking MSDTC from participating in Transactions with SQL Server.  You can modify the MSDTC Firewall rules in the Control Panel.

    Also check the most common errors that block MSDTC connectivity in a network are:
     1. MSDTC Security settings are not configured properly: http://blogs.msdn.com/florinlazar/archive/2004/06/18/159127.aspx
     2. MSDTC is not in the exception list in the firewall. Refer: https://technet.microsoft.com/en-us/library/cc725913(v=ws.10).aspx
     3. The two machines involved in the transaction cannot see each other by the NetBIOS name. Try ping-ing the machines by name. If that fails, MSDTC will fail. You can solve this by updating your system32\drivers\etc\hosts file.
     4. If the machines were imaged improperly, the CID for MSDTC under HKEY_CLASSES_ROOT\CID may be the same on the two machines, while they need to be unique. The KB 306843 mentioned above gives the instructions to correct this issue.

    Ensure you have covered all of the options available https://msdn.microsoft.com/en-us/library/aa561924



    Rachit Sikroria (Microsoft Azure MVP)

    • Proposed as answer by Angie Xu Monday, May 30, 2016 5:57 AM
    • Marked as answer by Angie Xu Monday, May 30, 2016 5:57 AM
    Tuesday, May 24, 2016 7:15 PM
    Moderator