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


ADVERTISEMENT

Managing Distributed Transactions With ADO.NET 2.0 Using TransactionScope Gives Error Message

Nov 14, 2007

 Hi, I am working  on vs2005 with sql server 2000. I  have used TransactionScope class. Example Reference: http://www.c-sharpcorner.com/UploadFile/mosessaur/TransactionScope04142006103850AM/TransactionScope.aspx   The code is given below.  using System.Transactions;   protected void Page_Load(object sender, EventArgs e)    {  System.Transactions.TransactionOptions transOption = new System.Transactions.TransactionOptions();         transOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;         transOption.Timeout = new TimeSpan(0, 2, 0);         using (System.Transactions.TransactionScope tranScope = new System.Transactions.TransactionScope(TransactionScopeOption.Required,transOption))         {            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["nwConnString"].ConnectionString))            {                int i;                con.Open();                SqlCommand cmd = new SqlCommand("update products set unitsinstock=100 where productid=1", con);                i = cmd.ExecuteNonQuery();                if (i > 0)                {                    using (SqlConnection conInner = new SqlConnection(ConfigurationManager.ConnectionStrings["pubsConnString"].ConnectionString))                    {                        conInner.Open();                        SqlCommand cmdInner = new SqlCommand("update Salary set sal=5000 where eno=1", conInner);                        i = cmdInner.ExecuteNonQuery();                        if (i > 0)                        {                            tranScope.Complete(); // this statement commits the executed query.                        }                    }                }            }            //  Dispose TransactionScope object, to commit or rollback transaction.        } } It gives error like
 "The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)" The database I have used is northwind database and pubs database which is by default in sql server 2000. So, Kindly let me know how to proceed further.  Thanks in advance,Arun. 

View 1 Replies View Related

Distributed Transactions With Multiple Instances Of Microsoft SQL Server

Jul 23, 2005

Hi,I'm having a problem running a distributed transaction between twolinked servers that both have multiple instances of SQL Serverinstalled on them. This is the error message that I receive:"The operation could not be performed because the OLE DB provider'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in thespecified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a]."The query follows the format:"BEGIN DISTRIBUTED TRANUPDATE [LINKEDSERVER1INSTANCE_NAME].DB.OWNER.TABLENAMESET fieldname = alias2.fieldnameFROM tablename alias2JOIN [LINKEDSERVER1INSTANCE_NAME].DB.OWNER.TABLENAME alias1on alias2.urn=alias1,urn"[color=blue]>From what I can gather from various sources the SQL Server must be[/color]named the same as the computer which it is installed on. However, if Ihave two instances of SQL Server, they cannot both be named the same asthe computer. Does anyone know of a way around this or whether I'mbarking up the wrong tree completely?Many thanks.

View 2 Replies View Related

Distributed Broker Queue Performance Issue

Aug 7, 2007

Hi,

We are doing a POC for transferring a huge number of messages(millions) from oner machine to another. The two approaches we are examining are MSMQ and SQL Broker. The MSMQ is set up as a remote queue on the target machine, and the source machine takes as little as 1 millisecond to send the message (using a .NET program). However, when testing on Service Broker, we find that the time taken to send message to the queue is significantly higher - like 70 millisecond. Could you please help us in understanding why this is happening?

The service broker distributed queues have been set up as per the directions in the posting at http://www.sqlservercentral.com/columnists/sindukuri/2797.asp

The source program (written in .NET) is calling a stored procedure in the source machine to write to the SSB queue. When we run SQL Trace, we find that the SP is responsible for 99% of the time taken. Here is our SP that send the message:
Declare @ConversationHandle uniqueidentifier
Begin Dialog @ConversationHandle
From Service SenderService
To Service 'ReceiverService'
On Contract SampleContract
WITH Encryption=off;
SEND
ON CONVERSATION @ConversationHandle
Message Type SenderMessageType
(<<XML String>>)

Please let us know if there are any additional settings required in the Service Broker to improve its performance. Or , what are the other approaches for building a distributed SSB application?

View 3 Replies View Related

How To Distributed Transactions

May 6, 2004

Hello,

does the .NET framework support distributed transactions somehow?

