WCF-Custom sql polling is not working if there is no record from stored procedure RRS feed

  • Question

  • need to handle empty result from stored procedure but polling not working if there is no record. i have used TypedPolling and useAmbientTransaction=False
    Thursday, May 2, 2019 4:38 PM

All replies

  • Sounds like you are not correctly using the "PolledAvailableDataStatement"

    This should be some SQL or a stored procedure call that returns the number of records available. If it returns zero, then the PollingStatement does not get run, and so you never get an empty record set. 

    Down there somewhere, its just ones and zeros.

    Tuesday, May 7, 2019 4:49 PM
  • I have used the configurations as below

    inboundOperationType => TypedPolling

    PolledDataAvailableStatement => select 1

    PollingStatement => EXEC spname (no records)

    useAmbientTransaction => False

    want to get empty polling xml but polling not working if there is no record

    Wednesday, May 8, 2019 7:38 AM
  • Well, why?  This is the most often used behavior, if there are no records, there's nothing to do.

    Now, if you have a triple verified business requirement that you must do something specific when there are no records, then...

    Modify the Stored Procedure to return a different message when there are no records.

    Wednesday, May 8, 2019 1:16 PM
  • Your problem is your PolledDataAvailableStatement. It will always make your pollingstatement run, and that is not what you want. 

    Create another version of your stored procedure spname in which instead of returning a recordset it  returns a count of records.

    So if spname has:  Select field, anotherfield, FRom blah where foo

    You would create spname2 that has : Select count(*) from blah where foo

    Same thing except a count rather than data. Use spname2 as your polling statement

    Then spname will never get run when spname2 returns zero

    Down there somewhere, its just ones and zeros.

    Wednesday, May 8, 2019 1:17 PM

  • As Johns has said, you will need to modify your stored procedure to return something when there are no records.

    Note: Setting useAmbientTransaction=False will mean it does not execute the PolledDataAvailableStatement it will then only execute the PollingStatement

    Monday, May 20, 2019 9:40 PM