June 18, 2012 at 5:37 am
Hi Guys
I am attempting to use CLR to circumvent some microsoft 'features'
namely, I'm using it to call a UDF remotely 🙂
The purpose of the function is to predict what date a transaction is going to hit our main accounts system based on what date the transaction was performed on, taking into account bank holidays etc so I don't want to have to maintain the bank holiday lists and the programs on 2 separate servers.
it works fine, the C# function takes a string as a parameter, uses an SqlConnection to access the remote server and executes a query string that is essetially "SELECT dbo.functionName('" + parameter + "')"
It then takes the resulting DateTime value and returns it. There is a try-catch in there that on error returns 1900-01-01 00:00:00 and writes the message from .net to the eventlog.
If I call this function from ssms on a one-off it works fine.
If I embed this function in an update statement on an 18000 row table (it's going to be slow, I know, I don't care though as it's for a over-night job) I get a mass of errors in the event-log saying :
Event Type:Warning
Event Source:RemoteUDF.dll
Event Category:None
Event ID:0
Date:18/06/2012
Time:12:04:33
User:N/A
Computer:FINTESTSQL
Description:
Error processing GL Date. Error: The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.%5B/code%5D
I cancel the query and then when I try and run it in the ssms as a one-off I get this error:
Event Type:Warning
Event Source:RemoteUDF.dll
Event Category:None
Event ID:0
Date:18/06/2012
Time:12:07:57
User:N/A
Computer:FINTESTSQL
Description:
Error processing GL Date. Error: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Now, I'm assuming the server with the UDF on it that I'm trying to call is booting me off because I'm throwing too many queries at it in a very short time.
I have made sure that the program always closes the connection wether it is successfully executed or not but it still seems to be using up all the connections on the server.
What I want to know is, how do I prevent the partner transaction manager from disabling it's support for remote network transactions?
Any ideas at all ?? So far I've been through about 10 different errors and fixed them with the help of good old google but this one they're saying things about the MSDTC settings on component management and seeing as I know absolutely nothing about this technology I don't want to message with it based on some googled info.
Thanks,
Ben
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 18, 2012 at 6:01 am
OK this is really strange.
If I run this:
SELECT dbo.MyClrFunction(sDateField)
FROM #myTempTable
it works fine on 18000 rows.
If I run this:
UPDATE #myTempTable
SET sDateField2 = dbo.MyClrFunction(sDateField)
FROM #myTempTable
I get the errors and have to drop and recreate the function and assembly to make it work again.
I really dont understand this...
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply