What kind of code in a stored procedure is actually run on client?

23 Jun 2006 4:38 PM

nick

I created a stored procedure Sql Server 2000. There is a bug in the sp - an
infinite loop. I run it on my client pc using SSMS. And my client PC CPU soar
to 100%.

I thought the whole SP will be run on the server side. It seems it runs on
the client side? Will the data in @tableVar be sent to client side too if I
have the following code in a SP?

insert into @tableVar
select ....
from ....

update @tableVar
......

23 Jun 2006 4:59 PM

Arnie Rowland

None of the Stored Procedure is executed on the client. (Of course, if the
client is actually executing on the server computer, then ...) Oh, and SSMS
is not the 'typical' client in that it maintains constant communication with
the server, and a looping sproc 'could' be sending a constant stream of
output back to SSMS. Try executing your stored procedure from Query Analyzer
from the client and see if the CPU maxes out.

The data in @TableVar will NOT be sent down to the client UNLESS that is the
output from the Stored Procedure or Function.

To paraphrase: What happens in Vegas, stays in Vegas -except for the
trinkets taken home. What happens in SQL Server stays in SQL Server -except
for the results requested and expected.

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"nick" <n***@discussions.microsoft.com> wrote in message
news:C038B132-DF9E-4C59-8454-32FB806B1F58@microsoft.com...
>I created a stored procedure Sql Server 2000. There is a bug in the sp - an
> infinite loop. I run it on my client pc using SSMS. And my client PC CPU
> soar
> to 100%.
>
> I thought the whole SP will be run on the server side. It seems it runs on
> the client side? Will the data in @tableVar be sent to client side too if
> I
> have the following code in a SP?
>
> insert into @tableVar
> select ....
> from ....
>
> update @tableVar
> .....

23 Jun 2006 5:12 PM

nick

I guess the 100% is caused by output. However, SSMS was so busy that I
couldn't stop the executing and I had to kill it using task manager.

Show quote

"Arnie Rowland" wrote:

> None of the Stored Procedure is executed on the client. (Of course, if the
> client is actually executing on the server computer, then ...) Oh, and SSMS
> is not the 'typical' client in that it maintains constant communication with
> the server, and a looping sproc 'could' be sending a constant stream of
> output back to SSMS. Try executing your stored procedure from Query Analyzer
> from the client and see if the CPU maxes out.
>
> The data in @TableVar will NOT be sent down to the client UNLESS that is the
> output from the Stored Procedure or Function.
>
> To paraphrase: What happens in Vegas, stays in Vegas -except for the
> trinkets taken home. What happens in SQL Server stays in SQL Server -except
> for the results requested and expected.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
>
> *Yet Another Certification Exam
>
>
> "nick" <n***@discussions.microsoft.com> wrote in message
> news:C038B132-DF9E-4C59-8454-32FB806B1F58@microsoft.com...
> >I created a stored procedure Sql Server 2000. There is a bug in the sp - an
> > infinite loop. I run it on my client pc using SSMS. And my client PC CPU
> > soar
> > to 100%.
> >
> > I thought the whole SP will be run on the server side. It seems it runs on
> > the client side? Will the data in @tableVar be sent to client side too if
> > I
> > have the following code in a SP?
> >
> > insert into @tableVar
> > select ....
> > from ....
> >
> > update @tableVar
> > .....
>
>
>