Debugging stored procedures in Visual Studio

Question

Debugging stored procedures in Visual Studio

James Hoagland

Grasshopper

Points: 14
More actions
September 15, 2005 at 9:25 am

#165320

Can any one give me a hint as to what needs to be done to allow debugging of stored procedure via Visual Studio? I'm getting the message: 'Cannot debug stored procedure because the SQL Server database is not set up correctly or user does not have permission to execute maste.sp_sdidebug.'
I can use the Visual Studio debugger on Database A. I created database B via restore of a backup of database A, yet I get the upbove message when trying to debug. I suspect there's a simple setting I need to change, but don't know what it is.
Any advice greatly appreciated!
Jim

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Casey Christensen Old Hand Points: 364 More actions · Answer 1

Does the user your program is using for the connection have execute permissions on the stored proc? (If you're not sure what user you're connecting as, test by giving everyone exec rights to that sp, just long enough to see if the debugger works.)

Two muffins are sitting in an oven. The first one turns to the second and says "pretty hot in here, huh?"
The second muffin glances at the first and then shrieks in fear,
"AAAAAAHH!!! A TALKING MUFFIN!!!"

James Hoagland Grasshopper Points: 14 More actions · Answer 2

Thanks for the reply (especially the second part!)

Yup, we've got execute rights, so it's something else.

Casey Christensen Old Hand Points: 364 More actions · Answer 3

Is the sp supposed to be in your master database?

Two muffins are sitting in an oven. The first one turns to the second and says "pretty hot in here, huh?"
The second muffin glances at the first and then shrieks in fear,
"AAAAAAHH!!! A TALKING MUFFIN!!!"

Gary Johnson-259336 SSCommitted Points: 1673 More actions · Answer 4

Are you running XP? And do both databases run on machines that have SQL SP4 installed? I found I have a problem when running XP against a server that is using SQL SP3. I get the same errors.

Make sure to fully test SP4 before installing on a production box! I've had problems with linked servers after installing it going from a 2000 box to a 2003 box.

Gary Johnson
Sr Database Engineer

dbuchan SSCrazy Points: 2328 More actions · Answer 5

You may already know this but have you read the help for "Setting up SQL Debugging" ? There is quite a lot of info there.

DB

The systems fine with no users loggged in. Can we keep it that way ?br>

bledu SSChampion Points: 12335 More actions · Answer 6

you have to be running the sql server under a user defined user and not the local system account to be able to debug your stored procs.try to run the sql service using your windows user account and password.

Everything you can imagine is real.

CMitesh Say Hey Kid Points: 674 More actions · Answer 7

Hi,

What i have read so far is you can only debug stored procedure in visual studio's enterprise edition and not in any other version.

Mits

barsuk SSChampion Points: 12280 More actions · Answer 8

Apply SQL Hotfix 944 on Server.

Turn off your Firewall. Or add exception (add ports) to it which are related to your machine/remote machine using Stored Proc.

Also in the Firewall Settings exclude the program C:\Program

Files\Common Files\Microsoft Shared\SQL Debugging\sqldbreg2.exe and Port 135

bledu SSChampion Points: 12335 More actions · Answer 9

Mits

Yap you can only debug stored procs in vs enterprise edition only, but you can also use query analyser to debug stored procs.

in QA right click on the stored proc and choose debug.

i think that is the functionality you want.

Everything you can imagine is real.