Debugging a worker thread inside SQL Server

Hi Everyone!

Just started looking into Assemblies within SQL Server in the hope of creating a multi-threaded application and when writing a basic test assembly I came across the following problem...

When I create a worker thread within my assembly I am unable to do the following in my newly created thread:

Stop on a breakpoint in VS2005 Use Debug.Writeline to output to the output window

I know the thread is running as I am able to write to the EventLog, but VS2005 seems to be unaware of any thread other than the main SQL server thread.

If anyone could tell me whether there it is possible to debug non-SQL server threads, whether I am doing something wrong, or whether I should be doing things differently that would be great!

Thanks in advance for any help!!

The code is as follows (apologies as its not very well written!):

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Threading;

using System.Diagnostics;

public partial class StoredProcedures

{

/// <summary>

/// This stored procedure starts the comms

/// </summary>

[Microsoft.SqlServer.Server.SqlProcedure]

public static void StartComms()

{

try

{

// Output some debug output to show the thread is running

Debug.WriteLine("Comms is starting");

// Create the thread to run in the main thread class

Thread zThread = new Thread(new ThreadStart(ThreadFunction));

// Start the thread running

zThread.Start();

// Sleep to let the other one run

Thread.Sleep(2000);

// Output some debug output to show the thread is running

Debug.WriteLine("Comms is about to wait for thread to finish");

// Wait for the thread to complete

zThread.Join();

// Output some debug output to show the thread is running

Debug.WriteLine("Comms is ending");

}

catch

{

throw;

}

}

/// <summary>

/// The main thread function

/// </summary>

public static void ThreadFunction()

{

try

{

// Flag to keep thread running

bool bRunning = true;

// While the thread is running

while (bRunning == true)

{

// Output some debug output to show the thread is running

Debug.WriteLine("Thread function running");

System.Diagnostics.EventLog.WriteEntry("SQLServerThreadTest",

"Thread function is running");

// Sleep for two seconds

Thread.Sleep(5000);

}

}

catch

{

throw;

}

}

};

If you start debugging by doing "Attach to Process" and selecting sqlservr.exe before executing your proc then the breakpoint is hit and Debug.Writeline does work in VS as expected.

I've forwarded this thread to the VS guys to see why your bp is not being hit when you start debugging either from a t-sql debug script or by doing "Step Into" your stored procedure.

Steven

|||

Hi Steven!

Thanks very much for your response.

I have tried out the "Attach to Process" this morning and by debugging this way everything works as expected - a treat!

If you find out any more information from the VS guys if you could post that for me that would be great, although I am very happy just knowing that there is a way to debug the application that I am going to write.

Thank you very much again for your help!!