Using Threads in CLR Assembly

  • 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

  • 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]

  • 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

  • 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