Removal from sys.transmission_queues on exceeding LIFETIME

Hello,

We needed to detach a database that contained a target ServiceBroker service and wanted to do this without impacting the rest of our system. The idea was the ServiceBroker would enqueue message to this service to be pickedup when the database was re-attached. Our sequence to do this was:

1. Disable the queue on the target service forcing ServiceBroker to queue in the initating side transmission queue.

2. Detach the database.

3. Re-attach the database.

4. Re-enable the queue

Messages sent to the service have a specified LIFETIME. What appeared to happen was that messages were being stored in the transmission queue as expected but on hiting the LIFETIME period they were being removed from the transmission queue and were therfore "lost".

Has anyone else experienced this or can anyone suggest what we are doing wrong.

Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)

Any suggestions gratefully recieved.

We have now removed the LIFETIME property from our dialogs in order to reduce the risk of this happening and it all seems fine. My only concern now is that the LIFETIME EXCEEDED message is a Broker error, so does this mean that the same will hold true for all Broker errors that are generated whilst a message is in the transmission queue?|||

Prior to SP2 when receiving an Error or EndDialog message the sys.transmission_queue was drained for the that dialog. The idea was that applications that needed to look at the messages sent in case of error whould use queue RETENTION = ON. With SP2 we introduced a change in which the messages stay in sys.transmission_queue but ar enot being attempted to delivery and they are deleted only after the applications issues the END CONVERSATION.

So yes, any error message will cause the behavior your observed. Either use retention to save the messages sent or upgrade to SP2.

|||

Ian Mitchen wrote:

Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)

Also restore of a backup will disable the broker. SSB is intedend primarily for distributed applications and one has to consider the problems that can happen when only one side of the conversation is moved (i.e. database is detached/attached): routes have to be fixed, endpoint security has to be set up again for the new host etc. When a backup is restored, the situation is more dramatical, as the peer might be in a more advanced stage of a conversation (i.e. restorer db has next sequence number on a conversation N, but peer is already expecting N+2). In this case the only solution is to error the conversation.

For these considerations the restore and attach operations disable the service broker in the database. One has to inspect the database, ensure is in a consistent state with it's peers and then enable it.

|||

Thanks for a great response, that's filled another gap in my knowledge.

Cheers,

Ian

Removal from sys.transmission_queues on exceeding LIFETIME

Hello,

We needed to detach a database that contained a target ServiceBroker service and wanted to do this without impacting the rest of our system. The idea was the ServiceBroker would enqueue message to this service to be pickedup when the database was re-attached. Our sequence to do this was:

1. Disable the queue on the target service forcing ServiceBroker to queue in the initating side transmission queue.

2. Detach the database.

3. Re-attach the database.

4. Re-enable the queue

Messages sent to the service have a specified LIFETIME. What appeared to happen was that messages were being stored in the transmission queue as expected but on hiting the LIFETIME period they were being removed from the transmission queue and were therfore "lost".

Has anyone else experienced this or can anyone suggest what we are doing wrong.

Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)

Any suggestions gratefully recieved.

We have now removed the LIFETIME property from our dialogs in order to reduce the risk of this happening and it all seems fine. My only concern now is that the LIFETIME EXCEEDED message is a Broker error, so does this mean that the same will hold true for all Broker errors that are generated whilst a message is in the transmission queue?|||

Prior to SP2 when receiving an Error or EndDialog message the sys.transmission_queue was drained for the that dialog. The idea was that applications that needed to look at the messages sent in case of error whould use queue RETENTION = ON. With SP2 we introduced a change in which the messages stay in sys.transmission_queue but ar enot being attempted to delivery and they are deleted only after the applications issues the END CONVERSATION.

So yes, any error message will cause the behavior your observed. Either use retention to save the messages sent or upgrade to SP2.

|||

Ian Mitchen wrote:

Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)

Also restore of a backup will disable the broker. SSB is intedend primarily for distributed applications and one has to consider the problems that can happen when only one side of the conversation is moved (i.e. database is detached/attached): routes have to be fixed, endpoint security has to be set up again for the new host etc. When a backup is restored, the situation is more dramatical, as the peer might be in a more advanced stage of a conversation (i.e. restorer db has next sequence number on a conversation N, but peer is already expecting N+2). In this case the only solution is to error the conversation.

