Hi,

I am using VS 2008 and trying to step into a stored procedure on a SQL Server 2005 database. I bring up Server Explorer, double-click on the procedure. It comes up in the text window. I set a break point in it, right click and select "Step into stored procedure". It comes back with "canceled by user". Can you tell me what I am doing wrong ? This is the 1st time I've tried to do this.

Thank you,

Tom Vicker

we have been investigating this issue and was able to repro this in one my machine.
OS: Vista SP1
VS2008 targetting SQL server 2005

Turning ON the file sharing did the trick for me, Stepping in to stored procedure worked.
To Turn ON the file sharing in VistaControl Panel ->Network and sharing center
On Sharing and discovery TURN on the File sharing. You need to be administrator to change this setting



Try this step and let us know if it resolves the issue.

Karthik

Hi Tom,

Could you check for the things below and get back with your findings?

1. Ensure VS is running as an Administrator. The account must be an Admin on the machine running SQL Server 2005.

2. The account used to make the connection to SQL Server must be an Administrator account as well. If you specifiedWindows Authentication when creating the connection and are doing (1) then this is true.

3. Ensure that there are no "dead" connections in Server Explorer i.e. connections to servers that aren't around etc. Easiest is to have only one connection.

4. Verify that executing the stored procedure works fine.

Azeem Khan

VS Debugger.

Hi Azeem,

Thanks for response. Here are my responses to your checklist:

1. The user that VS is running under is in the Windows Administrator group on the PC. I also issued the following command on SQL Server (mhf1\tsvicker is the windows id I am using)

sp_addsrvrolemember 'mhf1\tsvicker', 'sysadmin'

2. I connected to SQL Server from VS using the system admin account (sa)

3. There is only 1 connection in Server Explorer, which is the one I am trying to use.

4. The stored procedure works fine.

I also connected with Windows Authentication and I still get "canceled by user". So the failure happens whether I am using Windows or SQL Server authentication.

It does make sense that it might be permissions related, although I wish there were more info in the error message. Is there more I can do check the permissions ? Is there any way I can get more diagnostic info to tell me what's wrong ?

Thanks,

Tom V.

Tom,

I assume in this case that SQL Server 2005 is running on a remote machine. SQL Server debugging requires that DCOM work between the two machines involved. Could you verify this? One way to check thisis totry andattach to a managed process on the SQL Server machine from the VS machine? This requires DCOM to work. You can launch msvsmon on the SQL Server machine by doing a net use back to the VS machine.

It is not clear if the account mhf1\tsvicker is an Admin on the SQL Server machine. It will have SA role if it is in the local Adminstrators group. Was eithermachine renamed or moved from one domain to another? Anything else that could affect DCOM communication. I assume both machines are on the same domain.

As for the error message, it is definitelynot by design. It isn't something we ran into in our testing unfortunately.

Azeem Khan

VS Debugger.

Hi Azeem,

I did not have Remote Debugging installed on the remote SQL Server machine. I installed it as a service with the tsvicker account which is in the administrators group on both the SQL Server machine and the machine I am trying to debug from (the VS machine). The service started successfully. I tried to step into the stored procedure and it still responded with "canceled by user".

I then executed a "net use isco1dp0858" which is my machine name. This is the response I got:

C:\>net use \\isco1dp0858
The command completed successfully.

C:\>net use
New connections will be remembered.

Status Local Remote Network

-------------------------------------------------------------------------------
Disconnected Z: \\Isco1dp0858\EDrive Microsoft Windows Network
OK \\isco1dp0858\IPC$ Microsoft Windows Network
The command completed successfully.

I am not an administrator and I do not use the "net" command normally. Does this look correct ?

Anyway, I tried to step into the stored procedure again and still got "canceled by user".

Can you provide further help ?

Thanks,

Tom

Tom,

Could you trya remote native or managed attach and not TSQL debugging?

Launch notepad on SQL Server machine. Then from VS machine do Debug -> Attach to Process. In the Qualifier specify the remote machine name.notepad.exe which was launched should show up. Select itand do an attach and see if that works.

Also was the VSmachinerenamed or moved from another domain etc. i.e. anything to affect DCOM working. Thanks.

Azeem Khan

VS Debugger.

Azeem,

