Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multi thread on SQLCLR


Multi thread on SQLCLR

Author
Message
michel-834898
michel-834898
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 3
Hi all,

I need to retrieve some value from 100 different servers trough DBLINK. This connection is trough a VPN on Internet.
Each call take time, and the total off the 100 call take 5 minutes.

I would write a SQLCLR proc and work with a ThreadPool to execute many call on the same time.

It's possible, you have some simple sample or a guide line.

Thank for your help.

Michel
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9454 Visits: 9517
I don't know about threading and SQLCLR, but I do know that you can do this with Linked Servers and Service Broker.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Jonathan Kehayias
Jonathan Kehayias
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1826 Visits: 1807
While you can do threading in SQLCLR, it is UNSAFE and not recommended. This seems like a job for a SSIS package and a staging table to me. The SSIS package can be wrapped in a SQL Agent job, and called from a stored procedure with sp_start_job, or through service broker. I would strongly advise against doing this in SQLCLR for a number of reasons.

First SQLCLR runs in the MemToLeave memory area of the VAS. This is a very limited space, and you are likely to overflow it and have a memory abort of your CLR process which will unload the app domain.
Second, if you have a runaway process it could cause an unyielding scheduler which may cause a stack dump in SQL, and again unloading of the CLR process and your assembly.

CLR has its place in SQL, and what can and can't be done is highly limited, and for a number of really good reasons. There are much better tools for solving this problem than CLR.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search