September 21, 2005 at 2:38 pm
I am trying to debug a stored procedure from my desktop machine and where the actual stored procedure resides on a Windows 2000 Server on our LAN. However, I get the following error:
Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger on SIS2SERVER (Error = 0x80070005). Ensure that client-side components, such as SQLDBREG.EXE, are installed and registered on SIS2XPTEST. Debugging disabled for connection 58.
For my desktop, I am using XP Professional SP2 and the database is SQL Server 2000 SP1
Any help would be greatly appreciated!
September 22, 2005 at 9:44 am
I believe you may have a couple problems. The first would be thatyour local computer configuration, SQL Server SP1, is badly outdated. Service pack 3 should be your bare minimum for SQL Server 2000. The second would be the your permissions on SIS2SERVER which I'm assuming is the server on your LAN. First update to SP3 and check whether the problem persists. I expect it will but your error even states you may not have the correct files on SIS2XPTEST. Next search for sp_sdidebug in BOL and read the article on troubleshooting the debugger. If you fail with that then search the Microsoft Knowledgebase which has more information. It's always best to try to locate the answer on BOL first, the knowledgebase second, and forums third.
September 22, 2005 at 10:12 am
I apologize, the SQL Server is running SP3a. I will check with BOL.
September 22, 2005 at 4:07 pm
You know what, I recently have come across the same issue and so far none of the troubleshooting I have tried has worked. I'll be interested in reading these posts. Here is my scenario:
I have two WinXP Pro SP2 machines. One has a local instance of SQL 2000 SP3. The production server (W2K SP3) runs SQL 2000 SP3 as well. One day about a month ago, for no apparent reason ,the production SQL server started throwing the same error at me, despite the fact I had been debugging sprocs on it earlier in the day. I swear I didn't install anything on my PC, I checked the windows automatic update log as well. I checked with the network administrator and nothing was installed on the production server either.
What's even more interesting is that I can debug the local server sprocs just fine. Then I went over to the other PC, without an instance of SQL Server but with the Client Tools. I was able to debug both the production and my "local" test SQL Server without a hitch.
So.........I put SP3A on the local SQL Server and had our administrator put it on the production SQL server. Sproc debugging lasted about a day on the production server and now is is throwing the same error again, but only against the sprocs on the production server. I'm confused!!!!
Linda
September 22, 2005 at 6:43 pm
I tired everything as stated in BOL and still nothing seems to work. Any further suggestions?
September 26, 2005 at 10:32 am
See if it works:
Apply SQL Hotfix 944 on Server. If client doesnt have SP4 installed, install hotfix 944 too.
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
September 26, 2005 at 1:27 pm
SP4 installed on both server and client. sqldbreg2.exe added to client personal firewall exceptions list and port 135. Still cannot remotely debug. Any other things to check?
September 26, 2005 at 1:32 pm
Grant permissions to the sp_sdidebug stored procedure.
http://msdn.microsoft.com/library/?url=/library/en-us/trblsql/tr_servtools_5cfm.asp
A.J.
DBA with an attitude
September 26, 2005 at 1:36 pm
On server or client?
September 26, 2005 at 1:38 pm
In the master db on the server.
A.J.
DBA with an attitude
September 26, 2005 at 1:40 pm
I am using the sa username and password to connect to the database instance? Do I still need to grant permissions on sp_sdidebug on the master db on the server? Would this be the command?
GRANT EXECUTE ON sp_sdidebug TO sa
September 26, 2005 at 1:44 pm
I wasn't aware that you were connecting as sysadmin. sa has permissions already (obviously). You may want to review the link in the previous post for further suggestions.
A.J.
DBA with an attitude
September 27, 2005 at 12:32 pm
So did you apply hotfix 944 as I suggested earlier? Try to turn off Firewall after that. Let us know.
September 27, 2005 at 12:39 pm
I thought that SP4 inlcuded "hotfix 944". Even if I applied SQL Server 2000 SP4 to both the server and client, do I still need to apply the fix? Where can I get hotfix 944? Is there a URL, web form, e-mail address, etc.
September 27, 2005 at 1:21 pm
Actually you are right. I have Window 2000 and SQL Server SP3A on a remote server (debugging from XP SP2), that's why I got hotfix 944 from MS PSS. Anyway, here are a couple of links:
http://support.microsoft.com/default.aspx?scid=kb;en-us;841249
http://support.microsoft.com/default.aspx?scid=kb;en-us;833977
Also, If you have the Firewall turned off and have a debugging session running on the Windows XP (Client) machine, you should be able to run
netstat –an > netstat.txt from a command prompt and see which ports are open between the Client machine and the Remote Server machine. These should be the ports that need to be opened on the Firewall to allow traffic through.
P.S.It is considered a bad practice to run Debugging on Prod Servers
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply