Remove a constraint

I executed the following sp several times

ALTER PROCEDURE [dbo].[sp_GetItemsByCategoryOnPage]

AS

BEGIN

CREATE TABLE #TemporaryItems

(

ID int IDENTITY PRIMARY KEY,

...

[auto] [bit] NULL CONSTRAINT [DF_CostupdateItems_auto] DEFAULT ((1)),

...

)

...

END

Suddlenly I got a message:

The constraint [DF_CostupdateItems_auto] already exists. How can I remove it from DB?

Hi there,

You can use such a statement :

alter table YourTableNameWithThisConstraint drop constraint [DF_CostupdateItems_auto]

The constraints store in each Table's structures not in global.

__
May the God blessings be.

LotraSoft Ltd.

|||You will get the error message if multiple connections call the SP simultaneously. Even though the temporary table name is uniquefied automatically by SQL Server since you have a named constraint for the default you can't only execute the SP serially. So either remove the constraint name from the column definition so that SQL Server can automatically generate a unique name or don't call the SP concurrently.

Remote Query Performance

I have a query that when executed against the host server, it completes in 1
1
seconds. When I execute the same query against a linked server it takes 100
seconds to run. I have checked the execution plan for the linked server
query and it shows that 100% of the cost is the Remote Query. The resultset
is only 260 rows and 5 columns of data. Both servers are SQL2000 and Window
s
2000 OS.
Since both queries ran from query analyzer on my workstation, why is there
such a drastic difference in speed? Also, how do I fix the performance issu
e
for the remote query so that it runs in 15 seconds or less?Brandon Lunt wrote:
> I have a query that when executed against the host server, it completes in
11
> seconds. When I execute the same query against a linked server it takes 1
00
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The results
et
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and Wind
ows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance is
sue
> for the remote query so that it runs in 15 seconds or less?
>
The resultset is 260 rows, but how many rows are in the base table?
What indexes are available? Such a query is going to be slower by
default simply because of the network, but often the query engine can't
determine an "optimal" execution plan for a remote query, and will end
up pulling an entire table across the network, and then filtering the
results on the local side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The base table has aprox 3 Million records. The servers are communicating o
n
a Gigabit network. I am connected to the network with a 100 Megabit
connection. Why would SQL pull the table over the network if the query plan
shows that it was executed remotely?
"Tracy McKibben" wrote:

> Brandon Lunt wrote:
> The resultset is 260 rows, but how many rows are in the base table?
> What indexes are available? Such a query is going to be slower by
> default simply because of the network, but often the query engine can't
> determine an "optimal" execution plan for a remote query, and will end
> up pulling an entire table across the network, and then filtering the
> results on the local side.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
>I have a query that when executed against the host server, it completes in
>11
> seconds. When I execute the same query against a linked server it takes
> 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The
> resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance
> issue
> for the remote query so that it runs in 15 seconds or less?
>
Can you run it through OPENQUERY? This would pass the query text to the
linked server, and just return you the results.
David|||Brandon Lunt wrote:
> The base table has aprox 3 Million records. The servers are communicating
on
> a Gigabit network. I am connected to the network with a 100 Megabit
> connection. Why would SQL pull the table over the network if the query pl
an
> shows that it was executed remotely?
>
The remote "query" is simply indicating that "something" was done on the
remote side. As David suggested, try using OPENQUERY, that will
guarantee that the query is executed on the remote side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I used the following syntax to run the openquery
select * from openquery([linkedserver], 'query string')
same results, 1:37 elapsed time.
"David Browne" wrote:

> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
> Can you run it through OPENQUERY? This would pass the query text to the
> linked server, and just return you the results.
> David
>
>

Remote Query Performance

