none
Issue polling same record multiple times using SQL Adapter RRS feed

  • Question

  • I have an issue polling the same record multiple times using SQL Adapter. I am using the following statement:

    UPDATE TOP(1) [MyDb].[dbo].[MyTable] SET status = 1 WHERE status = 0;
    SELECT TOP(1) [MyDataField] FROM [MyDb].[dbo].[MyTable] WHERE status = 1;
    UPDATE TOP(1) [MyDb].[dbo].[MyTable] SET status = 2; 

    All records start out at 0, then the status gets set to 1, that record is selected then updated to 2. The problem is that the very first record goes through the entire polling scenarios and gets set to 2. The other records stay at 1 and for some reason BT just keeps looping through the first couple of records. Any assistance is appreciated thanks.
    Monday, April 23, 2012 5:50 PM

Answers

  • How about:

    UPDATE TOP(1) [MyDb].[dbo].[MyTable] SET status = 1 WHERE status = 0;
    SELECT [MyDataField] FROM [MyDb].[dbo].[MyTable] WHERE status = 1;

    UPDATE [MyDb].[dbo].[MyTable] SET status = 2 WHERE Status = 1;

    Morten la Cour


    • Edited by la Cour Monday, April 23, 2012 6:52 PM
    • Marked as answer by MSDEVTECH Tuesday, April 24, 2012 11:13 AM
    Monday, April 23, 2012 6:50 PM

All replies

  • How about:

    UPDATE TOP(1) [MyDb].[dbo].[MyTable] SET status = 1 WHERE status = 0;
    SELECT [MyDataField] FROM [MyDb].[dbo].[MyTable] WHERE status = 1;

    UPDATE [MyDb].[dbo].[MyTable] SET status = 2 WHERE Status = 1;

    Morten la Cour


    • Edited by la Cour Monday, April 23, 2012 6:52 PM
    • Marked as answer by MSDEVTECH Tuesday, April 24, 2012 11:13 AM
    Monday, April 23, 2012 6:50 PM
  • How about:

    UPDATE TOP(1) [MyDb].[dbo].[MyTable] SET status = 1 WHERE status = 0;
    SELECT [MyDataField] FROM [MyDb].[dbo].[MyTable] WHERE status = 1;

    UPDATE [MyDb].[dbo].[MyTable] SET status = 2 WHERE Status = 1;

    Morten la Cour


     Thanks.
    • Edited by MSDEVTECH Tuesday, April 24, 2012 11:13 AM
    Tuesday, April 24, 2012 11:13 AM