For these considerations the restore and attach operations disable the service broker in the database. One has to inspect the database, ensure is in a consistent state with it's peers and then enable it.

|||

Thanks for a great response, that's filled another gap in my knowledge.

Cheers,

Ian

sql

Remote debugging problems

I'm having remote debugging problems. Here is what I've done so far:

Set CLR Enabled to 1 on the target server and executed Reconfigure.
Setup the Visual Studio 2005 Remote Debugger as a service on the target server, log on set to the same user id/password that I'm using on the client machine. User is in administrator group on both machines.
Ensured that Remote Procedure Call and Remote Procedure Call Locator are started on the server, log on as Network Service.
Created a VS database project on the client and added a stored procudure, during this created a server connection to the target server.
Checked application and SQLCLR debugging for the connection.
Added code to insert a record.
Modified the test.sql script to execute the stored procedure.
Verified that I'm in the debug configuration and not a release configuration.
Set a break point on the first line of code.
Press F5.

In the Output window in VS I get:
The thread 'servername [55]' (0x14e0) has exited with code 0 (0x0).
Debugging script from project script file.

The thread 'servername [55]' (0x14e0) has exited with code 0 (0x0).
The thread 'servername [55]' (0x14e0) has exited with code 0 (0x0).
Auto-attach to process '[1288] [SQL] servername' on machine 'servername' succeeded.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'MyStoredProcedure', No symbols loaded.
Auto-attach to process '[1288] sqlservr.exe' on machine 'servername' succeeded.

At this point it appears that it hangs. I've let it sit for over an hour and it never gets to the breakpoint. Also, if I click the Stop Debugging button I get a dialog box saying "Debugging is being stopped but is not yet complete. You can force debugging to stop immediately, but any process being detached may be terminated instead. This window will automatically close when the debugging has completely stopped." with a button saying Stop Now. I've let this sit overnight to see if it disappears on its own and it does not.

Any help would be greatly appreciated.

John
A little more info. I set the project database permission level to external then granted the user permission and set the database to trustworthy on. I then truncated the table. The test.sql script file contains:

exec myStoredProcedure '20040522'
select * from mytable where period = '20040522'

I tried to debug again by pressing F5. It still appeared to hang so after a while I stopped it. This time I noticed that the select statement returned a row so it obviously executed without going into the debugger.

John

|||

Hi,

I am having the exact same problem. Is there any new information regarding this issue?

Thanks,

Rocco

|||Same here, it's been posted for quite some time and there are no responses for solutions. It seems to happen on a variety of systems, whether Vista, XP, or 2003, local or remote, and it would appear only a few of us are affected. There doesn't seem to be much interest in addressing the problem.|||Have you found a solution for this issue? I am also experiencing the same problem.|||I still have not found a solution. I'm back living in the era of writing debug messages to a debug table to try and debug stored procedures and have basically given up on using the CLR. It's very frustrating. I wouldn't wish this on anyone but if more people had the problem someone would probably come up with a solution.

John
|||

What editions of VS do you use? and of SQL Server 2005? I hope they are not Express or Standard or lower than2005?

If no connection has been enabled for multi-tier debugging, or if the credentials are not sufficient for debugging, breakpoints set in SQL Server objects on the corresponding server will not be hit.[1]

Have you enabled multi-tier debugging by checking "Application Debugging" (on rigt-clicking db connection in Server Explorer)?

To successfully debug multi-tier applications, you must have enabled debugging for the application project [2]

Have you temporarily disabled SQl Server connection pooling
("Pooling = false" in connection string)? [3]

Do you have firewalls? Have you configured them on both machines [4]

Is the account under which VS2005 runs is the same as the account under which you connect to SQL server? [5]

Debugging automatic enabling is possible only if you use Windows Authentication for connectrion [ 6 ]

[1]

How to: Enable Multi-tier Debugging

http://msdn2.microsoft.com/en-us/library/ms165060(VS.80).aspx

[2]

