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


ADVERTISEMENT

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

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 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

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

Getting An Email (Exchange 2000) Message Into SQL Server 2000

May 9, 2002

I have a public mailbox that gets information mailed to it (in a pre-determined format).

Is there a way for that info to be put into a table in SQL Server without any user interaction (something running on the exchange server)?

I hope I've given enough info.

Thanks for any and all help!

Ron

View 1 Replies View Related

Sql Server 2000 Error Message

Jun 9, 2004

Hi All,
I'm running peoplesoft 8.8 on MS-SQL 2000 SP4. When I try to access my
peoplesoft application through web and add access some pages, I get some error like "SQL error. Stmt #: 651 Error
Position: 0 Return: 8601 - [Microsoft][ODBC SQL Server Driver][SQL Server]
fetch: The fetch type refresh not allowed with forward only cursors (SQLSTATE
37000) 16911
"
I need to solve this issue at the earliest.
Thanks in Advance,
Aniruddha.

View 1 Replies View Related

Connecting To SQL 2000 Server Gives SQL 2005 Error Message

Aug 11, 2007

Hi,
Here is the syntax of the connection string within the web.config file:<connectionStrings><clear />
<add name="MyConnectionString" connectionString="Data Source=000.000.000;Initial Catalog=Database Name;Persist Security Info=True;User ID=XXXXXX;Password=XXXXXX;" providerName="System.Data.SqlClient" />
</connectionStrings> 
The test page has a GridView in it which uses the connectionstring fron the web.config file.
Error Message:

View 4 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

Implementing Standby SQL Server

Feb 15, 2000

Can I implement the Standby SQL Server using the SQL Server Standard Edition? What's actually the difference between Standard and Enterprise Edition? As I know the Enterprise edition can be installed for up to 32 CPU while Standard edition can only be installed for up to 4 CPU.

Many Thanks in advance!

View 1 Replies View Related

Implementing DB Security In Existing Server

Mar 25, 2008

Dear Colleagues,

How do I install a Microsoft SQL Server 2005 database application in an existing server in an existing database server and still have the control over it and also restrict the new server Admin user from editing or even opening my DB. Any modification of my database or code should be implemented only by me. Is it possible to remove the Builtin Admin from the server role?? As in my case, there is no need for anyone else to open the DB in Management Studio at all as my VB application does all that is required.

Thanks and best regards,

Peter

View 3 Replies View Related

DB Design :: Implementing Hierarchies In Server

Jun 12, 2015

I want to be able to implement infinite levels of Hierarchies in SQL Server (2012), in addition to being able to address issues like same child having more than one parent (eg. An Employee could end up having 2 different managers - eg. Project Manager,  Delivery Manager).

One way is to have self referencing table (where each row has a parent id , referencing to a parent record - but this would not work in cases where a child  has more than 1 parent).

Are there other more efficient ways? What is the best way to implement this?

View 9 Replies View Related

Need Help Implementing Weka Into SQL Server 2005

Jul 29, 2007

Hi!

I'm a student of computer science, and for one of my projects I would need Weka plug-in for SQL Server 2005. I don't know much about plug-ins, so if I'm asking stupid questions, I hope you will forgive me. It was easy to get a library from weka that I can use in Visual Studio, but I just can't figure out, how to get Weka data mining algorithms into plug-ins for MSSQL 2005.

I would appreciate any help given...

AzDHeX

View 8 Replies View Related

Implementing Membership And Role Management On SQL Server 2005?

Feb 22, 2006

How can I implementing Membership and Role Management on SQL Server 2005 (not Express)? Anyone have any documentation? Are there any scripts to run that will basically set up the same Database schema on SQL Server 2005 that Express uses?
Thanks.

View 3 Replies View Related

Implementing A Lookup Field In SQL Server 2005 Via VWD Express

Feb 27, 2006

Hello Folks
I am a total new boy to Visual Web Developer and am struggling to implement a lookup field in the SQL Server Express that ships with it.
I am trying to find the equivelent of using the lookup wizard in MS Access. So in Access I can select the lookup wizard from the drop down list of datatypes and refer to another table of standing data. Once configured the tables contents appear as a drop down list in the field of the master table.
So how do I create a table in SQL Server 2005 that uses another table to give a drop down list in one of its fields?
I have found some excellent tutorials and books but none of them seem to drill down to this level of table design, can anyone recommend any books that do?
Thanks
Bradley

View 8 Replies View Related

Implementing Sql Server Express Within A Strict Domain Environment...

Nov 5, 2006

I am looking for some assistance from the grand knowledgebase out there concerning the implementation of Sql Server Express 2005 on a client's strict domain environment.

I am designing and implementing a pos software that resides on registers and a server within a number of stores. The registers are running WePos and the server is running Server 2003.  I run an instance of sql server express on all devices. The registers read the server when it can see it but when it cannot it reads the local instance. I am seeing a number of performance issues and I am trying to tweek the installation and coding of SSE on all devices.