The SqlConnection/SqlTransaction classes doesn't seem to support them...

TIA. -julio

View 2 Replies View Related

Distributed Transactions

May 24, 2001

Hi to everyone!
Any experience handling a Store Procedure that has distribution transactions?
Here is a story.

We have a store procedure #1 that inserts or updates data in two tables A and B on SQL Server 7.0 then it calls another store procedure #2 that:
1. updates C table in the same database on SQL Server from Oracle Server
2. deletes that record on Oracle Server
3. inserts a record on Oracle from table A
4. inserts the same record into table D on SQL Server from Oracle Server.

People who wrote store procedure #1 put Commit Tran before executing #2.
The questions are:
1. Is it correct that we don't have Commit Tran for #2?
2. What do you think about Nested Begin - Commit Tran?

Any help is highly appreciated.
Sima

View 2 Replies View Related

Distributed Transactions

Aug 3, 1998

Does SQL Server actually support distributed transactions over, say, two tables in different databases but that reside on the same server? When I try to execute such a transaction in which one part of the transaction violates referential integrity and hence should not be executed, causing the rest of the transaction to, supposedly, rollback, the transaction does not roll back but instead produces an error message and executes the second, valid half of the transaction anyway.
Any help or suggestions gratefully accepted.

View 1 Replies View Related

Distributed Transactions ?

Mar 27, 2006

Hi There

I realize this is probably a basic question or at least i hope so but i cannot get distributed transactions working.

BOL is just driving me in circles.
I am running SS2000, MSDTC is up and running on the server.

I have added a trigger to a table where on insert or update the triggers fires and inserts a row to a remote server.

When i execute the update or insert i get the error:
Cannot start a distributed transaction.

I have tried stating BEGIN DISTRIBUTED TRANSACTION before the update but i get the same error, i have also read up on SET REMOTE_PROC_TRANSACTIONS, but this is not a remote sp.

Can anyone please reccomend a good link as to how to get distributed transactions to work.

Thanx

View 3 Replies View Related

Heterogeneous Distributed Transactions.

Dec 12, 2001

I am using SQL server 7.0 and i have created oracle8i linked server(Using MSDAORA as provider) in it.
When i run distributed queries between SQl server and Oracle server it works fine.But when i try to run distributed transaction between two servers ( BEGIN DISTRIBUTED TRANSACTION..)it reports an error saying Distributed transactions are not supported by MSDAORA.

My question is; is it possible to run distributed transaction between SQL server and oracle server (where oracle server is a linked server in my SQL server)?


Actually i want to run this transaction in DTS package which updates and transfers data amongst various servers.


Thanx


Regards,

Rahul

View 1 Replies View Related

Distributed Transactions In SQL 2000

Nov 10, 2004

We just upgraded from SQL 7 to 2000 and for some reason I am unable to perform distributed transactions. I keep getting the following error:

OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransaction Join:: JoinTransaction returned 0x8004d00a]. Operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction

I tried a few of the fixes recommended on the microsoft website but they did not solve the problem. We are still using the same O/S (Windows 2000 Pro), so the only thing that has changed is the server.

Any help would be appreciated.

View 6 Replies View Related

Oracle Distributed Transactions

Nov 5, 2001

I am attempting to update an oracle table from a insert trigger on my SQL table. When using a trigger to run a sql command against the ole db oracle client (linked server) a distributed transaction is initiated and i get a message stating. "The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction."

I can update the oracle table through a query however just not throught a distributed transaction.

However according to all the documentation i can find it states that I should be able to perform this.

Can anyone shed any insight?

View 4 Replies View Related

SQL 2005 Distributed Transactions From WCF

Jan 22, 2008



Hello,

I've been redirected here from the Transaction Programming forum becuase I have e peculiar issue with SQL 2005 running INSERT stored procs from multiple WCF services all withing a TransactionScope.

The original post is http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2720665&SiteID=1&mode=1

The story goes, I have SRVC A with starts a TransactionScope which in turn calls SRVC B & C in sequence based on processing rules.