How to: Enable SQL Debugging For a Project

http://msdn2.microsoft.com/en-us/library/ms165038(VS.80).aspx

[3]
SQL Debugging Limitations
http://msdn2.microsoft.com/en-us/library/kkyhd4yb(VS.80).aspx
[4]
How to: Enable SQL Server 2005 Debugging
http://msdn2.microsoft.com/en-us/library/s0fk6z6e(VS.80).aspx
[5]
How to: Set SQL Server Permissions for Debugging
http://msdn2.microsoft.com/en-us/library/w1bhybwz(VS.80).aspx
[ 6 ]
How to: Enable SQL Debugging For a Project
http://msdn2.microsoft.com/en-us/library/ms165038(VS.80).aspx

|||Hi John,

I had exactly the same problem: Debugging hangs right after auto-attaching to SQL Server.
Following procedure solves the problem for me:
In VS <Server Explorer> right click on the data connection you're using and click on "Allow SQL/CLR Debugging" to disallow debugging. Now re-allow it the same way ( answer popup question if managed threads should be stopped on the server with yes ).
At this moment debugging works for me again.
Not sure if this helps for you but I wanted to share my finding ...

HTH,
Karsten|||Rumtata,

Thanks very much for posting that workaround. I was having the same problem and your solution fixed the problem as well. I filed bug 287329 on the issue.

Remote debugging problems

I'm having remote debugging problems. Here is what I've done so far:

Set CLR Enabled to 1 on the target server and executed Reconfigure.
Setup the Visual Studio 2005 Remote Debugger as a service on the target server, log on set to the same user id/password that I'm using on the client machine. User is in administrator group on both machines.
Ensured that Remote Procedure Call and Remote Procedure Call Locator are started on the server, log on as Network Service.
Created a VS database project on the client and added a stored procudure, during this created a server connection to the target server.
Checked application and SQLCLR debugging for the connection.
Added code to insert a record.
Modified the test.sql script to execute the stored procedure.
Verified that I'm in the debug configuration and not a release configuration.
Set a break point on the first line of code.
Press F5.

In the Output window in VS I get:
The thread 'servername [55]' (0x14e0) has exited with code 0 (0x0).
Debugging script from project script file.

The thread 'servername [55]' (0x14e0) has exited with code 0 (0x0).
The thread 'servername [55]' (0x14e0) has exited with code 0 (0x0).
Auto-attach to process '[1288] [SQL] servername' on machine 'servername' succeeded.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'MyStoredProcedure', No symbols loaded.
Auto-attach to process '[1288] sqlservr.exe' on machine 'servername' succeeded.

At this point it appears that it hangs. I've let it sit for over an hour and it never gets to the breakpoint. Also, if I click the Stop Debugging button I get a dialog box saying "Debugging is being stopped but is not yet complete. You can force debugging to stop immediately, but any process being detached may be terminated instead. This window will automatically close when the debugging has completely stopped." with a button saying Stop Now. I've let this sit overnight to see if it disappears on its own and it does not.

Any help would be greatly appreciated.

John
A little more info. I set the project database permission level to external then granted the user permission and set the database to trustworthy on. I then truncated the table. The test.sql script file contains:

exec myStoredProcedure '20040522'
select * from mytable where period = '20040522'

I tried to debug again by pressing F5. It still appeared to hang so after a while I stopped it. This time I noticed that the select statement returned a row so it obviously executed without going into the debugger.

John

|||

Hi,

I am having the exact same problem. Is there any new information regarding this issue?

Thanks,

Rocco

|||Same here, it's been posted for quite some time and there are no responses for solutions. It seems to happen on a variety of systems, whether Vista, XP, or 2003, local or remote, and it would appear only a few of us are affected. There doesn't seem to be much interest in addressing the problem.|||Have you found a solution for this issue? I am also experiencing the same problem.|||I still have not found a solution. I'm back living in the era of writing debug messages to a debug table to try and debug stored procedures and have basically given up on using the CLR. It's very frustrating. I wouldn't wish this on anyone but if more people had the problem someone would probably come up with a solution.

John
|||

