SQL yet again

Naredil sm za Service broker queue, contract, service

CREATE MESSAGE TYPE MyMessage VALIDATION = NONE;
go

CREATE CONTRACT MyContract(MyMessage SENT BY ANY);
go

CREATE QUEUE MySendingQueue
WITH 
	STATUS = ON,
	RETENTION = OFF
;
go

CREATE QUEUE MyReceivingQueue
WITH
	STATUS = ON,
	RETENTION = OFF
;
go

ALTER QUEUE MyReceivingQueue WITH ACTIVATION
(
	STATUS = ON,
	MAX_QUEUE_READERS = 1,
	PROCEDURE_NAME = baza.procedura,
	EXECUTE AS OWNER
)

CREATE SERVICE MySendingService ON QUEUE MySendingQueue(MyContract);
go

CREATE SERVICE MyReceivingService ON QUEUE MyReceivingQueue(MyContract);
go


--to sicer ni nuujno za local-------------
CREATE ROUTE MyRoute
WITH
	SERVICE_NAME = 'MyReceivingService',
	ADDRESS = 'LOCAL'
	
;
go

In ko na triggerju kličem

	begin try
		DECLARE @handler uniqueidentifier
		DECLARE @Message NVARCHAR(128)
		BEGIN DIALOG CONVERSATION @handler
		FROM SERVICE MySendingService
		TO SERVICE 'MyReceivingService'
		ON CONTRACT MyContract
		WITH ENCRYPTION = OFF
		-- Send messages on Dialog
		SET @Message = N'blabla';
		SEND ON CONVERSATION @handler
		MESSAGE TYPE MyMessage (@Message)
	end try
	begin catch
		if XACT_STATE() = -1
		ROLLBACK TRANSACTION
		exec zapisi_napako
	end catch

Mi procedura zapisi_napako v errorlog zapiše
Cannot find the object "MySendingQueue" because it does not exist or you do not have permissions.

Če sm pa v triggerj queriju pa zapišem select * from MySendingQueue mi pa lepo izpiše ta queue

Ima kdo kako idejo kaj mu dogaja


Pa če koga zanima odgovor je JA: nucam broker za asinhronost:)

bom poenostavil (in pozabil na pravice)

Kako z

service brokerjem dosežeš asinhron klic stored procedure?




Imam queue

ALTER QUEUE MyReceivingQueue WITH ACTIVATION
(
	STATUS = ON,
	MAX_QUEUE_READERS = 1,
	PROCEDURE_NAME = shema.write_history,
	EXECUTE AS OWNER
)

sem upal da ko bom poslal message da se bo stored procedure sam zagnal. Ali kdo ve kako je s tem

Closed