I could not attach to notepad as you suggested until I switched to using VS 2005 (instead of VS 2008). Then it worked. I saw the activity on the Remote Debugging monitor window on the SQL Server machine.I then tried to step into the stored procedure by connecting to SQL Server. This time I did not get "canceled by user". Instead I got I message box with "Unable to attach to T-SQL Debugging. Could not attach to SQL Server process on 'co1msv051-dev'. So with VS 2005 I got further, but it is still not fixed.

When I was struggling with getting to attach to Notepad with VS 2008, I tried configuring Remote Debugging as a service as well as running it manually under mhf1\tsvicker after sharing folder "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\Remote Debugger\x64" (which contains msvsmon.exe). I verified that I could access this folder from the VS machine. In both cases I got error "Unable to connect to the Visual Studio Remote Debugging Monitor named 'co1msv051-dev'. The Microsoft Visual Studio Remote Debugging (MSVSMON.EXE) Monitor Does Not Appear to Be Running on the Remote Computer". I also tried configuring msvsmon to allow connections from any computer vs just from the subnet. Still no change.

So I made progress, but still not there. It is interesting that VS 2008 behaves differently.

BTW, I checked that both machines are in the same domain mhf1. Also, the SQL Server machine is 64-bit and the VS machine is 32-bit. msvsmon is running in 64-bit mode.

Have I proven that DCOM is not the reason it is failing ? Any other ideas ? Thanks for sticking with me on this.

Tom

It sounds like the remote debugging monitor on your SQL server box is VS 2005 remote debugger. Can you try installing the VS 2008 remote debugger?

I tried it using the VS 2008 remote debugger on the SQL Server machine. The behavior is getting even more strange. When I tried to step into the stored procedure (from within VS 2008 on my PC) I got the same message that I mentioned in my very first post (canceled by user).

So then I started notepad on the SQL Server machine and tried to attach to notepad from VS 2008 on my PC. This worked. I was able to attach to notepad.

So then I thought I would try stepping into the stored procedure again. To my surprise, the stored procedure started to execute. The dialog box for the parameter values appeared. I entered values. But instead of it hitting my breakpoint I got message "T-SQL ended without debugging. You may not have sufficient permissions to debug". But even though it didn't hit my breakpoint, the procedure executed successfully.

What is really strange is when I try to step into the stored procedure I get "canceled by user" until I attach and detach to notepad on the SQL Server machine. Then, after that, when I step into the stored procedure it begins to execute, but doesn't allow me to use the debugger.

Once again, my windows userid is in the administrators group on both machines.

Tom

This is a shot in the dark, but msvsmon (the remote debugger) may be terminating early.

Could you try launching notepad, and attaching with a different debugger instance? This will keep msvmon running on the SQL server machine.

Then while that debugger is attached, try stepping into your SQL stored procedure.

Oh and you have to use the 2008 remote debugger with VS 2008. They are not compatible. And to be safe, I'd uninstall the 2005 remote debugger from the SQL server box. Perhaps it is using the 2005 remote debugger whenever you do SQL debugging.

We are experiencing the same problem:

Outline:

CLIENT: Windows XP Developer Machine with Visual Studio 2008 Team Suite (VSTS)
Connecting to
SERVER: Windows 2003 Server with SQL Server 2005 and VS2008remote debug tools installed.

From the CLIENTVSTS we can"Attach"the debugger to the SERVER
We have attempted and succeed attaching to the SERVER notepad.exe as previously suggestedin this post.

Selecting the option "Step into Procedure" from Client VSTS we get the error "Canceled by user."

Any help you can lend would be appreciated!

As you suggested I tried stepping into the stored proc in a 2nd instance of VS (the 1st instance being attached to notepad on remote SQL Server machine). Unfortunately there was no change. I tried this both with VS 2005 and VS 2008 (being consistent with the same VS on both ends).

I can't afford to spend more time on this right now. Hope to resume trying in a few days. I think I will try on a different SQL Server machinenext.I am beginning to think there is some unusual environmental issue with my current configuration, although its clear I am not the only one with this problem. Thanks for your help to this point.

It is also possible that windows firewall might be interfering with the communications between VS and the sql server. In order for sql debugging to work, you need to have an exception turned on for visual studio.

Also, make sure the scope of the exception allows communicating with the sql server. For example, if your sql server is not on you local subnet and the exception rule only applies to your local subnet, sql debugging won't work.

When you say "In order for sql debugging to work, you need to have an exception turned on for visual studio", do you mean any exception or a specific exception ?

Also, I don't understand "make sure the scope of the exception allows communicating with the sql server".

I am not familiar with writing exception rules in VS. I would appreciate more of an explanation or a reference to the doc I need to read to do this correctly.

Thanks,

Tom

One possibility for why sql debugging isn't working is that Windows firewall could be blocking VS from communicating with the sql server. In order for VS to be able to do sql debugging you either need the firewall to be turned off or you need an exception entry for the firewall that says to not block VS from accepting incoming connections.

By exceptions, I was referring to windows firewall exceptions, not Visual Studio exceptions. You can configure them in control panel->windows firewall (you need to be an admin on your local machine). Look for a check box that says something like "Microsoft Visual Studio".

If the firewall exception is there, check the full path and make sure it refers to Visual Studio 2008, as opposed to Visual Studio 2005. Also, firewall exceptions havea scope associated with them that lists a range of remote ip addresses for which the exception is valid. For example, if the firewall exception has a scope that applies only to the local subnet and the sql server isn't on you local subnet, sql debugging will not work even through the exception is there and turned on.

Also, if you are behind a router and the sql server isn't, it is also possible that your router might be blocking the sql debugging, independent of the settings on your computer. If you are using a router, try plugging your computer directly into your modem, bypassing the router, and see if that works.

I checked my firewall. It was off. I also added VS 2008 to the Exception list in Windows Firewall -> Exceptions and checked the box (on themachine running VS). I set the scope to all computers, not just my subnet. The full path to VS 2008 was specified. Still no change. Also, I am not using a router. This is a corporate intranet. Thanks again for your suggestions.

This thread is running ridiculously long.

I face the same problems, but...

I am totally inexperienced with some of the aspects here, like DCOM.

Here's my setup:
Windows2003 Server running SQL2005

Vista Business Laptop running Microsoft SQl Server Management Studio

SQL Server Business Intelligence Development Studio 2005 - doesn't have the optiuon to step int a procedure. Why??

SQL Server Business Intelligence Development Studio 2008 does have the option, but I get "Canceled by user".

Isn't there a third party tool that can do what this release botched-up? In SQL2000 it was a bit of work to get it running, but it would run.

Cees Cappelle

I have another scenario that cause the "Canceled by user".

The sqlserver service is configured login with a account(not 'LocalSystem'), and this account's password is expired. Then step in to procedure, the VS output "Canceled by user"

just for your infomation.

Hi,

Tell me please, will I get Remote SQL debugging working if I have SQL Server 2008 Express running on SERVER (under LocalSystem) and this server has admin account Godfather and Visual Studio 2008 TS running on ME and my machine has the same user Godfather ?

I mean I have SERVER\Godfather and ME\Godfather. Does having this account named equal gives the ability to connect to remote SQL server with Windows Authentication ?

I'm asking because now I have output 'Canceled by user' after running Debug from VS with successfully opened Data Connection via Sever Browser. Also I'm not able to start debugging from SQL Managment Studio with error 'Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'SERVER'.

we have been investigating this issue and was able to repro this in one my machine.
OS: Vista SP1
VS2008 targetting SQL server 2005

Turning ON the file sharing did the trick for me, Stepping in to stored procedure worked.
To Turn ON the file sharing in VistaControl Panel ->Network and sharing center
On Sharing and discovery TURN on the File sharing. You need to be administrator to change this setting



Try this step and let us know if it resolves the issue.

Karthik

local debugging definitely works

remote requires verification of DCOM setup

and remote 2008 debugging service set up.

I was able to resolve this issue by setting the "Log on as" account for SQL Server (MSSQLSERVER) to the server's Administrator account. For reference, my complete setup is as follows:

1. Client: Windows XP with VS Studio 2008 (9.0.30729.1), Server: Windows Server 2003 with SQL Server 2005 (9.0.4035)

2. Both computers in the same workgroup

3. Administrator account on both machines enabled and set to same password.

4. MSSQLSERVER and MSVCMON (running as a Service) run in the context of the Administrator account.

5. Visual Studio 2008 run in the context of my account (member of Administrators group) on the client machine. The same account was created on the server (member of Administrators group) and the password is the same on both.