What editions of VS do you use? and of SQL Server 2005? I hope they are not Express or Standard or lower than2005?

If no connection has been enabled for multi-tier debugging, or if the credentials are not sufficient for debugging, breakpoints set in SQL Server objects on the corresponding server will not be hit.[1]

Have you enabled multi-tier debugging by checking "Application Debugging" (on rigt-clicking db connection in Server Explorer)?

To successfully debug multi-tier applications, you must have enabled debugging for the application project [2]

Have you temporarily disabled SQl Server connection pooling
("Pooling = false" in connection string)? [3]

Do you have firewalls? Have you configured them on both machines [4]

Is the account under which VS2005 runs is the same as the account under which you connect to SQL server? [5]

Debugging automatic enabling is possible only if you use Windows Authentication for connectrion [ 6 ]

[1]

How to: Enable Multi-tier Debugging

http://msdn2.microsoft.com/en-us/library/ms165060(VS.80).aspx

[2]

How to: Enable SQL Debugging For a Project

http://msdn2.microsoft.com/en-us/library/ms165038(VS.80).aspx

[3]
SQL Debugging Limitations
http://msdn2.microsoft.com/en-us/library/kkyhd4yb(VS.80).aspx
[4]
How to: Enable SQL Server 2005 Debugging
http://msdn2.microsoft.com/en-us/library/s0fk6z6e(VS.80).aspx
[5]
How to: Set SQL Server Permissions for Debugging
http://msdn2.microsoft.com/en-us/library/w1bhybwz(VS.80).aspx
[ 6 ]
How to: Enable SQL Debugging For a Project
http://msdn2.microsoft.com/en-us/library/ms165038(VS.80).aspx

|||Hi John,

I had exactly the same problem: Debugging hangs right after auto-attaching to SQL Server.
Following procedure solves the problem for me:
In VS <Server Explorer> right click on the data connection you're using and click on "Allow SQL/CLR Debugging" to disallow debugging. Now re-allow it the same way ( answer popup question if managed threads should be stopped on the server with yes ).
At this moment debugging works for me again.
Not sure if this helps for you but I wanted to share my finding ...

HTH,
Karsten|||Rumtata,

Thanks very much for posting that workaround. I was having the same problem and your solution fixed the problem as well. I filed bug 287329 on the issue.

Remote debugging problems

I'm having remote debugging problems. Here is what I've done so far:

Set CLR Enabled to 1 on the target server and executed Reconfigure.

Setup the Visual Studio 2005 Remote Debugger as a service on the target server, log on set to the same user id/password that I'm using on the client machine. User is in administrator group on both machines.
Ensured that Remote Procedure Call and Remote Procedure Call Locator are started on the server, log on as Network Service.
Created a VS database project on the client and added a stored procudure, during this created a server connection to the target server.
Checked application and SQLCLR debugging for the connection.
Added code to insert a record.
Modified the test.sql script to execute the stored procedure.
Verified that I'm in the debug configuration and not a release configuration.
Set a break point on the first line of code.
Press F5.

In the Output window in VS I get:
The thread 'servername [55]' (0x14e0) has exited with code 0 (0x0).
Debugging script from project script file.

The thread 'servername [55]' (0x14e0) has exited with code 0 (0x0).
The thread 'servername [55]' (0x14e0) has exited with code 0 (0x0).
Auto-attach to process '[1288] [SQL] servername' on machine 'servername' succeeded.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'MyStoredProcedure', No symbols loaded.
Auto-attach to process '[1288] sqlservr.exe' on machine 'servername' succeeded.

At this point it appears that it hangs. I've let it sit for over an hour and it never gets to the breakpoint. Also, if I click the Stop Debugging button I get a dialog box saying "Debugging is being stopped but is not yet complete. You can force debugging to stop immediately, but any process being detached may be terminated instead. This window will automatically close when the debugging has completely stopped." with a button saying Stop Now. I've let this sit overnight to see if it disappears on its own and it does not.

Any help would be greatly appreciated.

JohnA little more info. I set the project database permission level to external then granted the user permission and set the database to trustworthy on. I then truncated the table. The test.sql script file contains:

