Parallel Transaction Woes

[woogoo]

Hi All,

Is it (really) possible to implement true parallel transacions using VB.NET (v3.5 and 2008) and SQL Server 2005?

I've tried various attempts at this but nothing seems to work. In my code I start up 10 instances of a SQLConnection using this connection string:

...
m_Connections.Add(New SqlConnection(String.Format("Server=127.0.0.1,1433;Integrated Security=SSPI;Database=CSVDump;MultipleActiveResultSets=False;Pooling=False;WorkStation ID=CSVPool_{1}", m_Config.CSvPools(), P)))
...

Whenever I need access to the database I 'take' a connection object from the pool and return it when finished. The application is multi-threaded and watches a Folder for the arrival of CSV Files, and when a file arrives it is sent off on a new thread to be loaded. This of course means that when two or more files arrive I have 2 or more threads running to load them.

However, due to the structure of the CSV files I used three stored procedures to insert the content of the file in to the applicable tables. But no matter what I do, it bitches at some point that parallel transactions aren't supported. Are they? I'm begining to think that I'm wasting my time unless anyone else can share their knowledge; I'd be very greatful.


woogoo

 

[woogoo]

Does anyone have any insight into this? I suspect that I'm barking up the wrong tree here and SQL Server doesn't really support true parallel transactions.

What I mean by 'true' is multiple threads concurrently accessing and using the same stored procedures but executed under uniquely named atomic transactions.

Anyone?



woogoo

 

[jebenson]

In SQL Server, parallel transactions are not supported with Multiple Active Result Sets (MARS), and MARS is enabled by default. In the connection string for your SQL connection, turn off MARS by using "MultipleActiveResultSets=false". More info here:

http://www.connectionstrings.com/?carrier=sqlserver2005

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[woogoo]

Hey, thanks for the reply, but as you'll see from the code listing it is turned off, which is why I can't understand why this doesn't work.

I've read and researched and according to what I have read then there is no reason as to why my approach shouldn't work, so this leaves I've not properly configured SQL Server to support these or SQL Server's broke!

--

woogoo

 

[woogoo]

Just to say that I now have this fixed, and I can now execute n transactions concurrently, having hived off a select on to another connection.

woogoo