I have created a Stored Procedure, under Stored procedures section under Enterprise Manager on SQL server 2000.
Could anybody tell me, how and what are the steps to follow to TRACE the procedure , using SQL PROFILER ?
Please advice me !
NicolFrom Enterprise Manager:
-- choose Tools
-- SQL Profiler
-- File
-- New
-- Trace
-- select/enter connection informatin as appropriate (I was able to accept the defaults)
-- press OK
-- click the General tab of the Trace Properties window
-- from the "Template name:" dropdown choose SQLProfilerTSQL_SPs
-- choose RUN
-- do whatever it is you need to do to run your Stored Procedure (from within Query Analyzer or ASP.NET)
-- go back to Profiler and click the red square "stop selected trace" icon
-- review the results
Terri|||Thanks Tmorton,
I got started with checking SP using SQL profiler.. :)
Now I found, I think, I have problem with coding in VB.NET or problem in SP.
Even though, I have given valid Username/Password, RETURNVALUE is always showing zero .
Stored procedure
-------
CREATE PROCEDURE test
(@.username varchar(100),
@.userpwd varchar(200)
)
AS
set nocount on
return (select count(*) as s from employer
WHERE user_name like '%@.username%' and
password ='@.userpwd')
GO
VB.NET Code using SP
--------
CmdCoInfo = New SqlCommand("test", ConVM)
CmdCoInfo.CommandType = CommandType.StoredProcedure
CmdCoInfo.Parameters.Add("@.username", Trim(StrUser))
CmdCoInfo.Parameters.Add("@.userpwd", Trim(StrPass))
Dim paramOut As SqlParameter
paramOut = CmdCoInfo.Parameters.Add("Returnvalue", SqlDbType.Int)
paramOut.Direction = ParameterDirection.ReturnValue
paramOut.Size = 40
' Retrieve the record that matches the username/password
ConVM.Open()
CmdCoInfo.ExecuteNonQuery()
Dim retrr As String
If Not IsDBNull(CmdCoInfo.Parameters("Returnvalue").Value) Then
retrr = (CmdCoInfo.Parameters("Returnvalue").Value)
Else
retrr = "Not know"
End If
Response.Write(retrr)
--------------
Is there problem with SP or VB.net code ?
Please advice !|||The problem is in your stored procedure.
Instead of this:
return (select count(*) as s from employer
WHERE user_name like '%@.username%' and
password ='@.userpwd')
GO
This would be more correct:
DECLARE @.RecordCount integer
select @.RecordCount= count(*) from employer
WHERE user_name like '%'+@.username +'%' and
password =@.userpwd
RETURN @.RecordCountGO
HOWEVER, ReturnValue is meant to signal back to the calling program whether or not the stored procedure was successful. So you are misusing it to return a COUNT. You should use an Output parameter instead. Add an additional parameter called RecordCount with ParameterDirection of Output. Add the Output parameter to your stored procedure, which will look like this:
CREATE PROCEDURE test
(@.username varchar(100),
@.userpwd varchar(200) ,
@.RecordCount int OUTPUT
)
AS
set nocount on
select @.RecordCount= count(*) from employer
WHERE user_name like '%'+@.username +'%' and
password =@.userpwd
RETURN @.@.ERRORGO
I haven't tested this code but it should be about right.
Terri|||Terri,
ur 2nd option worked for me. But i didnt understand why I am still getting zero as ReturnValue , when I tried with 1st option(given below), eventhough record exists for that username/pwd.
DECLARE @.RecordCount integer
select @.RecordCount= count(*) from employer
WHERE user_name like '%'+@.username +'%' and
password =@.userpwd
RETURN @.RecordCount
GO
Please advice !|||I used that same stored procedure, and your code modified slightly which executes the procedure and puts the returnvalue into a label and it works as expected:
CmdCoInfo = New SqlCommand("returntest", SqlConnection)
CmdCoInfo.CommandType = CommandType.StoredProcedure
CmdCoInfo.Parameters.Add("@.test1", 1)
Dim paramOut As SqlParameter
paramOut = CmdCoInfo.Parameters.Add("Returnvalue", SqlDbType.Int)
paramOut.Direction = ParameterDirection.ReturnValue
paramOut.Size = 40SqlConnection.Open()
CmdCoInfo.ExecuteNonQuery()Label1.Text = CmdCoInfo.Parameters("ReturnValue").Value
Terrisql
