Profiler - details of SP

How can I setup Profiler to track/watch the execution of the lines in a
stored procedure?
It looks like I just see exec sp_DoWork, as TextData.
I want to see the sql statements in the SP and how long they each take.
--
Thanks,
ThomasLLHi,
I am not sure whether this is possible...
But as soon as you have identified the SP thats taking time, why dont
you check the execution plan of that SP instead?
Thomas.LeBlanc@.NoSpam.Com wrote:
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL|||Hi Thomas
You can trace SP:StmtStarting and SP:StmtCompleted
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL|||That gives me the duration of each Insert, Updaet and Select in the SP?
Thanks Karen, you are a blessing to the SQL Server community
--
Thanks,
Thomas
"Kalen Delaney" wrote:
> Hi Thomas
> You can trace SP:StmtStarting and SP:StmtCompleted
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Thomas.LeBlanc@.NoSpam.Com"
> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
> news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> > How can I setup Profiler to track/watch the execution of the lines in a
> > stored procedure?
> >
> > It looks like I just see exec sp_DoWork, as TextData.
> >
> > I want to see the sql statements in the SP and how long they each take.
> > --
> > Thanks,
> > ThomasLL
>
>|||SP:StmtCompleted can give you the duration of each statement in the SP.
Thanks for your kind words. Karen thanks you too. ;-)
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:CC7D6F54-4DE7-4DE8-B7CC-7904572FDF90@.microsoft.com...
> That gives me the duration of each Insert, Updaet and Select in the SP?
>
> Thanks Karen, you are a blessing to the SQL Server community
> --
> Thanks,
> Thomas
>
> "Kalen Delaney" wrote:
>> Hi Thomas
>> You can trace SP:StmtStarting and SP:StmtCompleted
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "Thomas.LeBlanc@.NoSpam.Com"
>> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
>> news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
>> > How can I setup Profiler to track/watch the execution of the lines in a
>> > stored procedure?
>> >
>> > It looks like I just see exec sp_DoWork, as TextData.
>> >
>> > I want to see the sql statements in the SP and how long they each take.
>> > --
>> > Thanks,
>> > ThomasLL
>>

Profiler

I really have never used the SQL Profiler, but I'm trying. I have a stored
procedure which runs just fine in query analyzer returning a few rows.
When I run it from a C# application, it returns "0" in the @.ERETURN_VALUE"
but no rows of data are returned.
I brought up Profiler and I am not really able to make heads or tails out
of this. What values should I select in profiler to see if any rows are
returned to the application?
Thanks in advance for your assistance...In your database exeute
select db_id() and take a note of the result
In the profiler on the filters tab go to the databaseID
equals entry and type in the value you extracted.
In events get rid of security audit and sessions.
Now when you run the profiler you will just trace
interactions with your database - usually that's enough to
find entries.
Run the app and you should see the SP call.
Copy this from the result window and run it in query
analyser (in a transaction if it does updates) and you
should see the problem.
>--Original Message--
>I really have never used the SQL Profiler, but I'm
trying. I have a stored
>procedure which runs just fine in query analyzer
returning a few rows.
>When I run it from a C# application, it returns "0" in
the @.ERETURN_VALUE"
>but no rows of data are returned.
>I brought up Profiler and I am not really able to make
heads or tails out
>of this. What values should I select in profiler to see
if any rows are
>returned to the application?
>
>Thanks in advance for your assistance...
>.
>

Profile Trace: how to tell which database when filtering on user

Let's say there are two databases, DB#1 and DB#2, and a user JOE. DB#1 and
DB#2 have stored procedures identically named (.e.g. "uspGetOrders") with
the same signature. Is it possible when running a trace that filters on
user like JOE, to determine which database the procedure belongs to which
JOE happens to be executing? How can I tell which it is? When I double
click on the trace line to view the details, the sp name is displayed
without a db or owner prefix. And if I filter on database name, I see this:
exec uspGetOrders @.p1 no matter which database I happen to choose. The
trace doesn't seem to tell me where the procedure "originates" so to speak.
I'm trying to figure out why the procedure is returning no data when it is
moved to DB#2 and points back to tables in DB1. The procedure worked fine
when it was in DB#1. The stored procedure refers to the table as
DB1.dbo.Orders and JOE has been granted execute privileges (via a role) on
DB2.dbo.uspGetOrders. JOE had no problem working with the procedure when
it was in the same database as the table. JOE belongs to a role that has
select privileges on Orders.
The only other thing that has changed is the Initial Catalog when JOE
connects. Now it is DB2.
Thanks!
TimoIf a user is in DB#1 and what you see executed is
uspGetOrders, it's executing the stored procedure in DB#1.
It would have to be qualified by the database name to
execute the stored procedure in DB#2.
If you are on SQL Server 2000 and if you are on SP3, then
the issue with the stored procedure could be due to not
having cross database ownership chaining enabled. If you are
running under this configuration and have updated your books
online, you can find more information in books online under
the topic:
Cross DB Ownership Chaining
-Sue
On Mon, 12 Apr 2004 12:25:28 -0400, "Timo"
<timo@.anonymous.com> wrote:

>Let's say there are two databases, DB#1 and DB#2, and a user JOE. DB#1 and
>DB#2 have stored procedures identically named (.e.g. "uspGetOrders") with
>the same signature. Is it possible when running a trace that filters on
>user like JOE, to determine which database the procedure belongs to which
>JOE happens to be executing? How can I tell which it is? When I double
>click on the trace line to view the details, the sp name is displayed
>without a db or owner prefix. And if I filter on database name, I see this
:
>exec uspGetOrders @.p1 no matter which database I happen to choose. The
>trace doesn't seem to tell me where the procedure "originates" so to speak.
>I'm trying to figure out why the procedure is returning no data when it is
>moved to DB#2 and points back to tables in DB1. The procedure worked fine
>when it was in DB#1. The stored procedure refers to the table as
>DB1.dbo.Orders and JOE has been granted execute privileges (via a role) on
>DB2.dbo.uspGetOrders. JOE had no problem working with the procedure when
>it was in the same database as the table. JOE belongs to a role that has
>select privileges on Orders.
>The only other thing that has changed is the Initial Catalog when JOE
>connects. Now it is DB2.
>Thanks!
>Timo
>
>
>|||Sue,
Thanks for the reply. It doesn't appear to be Cross DB Ownership chaining;
same issue after this:
EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
EXEC sp_dboption 'DB1', 'db chaining', 'ON'
EXEC sp_dboption 'DB2', 'db chaining', 'ON'
Trace shows the procedure being invoked; the IN parameter is there, but all
the OUT parameters are null. When I've encountered similar symptoms in Trace
before, it was because the user lacked execute privileges on the procedure.
But JOE has these privileges by virtue of membership in a db role.
Any other way to troubleshoot this scenario using Trace?
Thanks again.
Timo
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:vcil70hj0j37239l9ds5tevbtfotc6n506@.
4ax.com...
> If a user is in DB#1 and what you see executed is
> uspGetOrders, it's executing the stored procedure in DB#1.
> It would have to be qualified by the database name to
> execute the stored procedure in DB#2.
> If you are on SQL Server 2000 and if you are on SP3, then
> the issue with the stored procedure could be due to not
> having cross database ownership chaining enabled. If you are
> running under this configuration and have updated your books
> online, you can find more information in books online under
> the topic:
> Cross DB Ownership Chaining
> -Sue
> On Mon, 12 Apr 2004 12:25:28 -0400, "Timo"
> <timo@.anonymous.com> wrote:
>
and
which
this:
speak.
is
on
when
>|||In Profiler, you'd want to select exceptions from the Errors
and Warnings event class. That will give you any errors
being raised. You should also make sure you are capturing
the completion of the stored procedure, not just the
starting event.
Did you check the owners of the objects (the stored
procedure and the underlying tables) and databases involved?
The databases need to be owned by the same login. The
objects need to have the same object owner.
-Sue
On Mon, 12 Apr 2004 13:40:39 -0400, "Timo"
<timo@.anonymous.com> wrote:

>Sue,
>Thanks for the reply. It doesn't appear to be Cross DB Ownership chaining;
>same issue after this:
>EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
>EXEC sp_dboption 'DB1', 'db chaining', 'ON'
>EXEC sp_dboption 'DB2', 'db chaining', 'ON'
>Trace shows the procedure being invoked; the IN parameter is there, but all
>the OUT parameters are null. When I've encountered similar symptoms in Trac
e
>before, it was because the user lacked execute privileges on the procedure.
>But JOE has these privileges by virtue of membership in a db role.
>Any other way to troubleshoot this scenario using Trace?
>Thanks again.
>Timo
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:vcil70hj0j37239l9ds5tevbtfotc6n506@.
4ax.com...
>and
>which
>this:
>speak.
>is
>on
>when
>|||To add to Sue's response, if your objects are owned by 'dbo', the database
owners need to be the same in order to maintain an unbroken ownership chain
for dbo-owned objects. If needed, you can use sp_changedbowner to ensure
the databases are owned by the same login:
USE DB1
EXEC sp_changedbowner 'MyOwnerLogin'
USE DB2
EXEC sp_changedbowner 'MyOwnerLogin'
Users will need access to both databases but permissions on indirectly
referenced objects are not needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Timo" <timo@.anonymous.com> wrote in message
news:%23t2AiVLIEHA.580@.TK2MSFTNGP12.phx.gbl...
> Sue,
> Thanks for the reply. It doesn't appear to be Cross DB Ownership
chaining;
> same issue after this:
> EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
> EXEC sp_dboption 'DB1', 'db chaining', 'ON'
> EXEC sp_dboption 'DB2', 'db chaining', 'ON'
> Trace shows the procedure being invoked; the IN parameter is there, but
all
> the OUT parameters are null. When I've encountered similar symptoms in
Trace
> before, it was because the user lacked execute privileges on the
procedure.
> But JOE has these privileges by virtue of membership in a db role.
> Any other way to troubleshoot this scenario using Trace?
> Thanks again.
> Timo
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:vcil70hj0j37239l9ds5tevbtfotc6n506@.
4ax.com...
> and
with
on
> which
> this:
The
> speak.
> is
fine
> on
> when
has
>|||Thank you Dan for the dbchangeowner tip, and Sue for the Errors and Warnings
event class tip. I will use them to advantage. In Trace, I was not seeing
the completion of the sp, only the starting event, and so was unable to
determine what database the client was pointing at. After having the
developer re-check the connection string in the client-side code, we found
that the wrong database was being specified as the initial catalog.
Timo
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ukvyQdVIEHA.2972@.TK2MSFTNGP12.phx.gbl...
> To add to Sue's response, if your objects are owned by 'dbo', the database
> owners need to be the same in order to maintain an unbroken ownership
chain
> for dbo-owned objects. If needed, you can use sp_changedbowner to ensure
> the databases are owned by the same login:
> USE DB1
> EXEC sp_changedbowner 'MyOwnerLogin'
> USE DB2
> EXEC sp_changedbowner 'MyOwnerLogin'
> Users will need access to both databases but permissions on indirectly
> referenced objects are not needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Timo" <timo@.anonymous.com> wrote in message
> news:%23t2AiVLIEHA.580@.TK2MSFTNGP12.phx.gbl...
> chaining;
> all
> Trace
> procedure.
DB#1
> with
> on
double
see
> The
it
> fine
role)
> has
>

Profile / Stored Procedure

Hi,

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 @.RecordCount
GO

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 @.@.ERROR
GO

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 = 40
SqlConnection.Open()
CmdCoInfo.ExecuteNonQuery()
Label1.Text = CmdCoInfo.Parameters("ReturnValue").Value

Terrisql

Production debugging of SQL Server 2005 without VS 2005?

Hi,
Apparently Visual Studio 2005 is normally needed to debug
SQL Server 2005 problems. For example stored procedures
written in managed code. What is the recommended way to
do debugging on a production server that doesn't have VS
on it? Remote debugging from another machine on the
same LAN? What if that isn't possible? ADPlus, dump
files and the SOS debugger extension?
Thanks,
Alan Cobb
Hi
Usually you would not want to do this on a live system, but a copy, as
potentially you could cause havoc if you do this. Any code change should go
through a proper review/test/release process.
John
"Alan Cobb" wrote:

> Hi,
> Apparently Visual Studio 2005 is normally needed to debug
> SQL Server 2005 problems. For example stored procedures
> written in managed code. What is the recommended way to
> do debugging on a production server that doesn't have VS
> on it? Remote debugging from another machine on the
> same LAN? What if that isn't possible? ADPlus, dump
> files and the SOS debugger extension?
> Thanks,
> Alan Cobb
>
|||Hi John,
Let's say we've exhausted all the "non-live" approaches
and there is some bug (not just the stored procedure example)
that only happens on a particular live system.
Maybe "production debugging" just doesn't come up that much
in the SQL Server world, but in the ASP.NET world people do
have to occasionally debug problems on live ASP.NET servers.
Partly for security reasons people often don't want to install
Visual Studio's debugger on a production ASP.NET server, so
they capture dump files with ADPlus. Then they analyze the
dump files in a debugger somewhere else. But maybe that's
not as common in the SQL Server world?
Thanks,
Alan Cobb
On Thu, 26 Jan 2006 00:47:02 -0800, John Bell
<jbellnewsposts@.hotmail.com> wrote:
[vbcol=seagreen]
>Hi
>Usually you would not want to do this on a live system, but a copy, as
>potentially you could cause havoc if you do this. Any code change should go
>through a proper review/test/release process.
>John
>"Alan Cobb" wrote:
|||Hi Alan
With backend systems control and auditability can be a mandatory requirement
depending on your circumstances. Also if a business' wellbeing it dependent
on such systems it would be outside of the remit for a developer to decide
what should/should not be done on these servers. This is obviously dependent
on the company and may well be different for yours.
If you have the web logs maybe you can recreate the problem by replaying
them using ACT. This may give you the opportunity to debug the application in
a controlled environment, alternatively if you can validate that you will not
get the issue one a set of hardware you may want to consider
changing/rebuilding the system.
John
"Alan Cobb" wrote:

> Hi John,
> Let's say we've exhausted all the "non-live" approaches
> and there is some bug (not just the stored procedure example)
> that only happens on a particular live system.
> Maybe "production debugging" just doesn't come up that much
> in the SQL Server world, but in the ASP.NET world people do
> have to occasionally debug problems on live ASP.NET servers.
> Partly for security reasons people often don't want to install
> Visual Studio's debugger on a production ASP.NET server, so
> they capture dump files with ADPlus. Then they analyze the
> dump files in a debugger somewhere else. But maybe that's
> not as common in the SQL Server world?
> Thanks,
> Alan Cobb
> On Thu, 26 Jan 2006 00:47:02 -0800, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
>
>

Production debugging of SQL Server 2005 without VS 2005?

Hi,
Apparently Visual Studio 2005 is normally needed to debug
SQL Server 2005 problems. For example stored procedures
written in managed code. What is the recommended way to
do debugging on a production server that doesn't have VS
on it? Remote debugging from another machine on the
same LAN? What if that isn't possible? ADPlus, dump
files and the SOS debugger extension?
Thanks,
Alan CobbHi
Usually you would not want to do this on a live system, but a copy, as
potentially you could cause havoc if you do this. Any code change should go
through a proper review/test/release process.
John
"Alan Cobb" wrote:
> Hi,
> Apparently Visual Studio 2005 is normally needed to debug
> SQL Server 2005 problems. For example stored procedures
> written in managed code. What is the recommended way to
> do debugging on a production server that doesn't have VS
> on it? Remote debugging from another machine on the
> same LAN? What if that isn't possible? ADPlus, dump
> files and the SOS debugger extension?
> Thanks,
> Alan Cobb
>|||Hi John,
Let's say we've exhausted all the "non-live" approaches
and there is some bug (not just the stored procedure example)
that only happens on a particular live system.
Maybe "production debugging" just doesn't come up that much
in the SQL Server world, but in the ASP.NET world people do
have to occasionally debug problems on live ASP.NET servers.
Partly for security reasons people often don't want to install
Visual Studio's debugger on a production ASP.NET server, so
they capture dump files with ADPlus. Then they analyze the
dump files in a debugger somewhere else. But maybe that's
not as common in the SQL Server world?
Thanks,
Alan Cobb
On Thu, 26 Jan 2006 00:47:02 -0800, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>Usually you would not want to do this on a live system, but a copy, as
>potentially you could cause havoc if you do this. Any code change should go
>through a proper review/test/release process.
>John
>"Alan Cobb" wrote:
>> Hi,
>> Apparently Visual Studio 2005 is normally needed to debug
>> SQL Server 2005 problems. For example stored procedures
>> written in managed code. What is the recommended way to
>> do debugging on a production server that doesn't have VS
>> on it? Remote debugging from another machine on the
>> same LAN? What if that isn't possible? ADPlus, dump
>> files and the SOS debugger extension?
>> Thanks,
>> Alan Cobb|||Hi Alan
With backend systems control and auditability can be a mandatory requirement
depending on your circumstances. Also if a business' wellbeing it dependent
on such systems it would be outside of the remit for a developer to decide
what should/should not be done on these servers. This is obviously dependent
on the company and may well be different for yours.
If you have the web logs maybe you can recreate the problem by replaying
them using ACT. This may give you the opportunity to debug the application in
a controlled environment, alternatively if you can validate that you will not
get the issue one a set of hardware you may want to consider
changing/rebuilding the system.
John
"Alan Cobb" wrote:
> Hi John,
> Let's say we've exhausted all the "non-live" approaches
> and there is some bug (not just the stored procedure example)
> that only happens on a particular live system.
> Maybe "production debugging" just doesn't come up that much
> in the SQL Server world, but in the ASP.NET world people do
> have to occasionally debug problems on live ASP.NET servers.
> Partly for security reasons people often don't want to install
> Visual Studio's debugger on a production ASP.NET server, so
> they capture dump files with ADPlus. Then they analyze the
> dump files in a debugger somewhere else. But maybe that's
> not as common in the SQL Server world?
> Thanks,
> Alan Cobb
> On Thu, 26 Jan 2006 00:47:02 -0800, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> >Hi
> >
> >Usually you would not want to do this on a live system, but a copy, as
> >potentially you could cause havoc if you do this. Any code change should go
> >through a proper review/test/release process.
> >
> >John
> >
> >"Alan Cobb" wrote:
> >
> >> Hi,
> >>
> >> Apparently Visual Studio 2005 is normally needed to debug
> >> SQL Server 2005 problems. For example stored procedures
> >> written in managed code. What is the recommended way to
> >> do debugging on a production server that doesn't have VS
> >> on it? Remote debugging from another machine on the
> >> same LAN? What if that isn't possible? ADPlus, dump
> >> files and the SOS debugger extension?
> >>
> >> Thanks,
> >> Alan Cobb
> >>
>sql

Production debugging of SQL Server 2005 without VS 2005?

Hi,
Apparently Visual Studio 2005 is normally needed to debug
SQL Server 2005 problems. For example stored procedures
written in managed code. What is the recommended way to
do debugging on a production server that doesn't have VS
on it? Remote debugging from another machine on the
same LAN? What if that isn't possible? ADPlus, dump
files and the SOS debugger extension?
Thanks,
Alan CobbHi
Usually you would not want to do this on a live system, but a copy, as
potentially you could cause havoc if you do this. Any code change should go
through a proper review/test/release process.
John
"Alan Cobb" wrote:

> Hi,
> Apparently Visual Studio 2005 is normally needed to debug
> SQL Server 2005 problems. For example stored procedures
> written in managed code. What is the recommended way to
> do debugging on a production server that doesn't have VS
> on it? Remote debugging from another machine on the
> same LAN? What if that isn't possible? ADPlus, dump
> files and the SOS debugger extension?
> Thanks,
> Alan Cobb
>|||Hi John,
Let's say we've exhausted all the "non-live" approaches
and there is some bug (not just the stored procedure example)
that only happens on a particular live system.
Maybe "production debugging" just doesn't come up that much
in the SQL Server world, but in the ASP.NET world people do
have to occasionally debug problems on live ASP.NET servers.
Partly for security reasons people often don't want to install
Visual Studio's debugger on a production ASP.NET server, so
they capture dump files with ADPlus. Then they analyze the
dump files in a debugger somewhere else. But maybe that's
not as common in the SQL Server world?
Thanks,
Alan Cobb
On Thu, 26 Jan 2006 00:47:02 -0800, John Bell
<jbellnewsposts@.hotmail.com> wrote:
[vbcol=seagreen]
>Hi
>Usually you would not want to do this on a live system, but a copy, as
>potentially you could cause havoc if you do this. Any code change should go
>through a proper review/test/release process.
>John
>"Alan Cobb" wrote:
>|||Hi Alan
With backend systems control and auditability can be a mandatory requirement
depending on your circumstances. Also if a business' wellbeing it dependent
on such systems it would be outside of the remit for a developer to decide
what should/should not be done on these servers. This is obviously dependent
on the company and may well be different for yours.
If you have the web logs maybe you can recreate the problem by replaying
them using ACT. This may give you the opportunity to debug the application i
n
a controlled environment, alternatively if you can validate that you will no
t
get the issue one a set of hardware you may want to consider
changing/rebuilding the system.
John
"Alan Cobb" wrote:

> Hi John,
> Let's say we've exhausted all the "non-live" approaches
> and there is some bug (not just the stored procedure example)
> that only happens on a particular live system.
> Maybe "production debugging" just doesn't come up that much
> in the SQL Server world, but in the ASP.NET world people do
> have to occasionally debug problems on live ASP.NET servers.
> Partly for security reasons people often don't want to install
> Visual Studio's debugger on a production ASP.NET server, so
> they capture dump files with ADPlus. Then they analyze the
> dump files in a debugger somewhere else. But maybe that's
> not as common in the SQL Server world?
> Thanks,
> Alan Cobb
> On Thu, 26 Jan 2006 00:47:02 -0800, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
>
>

Product Search question

I have a search box on my website which is used to search the products database.

I will be using the search text in an SQL stored procedure that uses LIKE statements. The search string could realistically contain any character.

How do I prevent SQL injection when any search string is reasonably feasible? .

supergriff:

How do I prevent SQL injection when any search string is reasonably feasible?

I recommend that you read the weblog entry by Microsoft's Bertrand Le Roy,Please, please, please, learn about injection attacks!|||

Great, so if i'm using stored procedures, rather than string concatenation, this inherently prevents SQL injection? My search function is below:

' Select all ProductsPublicFunction GetAllVisibleProductsBySearchString(ByVal SearchStringAsString)As DataTable

Dim connAs SqlConnection =New SqlConnection(_connectionString)Dim cmdStoredProcedureAsNew SqlCommand("dis_GetAllVisibleProductsBySearchString", conn)

cmdStoredProcedure.CommandType = CommandType.StoredProcedure

cmdStoredProcedure.Parameters.Add(

"@.SearchString", SqlDbType.VarChar, 250).Value = SearchStringDim daAs SqlDataAdapter =New SqlDataAdapter(cmdStoredProcedure)Dim dsAs DataSet =New DataSet()Try

conn.Open()

da.Fill(ds,

"Products")

Catch eAs SqlException

' Handle exception.Finally

conn.Close()

EndTryReturn ds.Tables("Products")EndFunction

procs that reference a table

In SQL Server 2000, I would like to find what stored procedures are accessin
g
a table.nm, got it.
SELECT name, xtype
FROM sysobjects
WHERE id IN (
SELECT id
FROM sysdepends
WHERE depid IN (
SELECT id
FROM sysobjects
WHERE name = @.Table
)
)
"JayKon" wrote:

> In SQL Server 2000, I would like to find what stored procedures are access
ing
> a table.
>|||JayKon,
In an ideal world, you would have your answer, but the world is not always
ideal. Due to deferred name resolution, etc. the sysdepends table is not
always up-to-date. You might find Erland and Roy's comments in this thread
interesting on finding dependencies.
6b64a938d50b3a68" target="_blank">http://groups.google.com/group/micr...b64a938d50b3a68
RLF
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:77339A0F-1895-4856-A80C-EEB0151CF923@.microsoft.com...[vbcol=seagreen]
> nm, got it.
> SELECT name, xtype
> FROM sysobjects
> WHERE id IN (
> SELECT id
> FROM sysdepends
> WHERE depid IN (
> SELECT id
> FROM sysobjects
> WHERE name = @.Table
> )
> )
> "JayKon" wrote:
>

procs that reference a table

In SQL Server 2000, I would like to find what stored procedures are accessing
a table.nm, got it.
SELECT name, xtype
FROM sysobjects
WHERE id IN (
SELECT id
FROM sysdepends
WHERE depid IN (
SELECT id
FROM sysobjects
WHERE name = @.Table
)
)
"JayKon" wrote:
> In SQL Server 2000, I would like to find what stored procedures are accessing
> a table.
>|||JayKon,
In an ideal world, you would have your answer, but the world is not always
ideal. Due to deferred name resolution, etc. the sysdepends table is not
always up-to-date. You might find Erland and Roy's comments in this thread
interesting on finding dependencies.
http://groups.google.com/group/microsoft.public.sqlserver.tools/browse_thread/thread/d8b39c5fd36ad806/6b64a938d50b3a68?lnk=st&q=ROY+HARVEY+SYSCOMMENTS&rnum=8&hl=en#6b64a938d50b3a68
RLF
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:77339A0F-1895-4856-A80C-EEB0151CF923@.microsoft.com...
> nm, got it.
> SELECT name, xtype
> FROM sysobjects
> WHERE id IN (
> SELECT id
> FROM sysdepends
> WHERE depid IN (
> SELECT id
> FROM sysobjects
> WHERE name = @.Table
> )
> )
> "JayKon" wrote:
>> In SQL Server 2000, I would like to find what stored procedures are
>> accessing
>> a table.

procs that reference a table

In SQL Server 2000, I would like to find what stored procedures are accessing
a table.
nm, got it.
SELECT name, xtype
FROM sysobjects
WHERE id IN (
SELECT id
FROM sysdepends
WHERE depid IN (
SELECT id
FROM sysobjects
WHERE name = @.Table
)
)
"JayKon" wrote:

> In SQL Server 2000, I would like to find what stored procedures are accessing
> a table.
>
|||JayKon,
In an ideal world, you would have your answer, but the world is not always
ideal. Due to deferred name resolution, etc. the sysdepends table is not
always up-to-date. You might find Erland and Roy's comments in this thread
interesting on finding dependencies.
[url]http://groups.google.com/group/microsoft.public.sqlserver.tools/browse_thread/thread/d8b39c5fd36ad806/6b64a938d50b3a68?lnk=st&q=ROY+HARVEY+SYSCOMMENTS&r num=8&hl=en#6b64a938d50b3a68[/url]
RLF
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:77339A0F-1895-4856-A80C-EEB0151CF923@.microsoft.com...[vbcol=seagreen]
> nm, got it.
> SELECT name, xtype
> FROM sysobjects
> WHERE id IN (
> SELECT id
> FROM sysdepends
> WHERE depid IN (
> SELECT id
> FROM sysobjects
> WHERE name = @.Table
> )
> )
> "JayKon" wrote:

Processing the resultset of another proc from a proc

Is it possible to retrieve the resultset of a stored procedure from another procedure in sql server 2000.
Basically I am calling proc2 from the inside of proc1.
proc2 returns 2 resultsets. I want to process these two resultsets
from within proc1.

If its possible , please provide sample code.

thanks in advance,
Alok.Yes, with temporary tables for instance. But it will be easier if you can convert proc2 to two UDF table functions. Your code will be much clearer and simpler to maintain.

process result recordset from storedproc

hi!
I have a stored proc which aggregates some values and
returns a recordset with one record
...looking something like this:
val1 val2 val3
-- -- --
24723 26457 342579
how can I use this record for further processing
e.g. write it into an existing table (for logging)
hmm, this is probably a newbie-question
but I couldnt find the answer in BOL...
any help is greatly appreciated,
best regards,
haraldIf you want to handle only one record you can use variables to store and use
it.
For eg:
Declare @.Val1 int , @.Val2 int ...
Select @.Val1 = Val1, @.Val2 = Val2 etv...
Insert into TableName (@.Val1, @.val2 etc..
Other wise you can use a cursor for looping it.
Please refer books online.
Thank you
Baiju
"Harald Peki" <mabuse007@.yahoo.com> wrote in message
news:1109832522.363076@.news.liwest.at...
> hi!
> I have a stored proc which aggregates some values and
> returns a recordset with one record
> ...looking something like this:
> val1 val2 val3
> -- -- --
> 24723 26457 342579
> how can I use this record for further processing
> e.g. write it into an existing table (for logging)
>
> hmm, this is probably a newbie-question
> but I couldnt find the answer in BOL...
> any help is greatly appreciated,
> best regards,
> harald
>
>|||hello baiju,
thanks very much - I am aware of cursors, but hoped there would be a
easier and more elegant solution.
generally asked: isnt there a way to use the storedproc-recordset in the
same
way like the resultset of a function which returns a table?
I this case I can use a statement like this:
INSERT INTO StatsTables
SELECT * FROM dbo.adm_CountAllTables_function()
...where dbo.adm_CountAllTables_function() is - obviously ;))) - a
function...
hmm, I guess it IS the most usefull solution to change the storedproc to a
function.
Im most thankful for any further comments.
best regards,
harald
"Baiju" <baiju@.indus-systems.com> schrieb im Newsbeitrag
news:uhfN$f8HFHA.4032@.TK2MSFTNGP12.phx.gbl...
> If you want to handle only one record you can use variables to store and
use
> it.
> For eg:
> Declare @.Val1 int , @.Val2 int ...
> Select @.Val1 = Val1, @.Val2 = Val2 etv...
> Insert into TableName (@.Val1, @.val2 etc..
>
> Other wise you can use a cursor for looping it.
> Please refer books online.
>
> Thank you
> Baiju
>
> "Harald Peki" <mabuse007@.yahoo.com> wrote in message
> news:1109832522.363076@.news.liwest.at...
>|||use
insert into table_name
exec proc_name
"Harald Peki" wrote:

> hi!
> I have a stored proc which aggregates some values and
> returns a recordset with one record
> ...looking something like this:
> val1 val2 val3
> -- -- --
> 24723 26457 342579
> how can I use this record for further processing
> e.g. write it into an existing table (for logging)
>
> hmm, this is probably a newbie-question
> but I couldnt find the answer in BOL...
> any help is greatly appreciated,
> best regards,
> harald
>
>|||hi tom!

> insert into table_name
> exec proc_name
oops! great!!!
...but funny...tried that one but obviously had some syntax mistake or
something...
WORKS GREAT NOW!!
Thanks very, very much!
all systems up and running ;))
best regards,
harald
"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> schrieb im
Newsbeitrag news:4895E3C4-C967-46BB-99F4-54724C98CE71@.microsoft.com...
> use
> insert into table_name
> exec proc_name
>
> "Harald Peki" wrote:
>

Process on Linked Server doesn't stop

Hello,
I have the same problem with Linked Server. I receive recordset from stored
procedure, which executes inside it stored procedure on the Linked Server. If
the application, that run stored procedure work successfully, then everything
is OK, but if there are some troubles (e.g. I close the parent application,
before I get recordset), I have the problem: the process on the Linked Server
doesn’t stop itself. It’s continuing executing for eternity and locks other
processes. The only way to stop it – is to kill it. I’ll try to set timeouts
in the configuration of my application
and Query Timeout in the Server Options of Linked Server, but it doesn’t
help. This problem shows itself only if I work through Linked Server.
Thanks for your help.
Boris.
Seems to me that if you were to close the application "unexpectedly", then
it leaves an orphaned transaction which probably would explain the
behaviour you are seeing. You might want to consider making some
application code changes that has some sort of logic on open transactions
from the client end, and to close them on an exit, or some such thing.
HTH!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Process on Linked Server doesn't stop

Hello,
I have the same problem with Linked Server. I receive recordset from stored
procedure, which executes inside it stored procedure on the Linked Server. If
the application, that run stored procedure work successfully, then everything
is OK, but if there are some troubles (e.g. I close the parent application,
before I get recordset), I have the problem: the process on the Linked Server
doesnâ't stop itself. Itâ's continuing executing for eternity and locks other
processes. The only way to stop it â' is to kill it. Iâ'll try to set timeouts
in the configuration of my application
and Query Timeout in the Server Options of Linked Server, but it doesnâ't
help. This problem shows itself only if I work through Linked Server.
Thanks for your help.
Boris.Seems to me that if you were to close the application "unexpectedly", then
it leaves an orphaned transaction which probably would explain the
behaviour you are seeing. You might want to consider making some
application code changes that has some sort of logic on open transactions
from the client end, and to close them on an exit, or some such thing.
HTH!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Process on Linked Server doesn't stop

Hello,
I have the same problem with Linked Server. I receive recordset from stored
procedure, which executes inside it stored procedure on the Linked Server. I
f
the application, that run stored procedure work successfully, then everythin
g
is OK, but if there are some troubles (e.g. I close the parent application,
before I get recordset), I have the problem: the process on the Linked Serve
r
doesn’t stop itself. It’s continuing executing for eternity and locks ot
her
processes. The only way to stop it – is to kill it. I’ll try to set time
outs
in the configuration of my application
and Query Timeout in the Server Options of Linked Server, but it doesn’t
help. This problem shows itself only if I work through Linked Server.
Thanks for your help.
Boris.Seems to me that if you were to close the application "unexpectedly", then
it leaves an orphaned transaction which probably would explain the
behaviour you are seeing. You might want to consider making some
application code changes that has some sort of logic on open transactions
from the client end, and to close them on an exit, or some such thing.
HTH!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

process is blocking itself

I have a stored proc created for report and now suddenly the report is takin
g
lot of time to run. The store proc is having simple select statement with
multiple case statements.
I checked the sysprocesses table and noticed that the process is blocking
itself.
What could be reason and how to solve this issue'
ThanksSo you are saying that when you run sp_who the spid that appears in
the "blk" column is the SAME as the value in the "spid" column of the
same line?
Roy Harvey
Beacon Falls, CT
On Thu, 8 Jun 2006 12:54:02 -0700, TSQL
<TSQL@.discussions.microsoft.com> wrote:

>I have a stored proc created for report and now suddenly the report is taki
ng
>lot of time to run. The store proc is having simple select statement with
>multiple case statements.
>I checked the sysprocesses table and noticed that the process is blocking
>itself.
>What could be reason and how to solve this issue'
>Thanks|||You're running SQL 2000 with SP4 installed:
http://support.microsoft.com/defaul...KB;EN-US;906344
TSQL wrote:
> I have a stored proc created for report and now suddenly the report is tak
ing
> lot of time to run. The store proc is having simple select statement with
> multiple case statements.
> I checked the sysprocesses table and noticed that the process is blocking
> itself.
> What could be reason and how to solve this issue'
> Thanks|||Yes Exactly.
I ran -
select * from sysprocesses
where physical_io>25 or cpu>15 or memusage>15
order by blocked desc
"Roy Harvey" wrote:

> So you are saying that when you run sp_who the spid that appears in
> the "blk" column is the SAME as the value in the "spid" column of the
> same line?
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 8 Jun 2006 12:54:02 -0700, TSQL
> <TSQL@.discussions.microsoft.com> wrote:
>
>|||seems wierd. Try it with
OPYION (MAXDOP 1) and
and recompile the stored procedure.
Can you post the script?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||SQL 2000 latches quite often block other latch request from the same
SPID by design (which Tracy also acknowledged; see
http://support.microsoft.com/default.aspx/kb/906344). With SP4
Microsoft have started displaying those latch blocks (in addition to the
lock blocks) in the blocked column of sysprocesses, so now it looks like
a SPID is blocking itself (which, I guess, technically, it is) but in
fact it's usually just waiting on a page to be read into memory due to
slow I/O.
*mike hodgson*
http://sqlnerd.blogspot.com
Omnibuzz wrote:

>seems wierd. Try it with
>OPYION (MAXDOP 1) and
>and recompile the stored procedure.
>Can you post the script?
>|||Thanks Mike, Tracy. Went through the article. Makes sense. Makes a lot of
sense.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/

Process 53 generated fatal exception

Is anyone having problems with text columns once SP3A is installed?
I have a stored procedure that simply updates a text column with changes and
I'm getting what looks like a fatal error in SQL Server. It shows up with
this error posted through JDBC:
53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
I didn't see anything in the MS knowledge base. If we remove sp3a and put o
n sp3, it doesn't happen.Amy,
I would raise a call with Microsoft support. You may be encountering a bug.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amy Thropp wrote:
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes a
nd I'm getting what looks like a fatal error in SQL Server. It shows up wit
h this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Serv
er is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3,
it doesn't happen.|||If the KB articles on this issue don't apply to your environment, consider
opening a case with Microsoft PSS. You won't be charged if this is a bug,
which is likely with an AV.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amy Thropp" <AmyThropp@.discussions.microsoft.com> wrote in message
news:8E65B0E2-73CD-472D-8947-03ED80BA3F85@.microsoft.com...
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes
and I'm getting what looks like a fatal error in SQL Server. It shows up
with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put
on sp3, it doesn't happen.

Process 53 generated fatal exception

Is anyone having problems with text columns once SP3A is installed?
I have a stored procedure that simply updates a text column with changes and I'm getting what looks like a fatal error in SQL Server. It shows up with this error posted through JDBC:
53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3, it doesn't happen.Amy,
I would raise a call with Microsoft support. You may be encountering a bug.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amy Thropp wrote:
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes and I'm getting what looks like a fatal error in SQL Server. It shows up with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3, it doesn't happen.|||If the KB articles on this issue don't apply to your environment, consider
opening a case with Microsoft PSS. You won't be charged if this is a bug,
which is likely with an AV.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Amy Thropp" <AmyThropp@.discussions.microsoft.com> wrote in message
news:8E65B0E2-73CD-472D-8947-03ED80BA3F85@.microsoft.com...
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes
and I'm getting what looks like a fatal error in SQL Server. It shows up
with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put
on sp3, it doesn't happen.

Process 53 generated fatal exception

Is anyone having problems with text columns once SP3A is installed?
I have a stored procedure that simply updates a text column with changes and I'm getting what looks like a fatal error in SQL Server. It shows up with this error posted through JDBC:
53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3, it doesn't happen.
Amy,
I would raise a call with Microsoft support. You may be encountering a bug.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Amy Thropp wrote:
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes and I'm getting what looks like a fatal error in SQL Server. It shows up with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put on sp3, it doesn't happen.
|||If the KB articles on this issue don't apply to your environment, consider
opening a case with Microsoft PSS. You won't be charged if this is a bug,
which is likely with an AV.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amy Thropp" <AmyThropp@.discussions.microsoft.com> wrote in message
news:8E65B0E2-73CD-472D-8947-03ED80BA3F85@.microsoft.com...
> Is anyone having problems with text columns once SP3A is installed?
> I have a stored procedure that simply updates a text column with changes
and I'm getting what looks like a fatal error in SQL Server. It shows up
with this error posted through JDBC:
> 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
> I didn't see anything in the MS knowledge base. If we remove sp3a and put
on sp3, it doesn't happen.