January 23, 2009 at 3:45 am
Hi,
I'm trying to build a CLR assembly for SQL Server 2005 in C# (VS 2005), but it seems like the threads are started but dont do anything until they stop.
This is my code:
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Threading;
using System.Xml.Serialization;
using System.IO.IsolatedStorage;
using System.Diagnostics;
using System.Reflection;
using System.Collections;
public class MyThread
{
public static int i = 0;
public static string[] test = new string [5];
[Microsoft.SqlServer.Server.SqlProcedure]
public static void sqlTest()
{
Thread[] ta = new Thread[5]; // Thread-Array
for (i = 0; i < 5; i++)
{
test = "exec proc_test_email " + i.ToString();
ta = new Thread(new ThreadStart(tuwas)); // Threads werden erzeugt
ta.Start(); // Threads werden gestartet
//while (ta[j].ThreadState.ToString().ToLower() != "stopped")
//{
SqlContext.Pipe.Send(ta.ThreadState.ToString());
SqlContext.Pipe.Send(ta.ManagedThreadId.ToString());
SqlContext.Pipe.Send(ta.IsAlive .ToString ());
//}
//SqlContext.Pipe.Send(test);
SqlContext.Pipe.Send("Test " + i.ToString() + " gestartet");
}
}
public static void tuwas()
{
SqlCommand myCmd = new SqlCommand(test);
SqlConnectionStringBuilder myBuilder = new SqlConnectionStringBuilder();
myBuilder.DataSource = "SIAM";
myBuilder.InitialCatalog = "verwaltung";
myBuilder.IntegratedSecurity = true;
myBuilder.Pooling = false;
SqlConnection myConnection = new SqlConnection(myBuilder .ConnectionString );
myConnection.Open();
myCmd.Connection = myConnection;
myCmd.ExecuteNonQuery();
myConnection.Close();
//Just to be sure, maybe Pipe works if my SqlCommand doesnt.
SqlContext.Pipe.ExecuteAndSend(myCmd);
}
}
Running this code will produce the following output on SQL Server:
Running
96
True
Test 0 gestartet
Running
97
True
Test 1 gestartet
Running
98
True
Test 2 gestartet
Running
99
True
Test 3 gestartet
Running
100
True
Test 4 gestartet
Any ideas what goes wrong or how to get a more detailed view whats going on?
Regards,
Jan
January 27, 2009 at 11:20 am
Well first you have statics that aren't readonly which is both UNSAFE and fails to guarantee valid results at any point of your execution. What exactly are you expecting this to do, and why do you need to do multiple threaded operations like this in SQL 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[/url]
January 30, 2009 at 2:06 am
Hi Jonathan,
what I want to do is easy: I will have a staging table with parameters, and I have to run a stored procedure with each of them. My assembly should do this in parallel using threads instead of using a sql cursor and calling this stored procedure on each iteration.
But I just found this post (http://www.sqlservercentral.com/Forums/Topic543963-386-1.aspx?Highlight=thread), so threading in CLR doesnt seem to a good idea anyway, so I will check if I can handle this in SSIS.
Regards,
Jan
January 30, 2009 at 5:54 am
Jan Wagner (1/30/2009)
Hi Jonathan,what I want to do is easy: I will have a staging table with parameters, and I have to run a stored procedure with each of them. My assembly should do this in parallel using threads instead of using a sql cursor and calling this stored procedure on each iteration.
But I just found this post (http://www.sqlservercentral.com/Forums/Topic543963-386-1.aspx?Highlight=thread), so threading in CLR doesnt seem to a good idea anyway, so I will check if I can handle this in SSIS.
As You Wish. however, what you have described so far is a perfect
fit for Service Broker.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply