How To Use A Debugger On Stored Procedures
Jul 11, 2005Please kindly advise in details how to use a debugger on stored procedures.Thanks.
View 4 Replies
ADVERTISEMENT
T-SQL Debugger Doesn't Allow Stepping Through Stored Procedures No Other Procedure With The Same Name
May 10, 2006T-SQL Debugger Doesn't Allow Stepping Through Stored ProceduresAnd there is no other procedure with the same name owned by dbo or anyother users.There is no error messages also, it just completes procedure andreturns resultserver:Microsoft SQL Server 2000 - 8.00.818 (Intel X86)Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build2195: Service Pack 4) and I don't know how do determine Client'sversion.What should be done (on client site or server site )to fix thisproblem.Thank you
View 12 Replies View RelatedDebugger For Stored Procedure In SQL Server 7.0
Sep 2, 2000Hi
Can anyone let me know if there is any Debugger for stored procedure in SQL Server 7.0, if yes
where can i find one
thanks and regards
Arun
Transact SQL Debugger Not Stopping In Stored Procedure
Sep 5, 2007I am trying to use the Transact-SQL debugger to step through a stored procedure on a server to determine where it is failing. I right-click the stored procedure I want to debug in the Query Analyzer's Object Browser and choose Debug... In the Debug Procedure dialog I fill in the values for the parameters, choose the Auto roll back checkbox, and press Execute.
The T-SQL Debugger screen comes up with my stored procedure and a message on the bottom of Waiting for user input. But I can input nothing, neither click on the icons on top, set breakpoints, or doing anything else, as the stored procedure runs to the end and gives me the return code.
If I subsequently set some breakpoints in my stored procedure and try the Go button. Once again the stored procedure runs to the end without stopping at any breakpoint.
In both cases, the stored procedure shows the waiting icon as it proceeds to the end, so that I can not click on anything to stop it.
I have been told this is may be a permissions issue between myself as a client on my machine and the stored procedure on the server. If so, does anybody know what I the things that have to be done on the permissions side to be able to debug the stored procedure using the Transact SQL debugger.
ARGH!!! Can't Pass Smalldatetime To Stored Procedure In DEBUGGER!
Feb 6, 2004I know this is probably user error, but here's the scoop:
I have a stored procedure...
CREATE PROCEDURE [dbo].[sp_Build_CurrentPortfolio_By_Date]
@NewDate smalldatetime
and when I try to run it in debug in the SQL Query Analyzer, I sent in the parameter via the debug interface as '2004/02/02', and as GETDATE(), and a number of other formats, but cannot even get into the procedure once debugger starts up!
I get the following error once I hit "execute" after putting in the above data in the parameter text box...
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
HELP PLEASE!!! (I'm about to punch my new monitor!!!)
Oracle Stored Procedures VERSUS SQL Server Stored Procedures
Jul 23, 2005I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedStored Procedures 2005 Vs Stored Procedures 2000
Sep 30, 2006Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
All My Stored Procedures Are Getting Created As System Procedures!
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures
Apr 29, 2008How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results
Jun 13, 2007Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies View RelatedHow To Save Stored Procedure To NON System Stored Procedures - Or My Database
May 13, 2008Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
Stored Procedure Being Saved In System Stored Procedures
Apr 7, 2006We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?
Apr 23, 2008Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
SSIS And Stored Procedures Results Stored In #Tables
Mar 26, 2008Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
MS SQL Stored Procedures Inside Another Stored Procedure
Jun 16, 2007Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
Calling Stored Procedures From Another Stored Procedure
May 8, 2008I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)
Nov 1, 2007Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
T-sql Debugger
Apr 22, 1999Anyone know of any third party T-SQL debuggers for stepping through stored procedures? I understand there is one with VB6, but a student in my class doesn't work with VB. TIA.
View 1 Replies View RelatedT-SQL Debugger
Jun 18, 2002Hi,
I've just read in books on line that in Query Analyser there is a debugger tool. Unfortunately I can't work out how to access and use it. Can any one send me some info on this please.
Thanks
Steve
SQL Debugger
Jan 13, 2006Hi All,
Once I open my TSQL Debugger all the icons are disables ( Step Into , Step Over ).
This is the fisrt time I am using it can you please point be in the right direction.
This happens after giving the Input parameters in the Dubug Window for the Stored Procedure
and when I click the EXECUTE button.
NOTE : This happeds when I lauch the Debugger from the ClIENT Machines. If I run the debugger on the
server directly it works fine. Am I missing any of the DLLs
Thx
venu
SQl Debugger ! How
Jul 20, 2005Hi,Could u tell me how to use the debug tool to debug out t-sql statements .With Thanks
View 2 Replies View RelatedOnly Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod
May 15, 2008i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
T-SQL Debugger Problem
Nov 11, 1999I'm trying to get the T-SQL Debugger to work between SQL Server 7 and Visual Studio 6.0.
First of all, I am confused by the various articles I have read in MSDN. Some indicate that the debugger will work in Visual Basic and some indicate Visual Interdev. Does it work in both environments? After following all of the steps listed in the MSDN articles I cannot get the T-SQL Debugger to appear in my Add-Ins window in Visual Basic. Is there something else I need to do?
Secondly, I can see my stored procedures in Visual Interdev and once I open one in the editor the only commands enabled on the DEBUG menu are Clear All Breakpoints, Breakpoints... and Processes.... According to one of the MSDN articles I should see all of the DEBUG menu commands enabled. Any ideas what I can do to get the rest of the menu commands enabled?
Also, I can set a breakpoint in a stored procedure by clicking in the gray area to the left of the line of code where I want the debugger to stop. The red dot appears next to the line of code. But when I execute the stored procedure the debugger asks for the parameters but never stops at the breakpoint. It runs the stored procedure to completion.
I would appreciate any ideas on how to get this working.
TIA.
Jane
Procedure Debugger..
Mar 9, 1999Hello All
Can anyone tell me besttool for debugging the Stored Procedure user
friendly. We are using Powerbuilder/Sql Server 6.5 as back end. we got
lots of stored procedures and I am having tough time in debugging.
I actually moved from Oracle and don't know much about Sqlserver just started
learning.
If anyone tell me best tool,method for doing then I will be very greatful
Thanks In Advance
Sundar
SQL Debugger Problem
Jul 23, 2005I can get the debugger to work: I get a message:"sp debugging may not work if you log as as Local System Account" whileSQL Service is configured to run as a service. You can open Event Viwerto see details." (Viwer means "Viewer" this is not one of my typos...)My text book says that this message means there is a problem that canbe fixed in the SQL Server registration node (just under the servergroup in Enterprise Manager), Properties dialog, Security tab, Start UpService.The book says to enter a valid user account and password in the StartUp Service area. However, there is in my case a "LocalSystem" and anencrypted password entered in this area and and I have no idea howthese got in there.I assume the latter are default settings from when SQL wasinstalled...Can I can this to "sa" and no password? I'm afraid tochange the existing setting if I don't understand what it is and whereit came from..Thank you **very** much for help...I would like to use the debugger.J.
View 1 Replies View RelatedT-SQL Debugger Error
Apr 25, 2007Hi,When I debug our sp by using T-SQL Debugger, I got following error.Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permissiondenied on object 'sp_sdidebug', database 'master', owner 'dbo'.and when I grant the permission of sp_sdidebug, I got following error.Server: Msg 4610, Level 16, State 1, Line 1You can only grant or revoke permissions on objects in the currentdatabase.I am using following statement to grant the permission.GRANT EXECUTEON sp_sdidebugTO myuserHere, I want to clear, myuser is I, which one i log QA.and myuser has public and dbowner permission--Rahul
View 2 Replies View RelatedUsing The Transact-SQL Debugger
Jul 20, 2005I'm using the Transact-SQL Debugger for the first time here.I go to Object Browser, find my stored procedure, right click andchoose "Debug".The "Debug Procedure" window comes up, and I click on the Executebutton.The Debug environment comes up and automatically executes myprocedure. It doesn't allow me to single step through the code.What am I missing here?All of the "step" buttons, "restart", and "stop debugging" buttonsare dimmed out.Thanks.Ken
View 3 Replies View RelatedHow Do I Get Into Transact SQL Debugger?
May 28, 2008How do I get into Transact SQL Debugger?
I am using Visual Studio 2005, and I go to server explorer and make a new connection to a database.
I select a stored procedure and right mouse click.
There is no menu item called "Step Into ... " in the pop up menu.
So what am I missing? Do I have to install or set up something
in order to use Transact SQL Debugger?
Any pointer or answer is greatly appreciated. Thanks a lot.
Chris
Selecting Debugger
Feb 14, 2007hi,
while trying out one function which needs to connect to the database,i got this error:
An unhandled win32 exception occurred in name.vshost.exe[1668].
it prompted me to select a debugger.what does this error mean?
Help With SQL 2000 Debugger
Apr 11, 2007I installed the SQL 2000 trial as a local server on a standalone XP Pro system. When use the debugger, it executes the stored procedure without allowing me to step into the code.
Any help would be greatly appreciated.
Stored Procedures
Jun 17, 2006hi
i need to use only one stored procedure and access many tablesso how write a stored procedure for that dohelp me looking forward for a reply to the earliest i am developing web page using asp.net using c# and sqlserver as backend
looking forward for a replygayathri
Stored Procedures
Dec 15, 2006I am interested to know about stored procedures in Mssql .Can anyone please help me out.
Thanx in advance.
Help With Stored Procedures
Feb 24, 2007Hello I have two stored procedures
@ID INT
AS
SELECT (CASE WHEN NUM >= 10 THEN CAST(PAID AS FLOAT) / CAST(NUM AS FLOAT) * 100 WHEN NUM < 10 THEN 0 END) AS PER
FROM (SELECT (SELECT COUNT(*) AS Expr1
FROM Event_data AS D LEFT OUTER JOIN
Events AS E ON E.id = D.Event_id
WHERE (D.Transaction_type = 1) AND (D.Player_id = @ID)) AS NUM,
(SELECT COUNT(*) AS Expr1
FROM Event_data AS D LEFT OUTER JOIN
Events AS E ON E.id = D.Event_id
WHERE (D.Transaction_type = 1) AND (D.Transaction_value > 0) AND (D.Player_id = @ID)) AS PAID) AS X
and
@ID INT
AS
SELECT P.*,'/' + DBO.GETCHIPFOLDER(@ID) + '/' + ISNULL(P.PHOTO,'BLANK.GIF') AS PIC,ISNULL(
(SELECT SUM(TRANSACTION_VALUE)
FROM EVENT_DATA WHERE PLAYER_ID=@ID AND TRANSACTION_TYPE=1
GROUP BY PLAYER_ID),0) AS WINNINGS FROM PLAYERS P
undefined P
The first returns a percentage for player wins, the second gives me a photo and sums the player winnings
I would like to combine the results so I can get the percentage and wininngs in one query, in another matter all together I would like create a procedure like the first but instead of returning only one player, I would like to return the percentage for each player
Thanks in advance for any light you can shine on this.