SRVC A is a Sequential Workflow which Starts and Completes the TransactionScope
SRVC B Creates a new Customer into the database
SRVC C Creates new Accounts for that Customer and Initialises the accounts with funds


The DB Tables underneath are Customer, Account and AccountLog

DDL




Code Block
CREATE TABLE [Member].[Customers](

[CustomerId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](32) NOT NULL,
[CreatedUtc] [datetime] NOT NULL ,


CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(

[CustomerId] ASC
)

CREATE TABLE [Bank].[Accounts](

[AccountId] [int] IDENTITY(1,1) NOT NULL,
[CustomerId] [int] NOT NULL,
[CurrentBalance] [money] NOT NULL,
[LastUpdateDate] [datetime] NULL,
[CreatedDate] [datetime] NOT NULL,
[timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_Bank_Account] PRIMARY KEY CLUSTERED
(

[AccountId] ASC
)

) ON [PRIMARY]
GO
ALTER TABLE [Bank].[Accounts] WITH CHECK ADD CONSTRAINT [FK_Account_Customer] FOREIGN KEY([CustomerId])
REFERENCES [Member].[Customers] ([CustomerId])

CREATE TABLE [Bank].[AccountLog](

[AccountLogId] [int] IDENTITY(1,1) NOT NULL,
[AccountId] [int] NOT NULL,
[Amount] [money] NOT NULL,
[UtcDate] [datetime] NOT NULL,
CONSTRAINT [PK_Bank_AccountLog] PRIMARY KEY CLUSTERED
(

[AccountLogId] ASC
)
) ON [PRIMARY]
GO
ALTER TABLE [Bank].[AccountLog] WITH CHECK ADD CONSTRAINT [FK_AccountLog_Account] FOREIGN KEY([AccountId])
REFERENCES [Bank].[Accounts] ([AccountId])






NB. I've removed most fields not essential for this example.

So from SRVC A I invoke SRVC B and the Customer is created, however when I get to SRVC C and the accounts are to be created I get a lock. Only when the Transaction aborts due to timeout, do I see in SQL Profiler that the call to the SP that created the Account is executed but eventually rolls back as it is part of the distributed transaction.

Now, If I set the Isolation level in the TransactionScope to ReadUncommitted (urgh) the problem remains. When I set the IsolationLevel to Read Uncommitted in the SP that creates the account the problem remains but when I remove the FK constraint the problem disappers. The other curious thing is that with the Customer -> Account FK removed and when SRVC C calls to insert funds into the AccountLog which also updates an aggregated total in the Account from within the same transaction scope and with Account -> AccountLog FK constraints in place there is no locking even with Isolation Serializable.

I'm quite at a loss as to what could be causing these issues. If anyone has any suggestions I would greatly appreciate any help.

Thanks

Andy

View 1 Replies View Related

Distributed Transactions Between Servers On Different VLANs

Oct 18, 2006

Hi there,

I have two SQL servers, SERVERA and SERVERB. They are on two different VLAN's within our corporate network. They are physically only about 5 feet apart. SERVERA is SQL2005 while SERVERB is SQL2000. I am running a distruted transaction as a result of an insert on SERVERA which then causes a Stored procedure stored on SERVERA to insert a record on a table on SERVERB. I know my syntax is correct, 'cause I can get the Process to work between two servers (one SQL2000 and the other SQL2005) on the same VLAN. But when I run it in the problem environment, I get the following error message:

SQL Execution Error

Executed SQL Statement: INSERT INTO Tablea(recordid, recordtext) values(3, 'Testagain')

Error source: .Net SQLClient Data Provider

Error Message: The operation could not be performed because OLE DB provider "SQLNCL1" for linked server "SERVERB" was unable to being a distributed transaction.



I have checked to make sure that MSDTC is running and it is on both servers.

Any help would be greatly appreciated. Thanks! - Eric-



View 5 Replies View Related

Distributed Transactions Between SQL2005 And SQL2000

Oct 17, 2006

Hi there,

We have two servers, one (we'll call 'SERVERA') has SQL2005 running on it. The second (we'll call 'YELLOWSTEONE') is running both SQL2000 and SQL2005 on it. The SQL instances on YELLOWSTONE are 'YELLOWSTONESQL2000' and 'YELLOWSTONESQL2005'. As a linked server, I have an entry for YELLOWSTONE which then links to the SQL Server of YELLOWSTONESQL2000 on the server network name of YELLOWSTONE. By them selves they seem to run fine. However, if I have trigger that Runs on SERVERA to do a distributed transaction on 'YELLOWSTONESQL2000', I get the following error:

OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 2, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [2].

If You can provide me any assistance, I would greatly appreciate it. Thanks! - Eric -

View 1 Replies View Related

SQL 2012 :: Transaction Isolation Level And Distributed Transactions

Mar 5, 2015

I vaguely remember reading somewhere that all distributed transactions are executed at Serializable Isolation Level "under the covers."

1. Is this true?
2. What does "under the covers" mean in this case; i.e. will I not see the isolation level represented accurately in requests?

View 9 Replies View Related

Seeking Testing Volunteers W2K MTS/DTC To VMS DECdtm Distributed 2PC Transactions

Jul 20, 2005

Hi,I am seeking the help of volunteers to test some software that I'vedeveloped which facilitates distributed two-phase commit transactions,encompassing any resource manager (e.g. SQL/Server or Oracle) controlled byMicrosoft's Distributed Transaction Coordinator in a Windows2000environment, with any resource manager under the control of DECdtm (e.g. Rdb(or Oracle via the XA Veneer)) in a VMS environment.[Yes, at some stage, I hope to sell this software and make money out of it,so unless you have a large philanthropic streak or are simply a techie wholikes to stay on top of Windows<->VMS connectivity issues, then you may wishto look away now. But if you do choose to participate, then rest assuredthat I have no interest in your personal or company details. (Just yourwork-rate :-)]What differentiates my Transaction Manager software from existingTransaction Monitor packages that are already in the marketplace (and whyyou should be interested) is that it is based on the Transaction InternetProtocol TIP standard. (RFC 2372) For those of you who don't know, thebeauty of TIP's "Two-Pipe" strategy is it's application-pipe (or middleware)neutrality. Whereas most XA implementations mandate homogenous TransactionMonitor deployments (such as Tuxedo everywhere, Encina everywhere, MQSerieseverywhere, ACMSxp everywhere and so on . . .), hotTIP from TIER3 Softwaregives you complete freedom to choose the middleware product(s) that bestsuite your particular application and heterogeneous network needs.Would you like to talk to VMS with TIER3 Sockets, COM or DCE/RPC? BEAMessageQ, IBM MQSeries or HTML? The choice is yours and yours alone. Butonce you realize that you need to encase your critical transactions withinthe ACID properties of a true Heterogeneous Two-Phase Commit then you willcome to the conclusion that you need a Transaction Manager that looks a lotlike this.Another drawback of traditional "One-Pipe" strategies is that they precludethe run-time determination of transaction participants. (Functionalitywhich may be advantageous in a wide-area or Internet based application.)Anyway, this is what I have: -On the Windows side, you need absolutely *NO* additional software! I'llreply to this note with a brief description of the COM+ and DTC functionsthat you would need to invoke in order to successfully push a MTS/DTCtransaction to VMS. NB: These are standard Windows APIs that are fullydocumented on MSDN.On the VMS side, I have a VMSINSTAL saveset that (all zipped up) is some150KB that I'm happy to e-mail to you along similar lines to the VMShobbyists (non-commercial use) license. I'll reply to this note with anInternet Daemon (INETd) example of code that uses my software to cedetransactional control, over an SQL insert into a Rdb database, to MTS/DTC.It's under 500 lines long and contains all of the DCL, 3GL, SQL required toproduce a working example of a TIP-2PC capable TCP/IP auxiliary server. Thisexample will insert a row into the MF_PERSONNEL.Employees table on the VMSside in co-operation with Windows2000 MTS/DTC client that is inserting a rowinto the NORTHWIND.Employee table. Commit them all or roll them all back.So, in summary, If you'd like to volunteer to put hotTIP through it's pacesthen simply reply to this mail.Regards Richard MaherPS. The following are a few functionality restrictions with the currentversion of my software that may effect your decision to participate: -1) Transaction has to be started/mastered/coordinated by W2K MTS/DTC2) Transactions cannot be PULLed from VMS and must be PUSHed from W2K3) No cluster-wide recovery.(If a txn falls over after being prepared then you have to wait for thatspecific node to become contactable again even though that lovely RDMrecovery job is sitting on another node protecting the database until myhotTIP TM tells it to commit or abort.)4) There is currently no Alpha or Itanium version available. The Alpha portis currently in progress but, for the time being, you'll either need a VAXor a VAX emulator on your PC.

View 7 Replies View Related

DB Engine :: Distributed Transactions Fail On Linked Server

Feb 12, 2009

We get the below error while performing a distributed transaction on linked server. We have several linked servers configured in the source server and all of them succeed with the distributed transaction except on one.
 
We did all the basic troubleshooting and moreover the distributed transactions work fine if we use a remote server instead.

Error:
OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" was unable to begin a distributed transaction.
 
 Test code:
begin distributed transaction
select top 10 * from [SERVERNAME.REDMOND.CORP.MICROSOFT.COM].master.sys.objects
 ROLLBACK
 
Source server :   
Microsoft SQL Server 2008 (RTM) - 10.0.1779.0 (X64)
       Nov 12 2008 12:10:04
       Copyright (c) 1988-2008 Microsoft Corporation
       Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)
 
