Real Time DSS: receive

Showing posts with label receive. Show all posts

Friday, March 30, 2012

recieve error in vdi.log when backup SQL 2000 Server DB

using arcserve 2000 to backup database receive error in
VDI.log:
2006/10/05 02:03:24 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at TriggerAbort: invoked
2006/10/05 02:03:43 pid(1312) tid(1892)
Error on DBASQL70TID500VD0
Error at SVDS::Open: Dup(stateEvent) Status Code: 6, x6
Explanation: The handle is invalid.
2006/10/05 02:03:45 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at TriggerAbort: invoked
2006/10/05 02:03:46 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at TriggerAbort: StateEvent Status Code: 6, x6
Explanation: The handle is invalid.
2006/10/05 02:03:46 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at TriggerAbort: CompletionSem Status Code: 6, x6
Explanation: The handle is invalid.
2006/10/05 02:03:46 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at CVDS::Cleanup: Close(State) Status Code: 6, x6
Explanation: The handle is invalid.
2006/10/05 02:03:47 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at CVDS::Cleanup: Close(CompleteSem) Status Code: 6, x6
Explanation: The handle is invalid.
MS SQL Server version is 2000.
Pls help. Thanks
Don't have an answer but check out similar post @. http://forums.databasejournal.com/showthread.php?t=40177sql

Receiving this error since migrating to SQL 2000 from SQL 7

Ever since migrating to SQL 2000 from SQL 7 I receive this error at least
once a day while running jobs through SQL Agent. It may not be the same job
having the problem. Any thoughts?
Executed as user: domain\user. DTSRun: Loading... Error: -2147467259
(80004005); Provider Error: 0 (0) Error string: Timeout expired
Error source: Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0. Process Exit Code 1. The step failed.Three simple suggestions:
1- Run the DTS package manually and see where (Which step)
you get the error.
2- Create an output file through Package-->Properties--
>Logging in the DTS package and read the output file for
the error.
3- If you upgraded from SQL Server 7.0 to 2000, run
sp_updatestats stored proc for all the databases in the QA.
>--Original Message--
>Ever since migrating to SQL 2000 from SQL 7 I receive
this error at least
>once a day while running jobs through SQL Agent. It may
not be the same job
>having the problem. Any thoughts?
>Executed as user: domain\user. DTSRun: Loading...
Error: -2147467259
>(80004005); Provider Error: 0 (0) Error string:
Timeout expired
>Error source: Microsoft OLE DB Provider for SQL
Server Help file:
> Help context: 0. Process Exit Code 1. The step failed.
>.
>|||The job is only failing once or twice a day even though it runs every hour or
in the case of another job, every 1/2 hour. It is never the same time of
day. I can run the DTS job manually and it work without failing. I will
give the logging a try and the sp_updatestats. I didn't upgrade the server -
it was a clean install and a restore from SQL 7. Thanks for your help
"Mark" wrote:
> Three simple suggestions:
> 1- Run the DTS package manually and see where (Which step)
> you get the error.
> 2- Create an output file through Package-->Properties--
> >Logging in the DTS package and read the output file for
> the error.
> 3- If you upgraded from SQL Server 7.0 to 2000, run
> sp_updatestats stored proc for all the databases in the QA.
>
>
> >--Original Message--
> >Ever since migrating to SQL 2000 from SQL 7 I receive
> this error at least
> >once a day while running jobs through SQL Agent. It may
> not be the same job
> >having the problem. Any thoughts?
> >
> >Executed as user: domain\user. DTSRun: Loading...
> Error: -2147467259
> >(80004005); Provider Error: 0 (0) Error string:
> Timeout expired
> >Error source: Microsoft OLE DB Provider for SQL
> Server Help file:
> > Help context: 0. Process Exit Code 1. The step failed.
> >.
> >
>|||I have ran the jobs numerous times with DTS and they do not fail. I have run
the sp_updatestats on all databases on this server and I have turned on
logging with DTS and this job does not log anything when I get the timeout
message. Thanks for any other advice you can give. My customer is getting
very frustrated that this keeps not running.
"Connie" wrote:
> Ever since migrating to SQL 2000 from SQL 7 I receive this error at least
> once a day while running jobs through SQL Agent. It may not be the same job
> having the problem. Any thoughts?
> Executed as user: domain\user. DTSRun: Loading... Error: -2147467259
> (80004005); Provider Error: 0 (0) Error string: Timeout expired
> Error source: Microsoft OLE DB Provider for SQL Server Help file:
> Help context: 0. Process Exit Code 1. The step failed.sql

REceiving notification in windows application

Hi,

Is this possible to receive notifications from Notification Service in my windows application written in Framework 1.1? I am using SqlServer 2K.

I want to create a notification when number of rows in a given table reach a specific limit. I want to receive the notification in my application and prompt user through my application's user-interface instead of sending sms or emails to users.

Thanks,

You can do this by creating your own custom delivery protocol.

HTH...

|||Any sample ?|||

Why not just use a MessageBox ?

You could simply write a SqlCommand counting the number of rows already in your databasetable...

|||Its a little complicated and then I need to do this very frequently and speed is an important factor here as well.|||

I don't have one off hand that I can post. Shyam Pather's book describes how to build one though. It's definitely worth the purchase price.

HTH...

RECEIVING messages from a Windows service

I've done a bit of work with the External Activator but I think it may be a bit overkill for what I need to do (which is RECEIVE messages from a single queue and process them with managed code). I've tried creating a Service Broker Interface service that retrieves messages from this queue, but I notice that if I set the timeout to -1 to watch for messages indefinitely, the Service never completes the OnStart code.

I notice if I change the service's timeout to something greater than 0, the message is retrieved, but this defeats the purpose of using a Windows Service app, which I want to continuously monitor the queue. I noticed the External Activator spawns a thread to start monitoring an EventNotification queue, which I can bypass since I want to monitor the notification's target queue.

Rushi, can you point me in the right direction to create a Windows Service that constantly monitors a queue? Also, I'd like the ability to monitor multiple databases (the queue name would be the same) as well, so if that is not feasible from a Windows Service please let me know.

Also, am I sacrificing scalability by NOT using the External Activator and switching to a Windows Service (I believe the External Activator will spawn multiple instances of the processing executable)?

Thanks,

Chris

If you do not need dynamic launching of one or more instances of your service program then you are fine with your approach of writing a single threaded process (which could be a Windows Service) that receives messages from the queue serially. (Of course, you could get fancy and have multiple (but a fixed number of) threads in your process simultaneously pulling messages from the queue). But if you need the ability to control the number of instances of your service program (processes or threads) based on rate of incoming messages, you will need to either implement the external activator based on the QUEUE_ACTIVATION event notification or use the sample.

If you choose the non-activation approach (i.e. your process is always running and waiting for messages), then setting WaitforTimeout to -1 seems appropriate to me. The Service class does not have an OnStart method, so I'm not sure what you are referring to. It does have a Run() method; and if you set WaitforTimeout to -1, Run will never return. While it would be nice to have a Cancel() method that would gracefully tear down the Service class, but since the Service Broker Interface is only a sample, we did not implement that. It should be fine to terminate the process while Run() has not returned. (The transaction will automatically be rolled back).

Rushi

|||

Rushi,

Thanks for the reply. Sorry, I was using the term "service" to refer to the Service class defined in the ServiceBrokerInterface project and a Windows Service app. What I mean is, in the OnStart() method of a Windows Service app, I would need to call the Service class' Run() method, which will never return. This means that the Windows Service's OnStart() method never completes, and you end up getting an error message from the ServiceInstaller (I think). I realize this may be more of a development question, and if so, I can try posting in another forum. I was just hoping you'd seen an example of RECEIVEing messages from a queue with a timeout of -1 from a Windows Service app.

Thanks,

Chris

|||

You will need to create a Thread in the constructor of your Windows Service class which is started by the OnStart() method. The ThreadStart should point to the ServiceBrokerInterface Service.Run() method.

Hope that helps,
Rushi

|||

Rushi:

You say...."While it would be nice to have a Cancel() method that would gracefully tear down the Service class, but since the Service Broker Interface is only a sample"

What would be those graceful tear down steps ..If I want to implement one?. I can see the following...

If there are still pending messages read from the queue but not yet processed ...return such entries back to the queue by rolling back the transaction.

Implementation wise.... "cancel()" can set a flag and the Dispatch function does not dispatch messages if this flag is set ....this way messages that are already read from queue can be prevented from getting processed.

What else I am missing.?

RECEIVING messages from a Windows service

Thanks,

Chris

Rushi

|||

Rushi,

Thanks,

Chris

|||

Hope that helps,
Rushi

|||

Rushi:

You say...."While it would be nice to have a Cancel() method that would gracefully tear down the Service class, but since the Service Broker Interface is only a sample"

What would be those graceful tear down steps ..If I want to implement one?. I can see the following...

If there are still pending messages read from the queue but not yet processed ...return such entries back to the queue by rolling back the transaction.

What else I am missing.?

sql

RECEIVING from QUEUE by ConversationHandle

Is it possible to receive from a queue by a conversation handle? In the documentation there is an example that show you how to do it. Yet, if you "read" the whole document it says that the conversation handle can not be an expression.

The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id. The search condition may not contain any of the other columns in the queue. The conversation_handle or conversation_group_id may not be an expression.

Here is what I'm trying to do:

;RECEIVE TOP(1) @.MsgBody = CAST(message_body as XML)

FROM ProcessingLetters

WHERE conversation_handle = @.Conversation_Handle

It doesn't seem to matter if I use RECEIVE or SELECT. It will return nothing.

I've even tried this:

where cast(Conversation_Handle as varchar(100)) = cast(@.Conversation_Handle as varchar(100))

Why am I doing this? I've put something into the queue to let me know that something is processing. When it is done I want to pull it out and end the conversation.

So is the WHERE conversation_handle = @.Conversation_Handle supposed to work?

Thanks.

Trish wrote:
So is the WHERE conversation_handle = @.Conversation_Handle supposed to work?

It does work, but a message with the conversation handle value (@.Conversation_Handle) has to exist in the queue in order to be RECEIVEd. It seems you are asking for a conversation handle that does not have any message ready to be received. You can look into queue using SELECT * FROM [ProcessingLetters] to see what messages are available for RECEIVE in the in the first place.

HTH,
~ Remus

|||

When I do a SELECT * FROM ProcessingLetters it does have a record in it and it has the conversationhandle that I'm looking for.

|||

Make sure that the conversation is the same, often is confusing that the initiator and target handle of the same conversation are sequential guids and look identical.

HTH,
~ Remus

|||The record that is in the queue has a field named conversation_handle. In that field is a GUID. That GUID is 598C2A9E-EE11-DB11-ABC7-00150042E356. This is what is in my variable that I'm using in the WHERE clause:

568C2A9E-EE11-DB11-ABC7-00150042E356.

They sure look the same to me. But I'm becoming more convinced I'm just not doing something right.

|||One starts with 598, one starts with 568.|||

Ok...here is a complete test senerio. It was working until I passed the conversation handle into a stored procedure (exec SB_FinalizeQBForLetterGeneration 0,@.Conversation_Handle_To_Start_Processing,null). It will create everything and then drop everything when it's done. All I did was copy the code into a stored procedure and called the stored procedure...then it quit working.

use adventureworks

-- Drop stored procedure if it already exists

IF EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_SCHEMA = N'dbo'

AND SPECIFIC_NAME = N'SB_FinalizeQBForLetterGeneration'

)

DROP PROCEDURE dbo.SB_FinalizeQBForLetterGeneration

CREATE PROCEDURE dbo.SB_FinalizeQBForLetterGeneration

@.Result int,

@.ProcessingHandle uniqueidentifier,

@.FailureText nvarchar(256)

DECLARE @.MsgBody XML

;RECEIVE TOP(1) @.MsgBody = CAST(message_body as XML)

FROM ProcessingLetters

WHERE conversation_handle = @.ProcessingHandle

print 'receiving letter from in processing'

if @.MsgBody is null

BEGIN

print 'conversation handle was not found - '+cast(@.ProcessingHandle as varchar(50))

END

ELSE

BEGIN

print 'conversation handle FOUND!!!! '

END

CREATE MESSAGE TYPE SubmitQB VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE ProcessQB VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE SubmitGrouping VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT QBLetterCreation (SubmitQB SENT BY INITIATOR, ProcessQB SENT BY INITIATOR, SubmitGrouping SENT BY INITIATOR)

CREATE QUEUE WaitingQBs WITH STATUS=ON

CREATE QUEUE ProcessingLetters WITH STATUS=ON

CREATE QUEUE WaitingToGroup WITH STATUS=ON