exec myStoredProcedure '20040522'
select * from mytable where period = '20040522'

I tried to debug again by pressing F5. It still appeared to hang so after a while I stopped it. This time I noticed that the select statement returned a row so it obviously executed without going into the debugger.

John|||

Hi,

I am having the exact same problem. Is there any new information regarding this issue?

Thanks,

Rocco

|||Same here, it's been posted for quite some time and there are no responses for solutions. It seems to happen on a variety of systems, whether Vista, XP, or 2003, local or remote, and it would appear only a few of us are affected. There doesn't seem to be much interest in addressing the problem.|||Have you found a solution for this issue? I am also experiencing the same problem.|||I still have not found a solution. I'm back living in the era of writing debug messages to a debug table to try and debug stored procedures and have basically given up on using the CLR. It's very frustrating. I wouldn't wish this on anyone but if more people had the problem someone would probably come up with a solution.

John|||

What editions of VS do you use? and of SQL Server 2005? I hope they are not Express or Standard or lower than2005?

If no connection has been enabled for multi-tier debugging, or if the credentials are not sufficient for debugging, breakpoints set in SQL Server objects on the corresponding server will not be hit.[1]

Have you enabled multi-tier debugging by checking "Application Debugging" (on rigt-clicking db connection in Server Explorer)?

To successfully debug multi-tier applications, you must have enabled debugging for the application project [2]

Have you temporarily disabled SQl Server connection pooling
("Pooling = false" in connection string)? [3]

Do you have firewalls? Have you configured them on both machines [4]

Is the account under which VS2005 runs is the same as the account under which you connect to SQL server? [5]

Debugging automatic enabling is possible only if you use Windows Authentication for connectrion [ 6 ]

[1]

How to: Enable Multi-tier Debugging

http://msdn2.microsoft.com/en-us/library/ms165060(VS.80).aspx

[2]

How to: Enable SQL Debugging For a Project

http://msdn2.microsoft.com/en-us/library/ms165038(VS.80).aspx

[3]
SQL Debugging Limitations
http://msdn2.microsoft.com/en-us/library/kkyhd4yb(VS.80).aspx
[4]
How to: Enable SQL Server 2005 Debugging
http://msdn2.microsoft.com/en-us/library/s0fk6z6e(VS.80).aspx
[5]
How to: Set SQL Server Permissions for Debugging
http://msdn2.microsoft.com/en-us/library/w1bhybwz(VS.80).aspx
[ 6 ]
How to: Enable SQL Debugging For a Project
http://msdn2.microsoft.com/en-us/library/ms165038(VS.80).aspx

|||Hi John,

I had exactly the same problem: Debugging hangs right after auto-attaching to SQL Server.
Following procedure solves the problem for me:
In VS <Server Explorer> right click on the data connection you're using and click on "Allow SQL/CLR Debugging" to disallow debugging. Now re-allow it the same way ( answer popup question if managed threads should be stopped on the server with yes ).
At this moment debugging works for me again.
Not sure if this helps for you but I wanted to share my finding ...

HTH,
Karsten|||Rumtata,

Thanks very much for posting that workaround. I was having the same problem and your solution fixed the problem as well. I filed bug 287329 on the issue.

Remote debugging problems

I'm having remote debugging problems. Here is what I've done so far:

Set CLR Enabled to 1 on the target server and executed Reconfigure.
Setup the Visual Studio 2005 Remote Debugger as a service on the target server, log on set to the same user id/password that I'm using on the client machine. User is in administrator group on both machines.
Ensured that Remote Procedure Call and Remote Procedure Call Locator are started on the server, log on as Network Service.
Created a VS database project on the client and added a stored procudure, during this created a server connection to the target server.
Checked application and SQLCLR debugging for the connection.
Added code to insert a record.
Modified the test.sql script to execute the stored procedure.
Verified that I'm in the debug configuration and not a release configuration.
Set a break point on the first line of code.
Press F5.

In the Output window in VS I get:
The thread 'servername [55]' (0x14e0) has exited with code 0 (0x0).
Debugging script from project script file.

