Queue and Service Broker

Find db Users

SELECT * FROM sys.database_principals

Find Permission 

SELECT * FROM sys.database_permissions WHERE grantee_principal_id = 5

Create Queue and Service Broker

CREATE QUEUE PricesMessage;

CREATE SERVICE PricesNotifications

ON QUEUE PricesMessage

([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

Grant Access For Queue (only run 1 time for all Queue 

GRANT RECEIVE ON QueryNotificationErrorsQueue TO dbadmeen;

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO dbadmeen;

GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO dbadmeen;

Grant Access For Queue and ServiceBroker (Run for every queue and service) 

GRANT SEND ON SERVICE::[PricesNotifications] To dbadmeen;

GRANT RECEIVE ON [dbo].[PricesMessage] TO dbadmeen;

-- for user to see the services

GRANT VIEW DEFINITION ON SERVICE::[PricesNotifications] to dbadmeen;

****
Disable Queue & Enabale Queue
alter database dbName set disable_broker with rollback immediate;
alter database dbName set enable_broker with rollback immediate;

Get all the queue in that DB and numbers of message
SELECT sq.name, p.rows 

FROM sys.service_queues sq Join sys.internal_tables it ON sq.object_id = it.parent_id AND it.parent_minor_id = 0 AND it.internal_type = 201 Join sys.indexes as i on i.object_id = it.object_id and i.index_id = 1 Join sys.partitions as p on p.object_id = i.object_id and p.index_id = i.index_id WHERE sq.object_id = it.parent_id AND it.parent_minor_id = 0 AND it.internal_type = 201

See the queue
Select * From sys.dm_qn_subscriptions

Rename Column

Use DBName

go

EXEC sp_rename '.dbo.Table.oldColumnName', 'NewColumnName', 'COLUMN'


-NewColumnName no need Tables or any . []

WF Errors

Error Message
Literal<Boolean>' is not of type 'Delay'. When loading this instance you must ensure that the activity with name 'Literal<Boolean>

Solve: It might be CorrelationID is null