Any words of wisdom for me out there???

Thank you,

Sir Robert

 

View 3 Replies View Related

Microsoft Best Practices For Implementing Windows Authentication For Sql Server 2005

Nov 10, 2005

Microsoft recommends using Windows authentication instead of SQL Server authentication in SQL Server 2005 for improved security. What are the Microsoft best practices for implementing this? Will be helpful if someone also provides some links that talks about this.... 

View 5 Replies View Related

Polling A Queue Sitting On Other Server

Nov 29, 2007

Hi,
I want to achieve the following. Please let me know if it is possible or not. If not reasons.

There are 2 servers A and B. Server B has one service and queue available and some data is available in the queue. The data in the queue is online and being received by some other database. Now I want to access the data in the queue in server B from a service which is residing in Server A.

In short I want to continuosly poll the Queue on Server B from Server A. Please let me know whether this is feasible or not.

waiting for your earliest reply.

Thanks,
Balram.

View 7 Replies View Related

Can See Service Broker Queue With SQL Server Login

Sep 7, 2006

I have an application that is reading a message from a Service Broker Queue. When I use integrated security with an NT account it works fine. When I use a SQL Server User through Management Studio I can select from the Queue however, when I use this same account through the web app with the SQL Server User, I cannot see the Queue. Is there a grant that I must do to this account to get it to see the Service Broker Objects?

Gary

View 2 Replies View Related

SQL 2000 TO MS ACCESS DTS Error Message Opened Exclusively By Another User

Aug 8, 2006

I would like to password protect my MS Access Database which is being exported to from SQL 2000 using DTS, but when I password protect it, I get an error message stating "The workgroup information file is missing or opened exclusively by another user". The ACCESS file has to be opened exclusively to add password protection. Any suggestions?

View 1 Replies View Related

How Do I Prevent SQL 2000 From Posting This Message To The Event Viewer - Application Log

Sep 12, 2005

How do I prevent SQL Server 2000 from posting successful backupcompletion messages to the Windows 2000 Application Event Log?I have scheduled jobs which backup my transaction logs on 50+ databasesand it always writes to the Windows 2000 application event log uponcompletion.Due to the frequency of the jobs it only takes a day for theApplication Log to fill up, which is causing other jobs to get hung upwhen trying to write to it. On my Windows 2000 server, I have theapplication log event viewer setting correctly set as:"When maximum log size is reached - Overwrite events as needed" but forsome reason this setting no longer applies like it did for the pastthree years. SQLServerAgent and MSSQLSERVER both run under a localAdmin account, without a domain.When I researched how prevent SQL Server from logging this type ofmessage, I found that I can use sp_update_alert to disable thismessage, but I cannot findthe message_id to correctly disable this message. In sysmessages, themessage I am trying to suppress iserror:18265severity:10dlevel:128description:Log backed up: Database:%1, creation date(time): %2(%3), first LSN: %4, last LSN: %5, number ofdump devices: %7!d!, device information: (%8).mslangid:1033I tried calling sp_update_alert as follows:exec sp_update_alert @name = 'Log backed up: Database: %1, creationdate(time): %2(%3), first LSN: %4, last LSN: %5, number of dumpdevices: %7!d!, device information: (%8).', @enabled = 0but got the error message:Server: Msg 14262, Level 16, State 1, Procedure sp_update_alert, Line105The specified @name ('Log backed up: Database: %1, creation date(time):%2(%3), first LSN: %4, last LSN: %5, number of dump devices: %7!d!,device inf') does not exist.Looks like it can only handle 128 characters.How can I disable this message from being logged in the ApplicationLog? Or alternatively, how can I get the event viewer to behave asexpected and"Overwrite events as needed"?Thanks,Mike Orlando

View 9 Replies View Related

SQL Server 2008 :: Service Broker Queue In Database

Feb 3, 2015

I had to restore a database in one of the staging servers the other day. However, upon completion, I found out that the service broker queue in the database is not working anymore. The service broker queue error was similiar to this error:
The activated proc dbo.procedure_name running on queue database_name.dbo.queue_name output the following: '<error message>.'

View 0 Replies View Related

Queue Insert Statement What Is The Best SQL Server 2005 Feature To Use?

Apr 4, 2007

Hello everybody, I'm not completly aware of the SQL server 2005 possibilities so I'd need an hints from somebody with a wide knowledge to understand the direction to take!

This is what I have to do.



I insert into a table XML message. the messages are pushed automatically by an application I have no ""control" on and I get several messages "at the same time".

Everytime the message is inserted into the database I need to trasform the XML data into the correspondent relational value and I know already that in some cases it could take a while (1 second can be considered a while..)
My worry is that in the moment I process one message I loose the other one inserted after ,,,


There is some tool that helps me to handte the process as I would..

I was looking into SQL service broker?

It can be the right choice?



Thank you for any help!!



Marina B.

View 1 Replies View Related







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