The thread 'servername [55]' (0x14e0) has exited with code 0 (0x0).
The thread 'servername [55]' (0x14e0) has exited with code 0 (0x0).
Auto-attach to process '[1288] [SQL] servername' on machine 'servername' succeeded.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'MyStoredProcedure', No symbols loaded.
Auto-attach to process '[1288] sqlservr.exe' on machine 'servername' succeeded.

At this point it appears that it hangs. I've let it sit for over an hour and it never gets to the breakpoint. Also, if I click the Stop Debugging button I get a dialog box saying "Debugging is being stopped but is not yet complete. You can force debugging to stop immediately, but any process being detached may be terminated instead. This window will automatically close when the debugging has completely stopped." with a button saying Stop Now. I've let this sit overnight to see if it disappears on its own and it does not.

Any help would be greatly appreciated.

John
A little more info. I set the project database permission level to external then granted the user permission and set the database to trustworthy on. I then truncated the table. The test.sql script file contains:

exec myStoredProcedure '20040522'
select * from mytable where period = '20040522'

I tried to debug again by pressing F5. It still appeared to hang so after a while I stopped it. This time I noticed that the select statement returned a row so it obviously executed without going into the debugger.

John

|||

Hi,

I am having the exact same problem. Is there any new information regarding this issue?

Thanks,

Rocco

|||Same here, it's been posted for quite some time and there are no responses for solutions. It seems to happen on a variety of systems, whether Vista, XP, or 2003, local or remote, and it would appear only a few of us are affected. There doesn't seem to be much interest in addressing the problem.|||Have you found a solution for this issue? I am also experiencing the same problem.|||I still have not found a solution. I'm back living in the era of writing debug messages to a debug table to try and debug stored procedures and have basically given up on using the CLR. It's very frustrating. I wouldn't wish this on anyone but if more people had the problem someone would probably come up with a solution.

John
|||

What editions of VS do you use? and of SQL Server 2005? I hope they are not Express or Standard or lower than2005?

If no connection has been enabled for multi-tier debugging, or if the credentials are not sufficient for debugging, breakpoints set in SQL Server objects on the corresponding server will not be hit.[1]

Have you enabled multi-tier debugging by checking "Application Debugging" (on rigt-clicking db connection in Server Explorer)?

To successfully debug multi-tier applications, you must have enabled debugging for the application project [2]

Have you temporarily disabled SQl Server connection pooling
("Pooling = false" in connection string)? [3]

Do you have firewalls? Have you configured them on both machines [4]

Is the account under which VS2005 runs is the same as the account under which you connect to SQL server? [5]

Debugging automatic enabling is possible only if you use Windows Authentication for connectrion [ 6 ]

[1]

How to: Enable Multi-tier Debugging

http://msdn2.microsoft.com/en-us/library/ms165060(VS.80).aspx

[2]

How to: Enable SQL Debugging For a Project

http://msdn2.microsoft.com/en-us/library/ms165038(VS.80).aspx

[3]
SQL Debugging Limitations
http://msdn2.microsoft.com/en-us/library/kkyhd4yb(VS.80).aspx
[4]
How to: Enable SQL Server 2005 Debugging
http://msdn2.microsoft.com/en-us/library/s0fk6z6e(VS.80).aspx
[5]
How to: Set SQL Server Permissions for Debugging
http://msdn2.microsoft.com/en-us/library/w1bhybwz(VS.80).aspx
[ 6 ]
How to: Enable SQL Debugging For a Project
http://msdn2.microsoft.com/en-us/library/ms165038(VS.80).aspx

|||Hi John,

I had exactly the same problem: Debugging hangs right after auto-attaching to SQL Server.
Following procedure solves the problem for me:
In VS <Server Explorer> right click on the data connection you're using and click on "Allow SQL/CLR Debugging" to disallow debugging. Now re-allow it the same way ( answer popup question if managed threads should be stopped on the server with yes ).
At this moment debugging works for me again.
Not sure if this helps for you but I wanted to share my finding ...

HTH,
Karsten|||Rumtata,

Thanks very much for posting that workaround. I was having the same problem and your solution fixed the problem as well. I filed bug 287329 on the issue.