CREATE SERVICE QBWaiting ON QUEUE WaitingQBs (QBLetterCreation)

CREATE SERVICE QBLetterProcessing ON QUEUE ProcessingLetters (QBLetterCreation)

CREATE SERVICE LetterGrouping ON QUEUE WaitingToGroup (QBLetterCreation)

-- add to the WaitingQBs queue

BEGIN TRANSACTION

DECLARE @.conversationHandle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @.conversationHandle

FROM SERVICE QBWaiting

TO SERVICE 'QBWaiting'

ON CONTRACT QBLetterCreation

WITH ENCRYPTION=OFF;

-- Send a message on the conversation

DECLARE @.message nvarchar(max), @.xmlmsg XML

SET @.message = '<createletter><info QBGroupID="111111" Who="111" Why="jest testing" Retries="0"/></createletter>' ;

SET @.xmlmsg = CAST(@.message as XML)

;SEND ON CONVERSATION @.conversationHandle MESSAGE TYPE SubmitQB(@.xmlmsg)

COMMIT TRANSACTION

END CONVERSATION @.conversationHandle

-- retrieve from waiting qbs

DECLARE @.Conversation_Handle_For_Waiting_QB uniqueidentifier;

DECLARE @.MsgBody XML

;RECEIVE TOP(1) @.MsgBody = CAST( message_body as XML ),

@.Conversation_Handle_For_Waiting_QB = conversation_handle

FROM WaitingQBs

IF @.Conversation_Handle_For_Waiting_QB is not null

BEGIN

-- end this conversation -- the initiator doesn't care so clean it up

print 'Ending the conversation for the waiting QB'

END CONVERSATION @.Conversation_Handle_For_Waiting_QB

DECLARE @.QBGroupID bigint,@.Who bigint, @.Why varchar(256), @.Retries int

SET @.QBGroupID = @.MsgBody.value('(/createletter/info/@.QBGroupID)[1]','bigint');

SET @.Who = @.MsgBody.value('(/createletter/info/@.Who)[1]','bigint');

SET @.Why = @.MsgBody.value('(/createletter/info/@.Why)[1]','nvarchar(256)');

SET @.Retries = @.MsgBody.value('(/createletter/info/@.Retries)[1]','int');

print @.QBGroupID

print @.Who

print @.Why

print @.Retries

-- now submit this QB to processing

-- first check to see if this qbgroupid is already in the queue

select message_body

from ProcessingLetters

WHERE cast(message_body as xml).value('(/createletter/info/@.QBGroupID)[1]','bigint') = @.QBGroupID

IF @.@.ROWCOUNT = 0

BEGIN

-- adding to processing queue

BEGIN TRANSACTION

DECLARE @.Conversation_Handle_To_Start_Processing uniqueidentifier;

-- 300 = 300 seconds = 5 minutes

BEGIN DIALOG CONVERSATION @.Conversation_Handle_To_Start_Processing

FROM SERVICE QBLetterProcessing

TO SERVICE 'QBLetterProcessing'

ON CONTRACT QBLetterCreation

WITH ENCRYPTION=OFF, LIFETIME = 300;

-- Send a message on the conversation

--DECLARE @.message nvarchar(max), @.xmlmsg XML

SET @.message = '<createletter><info QBGroupID="'+cast(@.QBGroupID AS nvarchar(20))+'" Who="'+cast(@.Who AS nvarchar(20))+'" Why="'+@.Why+'" Retries="'+cast(@.Retries as nvarchar(3))+'" Starttime="'+cast(getdate() as varchar(30))+'" /></createletter>' ;

SET @.xmlmsg = CAST(@.message as XML)

;SEND ON CONVERSATION @.Conversation_Handle_To_Start_Processing MESSAGE TYPE ProcessQB(@.xmlmsg)

COMMIT TRANSACTION

print 'conversation started for processingletters on '+cast(@.Conversation_Handle_To_Start_Processing as varchar(50))

-- we don't want to end the conversation...it stays open ..

-- it will end it after processing is done...properly

END

ELSE

BEGIN

print 'this qb group is already in processing letters'

END

ELSE

BEGIN

print 'getting next qb to process...no conversation handle'

END

-- now retrieve from the QBLetterProcess Queue

exec SB_FinalizeQBForLetterGeneration 0,@.Conversation_Handle_To_Start_Processing,null

DROP SERVICE QBWaiting

DROP SERVICE QBLetterProcessing

DROP SERVICE LetterGrouping

DROP QUEUE WaitingQBs

DROP QUEUE ProcessingLetters

DROP QUEUE WaitingToGroup

DROP CONTRACT QBLetterCreation

DROP MESSAGE TYPE SubmitQB

DROP MESSAGE TYPE ProcessQB

DROP MESSAGE TYPE SubmitGrouping

-- Drop stored procedure if it already exists

IF EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_SCHEMA = N'dbo'

AND SPECIFIC_NAME = N'SB_FinalizeQBForLetterGeneration'

)

DROP PROCEDURE dbo.SB_FinalizeQBForLetterGeneration

|||

Remus,

Did you run the script? Did you get the same results?

|||

Remus,

So what I'm seeing is that my @.Conversation_Handle_To_Start_Processing is not what is actually stored in the queue as the conversation_handle. So how can I point back to conversation?

CREATE MESSAGE TYPE ProcessQB VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT QBLetterCreation (ProcessQB SENT BY INITIATOR)

CREATE QUEUE ProcessingLetters WITH STATUS=ON

CREATE SERVICE QBLetterProcessing ON QUEUE ProcessingLetters (QBLetterCreation)

-- adding to processing queue

BEGIN TRANSACTION

DECLARE @.Conversation_Handle_To_Start_Processing uniqueidentifier;

-- 300 = 300 seconds = 5 minutes

BEGIN DIALOG CONVERSATION @.Conversation_Handle_To_Start_Processing

FROM SERVICE QBLetterProcessing

TO SERVICE 'QBLetterProcessing'

ON CONTRACT QBLetterCreation

WITH ENCRYPTION=OFF, LIFETIME = 300;

-- Send a message on the conversation

DECLARE @.message nvarchar(max), @.xmlmsg XML

SET @.message = '<createletter><info QBGroupID="11111" Who="111" Why="test" Retries="0" /></createletter>' ;

SET @.xmlmsg = CAST(@.message as XML)

;SEND ON CONVERSATION @.Conversation_Handle_To_Start_Processing MESSAGE TYPE ProcessQB(@.xmlmsg)

