none
SQL Broker: at application start queue isn't blank

    Frage

  • Hi,

    at first I tried my database with th solution like described in thread "SQL Broker: only one datatable per queue and service?".

    It doesn't function. Then I detected that my database was restored some time ago. So I used this

    ALTER DATABASE mydatabase SET NEW_BROKER WITH ROLLBACK IMMEDIATE

    Now the program runs but strangly the label at the bottom of the form shows that the counter incremented constantly. But no changes were accomplished! The incrementing goes on until formclosing.

    Then I used:

    SELECT        SCHEMA_NAME(q.schema_id) AS SchemaName, q.name AS QueueName, p.rows AS QueueRows
    FROM            sys.service_queues AS q INNER JOIN
                             sys.objects AS o ON o.object_id = q.object_id INNER JOIN
                             sys.objects AS i ON i.parent_object_id = q.object_id INNER JOIN
                             sys.partitions AS p ON p.object_id = i.object_id AND p.index_id IN (0, 1)

    to see what was going on and got this rows

    dbo    myChangeMessages                  689
    dbo    QueryNotificationErrorsQueue    0
    dbo    EventNotificationErrorsQueue     0
    dbo    ServiceBrokerQueue                   0
    So I think that the queue wasn't blank at the start of my programm.

    How can I do that, that in the beginning of the process no messages are existing? Some kind of initializing...

    Regards-


    Dietrich

    Montag, 28. Januar 2013 08:44

Alle Antworten

  • Hi dherrmann,

    Please view the message on the queue, the message may give us some information about how these message are inserted. You can refer to the codes on the following link:

    SQL Server: Service Broker Tips and Tricks
    http://myadventuresincoding.wordpress.com/2007/11/22/sql-server-service-broker-tips-and-tricks/ 


    Allen Li
    TechNet Community Support

    Dienstag, 29. Januar 2013 06:03
  • Hi Allen Li,

    I tried your link and did the selects:

    They show me, that there are 469 Messages in my queue. And when I use

    select * from sys.conversation_endpoints
    then there are 1876 different conversation_handles shown.

    Like this (snippet)

    a5f9f0f7-9269-e211-be88-ccaf78c42cc8
    a2f9f0f7-9269-e211-be88-ccaf78c42cc8
    b0fdf0f7-9269-e211-be88-ccaf78c42cc8
    adfdf0f7-9269-e211-be88-ccaf78c42cc8
    1391f5f1-9269-e211-be88-ccaf78c42cc8
    1091f5f1-9269-e211-be88-ccaf78c42cc8

    .....................

    So I think I can't use END CONVERSATION '73645350-72C4-DC11-9374-0003FF3A5C87'
    because of these different handles...

    Regards


    Dietrich


    • Bearbeitet dherrmann Dienstag, 29. Januar 2013 08:44
    Dienstag, 29. Januar 2013 08:16
  • hi,

    today I tried it anew with

    ALTER DATABASE mydatabase SET NEW_BROKER WITH ROLLBACK IMMEDIATE

    but when I start my testing the effects are the same as described above: incrementing the message counter all the time...

    BUT: when I do the same with Northwind database I have no problems!!

    BUT: the properties of both databases are the same!!

    I use SQL2012Express!!


    Dietrich



    • Bearbeitet dherrmann Mittwoch, 30. Januar 2013 13:40
    Mittwoch, 30. Januar 2013 08:40
  • Hi Allen Li,

    today I have restored my database and have made the procedures which have to be done in this case. Now my testing program doesn't receive some sort of messages when changes occur. But this automatic incrementing is gone!

    When I use Northwind database all works fine, and that I don't understand...

    Could you please give me a tipp, what to do or what informations you need to help me.

    Regards-


    Dietrich


    • Bearbeitet dherrmann Donnerstag, 31. Januar 2013 09:39
    Donnerstag, 31. Januar 2013 09:10
  • Hi,

    is it helpful, when I detected that at my queue the execute_as_principal_id is -2  ??

    At Nortwind database this value is NULL.


    Dietrich


    • Bearbeitet dherrmann Donnerstag, 31. Januar 2013 15:56
    Donnerstag, 31. Januar 2013 15:55
  • Hi,

    it's really a pity that no one answers me or has a tip to solve the problem!!!

    Ciao-


    Dietrich


    • Bearbeitet dherrmann Dienstag, 5. Februar 2013 15:33
    Dienstag, 5. Februar 2013 15:10
  • It is not clear what you are trying to do.

    I can't find "SQL Broker: only one datatable per queue and service?". can you post a link or provide a description of what your system does.

    Sonntag, 10. Februar 2013 10:02
  • it's really a pity that no one answers me or has a tip to solve the problem!!!

    We might be able to better help if you post your scripts.  Also, check the SQL Server error log for related messages.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sonntag, 10. Februar 2013 14:02