Can't Receive Message From Queue (Async Trigger)

Sep 1, 2006

Hi Folks,

I've found a pretty good code example on http://www.dotnetfun.com for a Asynchronous Trigger.

I've parsed through the Code, to understand how to wirte my own Async Trigger with a Service Broker, but the Code isn't working! It seems that the stored procedure don't receive the messages in the queue, but the queue get's filled.

MessageType


 CREATE MESSAGE TYPE  myMsgXML
 VALIDATION = WELL_FORMED_XML;
Contract

CREATE CONTRACT myContractANY
 (myMsgXML SENT BY ANY)
Queue

CREATE QUEUE myQueue
 WITH STATUS = ON,
 RETENTION = ON,
 ACTIVATION
 (
  STATUS = ON,
  PROCEDURE_NAME = sp_myServiceProgram,
  MAX_QUEUE_READERS = 5,
  EXECUTE AS SELF
 )
Service

CREATE SERVICE myService ON QUEUE myQueue  (myContractANY)
Procedure (greped from http://www.dotnetfun.com/)

CREATE PROC sp_myServiceProgram
AS
-- This procedure will get triggered automatically
-- when a message arrives at the
-- Let's retrieve any messages sent to us here:
DECLARE @XML XML,
  @MessageBody VARBINARY(MAX),
  @MessageTypeName SYSNAME,
  @ID INT,
  @COL2 VARCHAR(MAX);
DECLARE @Queue TABLE (
  MessageBody VARBINARY(MAX),
  MessageTypeName SYSNAME);
WHILE (1 = 1)
BEGIN
 WAITFOR (
  RECEIVE message_body, message_type_name
  FROM myQueue  INTO @Queue
 ), TIMEOUT 5000;
 -- If no messages exist, then break out of the loop:
 IF NOT EXISTS(SELECT * FROM @Queue) BREAK;
 DECLARE c_Test CURSOR FAST_FORWARD
  FOR SELECT * FROM @Queue;
 OPEN c_Test;
 FETCH NEXT FROM c_Test
  INTO @MessageBody, @MessageTypeName;
 WHILE @@FETCH_STATUS = 0
 BEGIN
  -- Let's only deal with messages of Message Type
  -- myMsgXML:
  IF @MessageTypeName = 'myMsgXML'
  BEGIN
   SET @XML = CAST(@MessageBody AS XML);
   -- Now let's save the XML records into the
   -- historical table:
   INSERT INTO tblDotNetFunTriggerTestHistory
    SELECT tbl.rows.value('@ID', 'INT') AS ID,
     tbl.rows.value('@COL2', 'VARCHAR(MAX)') AS COL2,
     GETDATE() AS UPDATED
    FROM @XML.nodes('/inserted') tbl(rows);
  END
  FETCH NEXT FROM c_Test
   INTO @MessageBody, @MessageTypeName;
 END
 CLOSE c_Test;
 DEALLOCATE c_Test;
 -- Purge the temporary in-proc table:
 DELETE FROM @Queue;
END
Send Message in a Update Trigger

SELECT @XML = (SELECT * FROM inserted FOR XML AUTO);
  -- Send the XML records to the Service Broker queue:
  DECLARE @DialogHandle UNIQUEIDENTIFIER,
   @ConversationID UNIQUEIDENTIFIER;
  /*
   The target Service Broker service is the same
   service as the initiating service; however, you
   can set up this type of trigger to send messages
   to a remote server or another database.
  */
  BEGIN DIALOG CONVERSATION @DialogHandle
   FROM SERVICE myService
   TO SERVICE 'myService'
   ON CONTRACT myContractANY;
  SEND ON CONVERSATION @DialogHandle
   MESSAGE TYPE myMsgXML
   (@XML);
  -- Let's detect an error state for this dialog
  -- and rollback the entire transaction if one is
  -- detected:
  IF EXISTS(SELECT * FROM sys.conversation_endpoints
   WHERE conversation_handle = @DialogHandle
   AND state = 'ER')
   RAISERROR('Dialog in error state.', 18, 127);
  ELSE
  BEGIN
   --I want to list the queue after the trigger so I disabled
   --END CONVERSATION @DialogHandle;
   COMMIT TRAN;
  END
The Problem is, that the Procedure doesn't even get started! So I tried to receive the Queues manually

WAITFOR (
  RECEIVE message_body, message_type_name
  FROM myQueue  INTO @Queue
 ), TIMEOUT 5000;

and I run always into the timeout and get nothing back. A Select * FROM myQueue gives me some results back. Why I can't recevie?
Would be grateful for help, or at least a good tutorial, I haven't found one yet....
thx and greez
     Karsten

View 1 Replies


ADVERTISEMENT

Dynamic Queue Receive Sql ?

Sep 27, 2006

Hi There

My activation sp must be able to read of various queues.

I load a variable with the queue name that activated the sp btu i cannot get the syntax working to receive or get a conversation group of a queue name that is a variable.

I have tried:

WAITFOR

(

RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM @callingQueue INTO @msgTable WHERE conversation_group_id = @conversationGroup

), TIMEOUT 2000;

But i get this error:

Incorrect syntax near '@callingQueue'.

Looks like you cannot use a variable.

So i tried the following:

SELECT @SQL = N' WAITFOR

(

RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM @callingQueue INTO @msgTable WHERE conversation_group_id = @conversationGroup

), TIMEOUT 2000'

EXEC sp_executesql @SQL, N'@msgTable table output'

But i get the same error.

How do i receive of a queue using a vriable holding the queue name ?

Thanx

View 13 Replies View Related

Receive All Messages On Queue

Feb 12, 2007

Hi i am trying to create a batch process then commit for all messages on the queue. The problem i am having is when i run my query (As below) I only receive the first message and the corresponding end dialog for the message although i have 2000 records sitting in the queue. It is my understanding that receive without any criteria i.e top(1) or where clause should select everything of the queue. I tried receive top(100) expecting 100 records but still only got 2 back.

any help appreciated.



WAITFOR(RECEIVE

queuing_order,

conversation_handle,

message_type_name,

message_body

FROM [RMIS_COMMS_Queue]

INTO @tableMessages), TIMEOUT 2000;



View 4 Replies View Related

Receive Messages From Queue Based On Message_type_name

Dec 15, 2006

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

View 1 Replies View Related

SQL 2012 :: Queue Activation Is Enabled But No Receive Executed

Sep 14, 2015

Our Sql server is not responding, So we restarted the server and modified one of the sp code. After that we are getting frequently every 2 min giving the below error

The queue 855365233 in database 9 has activation enabled and contains unlocked messages but no RECEIVE has been executed for 453199 seconds

View 0 Replies View Related

Unable To Pass Queue Name In A Parameter For The FROM Clause Of RECEIVE Statement

Mar 19, 2008

I'm writing some generalized stored procedures for use form managed code. The following stored proc works great:

CREATE PROCEDURE fnd_Send (
@Message VARCHAR(5000),
@Contract SYSNAME,
@MessageType SYSNAME,
@FromService SYSNAME,
@ToService VARCHAR(100),
@Encrypted Bit
)
AS
BEGIN
DECLARE @Handle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE @FromService
TO SERVICE @ToService
ON CONTRACT @Contract
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @Handle
MESSAGE TYPE @MessageType(@Message);

Notice the use of SYSNAME parameters €“ translating the parameter into an object name - allows it to work in the BEGIN DIALOG statement when an system object is needed. Works create and cues up generic messages.

The following will not compile:

CREATE PROCEDURE fnd_Receive (
@Queue SYSNAME,
@Message VARCHAR(5000) out,
@MessageType SYSNAME out
)
AS
BEGIN
DECLARE @Handle UNIQUEIDENTIFIER;

RECEIVE TOP (1)
@Handle = conversation_handle,
@MessageType = message_type_name,
@Message = message_body
FROM @Queue;
END
GO

It won€™t let me use the SYSNAME field @Queue in the FROM clause. I€™m getting an unfriendly:

Msg 102, Level 15, State 1, Procedure fnd_Receive, Line 15
Incorrect syntax near '@Queue'.

It looks like maybe I can€™t bind a variable in the FROM clause €“ perhaps like you cannot in a SELECT statement. I can fall back to dynamic SQL in the stored proc or in the managed code; I was hoping not to.


Can anyone shed any light on this? Also, are there any other techniques I'm missing to avoid falling back to a dynamic SQL statement.

Thank you in advance -

Jeff Odell
Catapult Systems

View 3 Replies View Related

SEND/RECEIVE And Message Order

May 12, 2007

I understand that SQL Service Broker will RECEIVE messages in the same order of SEND, so long as the messages are on the same conversation.



I would like to accomplish queue-level ordering instead of (or in addition to) conversation-level ordering.



There is a significant business case for this level of ordering. Consider an order processing system which is specified to fulfill orders in the sequence they are received. The reason for the ordering is as follows. Suppose the process(es) that RECEIVEs from the queue is down for several hours and the messages back up in the queue. Various customers place orders throughout this period of time. If more orders are placed than there are quantity for an item, the customers who placed their orders earliest in the day ought to be the ones that receive the merchandise and the later orders should be placed on backorder.



Limited experimentation showed that SQL Server totally disregards the order in which the messages were sent (on different conversations to the same queue).



The potential solution to use the same conversation has some drawbacks:

1. Difficult to do error handling because of the way error handling works in conversations.

2. It is not possible to RECEIVE using multiple threads. (Yes, RECEIVEing on multiple threads also would reorder the messages, but the reorderings would be localized in time so this would be tolerable by a lot of applications. In other words, orders from 1:00 AM would not be mixed with orders from 9:00 AM.)



Can anyone see a good solution to this in the current version?



If there are no good solutions, it would be great if Microsoft considers adding a feature where you can declare an ordering requirement when you CREATE QUEUE. Even support for an "approximate" ordering (messages can rearrange +/- several seconds) would be helpful, but the current way which seems to totally randomize the message order is not good for certain kinds of significant applications.

View 4 Replies View Related

Message Queue Task

Apr 18, 2006

Ok, im making some progress. So what i have is a Message Queue Task which is bound to a message queue connection manager (which 'tests' ok). The Message Queue Task is set to recieve, variable from string message (declared a variable of type string) and to remove the message from the queue. The output of that task is piped into the data flow task.
The data flow task expands into a XML Source which is configured to get its input from the string i declared in the Message Queue Task and i point the schemas path to an appropriate schema. I then pipe the output of that into a SQL server destination which ive mapped all the columns from the XML message to a table (which the SQL server destination created for me).

It all looks good on paper, and builds properly with no errors etc. There is already a message in the appropriate private queue. When i go to debug it, it just sits on the Message Queue Task node (its yellow) and goes no further. No data is put into the DB. I have put a watcher on the link between the XML Source and the SQL server destination, and can see no data being piped through.
Even if i send another message, the execution of my package doesnt step passed the Message Queue Task. Its just sitting there waiting for something? what? I thought it would block until there was a message on that queue, and then process it if and when it arrives. But it doesnt seem to do that.

Any ideas??

I read on MSDN that you need integration services installed. I have checked and i do, and its running. Is theres something else i need to configure?

Help!

View 15 Replies View Related

Message Queue Task

Aug 2, 2007

Simple Question.

I have a requirement to read XML Messages from a Remote private MSMQ.
These messages are essentially Database records that will need cleaning up and insterting into a Local Database table.

Is this possible with SSIS?
Would Biztalk be a more suitable tool for this type of process?

If its possible, are there any resources taht can point me in the right direction?

Thanks for your help!
J.

View 1 Replies View Related

Using Message Queue Task And Serialization

Mar 7, 2007

I have a C# application that get data (i.e. select Firstname, LastName from person) to form a DataSet object (i.e. PersonName variable inside my code). Then I want to post this DataSet object into a local private queue (the path is: .privatemyTestQ). Note that I carefully labled the message to be "Variables Message" (as needed per anothre thread discussion here in this forum).

I created a receiving package, in which I want to use SSIS's Message Queue task to retreive the above DataSet object (from C# application). I got failure:

[Message Queue Task] Error: An error occurred with the following error message: "Root element is missing.".

However, As a comparison research, I created another SSIS sending-package. And I used ADO.NET provider to get the same data to store the sull result set in a package-level variable. Then I use Message Queue task to post this variable (i.e. object) to the same private queue above. Then I run my above receiving package. I was successful to read back the messge that I posted from SSIS sending package. (Please note that if I use OLEDB provider for sending package to get data from database, the MSMQ task for sending failed due to serializtion issue for __ComObject. With ADO.NET provider, the result set is represented as a type of DataSet).



I then curiously looked into message body from Computer Management Counsol. I found that message sent from SSIS is in SOAP format while message from my C# application is NOT in SOAP (but only in XML format). Obviously SSIS MSMQ task serialize objects into SOAP format.



Can anyone here please help on how to serialize my DataSet object from my C# app) in compliance with the MSMQ task's spec so that I can read message from Q using SSIS package.



I use Visual Studio 2005 and MSMQ 3.0 version.

Your help is appreciated.

View 2 Replies View Related

Modify Message Order In Queue?

Jun 12, 2007

Is it possible to modify the sequence of messages in a SSB queue? Or to create messages so that their sequence is based on a value in the message rather than the order in which they were sent to the queue?



I am trying to determine if SSB will help me solve a situation where BizTalk needs to process a prescribed sequence of messages that may not be received in the correct order. (i.e. I need to resequence the messages). Each message contains a field with the sequence number and also a field identifying the total number of messages in the sequence.



I realise that the Sequential Convoy Aggregrator EAI pattern can potentially provide a solution to this, or even the Sequence Guards pattern by McGeeky, but I guess I was hoping that SSB might provide a slightly more efficient solution(?)



Thanks,

Dan

View 2 Replies View Related

How To Log That A Message Has Been Retained In The Transmission_queue When The Queue Is Disabled.

Jun 16, 2007

Hi was wondering whether it is possible to log somewhere outside SB that there are messages in the transmission_queue because the Target queue was disabled.



I was testing this scenario:

try to send messages on a disabled queue and log the problem.



But the transmission_status from the trasmission_queue is always empty.



This is the code that I tried to execute between the send and the commit and after the commit:




WHILE (1=1)

BEGIN

BEGIN DIALOG CONVERSATION .....


SEND ON CONVERSATION ......


if select count(*) from sys.transmission_queue <> 0
BEGIN

set @transmission_status = (select transmission_status from sys.transmission_queue where conversation_handle=@dialog_handle);

if @transmission_status = ''

--Successful send - Exit the LOOP

BEGIN

UPDATE Mytable set isReceivedSuccessfully = 1 where ID = @IDMessageXML;

BREAK;

END

ELSE

raiserror(@transmission_status,1,1) with log;

END

ELSE

BEGIN

UPDATE [dbo].[tblDumpMsg] set isReceivedSuccessfully = 1 where ID = @IDMessageXML;

BREAK;

END

END

COMMIT TRANSACTION;

As I wrote before the @transmission_status variable is always empty and I have the same result even if I put the code after the commit transaction!



Maybe what I'm trying to reach has no sense?


With the event notification I can notify when the queue is disable because the receive rollsback 5 times but what if by mistake the target queue is disabled outside the SB environment? I can I catch it and handle it properly?

Thank you!Marina B

View 3 Replies View Related

How To Use Message Queue Task In Integration Services

Jan 17, 2007

How To Use Message Queue Task In Integration Services

View 1 Replies View Related

How To Determine Date/time Message Was Placed Onto Queue?

Oct 2, 2006

I need to determine the actual date/time that a message was placed on the queue. In my "activated" procedure I want to log this information and pass it along to further processing routines. From what I can tell, the Queue table itself does not have this information captured.

View 4 Replies View Related

Junk Characters In Message Queue Task

Mar 12, 2008

I have a strange situation in an Message queue task in SSIS.
I serialize an object in a C# application and add that to an MSMQ as a string. I also ensure that I set the label to "String Message" so that my Message Queue Task can actually receive the message as a String message to variable.

I created an SSIS package that has an Message Queue listener that feeds into a Script task inside a for-each loop.
For each message that I obtain, I invoke a script task that retrieves the value of the variable and then processes this information.

When I enter the entry into the MSMQ, it goes in perfectly fine (since I also tested retrieving this entry from a C# app). However when I use the same logic on the SSIS package using the Script task, I get junk chinese characters.

Has this happened to anyone else?
Any feedback would be great!

Anup

Here is the code for the script task:
mports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()
Dim statusMessage As String
statusMessage = CType(ReadVariable("ReviewerHealthXmlMessage"), String)
System.Windows.Forms.MessageBox.Show(statusMessage)
Dts.TaskResult = Dts.Results.Success

End Sub

Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object

Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try

Return result
End Function

Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub

End Class

View 1 Replies View Related

Implementing Message Queue In SQL Server 2000

Jun 2, 2006

I am implementing a message queue system in SQL Server 2000. My queue table looks something like this:

[MessageId] [uniqueidentifier] NOT NULL,
[MessageType] [uniqueidentifier] NOT NULL,
[Status] [tinyint] NOT NULL,
[SubmittedTime] [datetime] NOT NULL,
[StartTime] [datetime] NOT NULL,
[DispatchedTime] [datetime] NULL,
[CompletedTime] [datetime] NULL,
[MessageData] [image] NULL

This is how I retrieve the next message for processing:

SELECT TOP 1 *
FROM [Queue].[MessageQueue] WITH (ROWLOCK, UPDLOCK, READPAST)
WHERE [StartTime]=@pStartTime AND [MessageType]=@pMessageType AND [Status]=@pStatus
ORDER BY [StartTime]

and mark it as being processed:

UPDATE [Queue].[MessageQueue] SET [Status]=1 WHERE [MessageId]=@pMessageId

After message has been processed I delete it from the queue:

DELETE FROM [Queue].[MessageQueue] WHERE [MessageId]=@pMessageId

All database accesses are transactional with default READ COMMITTED. The problems start when there are a few concurrent accesses: I get deadlocks when retrieving next message. If I do not delete message after processing then there is no deadlock. But this is not what I need.

I played with different isolation levels and locking hints and was able to avoid deadlock using TABLOCKX:

SELECT .... FROM [Queue].[MessageQueue] WITH (TABLOCKX)

But in this case you cannot concurrently retrieve messages which was my goal from the beginning. How do I achieve this?

Thank you,
Alex

View 18 Replies View Related

Message Queue Task 64 Bit Cluster Issue

Feb 2, 2006

Hello,

I'm using the Message Queuing task to create a local private queue message. Everything works great on a 32 bit machine. When I try this on a 64 bit Itanium Cluster I keep getting the message "Message queue service is not available" in my SSIS log. I've using this string as my path "ClusterNameprivate$QueueName". Does anyone know of any issues with the Message Queue task on 64 bit or a cluster? The Message Queue service is up and running, it doesn't make sense.

Thanks,

Andy

View 1 Replies View Related

Message Queue Task - Invalid Path Problem

Nov 17, 2007

I'm trying to use the message queue task in SSIS 2005 and not getting very far. Right off the bat I'm trying to create a Message Queue Connection Manager and when I enter the path, trying both "seawxxxx estq" (my local computer name) or ". estq" and test it, it comes back with "invalid path". Have done quite a bit of searching around and can't find anything on this particular error.

Suggestions? Thanks in advance.

View 5 Replies View Related

Problem With Distributed Transactions - Multiple Threads Pop The Same Message From Queue

Aug 14, 2007

Hi,

I am using distributed transactions where in I start a TransactionScope in BLL and receive data from service broker queue in DAL, perform various actions in BLL and DAL and if everything is ok call TransactionScope.Commit().

I have a problem where in if i run multiple instances of the same app ( each app creates one thread ), the threads pop out the same message and I get a deadlock upon commit.

My dequeue SP is as follows:

CREATE PROC [dbo].[queue_dequeue]
@entryId int OUTPUT
AS
BEGIN
DECLARE @conversationHandle UNIQUEIDENTIFIER;
DECLARE @messageTypeName SYSNAME;
DECLARE @conversationGroupId UNIQUEIDENTIFIER;

GET CONVERSATION GROUP @conversationGroupId FROM ProcessingQueue;
if (@conversationGroupId is not null)
BEGIN
RECEIVE TOP(1) @entryId = CONVERT(INT, [message_body]), @conversationHandle = [conversation_handle], @messageTypeName = [message_type_name] FROM ProcessingQueue WHERE conversation_group_id=@conversationGroupId
END

if @messageTypeName in
(
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
)
begin
end conversation @conversationHandle;
end
END

Can anyone explain to me why the threads are able to pop the same message ? I thought service broker made sure this cannot happen?

View 11 Replies View Related

Target Queue Disabled: Strange Message On The Event Viewer

Jun 15, 2007

Hello,

I have almost finished to design my Service Broker application and I found out something strange.



I was tring to handle the Target queue disabled scenario, because I want to save to message that was not sent so I can create an alert to the user to say that some messages as not been sent.



To disable to queue I run:

Alter queue [dbo].[Receivedqueue] with status = off



I had the Event Viewer in front of me and I have seen the suddenly it appeared a lot of this informational events:

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 9724
Date: 15/06/2007
Time: 15:00:47

Description:
The activated proc [dbo].[OnReceived] running on queue TestReceiver.dbo.Receivedqueue output the following: 'The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.'

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.



What this message means?

I would like to avoid to have it because it appears a lot of time and I don't want to fill up my Event Viewer!!



I don't know of it is important but in the moment I executed the code there was not CONVERSING conversation and all the queues were empty.



Somebody has seen this error before or I have to send to the forum the [dbo].[OnReceived] code as well?

thankx



Marina B.

View 3 Replies View Related

Message Queue Task Error: The ServicedComponent Being Invoked Is Not Correctly Configured (Use Regsvcs To Re-register).

Jan 15, 2008



This is the first time I've used SQL Server Integration Services, and I have it installed with SP2 applied on my local machine. I have created a Package, and dropped a Message Queue Task on it. I have pointed the Message Queue Connection Manager at a private queue on my machine. I have set the "Message" property to "Send message", I have changed the message type to "String Message" and entered "Test string" into the StringMessage property. When I execute the package (by clicking the "Start debugging" button on the toolbar), I get the following error message in the Execution Results:


[Message Queue Task] Error: An error occurred with the following error message: "The ServicedComponent being invoked is not correctly configured (Use regsvcs to re-register).".

There is no more diagnostic data that I can see in the Execution Results. Can anyone please explain to me how I can get this working?

Thanks!
Bryan

View 12 Replies View Related

Clarifications On Queue Service And Queue Readers

Jan 11, 2006

Hello,
This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:

A.
When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back?
1. Message is left on the Queue.
2. is the worker procedure triggered again for the same message by the queue?
3. I am hoping the Queue keeps on triggering workers until it is empty.

My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.

B.
For my scenario messages are independent and ordering does not matter.
Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.

Thank you very much for the time,

Lubomir

View 5 Replies View Related

Service Broker: Trigger On New Message

Jul 13, 2007

Hi,



I have a small problem with my two databases ( A and B ).



On database A I have a queue set up for receiving messages from a service broker which are sent via a stored procedure from database B ...



Each time a message hits the queue on database A I would like to run a stored procedure that takes the message and actions it.





I have my stored procedures in place but can't figure out how to trigger a procedure each time a message is received. I have read this ( http://technet.microsoft.com/en-us/library/ms171601.aspx ) but would really appreciate someone posting an example of setting up queue activation.



Many thanks



Chris

View 1 Replies View Related

Trigger :: Xp_smtp_sendmail :: Parameter Value In Message/subject :: Magic Table

Oct 23, 2007

OK so now I'm setting up a trigger to email info to different people based on values INSERTed into one of my tables. I'm using an IF statement to determine who I'm giong to send the mail to, but I don't really understand how to include the data (parameters) in my message. Thanks for any help. Here's what I have so far.





Code Block
ALTER TRIGGER [smallin].[trig_test]
ON [smallin].[DATALIST]
AFTER INSERT
AS
declare @rc int,
@post bit,
@pre bit
SELECT @post = [POST], @pre = [PRE] FROM inserted
IF @post = 1
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'ln.li@mydomain.com',
@FROM_NAME = N'Alert Mailer',
@TO = N's.mallin@mydomain.com',
@replyto = N'ln.li@mydomain.com',
@CC = N'',
@BCC = N'',
@priority = N'NORMAL',
@subject = N'This is post data',
@message = N'Goodbye MAPI and Outlook',
@messagefile = N'',
@type = N'text/plain',
@attachment = N'',
@attachments = N'',
@codepage = 0,
@server = N'exchange.mydomain.com'

ELSE IF @pre = 1
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'ln.li@mydomain.com',
@FROM_NAME = N'Alert Mailer',
@TO = N's.mallin@mydomain.com',
@replyto = N'ln.li@mydomain.com',
@CC = N'',
@BCC = N'',
@priority = N'NORMAL',
@subject = N'This is pre data',
@message = N'Goodbye MAPI and Outlook',
@messagefile = N'',
@type = N'text/plain',
@attachment = N'',
@attachments = N'',
@codepage = 0,
@server = N'exchange.mydomain.com'





View 5 Replies View Related

Max Async Io

Jan 25, 2001

In SQL Server 7.0 there was option that allows you to set 'MAX async io', but it no longer available in SQL 2000.

Does anyone know why this option is not available or did Microsoft replace this function with something else?

Thank You,
John

View 1 Replies View Related

Max Async Io

Jan 25, 2001

In SQL Server 7.0 there was option that allows you to set 'MAX async io', but it no longer available in SQL 2000.

Does anyone know why this option is not available or did Microsoft replace this function with something else?

Thank You,
John

View 1 Replies View Related

CLR Trigger -&&> Send Insert/Update/Delete Message To Windows Service

Feb 1, 2007

Hi,

I have an c# windows service, which is running on the same machine where my mssql server is installed. This service must be informed for each insert/update/delete event on one specific table.

My idea was to create an CLR Trigger for this table which can communicate with this service via .NET remoting. But the problem is, that the system.runtime.remoting assembly is not integrated within the mssql clr enviroment => i can't use remoting.

Are there any other idea's how can i solve this?

Best regards,
Thomas

View 2 Replies View Related

Async Processes - Help !!

Feb 10, 2000

SQL7 SP1 NT4 SP5

Hi.

Very long story and I will not bore you with it. What I need to do is call a stored procedure and from within that stored procedure, initiate other sp and get out before those other sp are finished running.

I know I can execute a job but my volume would be about 1 job per second with a life of 2-5 seconds. So in a very short period of time, I can stack up a large volume of jobs.

Anyone have any better ideas ?

Help Please !@!!

Craig Somberg
csomberg@stageone.com

View 2 Replies View Related

In Trouble With Async Replication..

Nov 21, 2006

Hello Everybody,

In the past few days I try to work with SQL Mobil and Replication.

And now I have a big problem.

When the replication ist running in my little application the pda-user wants not stop there working. So I try to implement some routines of code that I find in this onlinearticle: http://msdn2.microsoft.com/en-us/library/2ysxae29.aspx

The codes workes fine as long I don't start another SQL Task. When I start either (Select, Insert, Update or Delete) Statement the Database crash with errormessage: "The database file may be corrupted. Run the repair utility...



Can anyone give me a tip what I must do to fix this problem.



Many Thanks

Markus



View 1 Replies View Related

Async Database Access On Single SqlConnection

Mar 14, 2008

Hi,

I'm currently writing an windows application where a LOT of threads connect to database and send small amount of data.
I'd like to ask you for the best approach to do it.

The way it's implemented now:

Each therad uses it's own SqlConnection sends few bytes of data and closes the SqlConnection. Each thread has to send initliazation data which i guess is few times bigger then accual data and then it has to close connestion even more bytes that could be avoided are sent.

The way i'd like it to be:

There is only one SqlConnection all therads share it. It's opened when the applications starts and closed when it's application is closed. This way i send initialization data only once and by doing this i can save some bandwidth.

Does SqlConnection operations has to be in critical area? Can it be used pararelly by many threads? Maby there is better way to do it? Is it recomended to close SqlConnection as soon as possible after query? If so why?

Thanks in advance,
Erxar

View 1 Replies View Related

SQL 2012 :: AlwaysOn Async Replica Back To Sync

Aug 19, 2014

After failing over to the DR replica. All databases are out of sync. DR replicas were setup as async the other 2 are set up as sync. Is this by design. No data has been updated to any of these as they are test dbs. So all dbs should be the same, no data loss.

View 0 Replies View Related

DTS/Async Stored Procedure/Import Huge Data

Jul 20, 2005

I have a table which contains approx 3,00,000 records. I need toimport this data into another table by executing a stored procedure.This stored procedure accepts the values from the table as params. Mycurrent solution is reading the table in cursor and executing thestored procedure. This takes tooooooo long. approx 5-6 hrs. I need tomake it better.Can anyone help ?Samir

View 2 Replies View Related

SQL Server Admin 2014 :: AG Failover To Async Node - Replication

Sep 29, 2015

if for any reason AG fails over to async node, how replication behaves? As data will not be in sync with previous primary replica, how replication will work? I think that we have to reset replication from scratch as there's a high chance subscribers might be more updated than current primary replica as failover to this node causes data loss. How to keep replication in sync without resetting up? Can we achieve this?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved