Hi,
In SQL 2000, to debug a stored proc I would launch QA, right click and hit debug.
How do I accomplish this with SQL 2005. I can't see that it came with QA.
Thank you,
Steve
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies View RelatedIn SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".
You can then step through the SP one line at a time.I don't see this in SQL Server 2005 and have searched bu cannot find any documentation on what happened to this feature.What did Microsoft do with this?
In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".
You can then step through the SP one line at a time.
I don't see this in SQL Server 2005 and have searched but cannot find any documentation on what happened to this feature.
What did Microsoft do with this?
Hi,
I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this.
We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index.
Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement?
Thank you,
Michael Tzoanos
Say I have this query in my stored procedure. The problem I am facing is @OrgUID contains no value, so I put these 2 lines to debug. Am I using the right way to debug? I couldn't see the value of @OrgUID using this way. OrgUID is the PK in Organization. INSERT INTO Organization (OrgName) VALUES (@DealerName)SELECT @OrgUID = @@IDENTITYprint @OrgUIDreturn
View 4 Replies View RelatedHi,
I have Visual Studio 2008 installed on my PC. I had previously bought SQL Server 2000 Developer Edition. I installed the same and created a database and some stored procedures. I attempted to use the "Debug" feature query analyzer to test my stored procedures but received an error msg saying that "You must use SQL server management tools 2005 to connect to this server". I next downloaded and installed the express edition of SQL Server 2005, thinking that this would solve my problem. It didn't. Before I go any further I thought I'd ask if someone knew what was going on.
Thanks
I am trying to debug sql2000 sp from managed code app with VS.Net 2003 archetect Ed..
It did not stop at the break point within the sql sp.
I did granted execute permission for sp_sdidebug.
Do I need to attach any process?
Is there anything left off by the article?
I referenced msdn article option 2: http://support.microsoft.com/default.aspx?kbid=316549
Thanks.
I'm currently updating one of our web sites and have encountered astrange problem. The page that is giving me the problem is written inASP and hits a SQL 2K DB. When I click submit I have 4 procs thatneed to be executed. I always get a sql server timeout error. Iopened up Profiler and traced the events. The page hangs on the lastproc called which is basically a select * where id = @id. If I runthe same proc that hangs in query analyzer, literally copy fromprofiler into query analyzer the proc runs no problem but when I'mhitting it from the web it hangs. The proc takes in 1 argument whichI see when I view the trace in profiler. The id getting passed to theproc is correct. I don't think this is a web problem because the COMobject that runs the last 2 procs works on other productionapplications with no problems. I was wondering if anyone has anysuggestions. I'm not a dba but would like to know what the problem isfor future reference. I pasted the proc below just for the hell ofit. FYI, I changed the name of the proc, table and columns forsecurity purposes.CREATE PROCEDURE [dbo].[spName]@TNum integerASSELECT M.*FROM tblName MWHERE (M.[idColumn] = @TNum)GOThanks in advance,Bob
View 3 Replies View Related I'm having some problems debugging SQL Server stored procedures on a SQL Server 2005 server. I have installed Visual Studio 2005 on a workstation running Windows XP, now I'm trying to debug a ASP.Net web application that has some code that executes the stored procedures on a Windows 2003 Server running SQL Server 2005.
I opened VS2005 ... created a connection to the SQL Server 2005 instance ... open the Stored procedure ... right click the stored procedure name and selected Step into Stored Procedure and the following message is displayed:
Unable to start T-SQL debugging.Could not attach to SQL Server process on 'ServerName'.
Any ideas.
Thanks,
Dear Sir,
I am from ASP.NET back ground now I am working in SQL Server 2005. I want to debug my stored procedure after coding end. In SQL 2000 there is an option of debug right click on the stored procedure but No such option present in SQL Server 2005 so I am unable to debug my Stored Procedure when any error occur.
Can you please guide me?
Regards
Sandeep Mishra
Bangalore
I am trying to debug the SQL Server Stored Procedure from .net 2005.
The process is as :
After setting Breakpoints at SP , it cant stop at that break point.
If anybody konws please write in detail.
Shyam.
hello world,
how cani debug a stored procedure under sql server 2005?
Hi all,
I really need to know that
" How to debug stored procedure in SQL server 2005 ?"
please help me to solve my problem ?
regards
sujithf
I want to know if I can debug stored procedures remotely with the SA account.
I can not have Active Directory in my system right now.
Thanks
1). When you right click stored procedure in the Query Analyzer on 2000 you can select debug from the list of menues.
I can't find this functionality in the SQL Server Management studio.
2). By the way I also can't find where went the output of my print statement.
3). Does server cursor functionality is still working in 2k5?
OS: Windows XP Pro, SP2
SQL: SQL Server 2005 (Server 9.0.2047) SP1
VS: Visual Studio 2005 RTM.050727-4200
--------
After upgrading our SQL Servers to SP1 we have lost the ability to debug .net CLR stored procedures. The debugging process worked fine until we installed the Service Pack. All other features we use test out fine after the upgrade. The only and very important feature that does not work is debugging CLR procs. In fact this problem is happening in every environment we have. Be it SQL2005 running on Windows XP SP2, Windows Server 2003 SP1, Window Server 2000 SP4. SQL Server remote or local on the machine doing the debugging. All 5 developer workstations with Visual Studio in virtual machines or installed on in base OS are behaving exactly the same. Every developer on our team has the exact same problem regardless of their configuration after the upgrade to the service pack 1. All developers and environments were working correctly before the upgrade.
Even building a new SQL box from scratch and installing all tools and creating a new clean database we fail with same error.
We have verified CLR Debugging is enabled for the database connection. We have dropped the connection and recreated it.
Here is an example of the output from the output window:
This is the output up to the CLR call
The thread 'sql_test2 [62]' (0x68c) has exited with code 0 (0x0).
The thread 'sql_test2 [62]' (0x68c) has exited with code 0 (0x0).
Auto-attach to process '[1072] [SQL] sql_test2' on machine 'sql_test2' succeeded.
Output AFTER the CLR Call
Auto-attach to process '[1072] sqlservr.exe' on machine 'sql_test2' succeeded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32mscorlib2.0.0.0__b77a5c561934e089mscorlib.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnSqlAccess.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32System.Data2.0.0.0__b77a5c561934e089System.Data.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem2.0.0.0__b77a5c561934e089System.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32System.Transactions2.0.0.0__b77a5c561934e089System.Transactions.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Security2.0.0.0__b03f5f7f11d50a3aSystem.Security.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Xml2.0.0.0__b77a5c561934e089System.Xml.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'CIS_Release6a', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILMicrosoft.VisualBasic8.0.0.0__b03f5f7f11d50a3aMicrosoft.VisualBasic.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Configuration2.0.0.0__b03f5f7f11d50a3aSystem.Configuration.dll', No symbols loaded.
Program Locks up with blank screen and hangs. If we stop the Debugging then the correct results will appear in the output window.
Column1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<CIS><Header><MessageType>FosterParent</MessageType><Status>-1</Status><ErrorMsg><Code>-100</Code><Description>Requested Record Not Available</Description></ErrorMsg></Header></CIS>
No rows affected.
(1 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[qsp_FF_FD_FP_FosterParent].
Any ideas? TIA
Hi all,
I have a problem with a stored procedure that executes properly when running it in Query Analyzer. When I call the SP from an ASP.NET application, it doesn't seem to run properly. I have verified that the parameter values are correct, but there is one update command that does not update any rows when it executes although it should. I tried stepping through the SP from within Visual Studio and it still does not work properly even though all parameters have the correct values.
Why would a SP execute properly when used in QA but not when an application executes it?
I am trying to see if there is anyway to BCP a stored procedure from SQL query analyzer. The statement works fine from the command prompt or from within DTS but not from SQL QA.
The bcp statement is as follows:
master..xp_cmdshell "bcp "exec pubs.dbo.sp_employee" queryout dev01e$emp.txt /c /o dev01e$emp.out /T /SDEV01"
sp_employee has the script:
SELECT * FROM EMPLOYEE
Any help is appreciated. Thanks.
AJ
Can someone help me with debugging store proc. I am usig VS 2003 and SQL Server 2005 and I have no clue as how to do that if it is possible. I would like to place a break point in my store proc if that is possible. I know if I was in VS 2005 it would be a charm but it's not my case.
Thanks
I am trying to execute a stored procedure in Query anaylzer for SQL server 2005. I am not sure I am doing it correctly though, because I am getting an error message. Here is the command I am typing:
execute DetaHistoryGet(84,885,34,"EndDate")
Here is the error message I get:
Msg 102,Level 15, State 1, Line 1
Incorrect syntax near '84'
Here are a few lines from the stored procedure. I have not included it all here.
CREATE PROCEDURE [dbo].[DetaHistoryGet]
( @MarketId decimal, @OwnerId decimal, @QuarterId decimal, @SortExp nvarchar(50) )
AS SET NOCOUNT ON
hi ng,i am newbie to sqlserver and my problem seems simple, but i didn't findinformation about it:How can i display theRETURN @xvalue of a stored procedure in the sql analyzer of the sqlserver?thanks a lot,d
View 2 Replies View RelatedHi All!
I know this must be a very silly question but, what is the PLSQL string I have to use to delete a stored procedure in a database? Essentially I have to remove a stored procedure that comes from a database backup every night because it belongs to a user and that user has to be recreated in the new SQL Server 2000. Simply put:
1. Production database comes into test database
2. Remove copy of stored procedure since it can not be set to dbo user because there is another copy with the same name that belongs to dbo.
3. Remove user
4. Add user (this one brings login name since the restored one didn't)
5. Have a nice day
I've got everything except removing the stored procedure so I will really appreciate the help.
Thank you all!
HI,
I have an interesting situation. I have created a stored procedure which has a select union query and it accepts some parameters. When I execute this procedure it takes 8 minutes. When I copy the script in stored procedure and run it directly in Query Analyzer it takes 2 1/2 minutes?? Same numbers of rows are returned either way in the result set with about 13,000.
I cannot figure this out and it is almost the same thing except that in Query Analyzer I declare the parameters variables and its values?
Any feedback would be appreciated!
Thanks in advance...
Hi. I am having a problem using DTSRun in a stored procedure or in query analyzer.
My DTS package truncates a table and then imports data from an Excel spreadsheet into the table.
The DTS package runs fine if I run it manually on Enterprise Manager or if I use DTSRun in a command prompt.
However when I try to run the package in a stored procedure or in query analyzer as follows:
ie Exec master..xp_cmdshell 'DTSRUN /SSQL SERVER /NPackageName /Uuserid /Ppassword'
The package does not return an error but it does not actually import any data from the Excel spreadsheet. It is able to truncate the table without any problem.
I've tried all kinds of combination of using Windows user id that I know has access to the excel file instead of a sql login.
I've run Filemon on the excel file and it seems like DTS is not accessing the file when I use the stored proc or query analyzer method.
If anyone could shed any light on this I would be most grateful. Thanks.
Hi group,I have a select statement that if run against a 1 million recorddatabase directly in query analyzer takes less than 1 second.However, if I execute the select statement in a stored procedureinstead, calling the stored proc from query analyzer, then it takes12-17 seconds.Here is what I execute in Query Analyzer when bypassing the storedprocedure:USE VerizonGODECLARE @phonenumber varchar(15)SELECT @phonenumber = '6317898493'SELECT Source_Identifier,BADD_Sequence_Number,Record_Type,BAID ,Social_Security_Number ,Billing_Name,Billing_Address_1,Billing_Address_2,Billing_Address_3,Billing_Address_4,Service_Connection_Date,Disconnect_Date,Date_Final_Bill,Behavior_Score,Account_Group,Diconnect_Reason,Treatment_History,Perm_Temp,Balance_Due,Regulated_Balance_Due,Toll_Balance_Due,Deregulated_Balance_Due,Directory_Balance_Due,Other_Category_BalanceFROM BadDebtWHERE (Telephone_Number = @phonenumber) OR (Telephone_Number_Redef =@phonenumber)order by Service_Connection_Date descRETURNGOHere is what I execute in Query Analyzer when calling the storedprocedure:DECLARE @phonenumber varchar(15)SELECT @phonenumber = '6317898493'EXEC Verizon.dbo.baddebt_phonelookup @phonenumberHere is the script that created the stored procedure itself:CREATE PROCEDURE dbo.baddebt_phonelookup @phonenumber varchar(15)ASSELECT Source_Identifier,BADD_Sequence_Number,Record_Type,BAID ,Social_Security_Number ,Billing_Name,Billing_Address_1,Billing_Address_2,Billing_Address_3,Billing_Address_4,Service_Connection_Date,Disconnect_Date,Date_Final_Bill,Behavior_Score,Account_Group,Diconnect_Reason,Treatment_History,Perm_Temp,Balance_Due,Regulated_Balance_Due,Toll_Balance_Due,Deregulated_Balance_Due,Directory_Balance_Due,Other_Category_BalanceFROM BadDebtWHERE (Telephone_Number = @phonenumber) OR (Telephone_Number_Redef =@phonenumber)order by Service_Connection_Date descRETURNGOUsing SQL Profiler, I also have the execution trees for each of thesetwo different ways of running the same query.Here is the Execution tree when running the whole query in theanalyzer, bypassing the stored procedure:--------------------------------------Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))|--Bookmark Lookup(BOOKMARK:([Bmk1000]),OBJECT:([Verizon].[dbo].[BadDebt]))|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))|--Concatenation|--IndexSeek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Index]),SEEK:([BadDebt].[Telephone_Number]=[@phonenumber]) ORDERED FORWARD)|--IndexSeek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Redef_Index]),SEEK:([BadDebt].[Telephone_Number_Redef]=[@phonenumber]) ORDEREDFORWARD)--------------------------------------Finally, here is the execution tree when calling the stored procedure:--------------------------------------Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@phonenumber] OR[BadDebt].[Telephone_Number_Redef]=[@phonenumber]))|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),1, 10)))|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))--------------------------------------Thanks for any help on my path to optimizing this query for ourproduction environment.Regards,Warren WrightScorex Development Team
View 5 Replies View Related
Hi i have a script works in sql query analyzer;
declare @id decimal
declare mycur CURSOR SCROLL for select myRowID from myTable order by myRowID
open mycur;
Fetch ABSOLUTE 30 from mycur into @id
close mycur;
deallocate mycur;
select @id
this script turns me a value.
i create a stored procedure from above script and its syntax is ok;
CREATE PROCEDURE SELECT_MyRow
AS
declare @cur cursor
declare @RowID decimal
set @cur = CURSOR SCROLL
for select myRowID from myTable order by myRowID
open @cur
Fetch ABSOLUTE 30 from @cur into @RowID
close @cur
deallocate @cur
select @RowID
GO
my c# code using stored procedure is below;
Code Snippet
try
{
OleDbCommand cmd = new OleDbCommand("SELECT_MyRow", myconnection);
cmd.CommandType = CommandType.StoredProcedure;
myconnection.Open();
OleDbDataReader reader = cmd.ExecuteReader();
MessageBox.Show(reader.GetName(0));//here fails
while (reader.Read())
{
MessageBox.Show(reader.GetDecimal(0).ToString());
}
reader.Close();
myconnection.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
The code above fails because reader reads no values, error message is "No data exists for the row/column"
but i know exists. Can anyone help me, what is the difference between stored procedure and script ?
I tried. I really did to adapt to using Management studio as a replacement, but I just can't make the jump. I went ahead and installed SQL 2000 QA and have gone back to using it for ad-hoc query support and other routine SQL tasks. For me the main thing is the "Notepad-esque" lightness of QA compared to the heavier SQL tools that allows me to quickly open a SQL script file and get to work on it quickly. I appreciate an integrated tool, but I would like to see future versions allow you to use it either as a standalone or within integrated platform. Am I alone in this? Anyone else feeling this pain?
View 6 Replies View RelatedHi,Is there a way to align/indent the stored procedure script ( like VBAAdd-Ins Smart Indenter )Thanks, Eugene
View 2 Replies View RelatedI tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure:
CREATE PROCEDURE UserLogin
(
@Email nvarchar(100),
@Password nvarchar(50),
@UserName nvarchar(100) OUTPUT
)
AS
SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
GO
If I run the query in Query Analyzer directly, I can display @UserName:
DECLARE @UserName as nvarchar(100)
SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
Select @UserName
But how can I display @UserName if I call stored procedure:
exec UserLogin 'email', 'password', ''
I believed it return int, right?
Thanks in advance for help.
I am implementing the security and add different windows domain group and also assign then appropriate rights and permission.
No i want that if some knows the "sa" password and want to connect to sql server 2005 via query analyzer then message should display to user that u can not login with sa login while using SQL Server Query analyzer.
How i can identify and can display the message?
Thanks a lot in advance.
Hello,
we have a SQL server 2005 with Visual studio Prof. 2005 in the
employment.
The debuggers function only in Visual studio correctly, as long as no
code on the SQL server must be implemented.
If a BREAK POINT in a Stored Procedure is set, this is not activated,
since this cannot be bound.
Does someone know, what it lies and can like one it eliminate?
Thank you for your assistance in advance.
Yours sincerely
Big_Ben_31
This entry was translated automatically with the translation
service babel.altavista.com from the German into English.
I have two tables, Employee and EmployeeEval, and need to try and Select the LastName, FirstName, EmployeeID, and DeptID from the Employee Table where there is no record of that employee in the EmployeeEval Table. I tried this and it won't work, I should get back like 80 employees when the query is written correctly:
select e.deptid, (e.lastname + ', ' + e.firstname) as EmpName, e.employeeid from employee e INNER JOIN employeeeval ev ONe.employeeid <> ev.employeeid where periodid = 176 and e.companyid = '21' and e.FacilityID = '01'