Target server :   
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
       Jul  9 2008 14:43:34
       Copyright (c) 1988-2008 Microsoft Corporation
       Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

View 30 Replies View Related

SQL 2005 Service Pack 2 Distributed Transactions And Mirroring

Jun 4, 2007

Is it possible to use DTC (or cross database queries) with mirroring on SQL 2005 Service Pack 2?



Thnx,

GoranP

View 2 Replies View Related

SQL 2012 :: Enable Promotion Of Distributed Transactions For RPC - Set To TRUE Or FALSE

May 14, 2015

It is my understanding that when having LinkedServers, the option "enable promotion of Distributed Transactions for RPC" should be set to TRUE, so we can rollback , if needed, remote transactions. At least, that's my understanding of that setting.

Having said that, the TRUE setting is affecting this particular TSQL code, inside an sproc, which I would prefer not to alter:

Insert into #TempTable
EXEC ServerB.MyDatabase.MyStoreProcedure
@param1= '',
@param2= ''
When set is set to TRUE (current setting) I get this error:

OLE DB provider "SQLNCLI11" for linked server "ServerB" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 28

The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "ServerB" was unable to begin a distributed transaction.

... when set to off, the error goes away.

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

Accessing The Same Database Using Multiple Threads

Aug 22, 2007



hi,