COMMIT TRANSACTION

print 'conversation started for processingletters on '+cast(@.Conversation_Handle_To_Start_Processing as varchar(50))

DECLARE @.MsgBody2 nvarchar(max)

;RECEIVE TOP(1) @.MsgBody2 = CAST(message_body as nvarchar(max))

FROM ProcessingLetters

WHERE conversation_handle = @.Conversation_Handle_To_Start_Processing

print 'receiving letter from in processing'

if @.MsgBody2 is null

BEGIN

DECLARE @.c uniqueidentifier

print 'conversation handle was not found - '+cast(@.Conversation_Handle_To_Start_Processing as varchar(50))

select @.c = conversation_handle from ProcessingLetters

print 'in table: '+cast(@.c as varchar(200))

select * from ProcessingLetters

END

ELSE

BEGIN

print 'conversation handle FOUND!!!! '

print @.MsgBody2

END

DROP SERVICE QBLetterProcessing

DROP QUEUE ProcessingLetters

DROP CONTRACT QBLetterCreation

DROP MESSAGE TYPE ProcessQB

|||

I can't believe that you BEGIN a conversation by giving it a conversation handle and that is not what is stored in the queue(table) as the conversation_handle!!!! Why the heck then do you have to specifiy a conversation handle if it is never going to be used? And if you ARE going to do it that way, then have the SEND tell us what it created as a conversation handle!

So this is what I had to do.

SELECT @.Conversation_Handle_To_Start_Processing = conversation_handle

FROM ProcessingLetters

WHERE cast(message_body as xml).value('(/createletter/info/@.QBGroupID)[1]','bigint') = '11111'

Then I could do my RECEIVE. You have to make sure you have a unique identifier in your message body if you want to do this. Otherwise you have no way of knowing what the conversation handle is.

I think the documentation should say that the conversation handle you send in is not the actual conversation handle that is stored.

|||

A conversation consists from two endpoints, initiator and target, each with its own handle. The initiator endpoint is the one returned by the BEGIN DIALOG. The target endpoint is created by the first message arriving at the target service. Looking into sys.conversation_endpoints will show these two endpoints. The two endpoints belongig to the same conversation will have same value for conversation_id. BOL describes this here: http://msdn2.microsoft.com/en-us/library/ms166083.aspx

In your example, you are sending on the initiator's handle and trying to receive on the same handle. The initiator has no messages to availabe to be received, the message on the queue belongs to the target (since it was sent by initiator to target). You would have to receive this message (using the target's handle) and send back a reply to the initiator. Then the initiator would have a message available to be received.

HTH,
~ Remus

Receiving Export file name

Hello,
I work with Crystal in Visual Studio 2005.
Is it possible to receive the export file name after the report viewer finished exporting?I think it is not possible. It is also not possible to figure out which format was used for exporting, which optional parameters were used etc.
If I needed that information, I would probably create my own form for entering export file name and export options.

Wednesday, March 28, 2012

Receiving a message with a specific contract

If the service is defined with multiple contracts is there a way to receive a message with a specific contract?

No. I'm curious to understand why do you want this, can you give a bit of detail?

Thanks,
~ Remus

|||

I am implementing a queue processing system. You can get some of the info here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446600&SiteID=1. The queue can contain messages of different contracts/types. When I receive a message I instantiate a different processing component based on the contract/type of the message. There are processing servers that process different kind of messages. The single processing server might only have a subset of processing components. This is why I need a way to retrieve messages based on the contract/type.

Alex

|||

You can try having one service as front end that simply forwards each dialog to specialized services on the back end, similar to the approach described here: https://blogs.msdn.com/remusrusanu/archive/2006/03/28/563571.aspx

Each supported contract would be forwarded to a different service.

HTH,
~ Remus

|||

I have a working implementation without Service Broker which handles message types and message priority (another one Service Broker does not handle directly). It does not make sence to switch to Service Broker which would also require to increse the complexity of the architecture.

Alex

RECEIVE vs RECEIVE TOP(1)

