Messages stopped appearing in Queue!

I recently restored my database from a backup and it seems that something in my service broker setup has stopped working, no messages now appear.

I checked that the broker was enabled (it wasnt and i had to use NEW_BROKER because of the 'same id' message)

The database is still set to trustyworthy.

There are also no messages in sys.transmission_queue

I used profiler with all the broker events enabled and the result was as follows:

Broker:Conversation Group
Broker:Conversation STARTED_OUTBOUND

Broker:Conversation CONVERSING

Broker:Message Classify
Broker:Conversation CLOSED
Broker:Conversation Group
Broker:Message Classify

But i'm not sure whether this highlights a problem or not? Any help gratefully appreciated!!

Thanks,

Andy

Hi,

I had the complete conversation - including the end of the conversation - in a stored procedure.

This was the reason i wasn't getting anything in sys.transmission_queue - i didnt end the conversation and the error appeared.

It turned out to be a permissions thing as when i'd restored the database it had set the owner to [SA]

Sorry for wasting your time if you'd bothered to read my problem!

Cheers,

Andy

Messages stopped appearing in Queue!

I recently restored my database from a backup and it seems that something in my service broker setup has stopped working, no messages now appear.

I checked that the broker was enabled (it wasnt and i had to use NEW_BROKER because of the 'same id' message)

The database is still set to trustyworthy.

There are also no messages in sys.transmission_queue

I used profiler with all the broker events enabled and the result was as follows:

Broker:Conversation Group
Broker:Conversation STARTED_OUTBOUND

Broker:Conversation CONVERSING

Broker:Message Classify
Broker:Conversation CLOSED
Broker:Conversation Group
Broker:Message Classify

But i'm not sure whether this highlights a problem or not? Any help gratefully appreciated!!

Thanks,

Andy

Hi,

I had the complete conversation - including the end of the conversation - in a stored procedure.

This was the reason i wasn't getting anything in sys.transmission_queue - i didnt end the conversation and the error appeared.

It turned out to be a permissions thing as when i'd restored the database it had set the owner to [SA]

Sorry for wasting your time if you'd bothered to read my problem!

Cheers,

Andy

Messages back from SqlServer while a Backup is being completed?

I have a WinForms application in which I am wrapping the backing up of a sql
database. In order to back up the database I am using the BACKUP DATABASE
t-sql and the STATS=5 in order to get the progress back from sql server.
Here is the exact script:
BACKUP DATABASE test_dvpt
TO test_Backup
WITH INIT,
STATS=5
Does anyone know how I can get the messages like "5 percent backed up" ...
"10 percent backed up" to my win forms application in order to update a
progress bar?You can handle the connection object InfoMessage event to get these progress
messages.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jason Haley" <JasonHaley@.discussions.microsoft.com> wrote in message
news:3D7A28DB-F43E-4A3E-8909-14363404A177@.microsoft.com...
>I have a WinForms application in which I am wrapping the backing up of a
>sql
> database. In order to back up the database I am using the BACKUP DATABASE
> t-sql and the STATS=5 in order to get the progress back from sql server.
> Here is the exact script:
> BACKUP DATABASE test_dvpt
> TO test_Backup
> WITH INIT,
> STATS=5
> Does anyone know how I can get the messages like "5 percent backed up" ...
> "10 percent backed up" to my win forms application in order to update a
> progress bar?

messages aren't getting through after backup / restore on a different server...

after restoring a database and regenerating keys, I'm trying to queue messages, and they're all hanging out in sys.transmission_queue with the following message:
An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I can't find any documentation or blog info on this error... Help!

thanks!Have you set your database to trustworty?

ALTER DATABASE db_name
SET TRUSTWORTHY ON

Also, maje sure you have a master key in both databases.

CREATE DATABASE MASTER KEY
ENCRYPTION BY PASSWORD = 'somePassW0rd1'

Please let us know if this helps

Niels|||hi Niels.

Nope, the SET TRUSTWORTHY ON on the database did not work.

It would probably be helpful if I mentioned that the backup was taken on the April CTP server, and the restore on the September version...

also - both the initiator and target queues live in the same database...

|||

Which server principal (i.e. login) owns the restored database? Has anything changed with the SQL instance or Windows users since backup was taken? For example you are trying to restore on a machine not connected to a domain and the server principal owning the database was a domain user?

Later,
Rushi

|||Rushi -- a domain administrator owns both databases.
Both servers are connected to the domain, and all services are also run by the domain administrator.|||Probably the dbo of the database is the original login that created the database, on the original server, and that login cannot be impersonated on the new server (e.g. the login is the Windows login corresponding to the original server Administrator account).
Use ALTER AUTHORIZATION ON DATABASE <dbname> TO <loginname> to change the owner of the database to a new login (e.g. [sa]), it will change the dbo to map to a login than is OK to be impersonated.

HTH,
~ Remus|||thanks Remus -- that solved the problem.