I have a query that when executed against the host server, it completes in 11
seconds. When I execute the same query against a linked server it takes 100
seconds to run. I have checked the execution plan for the linked server
query and it shows that 100% of the cost is the Remote Query. The resultset
is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
2000 OS.
Since both queries ran from query analyzer on my workstation, why is there
such a drastic difference in speed? Also, how do I fix the performance issue
for the remote query so that it runs in 15 seconds or less?First, why are you using linked servers. In most cases this is not necessary
with RS. You can have multiple datasets against multiple data sources.
SQL 2000 can be very very bad with linked servers. SQL 2005 is much better.
I learned this while working with loading a datamart. If you must use linked
servers then you need to be using Openquery to use it. Do not use the 4 part
naming.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
>I have a query that when executed against the host server, it completes in
>11
> seconds. When I execute the same query against a linked server it takes
> 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The
> resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance
> issue
> for the remote query so that it runs in 15 seconds or less?|||The report I am producing combines data from 3 different servers. I
typically use linked servers so I don't have to put usernames and passwords
into my query strings (not all users have access to all databases so I have
to use a different account). I have usually had good results with Linked
servers (slight performance hit but never this bad). The other linked query
(to the 3rd server) runs in about 2 seconds and returns approximately the
same number or results.
My confusion I guess is why does it not run the same as a query analyzer
client connecting to the server?
"Bruce L-C [MVP]" wrote:
> First, why are you using linked servers. In most cases this is not necessary
> with RS. You can have multiple datasets against multiple data sources.
> SQL 2000 can be very very bad with linked servers. SQL 2005 is much better.
> I learned this while working with loading a datamart. If you must use linked
> servers then you need to be using Openquery to use it. Do not use the 4 part
> naming.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
> >I have a query that when executed against the host server, it completes in
> >11
> > seconds. When I execute the same query against a linked server it takes
> > 100
> > seconds to run. I have checked the execution plan for the linked server
> > query and it shows that 100% of the cost is the Remote Query. The
> > resultset
> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> > Windows
> > 2000 OS.
> >
> > Since both queries ran from query analyzer on my workstation, why is there
> > such a drastic difference in speed? Also, how do I fix the performance
> > issue
> > for the remote query so that it runs in 15 seconds or less?
>
>|||Are you doing this in a stored procedure?
If not, I have seen people post about issues where the query plan created
when a query is executed from RS is different than from query analyzer. As I
said, I haven't seen this but I have seen posts about that.
If you are using a stored procedure then that would not be an issue. If not,
try moving this to a stored procedure and see if that helps.
I have thought of one other thing. Depending on the parameterization, I have
seen in 2000 based on the parameters and the where clause where SQL Server
will decide to bring over all the data from the remote table and process it
locally on the server rather than having the query executed remotely and
bringing back the result. Based on 11 seconds when hitting the server
directly going to 100 seconds, I bet that is what is happening. If you use
openquery this will not occur. With 4 part naming you have to really be
careful.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
> The report I am producing combines data from 3 different servers. I
> typically use linked servers so I don't have to put usernames and
> passwords
> into my query strings (not all users have access to all databases so I
> have
> to use a different account). I have usually had good results with Linked
> servers (slight performance hit but never this bad). The other linked
> query
> (to the 3rd server) runs in about 2 seconds and returns approximately the
> same number or results.
> My confusion I guess is why does it not run the same as a query analyzer
> client connecting to the server?
> "Bruce L-C [MVP]" wrote:
>> First, why are you using linked servers. In most cases this is not
>> necessary
>> with RS. You can have multiple datasets against multiple data sources.
>> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
>> better.
>> I learned this while working with loading a datamart. If you must use
>> linked
>> servers then you need to be using Openquery to use it. Do not use the 4
>> part
>> naming.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
>> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
>> >I have a query that when executed against the host server, it completes
>> >in
>> >11
>> > seconds. When I execute the same query against a linked server it
>> > takes
>> > 100
>> > seconds to run. I have checked the execution plan for the linked
>> > server
>> > query and it shows that 100% of the cost is the Remote Query. The
>> > resultset
>> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
>> > Windows
>> > 2000 OS.
>> >
>> > Since both queries ran from query analyzer on my workstation, why is
>> > there
>> > such a drastic difference in speed? Also, how do I fix the performance
>> > issue
>> > for the remote query so that it runs in 15 seconds or less?
>>|||Currently this is in query form. I tried the openquery and got the same
results as the 4 part linked query. I also tried the openrowset and the time
came down to 57 seconds, but nowhere near the 10-15 seconds I would expect.
Also, the problem with openquery is that I need to pass parameters used by
the query. I didn't see a way to get those dynamically into the query.
I went ahead and created the sp and executed that against the linked server
and it ran in 50 seconds. Better, but still not what I was expecting.
"Bruce L-C [MVP]" wrote:
> Are you doing this in a stored procedure?
> If not, I have seen people post about issues where the query plan created
> when a query is executed from RS is different than from query analyzer. As I
> said, I haven't seen this but I have seen posts about that.
> If you are using a stored procedure then that would not be an issue. If not,
> try moving this to a stored procedure and see if that helps.
> I have thought of one other thing. Depending on the parameterization, I have
> seen in 2000 based on the parameters and the where clause where SQL Server
> will decide to bring over all the data from the remote table and process it
> locally on the server rather than having the query executed remotely and
> bringing back the result. Based on 11 seconds when hitting the server
> directly going to 100 seconds, I bet that is what is happening. If you use
> openquery this will not occur. With 4 part naming you have to really be
> careful.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
> > The report I am producing combines data from 3 different servers. I
> > typically use linked servers so I don't have to put usernames and
> > passwords
> > into my query strings (not all users have access to all databases so I
> > have
> > to use a different account). I have usually had good results with Linked
> > servers (slight performance hit but never this bad). The other linked
> > query
> > (to the 3rd server) runs in about 2 seconds and returns approximately the
> > same number or results.
> >
> > My confusion I guess is why does it not run the same as a query analyzer
> > client connecting to the server?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> First, why are you using linked servers. In most cases this is not
> >> necessary
> >> with RS. You can have multiple datasets against multiple data sources.
> >>
> >> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
> >> better.
> >> I learned this while working with loading a datamart. If you must use
> >> linked
> >> servers then you need to be using Openquery to use it. Do not use the 4
> >> part
> >> naming.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> >> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
> >> >I have a query that when executed against the host server, it completes
> >> >in
> >> >11
> >> > seconds. When I execute the same query against a linked server it
> >> > takes
> >> > 100
> >> > seconds to run. I have checked the execution plan for the linked
> >> > server
> >> > query and it shows that 100% of the cost is the Remote Query. The
> >> > resultset
> >> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> >> > Windows
> >> > 2000 OS.
> >> >
> >> > Since both queries ran from query analyzer on my workstation, why is
> >> > there
> >> > such a drastic difference in speed? Also, how do I fix the performance
> >> > issue
> >> > for the remote query so that it runs in 15 seconds or less?
> >>
> >>
> >>
>
>|||The only way to get a parameter to openquery (that I know of) is to
dynamically create the sql string. You end up having to do lots of messing
with single quotes.
In your stored procedure, are you able to insert into a temp table the
results from each individual query and then join the temp tables?
Also, just so you can see how it is done, here is a an example of
dynamically
select @.SQL = 'insert ' + @.TABLENAME + ' select * from
openquery(linkedservername,''' + 'SELECT * from ' + @.TABLENAME + ' where ' +
@.SYNCDATEFIELD + '> '' + convert(varchar(30),@.STARTDATE,9) + '' and '
+ @.SYNCDATEFIELD +' < '' + convert(varchar(30),@.ENDDATE,9) + '')'
execute (@.SQL)
Note all the single quotes mess. Not too friendly but it is the fastest way
to work with linked tables. In the above I am inserting into a real table
but you could easily have a temp table created that you insert into.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:EDE9A4A4-0992-4B9B-ACA3-2E6A0FB1645B@.microsoft.com...
> Currently this is in query form. I tried the openquery and got the same
> results as the 4 part linked query. I also tried the openrowset and the
> time
> came down to 57 seconds, but nowhere near the 10-15 seconds I would
> expect.
> Also, the problem with openquery is that I need to pass parameters used by
> the query. I didn't see a way to get those dynamically into the query.
> I went ahead and created the sp and executed that against the linked
> server
> and it ran in 50 seconds. Better, but still not what I was expecting.
> "Bruce L-C [MVP]" wrote:
>> Are you doing this in a stored procedure?
>> If not, I have seen people post about issues where the query plan created
>> when a query is executed from RS is different than from query analyzer.
>> As I
>> said, I haven't seen this but I have seen posts about that.
>> If you are using a stored procedure then that would not be an issue. If
>> not,
>> try moving this to a stored procedure and see if that helps.
>> I have thought of one other thing. Depending on the parameterization, I
>> have
>> seen in 2000 based on the parameters and the where clause where SQL
>> Server
>> will decide to bring over all the data from the remote table and process
>> it
>> locally on the server rather than having the query executed remotely and
>> bringing back the result. Based on 11 seconds when hitting the server
>> directly going to 100 seconds, I bet that is what is happening. If you
>> use
>> openquery this will not occur. With 4 part naming you have to really be
>> careful.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
>> news:4CCA53E6-1A11-48C0-A1F8-E60B95C0CC03@.microsoft.com...
>> > The report I am producing combines data from 3 different servers. I
>> > typically use linked servers so I don't have to put usernames and
>> > passwords
>> > into my query strings (not all users have access to all databases so I
>> > have
>> > to use a different account). I have usually had good results with
>> > Linked
>> > servers (slight performance hit but never this bad). The other linked
>> > query
>> > (to the 3rd server) runs in about 2 seconds and returns approximately
>> > the
>> > same number or results.
>> >
>> > My confusion I guess is why does it not run the same as a query
>> > analyzer
>> > client connecting to the server?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> First, why are you using linked servers. In most cases this is not
>> >> necessary
>> >> with RS. You can have multiple datasets against multiple data sources.
>> >>
>> >> SQL 2000 can be very very bad with linked servers. SQL 2005 is much
>> >> better.
>> >> I learned this while working with loading a datamart. If you must use
>> >> linked
>> >> servers then you need to be using Openquery to use it. Do not use the
>> >> 4
>> >> part
>> >> naming.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:D9838B81-C5D7-4DA9-85AE-3E2AF317F391@.microsoft.com...
>> >> >I have a query that when executed against the host server, it
>> >> >completes
>> >> >in
>> >> >11
>> >> > seconds. When I execute the same query against a linked server it
>> >> > takes
>> >> > 100
>> >> > seconds to run. I have checked the execution plan for the linked
>> >> > server
>> >> > query and it shows that 100% of the cost is the Remote Query. The
>> >> > resultset
>> >> > is only 260 rows and 5 columns of data. Both servers are SQL2000
>> >> > and
>> >> > Windows
>> >> > 2000 OS.
>> >> >
>> >> > Since both queries ran from query analyzer on my workstation, why is
>> >> > there
>> >> > such a drastic difference in speed? Also, how do I fix the
>> >> > performance
>> >> > issue
>> >> > for the remote query so that it runs in 15 seconds or less?
>> >>
>> >>
>> >>
>>

Remote Query Performance

I have a query that when executed against the host server, it completes in 11
seconds. When I execute the same query against a linked server it takes 100
seconds to run. I have checked the execution plan for the linked server
query and it shows that 100% of the cost is the Remote Query. The resultset
is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
2000 OS.
Since both queries ran from query analyzer on my workstation, why is there
such a drastic difference in speed? Also, how do I fix the performance issue
for the remote query so that it runs in 15 seconds or less?Brandon Lunt wrote:
> I have a query that when executed against the host server, it completes in 11
> seconds. When I execute the same query against a linked server it takes 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance issue
> for the remote query so that it runs in 15 seconds or less?
>
The resultset is 260 rows, but how many rows are in the base table?
What indexes are available? Such a query is going to be slower by
default simply because of the network, but often the query engine can't
determine an "optimal" execution plan for a remote query, and will end
up pulling an entire table across the network, and then filtering the
results on the local side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The base table has aprox 3 Million records. The servers are communicating on
a Gigabit network. I am connected to the network with a 100 Megabit
connection. Why would SQL pull the table over the network if the query plan
shows that it was executed remotely?
"Tracy McKibben" wrote:
> Brandon Lunt wrote:
> > I have a query that when executed against the host server, it completes in 11
> > seconds. When I execute the same query against a linked server it takes 100
> > seconds to run. I have checked the execution plan for the linked server
> > query and it shows that 100% of the cost is the Remote Query. The resultset
> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and Windows
> > 2000 OS.
> >
> > Since both queries ran from query analyzer on my workstation, why is there
> > such a drastic difference in speed? Also, how do I fix the performance issue
> > for the remote query so that it runs in 15 seconds or less?
> >
> The resultset is 260 rows, but how many rows are in the base table?
> What indexes are available? Such a query is going to be slower by
> default simply because of the network, but often the query engine can't
> determine an "optimal" execution plan for a remote query, and will end
> up pulling an entire table across the network, and then filtering the
> results on the local side.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
>I have a query that when executed against the host server, it completes in
>11
> seconds. When I execute the same query against a linked server it takes
> 100
> seconds to run. I have checked the execution plan for the linked server
> query and it shows that 100% of the cost is the Remote Query. The
> resultset
> is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> Windows
> 2000 OS.
> Since both queries ran from query analyzer on my workstation, why is there
> such a drastic difference in speed? Also, how do I fix the performance
> issue
> for the remote query so that it runs in 15 seconds or less?
>
Can you run it through OPENQUERY? This would pass the query text to the
linked server, and just return you the results.
David|||Brandon Lunt wrote:
> The base table has aprox 3 Million records. The servers are communicating on
> a Gigabit network. I am connected to the network with a 100 Megabit
> connection. Why would SQL pull the table over the network if the query plan
> shows that it was executed remotely?
>
The remote "query" is simply indicating that "something" was done on the
remote side. As David suggested, try using OPENQUERY, that will
guarantee that the query is executed on the remote side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I used the following syntax to run the openquery
select * from openquery([linkedserver], 'query string')
same results, 1:37 elapsed time.
"David Browne" wrote:
> "Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
> news:5FE2384F-B2B0-407B-91F7-7A39ADFBC690@.microsoft.com...
> >I have a query that when executed against the host server, it completes in
> >11
> > seconds. When I execute the same query against a linked server it takes
> > 100
> > seconds to run. I have checked the execution plan for the linked server
> > query and it shows that 100% of the cost is the Remote Query. The
> > resultset
> > is only 260 rows and 5 columns of data. Both servers are SQL2000 and
> > Windows
> > 2000 OS.
> >
> > Since both queries ran from query analyzer on my workstation, why is there
> > such a drastic difference in speed? Also, how do I fix the performance
> > issue
> > for the remote query so that it runs in 15 seconds or less?
> >
> Can you run it through OPENQUERY? This would pass the query text to the
> linked server, and just return you the results.
> David
>
>

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.