execute clr proc ftom tsql proc...

Question

execute clr proc ftom tsql proc...

nics

Ten Centuries

Points: 1084
More actions
January 25, 2009 at 11:12 am

#196469

Hi all,
I have a clr proc that runs ok when I execute it from SSMS...
But whem I execute it from inside a TSQL proc the clr proc starts but doesn't finish as it should...
(I don't have any inparameters...)
This clr proc works on one of our servers but not on the other one... it fells like it is a parameter setting but I don't know...
Is there any setting that I have missed or ???
Greatful for all help!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's the CLR proc doing?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nics Ten Centuries Points: 1084 More actions · Answer 2

It loops through a couple of big tables and based on the data it inserts data to a couple of other tables. It is a rule system and it does different things based on each rule.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Why is that done in the CLR? For procs that affect data, T-SQL's a better choice.

There's no setting or anything, so the only thing I can think of is that the CLR proc's blocked by something, is working through the data loops, slowly, or something similar.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nics Ten Centuries Points: 1084 More actions · Answer 4

Ok...

Well I have tried to build this rule module in T-SQL and also as a separate application but when I built it in CLR proc I got the best performance result... That is why I am using the CLR proc...

The database server is a virtual server so maybe there is some problem with memory management... I will post the solution if I find it...

Thanks for taking your time to try to help...

nics Ten Centuries Points: 1084 More actions · Answer 5

Hi again,

I found the problem... my mistake...

In the T-SQL Proc that calls my CLR proc SET NOCOUNT ON is used which affects all PROCS called by the "main" PROC. In my CLR Proc I check the number of affected rows which don’t return any value since the SET NOCOUNT is set to ON in the calling PROC...

I know set the NOCOUNT TO OFF just before calling my CLR proc and after I set NOCOUNT to ON again...