do i need to use specially synchronized code if i have multiple threads inserting, updating and reading rows to and from the same database?? in this case, i know that no 2 threads will try to insert or update the exact same row into the DB, however, multiple threads might try to read the same row from the database.

thanks!

View 3 Replies View Related

Slow Query When Run In Multiple Threads

Aug 9, 2006

Hi, I'm trying to stress test my web application, but when I get high load, the queries that used to take 10-20 ms starts taking 500 - 2000+ ms. Or to put it another way, when i run them single threaded i can do about 43000 a minute, when they are run in paralell it drops to about 2500 a minute.

What can i do about this ?

There are severeal queries thats affected, but here is one example:
update [user] with (ROWLOCK XLOCK) set timestamp = getdate() where userid = 1''

btw: im running sql server 2005 sp 1. The stress test is run on 3 machines total (web, sql and client) the client is simulation 400 users, cliking a page as soon as the last one is loaded, ie there will always be 400 page requests.

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

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

How To Process A Partitioned Cube In Multiple Threads

Dec 21, 2004

Our company is in the retail business, thus, the window for processing cubes is very small during Christmas season (only 4 hours each day).

To speed things up, we have partitioned our cube at monthly level so, potentially, 12 threads can be run simultantsly. However, when I looked at DTS, I am not so sure whether or how it can accomplish that task. Has anyone tried this before or is aware of another third party tool can do the trick?

thx in adcance,
Carl.

View 2 Replies View Related







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