In working through some examples, sometimes I will see this pattern for receiving messages: What is the purpose of the "nested" WAITFOR (RECEIVE? What is this actually doing? Is it receiving the same message in both RECEIVE?

WAITFOR (

RECEIVE @.dh = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = CAST([message_body] AS NVARCHAR(4000))

FROM [Queue]), TIMEOUT 1000;

WHILE @.dh IS NOT NULL

BEGIN

IF @.message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'

BEGIN

RAISERROR (N'Received error %s from service [Target]', 10, 1, @.message_body) WITH LOG;

END

END CONVERSATION @.dh;

COMMIT;

SELECT @.dh = NULL;

BEGIN TRANSACTION;

WAITFOR (

RECEIVE @.dh = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = CAST([message_body] AS NVARCHAR(4000))

FROM [Queue]), TIMEOUT 1000;

END

COMMIT;

Other times I will see this pattern for receiving messages: Why do a RECEIVE TOP(1) instead of just a RECEIVE?

WAITFOR(RECEIVE TOP(1)

@.conversationHandle = conversation_handle,

@.messageTypeName = message_type_name,

@.messageBody = message_body

FROM [Queue]), TIMEOUT 1000;

And other times I will see this pattern for receiving messages: What is the purpose of RECEIVING into an in-memory table when you can just process the message directly?

WAITFOR(RECEIVE

queuing_order,

conversation_handle,

message_type_name,

message_body

FROM [Queue]

INTO @.tableMessages), TIMEOUT 1000;

IF (@.@.ROWCOUNT = 0)

BEGIN

COMMIT;

BREAK;

END

What is the difference between the three approaches from an architectural and performance perspective? I need to process messages as fast as possible and I'm not sure why or when each should be used. Also, does the timeout have any impact on how FAST messages will be processed, or is it exactly what it says - a timeout - if a message is not found within the period then the procedure will break?

The samples all use RECEIVE TOP(1) because it's much easier code to write and explain. Receiving all the messages available in a conversation group is much more efficient but it makes for more complex code that's harder to understand so I don't usually write samples that way. Remus has some great tips for writing efficient services here: http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx|||

Well, I've reviewed that blog from Remus and actually ran the exact benchmarks and its true that I get pretty good performance. However, let's say that you have thousands of messages coming in per second and each message needs to be processed with some logic - what is the best way to do that using service broker. It seems counter-productive to load them into a temporary table as the example illustrates (but the example is only trying to show the capacity to clear the queue). Also, simply loading a conversation group could be unpredictable if you are recycling dialogs (every message would be a part of the same or a few dialogs). I see alot of benefit to the technology and I'm trying to use it in a very high transaction environment, but some of the advice seems to go against other recommendations and/or the examples are limited to single message processing. How about a boilerplate example of what one should strive for in the activated stored procedure when expecting the kind of load I mentioned above (thousands/sec).

|||

Well, if you trust your judgement more than the espertise of the guys who wrote Service Broker, I suupose you're free to make those judgements. The best way to scale to thousands of messages is going to depend on how big the messages are, how the dialogs are structured, etc. You may find that receiving a bunch of messages into a recordset in a .Net application running on a seperate server scales best for you if there's a significant amount of comutationally intensive processing for each message. A receive top(1) works just like a select top(1) - the whole query is executed and then after the first result is retreived the query is cancelled. If there are a lot of messages in a conversation group, this can be pretty inefficient but if each conversation group has only one message, it doesn't really matter whether you do a top(1) or not. Here are some scaleability thoughts I posted on another thread last week:

Beginning and ending a conversation takes longer than sending a message so starting up a new dialog for every message is pretty inefficient. Some thoughts here: http://blogs.msdn.com/rogerwolterblog/archive/2006/05/20/602938.aspx

Receiving one message at a time and commiting after each message is not a good way to scale an SSB application. Processing multiple messages per transaction makes error handling complex and receiving many messages per Receive statement is harder to do but if performance is a major issue, you need to do it. http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx

If you have to do a lot of processing per message - complex XML manipulation, serializing and deserial,izing, etc. A stored procedure probably isn't a good option. Moving the messaging processing off the database server can help scaleability.

Here's a discussion of some of the things I've learned about SSB applications: http://msdn2.microsoft.com/en-us/library/aa964144.aspx

The key to understanding total throughput is understanding what it means to process a message. I had one customer complain that he could only process 300 messages a second but when I asked for his code I found out processing a message involved doing 40 or 50 inserts into the database so his 300 messages a second translated to 15,000 inserts a second. It's not unusuall to see Service Broker process a couple thousand messages a second but not on a server that's also doing a thousand order-entry transactions a second.

|||

Well, of course I trust the expertise of the guys who wrote Service Broker. My message size is configurable because they are essentially batches of data - so I can configure them based on the performance of the processing procedures. So I guess my question still remains...how best to process multiple messages using RECEIVE. Since it works just like SELECT, do I have to create some sort of cursor or does service broker offer a better pattern or syntax for processing each message in a RECEIVE without having to create a table to hold the data? Can I do some sort of join or apply or something? A simple code sample illustrating would be great. I have read those articles and links many times, but they don't quite answer the question.

|||Have a look at my blog at http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx, it covers many of the questions you ask.sql

receive top 20

I am trying to set up a stored procedure to retrieve to 20 messages from a queue into a table to implement a batched process. I have the following code in a stored procedure.

WAITFOR (
RECEIVE top (20) -- get batched so that we can process same listid once
message_type_name,
message_body, -- the message contents
conversation_handle -- the identifier of the dialog this message was received on
FROM dbo.target
into @.PayloadData
), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away

However, the stored procedure is only retrieving 1 message at a time from the queue. Did I miss some other setting

thanks

RECEIVE can only return messages on one conversation group. Normally each conversation is its own conversation group. If you sent only one message on each conversation, RECEIVE cannot get more that one message at a time, even if there are more messages in the queue.|||

so, in your blog on T-SQL RECEIVE. Fast. : Set based Processing.

How are you able to receive the message in bulk? Is it because of the way you send the message in LoadQueueReceivePerfBlog?

P|||Yes. This is also the reason why I recommend reusing dialogs in my other entry at http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx|||

one follow up,

when I send the message using the same conversation handle, the receive top (20) statement waits until the previous batch is committed before it will start retrieving the next 20. I guess this is because of the its now part of the same conversation group and service broker need to guarantee process order?

This is what my proc looks like

BEGIN TRANSACTION

WAITFOR (

RECEIVE top (20) -- get batched so that we can process same listid once

message_type_name,

message_body, -- the message contents

conversation_handle -- the identifier of the dialog this message was received on

FROM dbo.target

into @.PayloadData

), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away

-- do some processing of the records in @.PayloadData

COMMIT TRANSACTION

if "-- do some processing of the records in @.PayloadData" is taking a long time, its going to block the messages in the queue.

If I remove the begin and commit transaction block, it only wait for the 3 seconds I specified.

Question: is the transaction block necessary in the activated procedure.

thanks

Paul

Receive notification on several clients at the same time

Hello.
I'd like to reveive notifications on the client application when table
data is changed.
How could I use Service Broker, queues and services? In my tests I
have test queue (NotifyTestQueue), test service (NotifyTestService).
If only one client application instance is running - all works fine.
Application runs sql query
WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
in the separate thread and thread execution is continued after message
in the queue is available.
But if I run two ore more client application instance only one of them
receives notification :(
Help me please - how could I set up service broker or queue or service
to enalbe notification for several client about the same event in the
queue?
Should I give up and use individual table with records as events for
clients?
Thank you.I think the problem is when your client polls the Q and does a RECEIVE, the
message is lost, thus if another client is also looking for the same
message, it will not be there.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140520111.673015.124620@.z14g2000cwz.googlegroups.com...
> Hello.
> I'd like to reveive notifications on the client application when table
> data is changed.
> How could I use Service Broker, queues and services? In my tests I
> have test queue (NotifyTestQueue), test service (NotifyTestService).
> If only one client application instance is running - all works fine.
> Application runs sql query
> WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
> in the separate thread and thread execution is continued after message
> in the queue is available.
> But if I run two ore more client application instance only one of them
> receives notification :(
> Help me please - how could I set up service broker or queue or service
> to enalbe notification for several client about the same event in the
> queue?
> Should I give up and use individual table with records as events for
> clients?
> Thank you.
>|||Consider using Notification Services.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140520111.673015.124620@.z14g2000cwz.googlegroups.com...
Hello.
I'd like to reveive notifications on the client application when table
data is changed.
How could I use Service Broker, queues and services? In my tests I
have test queue (NotifyTestQueue), test service (NotifyTestService).
If only one client application instance is running - all works fine.
Application runs sql query
WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
in the separate thread and thread execution is continued after message
in the queue is available.
But if I run two ore more client application instance only one of them
receives notification :(
Help me please - how could I set up service broker or queue or service
to enalbe notification for several client about the same event in the
queue?
Should I give up and use individual table with records as events for
clients?
Thank you.|||The easiest thing is to use QueryNotification which is one of the new
features within SQL Server 2005.
HTH, jens Suessmeyer.|||Hello Jens.
I've use query notification but it works only for last client. Here is
the code:

code:

private static void NotificationTest()
{
using (SqlConnection connection = new
SqlConnection(_connectionStringLocal))
{
connection.Open();
SqlCommand cmd = new SqlCommand(
"SELECT GenderID, IsActive, DescrLocal FROM dbo.cls_Genders",
connection);
SqlNotificationRequest notifyRequest = new SqlNotificationRequest();
notifyRequest.UserData = "Any User Data";
notifyRequest.Options = "service=NotifyTestService";
notifyRequest.Timeout = 600;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
Console.WriteLine(rdr[0] + "\t" + rdr[2] + "\t" + rdr[1]);
}
Console.WriteLine("notifyRequest was sent");
Console.ReadKey();
WaitForChanges();
}
private static void WaitForChanges()
{
using (SqlConnection connection = new
SqlConnection(_connectionStringLocal))
{
connection.Open();
SqlCommand cmd = new SqlCommand(
"WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)",
connection);
cmd.CommandTimeout = 0;
object o = cmd.ExecuteScalar();
Console.WriteLine(o);
}
}

So after key is pressed and event is fired on the server only one
client gots event.|||I should explain what is happened with the code above.
So when one client executes NotificationTest method all works fine.
Client gets notification from the SQL server.
But when two or more client were started only one (last started)
receives notification - all others don't. As SriSamp says one client
application polls the queue and other clients miss event. How could I
leave message in the queue when RECEIVE statement is executed? I cann't
find anything that may help me with it :(
Thank you.|||A message can only be dequeued once. There's no way around this.
What you want to do is to multiply that one message into N messages, for the
N clients. Instead of clients waiting on the NotifyTestQueue, each client
waits on a dedicated queue, NotifyClient1...NoifyClientN. Create activated
stored procedure on the NotifyTestQueue that when it receives a message it
forwards this message to all clients (by SENDing the message N times). This
is basically a pub-sub system implemented on top of dialogs. A practical
example on how to do this is at
http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
Note that you cannot do this if you have too many clients (hundreds). If
each client has a WAIFOR(RECEIVE) blocked waiting for a notification, each
of these statements will block one thread on the server, and you'll hit the
upper configured limit max_worker_threads
http://msdn.microsoft.com/library/d... />
g_09wu.asp
If you need a large number of clients the solution must involve partitioning
out the N client services/queues on a number of different SQL Instances,
since SSB can deliver the notification remotely.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140536881.331233.150770@.f14g2000cwb.googlegroups.com...
>I should explain what is happened with the code above.
> So when one client executes NotificationTest method all works fine.
> Client gets notification from the SQL server.
> But when two or more client were started only one (last started)
> receives notification - all others don't. As SriSamp says one client
> application polls the queue and other clients miss event. How could I
> leave message in the queue when RECEIVE statement is executed? I cann't
> find anything that may help me with it :(
> Thank you.
>|||Hello Remus.
Thank you for fast reply.
Can I use RETENTION = ON parameter on queue?

> Instead of clients waiting on the NotifyTestQueue, each client
waits on a dedicated queue, NotifyClient1...NoifyClientN.
Its not suitable for me - I don't know how many clients will be
connected to the database at future.

> Note that you cannot do this if you have too many clients (hundreds). If
each client has a WAIFOR(RECEIVE) blocked waiting for a notification,
each
of these statements will block one thread on the serve
I'd like to perform RECEIVE without WAITFOR from time to time in the
separate thread on the client.
It seems to me that there is still only one method to notify connected
clients that changes in data occured. Fill some special notify table
and polls this table from time to time at the client. Is it true?
Thank you.|||See inline
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140594793.299405.111750@.g44g2000cwa.googlegroups.com...
> Hello Remus.
> Thank you for fast reply.
> Can I use RETENTION = ON parameter on queue?
Remus: No, retention does not allow for a message to be receive more than
one time. Retention keeps a copy of the message in the queue if compensation
is needed, but this copy is not receivable.

> waits on a dedicated queue, NotifyClient1...NoifyClientN.
> Its not suitable for me - I don't know how many clients will be
> connected to the database at future.
Remus: That's manageable, clients can create a queue and then
subscribe/unsubscribe. Separate queues allow for clients to fan out and be
partitioned on several machines for scalability.

>
> each client has a WAIFOR(RECEIVE) blocked waiting for a notification,
> each
> of these statements will block one thread on the serve
> I'd like to perform RECEIVE without WAITFOR from time to time in the
> separate thread on the client.
Remus: Any reason for this? Pooling is in general worse than WAITFOR. Note
that
WAITFOR (RECEIVE) does not pool, it uses a notification mechanism.

> It seems to me that there is still only one method to notify connected
> clients that changes in data occured. Fill some special notify table
> and polls this table from time to time at the client. Is it true?
Remus: No, this is not true. Pooling for changes or pooling for change
notifications is limited in scalability. There is the potential to overload
the server with just answering the queries that are pooling for changes. The
way to address this is to fan out change notifications for each subscribed
client.
You can actually decide which approach is better, based on the specifics of
you case. Some back of the envelope cacluclation will give you a rough
answer:
First, lets figure out the pooling case. Consider you have N clients, each
pooling at an interval of t seconds and the pooling query batch consumes r
seconds on the server. On any interval of time T you are gonna consume a
total pooling runtime on the server R = r x t/N x T . The R/T ratio gives
how much of the total server runtime is consumed serving the pooling
queries. So basically the r x t/N number is giving you this ratio.
In the case of WAITFOR (RECEIVE), you're gonna have N clients blocked on the
server waiting for notifications. Considering each individual client is
going to be notified (per second) with a probability p and the server
runtime consumed for seting up the WAITFOR, delivering the notification and
returning the result is w, then the total runtime consumed on the server
during the interval T will be w x p x T x N. Again, dividing this by T gives
you the ratio of server runtime consumed in servicing these notifications.
So basically the ratio is w x p x N.
So in the end you're left comparing the r x t/N versus w x p x N.
If you consider the pool query runtime r as being based on a SELECT over one
clustered index and the WAITFOR w time as a RECEIVE from a queue, a rough
approximation of w vs. r should be w = 3 x r (maybe 4 x r), because the
WAITFOR will execute a similar scan as the SELECT, but twice (once before
suspending itself, once when is notified) and it also executes a write
operation (the dequeue)
So is all about comparing t/N vs. 3 x p x N.
- smaller t means clients cannot tolerate dirty cache, they need to pool
frequently, larger t means clients are OK with presenting potentially
outdated data. A large t favors the pooling model.
- larger p means the data is updated often and the cache will be invalidated
often for each client, smaller p means the cache invalidation is unfrequent.
A small p favors the WAITFOR.
Is also important if there is a corelation between p and N. If
each client is interested in a separate partition of the possible data
changes, then increasing N will likely decrease p, favouring the WAITFOR (or
at least hurting it less than the pooling). If all clients are interested in
same changes p does not decrease with an increase of N, this case hurst both
models.
Also, given a sufficient large N both models will crumble as the ratio
approaches 1, meaning all of the server resources are required just to
service the notifications. This case must reside to a fan-out mechanism the
WAITFOR model is better suited for.

> Thank you.
>|||Hello.
I cann't ever express how you post helps me. Thank you very much.
I should read more about Notification services and how it could be
connected with Service borker.
Good luck.

receive notification from server in application

Hello, I have an application which is running on multiple computers and is connected with one sql server. I would like to that the user is notified when another user is performing some special action. So I thought that when this action begins, the server is notified either by writing some data in a table or something like this. Then I would like other applications to be notified so is possible that notification service notifies the application? If yes how? ThanksSSNS may be used in instances like this.

If your "special actions" are not recognizable using the built-in event providers (SQL Server Event Provider & File System Event Provider), you'll need to create a custom event provider to gather the event data.

Assuming the criteria by which people want to be notified is fairly static, you can create subscriptions to match upon. SSNS is not a good choice if the criteria changes frequently (for example, I want to notified when the data behind the web page I'm looking at happens to change. People may bounce among web pages frequently so SSNS is not a good choice there.)

You'll definitely need to create a custom delivery protocol (and perhaps a custom content formatter too) to get the notification to the application.

BTW - you may also want to check out Query Notifications. That technology may offer some of the features you are looking for.

HTH...

Joe|||I want the users of the application to be notified when a particular field in particular table changes. How can the server find all the instances of my application that are connected to it and how can it send the information? How can my application receive them? Thanks

receive notification from server in application

You'll definitely need to create a custom delivery protocol (and perhaps a custom content formatter too) to get the notification to the application.

BTW - you may also want to check out Query Notifications. That technology may offer some of the features you are looking for.

HTH...

Receive multiple FTP files

Is there a way to get more than one file with a single ftp task in SQL 2005?

I need to get 5 files from one server. They are in two different directories is that makes any difference. Right now I have a separate task for each but would like to have one task if possible.

Thanks

You can use a wild card (*) in your path... If you can't use a wild card to get your 5 files, then you'll have to split up the tasks.|||

I figured something like that would be the answer...

Thats ok though... Thanks for the response.

|||

Bill, we actually use a table for our connection (directories, files, etc.) for FTP's and loop through the table replacing the FTP properties (in the FTP task) with the info from the table. If you did this, you could read the connection info into a recordset. Add a ForEach task and place the FTP task within the ForEach container.

The recordset info then drives the connection info for the FTP task... Since you mention two directories, you could use a row for each directory. Lots cleaner (and easier to maintain!) than having five separate FTP tasks firing...

sql

Receive messages from queue based on message_type_name

I have 2 messages types for a queue and would like to use one application that receives messages of one type from the queue and another application that will receieve messages of a different type from the same queue.

Is that possible and if yes how?

SampK

You must send the two message types on separate conversations, to two different services, bound to to different queues. Then you can have one app receive from one queue, the other form the second queue. It is not possible to selectively receive messages based on attributes (type, contract, payload etc) because it could break the order guarantee of the conversations.

HTH,
~ Remus

Receive mail when specific user connects

Hi,
I want to receive a mail when a specific user connects.
How can this be done best? Raise an alert? How?
Btw, the 'Login auditing' is set to 'Failed logins only'.
Suggestions are very welcome!
Cheers,
Eu can scan master..sysprocesses table periodacally to check if that user has connected by matching sid with sid of syslogins table. a job can send u the mail, if not as soon as the user connects (depends on the frequency of the job) ... there might be a better option not sure...

Receive java.io.InputStream frm PL/SQL

I am wondering how to recieve a stream of data from pl/sql to a java-class stored in the database.
Right now my plan is to pass a LOB to java and treat it as an InputStream, but I am not sure if it is possible.
Is it anyone here who knows?
-ajI found out myself!!!!!

If anyone has the same problem:

First create a temporary CLOB in PL/SQL and fill it with the data you need to stream.

This CLOB can be passed directly to the java-class via a java-wrapper. Use the datatype java.sql.Clob

In your java-method you receive an InputStream from the CLOB by calling the method getAsciiStream() on the Clob-object

RECEIVE in transactions

Hello!
I have an an event notifications audit solution that I'm working on. I am
using this activation procedure.
CREATE PROCEDURE dbo.AuditReceive
AS
DECLARE @.AuditMessage TABLE (EventData xml);
RECEIVECAST(message_body AS XML)
FROM AuditQueue
INTO @.AuditMessage
INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECTCAST(EventData.query('data(//PostTime)') AS varchar(23)),
CAST(EventData.query('data(//EventType)') AS sysname),
CAST(EventData.query('data(//LoginName)') AS sysname),
CAST(EventData.query('data(//UserName)') AS sysname),
CAST(EventData.query('data(//ServerName)') AS sysname),
CAST(EventData.query('data(//DatabaseName)') AS sysname),
CAST(EventData.query('data(//ObjectType)') AS sysname),
CAST(EventData.query('data(//SchemaName)') AS sysname),
CAST(EventData.query('data(//ObjectName)') AS sysname),
EventData
FROM @.AuditMessage
It's working nicely, but there is one weakness. I would like to have the
RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
not possible to use RECEIVE in transactions?
Best regards
Ola Hallengren
the books on line tell:
"The RECEIVE statement removes received messages from the queue unless the
queue specifies message retention. When the RETENTION setting for the queue
is ON, the RECEIVE statement updates the status column to 1 and leaves the
messages in the queue. When a transaction that contains a RECEIVE statement
rolls back, all changes to the queue within the transaction are also rolled
back, returning messages to the queue."
Could you expose the source code of the transaction you tried? there's
something wrong but i cannot imagine what without seeing an example.
Gilberto Zampatti
"Ola Hallengren" wrote:

> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVECAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECTCAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren
|||> I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working.
In what way doesn't it work? Error messages? Something else?

> Is it
> not possible to use RECEIVE in transactions?
Yes, but you want to be careful. Say you determine that you can't process this message so you
rollback. Since messages are received in order, you will just read the same message next time.
Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
topic is handled in chapter 8.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren
|||I overlooked one comment in Books Online"
"If the RECEIVE statement is not the first statement in a batch or stored
procedure, the preceding statement must be terminated with a semicolon (;),
the Transact-SQL statement terminator."
Now it's working.
About poisonous messages I have been doing some testing on that by changing
the datatypes in my auditing table, so that the insert fails. The queue then
gets deactivated as you write.
Now it's not so likely that the insert fails, since I have matching
datatypes. I mostly would like to have a transaction in case of a server
crash.
I have ordered the Roger Walters book today.
Thanks for you help.
/Ola
"Tibor Karaszi" wrote:

> In what way doesn't it work? Error messages? Something else?
>
> Yes, but you want to be careful. Say you determine that you can't process this message so you
> rollback. Since messages are received in order, you will just read the same message next time.
> Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
> in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
> topic is handled in chapter 8.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
>

RECEIVE in transactions

Hello!
I have an an event notifications audit solution that I'm working on. I am
using this activation procedure.
CREATE PROCEDURE dbo.AuditReceive
AS
DECLARE @.AuditMessage TABLE (EventData xml);
RECEIVE CAST(message_body AS XML)
FROM AuditQueue
INTO @.AuditMessage
INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
CAST(EventData.query('data(//EventType)') AS sysname),
CAST(EventData.query('data(//LoginName)') AS sysname),
CAST(EventData.query('data(//UserName)') AS sysname),
CAST(EventData.query('data(//ServerName)') AS sysname),
CAST(EventData.query('data(//DatabaseName)') AS sysname),
CAST(EventData.query('data(//ObjectType)') AS sysname),
CAST(EventData.query('data(//SchemaName)') AS sysname),
CAST(EventData.query('data(//ObjectName)') AS sysname),
EventData
FROM @.AuditMessage
It's working nicely, but there is one weakness. I would like to have the
RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
not possible to use RECEIVE in transactions?
Best regards
Ola Hallengrenthe books on line tell:
"The RECEIVE statement removes received messages from the queue unless the
queue specifies message retention. When the RETENTION setting for the queue
is ON, the RECEIVE statement updates the status column to 1 and leaves the
messages in the queue. When a transaction that contains a RECEIVE statement
rolls back, all changes to the queue within the transaction are also rolled
back, returning messages to the queue."
Could you expose the source code of the transaction you tried? there's
something wrong but i cannot imagine what without seeing an example.
Gilberto Zampatti
"Ola Hallengren" wrote:
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||> I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working.
In what way doesn't it work? Error messages? Something else?
> Is it
> not possible to use RECEIVE in transactions?
Yes, but you want to be careful. Say you determine that you can't process this message so you
rollback. Since messages are received in order, you will just read the same message next time.
Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
topic is handled in chapter 8.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||I overlooked one comment in Books Online"
"If the RECEIVE statement is not the first statement in a batch or stored
procedure, the preceding statement must be terminated with a semicolon (;),
the Transact-SQL statement terminator."
Now it's working.
About poisonous messages I have been doing some testing on that by changing
the datatypes in my auditing table, so that the insert fails. The queue then
gets deactivated as you write.
Now it's not so likely that the insert fails, since I have matching
datatypes. I mostly would like to have a transaction in case of a server
crash.
I have ordered the Roger Walters book today.
Thanks for you help.
/Ola
"Tibor Karaszi" wrote:
> > I would like to have the
> > RECEIVE and the INSERT as a transaction, but I can't get it working.
> In what way doesn't it work? Error messages? Something else?
>
> > Is it
> > not possible to use RECEIVE in transactions?
> Yes, but you want to be careful. Say you determine that you can't process this message so you
> rollback. Since messages are received in order, you will just read the same message next time.
> Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
> in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
> topic is handled in chapter 8.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> > Hello!
> >
> > I have an an event notifications audit solution that I'm working on. I am
> > using this activation procedure.
> >
> > CREATE PROCEDURE dbo.AuditReceive
> > AS
> > DECLARE @.AuditMessage TABLE (EventData xml);
> >
> > RECEIVE CAST(message_body AS XML)
> > FROM AuditQueue
> > INTO @.AuditMessage
> >
> > INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> > DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> > SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> > CAST(EventData.query('data(//EventType)') AS sysname),
> > CAST(EventData.query('data(//LoginName)') AS sysname),
> > CAST(EventData.query('data(//UserName)') AS sysname),
> > CAST(EventData.query('data(//ServerName)') AS sysname),
> > CAST(EventData.query('data(//DatabaseName)') AS sysname),
> > CAST(EventData.query('data(//ObjectType)') AS sysname),
> > CAST(EventData.query('data(//SchemaName)') AS sysname),
> > CAST(EventData.query('data(//ObjectName)') AS sysname),
> > EventData
> > FROM @.AuditMessage
> >
> > It's working nicely, but there is one weakness. I would like to have the
> > RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> > not possible to use RECEIVE in transactions?
> >
> > Best regards
> >
> > Ola Hallengren
>sql

Friday, March 30, 2012

recieve error in vdi.log when backup SQL 2000 Server DB

Receiving this error since migrating to SQL 2000 from SQL 7

Receiving this error since migrating to SQL 2000 from SQL 7

REceiving notification in windows application

RECEIVING messages from a Windows service

RECEIVING messages from a Windows service

RECEIVING from QUEUE by ConversationHandle

Receiving Export file name

Wednesday, March 28, 2012

Receiving a message with a specific contract

RECEIVE vs RECEIVE TOP(1)

receive top 20

Receive notification on several clients at the same time

receive notification from server in application

receive notification from server in application

Receive multiple FTP files

Receive messages from queue based on message_type_name

Receive mail when specific user connects

Receive java.io.InputStream frm PL/SQL

RECEIVE in transactions

RECEIVE in transactions

Real Time DSS

Blog Archive

About Me