Help! Debugging Stored Procedure From Query Analyser - Step Through Disabled
Jan 5, 2004
Can anybody explain how to do debug a stored procedure from SQL Query Analyser.
When i tried opening Query Analyser and pressing F8 i am able to see Object Browser on left side, i selected the d/b and expanded it then i selected a stored procdure by right click of mouse. I selected "Debug".
It shows me alert msg "SQL Debugging may not work properly if you log on as 'Local System Account' while SQl server is configured to run as a service. You can open Event Viewer to see details." DO U WISH TO CONTINUE- I selected "YES"
I am able to see 3 split windows on right side and GO, Toggle, Untoggle are enabled BUT Step Into, Step Over, Step Out...Stop debugging are disabled at menu bar.
The 1st right split window shows the proc code, 2nd split window shows Local-Global-Callstack none of them shows any values(blank), 3rd split window shows records(result) and
@RETURN_VALUE = 0 message
I had Toggled at each and every line of the procedure in 1st split window still it doesnt respond anything.
What might be the problem, how to solve it do i need to give any permissions.
i tried logging from wind Authentication and also from Sql Authentication (sa/sa), still same problem occurs. By the way i am using SQL Server 2000.
When I run this I got some errors: Server: Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax near 'GO'. Server: Msg 170, Level 15, State 1, Line 5 Line 5: Incorrect syntax near 'GO'. Server: Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near 'GO'. Server: Msg 111, Level 15, State 1, Line 8 'CREATE PROCEDURE' must be the first statement in a query batch. Server: Msg 137, Level 15, State 1, Line 12 Must declare the variable '@pIndex'. Server: Msg 178, Level 15, State 1, Line 15 A RETURN statement with a return value cannot be used in this context. Server: Msg 170, Level 15, State 1, Line 16 Line 16: Incorrect syntax near 'GO'. Server: Msg 170, Level 15, State 1, Line 18 Line 18: Incorrect syntax near 'GO'.
I am trying to use debug option for a stored procedure from SQL Query Analyser and I get the 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 TRAX2STAGE (Error = 0x800706ba). Ensure that client-side components, such as SQLDBREG.EXE, are installed and registered on WKVKARAMCHED. Debugging disabled for connection 80.
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
I've got a stored procedure which does a database search for me. It is working fine, but for some reason it isn't finding what I expect. I think the problem is that the vairables are not being passed as I expect but I cannot work out how to see the intermediate SQL in the stored proc... in oracle there used to be some sort of set feedback on option which meant that all SQL which was being run was shown... is there some way of doing this in SQL Server 2005?
I'm using SQL Server 2005 Express and I need to debug my stored prodedure. I've been reading online that it can be done in the Express edition but no one explains how to do it. There's plenty of instructions online on how to debug stored procedure in the other versions of SQL Server 2005. Do any of you know how to do it?
1/ I create stored procedure in Query Analyser using: ALTER PROCEDURE dbo.unshippedtotal @name char, @ytd int output AS select * from vwaccount select @ytd=sum(AccAccnnmb) from vwaccount return GO
But When I try to expand the list of stored procedures under the DB and the server name, I can t see my Stored Proc called "unshippedtotal"
Any ideas
2/ Another question pls: Can we use Query Analyser to do the same tasks we do with Entreprise Manager like creating and modifying tables, Creating Stored Procedures, Modifying Views... (I m more familiar with Entreprise Manager but somebody told me it s better to use Query Analyser)
In VS 2005 with SQL Server Express: How do I debug a stored procedure that is called from a object datasource control while the asp.net code is running? Thanks
I'm having trouble using VS2005 to remotely debug stored procedures in SQL Server 2005.
After setting a breakpoint, when I select "Step into Stored Procedure", I am NOT prompted to enter parameter values. I'm taken straight to the debug window, which is titled "Running" but nothing is happening. The breakpoint icon changes to a translucent icon with a yellow exclamation mark. The mouseover tooltip says:
"The breakpoint will not currently be hit. Unable to bind SQL breakpoint at this time. Object containing the breakpoint not loaded".
If I choose "Exceute" instead of "Step into Stored Procedure", I'm prompted to enter parameter values and everything works as expected.
I am not sure if this thread needs to go into this forum or there is one for C# stored procedures/CLR Database Objects.
To All:
Is this a known issue that when debugging CLR stored procedures the VS 2005 freezes even before reaching into the stored procedure? I can restart my computer and debugging works for a while and then it starts to take forever when I step into (F10) during debugging. I am running only one instance of sql server, and even that of my local machine as I have never been able to debug on a remote server.
This random and unstable behavior of the debugger is proving very unproductive and costly. Is there a microsoft update/fix/patch to make debugging CLR stored procedures a smooth process?
I cannot seem to get this to option to come up without having the full blown studio installed.
Is there something else that needs to be configured on the database before having this option enabled? I have enable clr (which is semi-unrelated to good ol' fashion stored procedure debugging) in the surface area configuration manager, but still no luck.
I am using SQL Server 2005 Standard Edition. I can get this feature working w/ no prob when I have the full blown Visual Studio Professional version installed, but not with just SQL Server - Futher insight would be helpful.
Hi ,I am using Visual studio 2005 with sql server 2005. I want to debug my stored procedure, which is situated on the server on the network(accessible through network share). I followed the following URL: http://aspnet.4guysfromrolla.com/articles/051607-1.aspxI have used Direct database debugging : When I right click my stored procedure and click 'step into stored procedure', I get the following error: "Unable to start T-SQL debugging. could not attach to SQL server process on 'sql_server_name'. The remote procedure call failed and did not execute" I am using windows authentication to login to sql server Any help?
I need help debugging a CLR stored procedure that is being called from an SSIS package. I can debug the procedure itself from within Visual Studio by using "Step into stored procedure" from Server Explorer, but really need to debug it as it is being called from SSIS.
I need help debugging a CLR stored procedure that is being called from an SSIS package. I can debug the procedure itself from within Visual Studio by using "Step into stored procedure" from Server Explorer, but really need to debug it as it is being called from SSIS.
I am running a stored procedure as a job step and in the stored procedure I use return to pass one of several possible values when there is an error in processing (not an system error) so that the job step will fail. However, even when I return a non-zero value using return the job step completes as successful. What should I be doing so that the job step picks up the non-zero value and then indicates the step failed?
When I start my stored procedure from Query Analyzer it ends in 1 h. I have created a job consisting of 1 step - the same procedure. If I start the job from Enterprise Manager it ends after some 10 h. What can I do to get the same performance?
I couldn't debug SQL Server by "Step into Stored Procedure" on Server Explorer of VS2008 (or VS2005) to SQL 2005 Developer on remote Windows Server 2003 machine, it allway issue exception "Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'Server'. Click Help for more information"
1) The environment: The Client: Windows XP SP2 (WORKGROUP) Visual Studio 2008 (or VS2005)
The SQL Server Machine: Windows 2003 Server Sp1 (DOMAIN) SQL Server 2005 Developer
2) User account and Permission login: I create the same user account for both Client and Domain Server with the same password, i also add that user to "Administrators" group in both machine.
At the SQL Server on Server machine, i added that account to ServerSecurityLogin with 'sysadmin' role already
3) Connection and authentication: I used "Windows Authentication" for my connection to SQL server, and i checked sure my account of the connection by SQL command
SELECT SYSTEM_USER, IS_SRVROLEMEMBER ('sysadmin')
4) Firewall: I checked firewall like MSDN helping (i also tried to test by turn off firewall in both machine)
5) Visual Studio Remote Debugger: I read "How to: Enable SQL Server 2005 Debugging" on MSDN with comment "The SQL Server can run on the same machine as the application or on a remote machine. If you are debugging T-SQL code only, then no remote setup is required." so i didn't config Visual Studio Remote Debugger any thing.
Note: If i "Step Into Store Procedure" at Server locally, it works okey, so on at my PC client locally. But if i move debugging from my client to my Server, it occur error "Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'Server'. Click Help for more information"???
If i execute store procedure on Server Explore, it works okey!
-- Get the new Customer Identifier, return as OUTPUT param SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned -- users. IF @FK_UserIDList IS NOT NULL EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record -- Retrieve Address reference into @AddressId -- EXEC spInsertForUserNote -- @FK_UserID, --@NoteID, -- @BeenRead -- @Fax, -- @PKId, -- @AddressId OUTPUT
COMMIT TRANSACTION
-------------------------------------------------- GO
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
This what i did , since i need to maintain five sql servers ,i thought i will build a repository so on my desk top (running sql server ) i created a table name master_dbscript with the following fields
server_name varchar(20), dbname varchar(20) db_create_scripts text
using enterprise manager-- all tasks --generate sql scripts , (cut & paste to the insert statement in query analyser, the following is the insert statement
insert into master_dbscript values ('isd11t','test','ALTER TABLE [dbo].[child] DROP CONSTRAINT FK_child_parent GO /****** Object: Trigger dbo.test_patcase Script Date: 25/08/2000 12:10:09 ******/ if exists (select * from sysobjects where id = object_id(N'[dbo].[test_patcase]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)drop trigger [dbo].[test_patcase] GO ')
oops it created all the objects in the database where i tried to run the insert statement. god saved me , i tried this with the test database.
when i tried the same with bcp it worked fine and i was able to see the record in my table (one record) ,note you cannot use dts because it will support maximum 8000 chars only .
I am executing the following query in the query analyser.
"select * from alien119700 order by alienid"
In the message pane it shows
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 4 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
(43 row(s) affected)
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 454 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
We use SQL 2K(service pack 1).Our query analyser will freeze often.So we loose all query production work.Does anyone know if Version 2 has a fix?Please help.
I noticed that query analyser is much more quicker than EManager when I access my database from my hosting provider... is there any way to see the properties of the table X for example as one can do with EM...
I would be grateful if you could provide me with any query sample conserning this issue...
