Limits to length of Stored Proc

[C Kirby]

In SQL Server 2000, I've got a rather lengthy stored procedure, which
creates a lot of temporary tables as it processes down through a few
sets of data.
When testing it through Query Analyzer, it runs fine (a bit slow
though). But when I try to run it through the ade, it doesn't do
anything. It runs through the procedure in milliseconds but doesn't
seem to ever actually start it. If I change the calling code in the
ade VBA to refer to a different SP, it will call/run the different SP,
so I don't think its the way I call it.
Is there a limit to the number of lines a stored procedure can have,
or some other limit on memory or transactions?

[Dan Guzman]

I doubt the proc size is the issue. Have you included SET NOCOUNT ON at
the beginning of the proc?

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"C Kirby" <ck****@mindspr ing.com> wrote in message
news:ps******** *************** *********@4ax.c om...

In SQL Server 2000, I've got a rather lengthy stored procedure, which
creates a lot of temporary tables as it processes down through a few
sets of data.
When testing it through Query Analyzer, it runs fine (a bit slow
though). But when I try to run it through the ade, it doesn't do
anything. It runs through the procedure in milliseconds but doesn't
seem to ever actually start it. If I change the calling code in the
ade VBA to refer to a different SP, it will call/run the different SP,
so I don't think its the way I call it.
Is there a limit to the number of lines a stored pr

[John Bell]

Hi

You would not be able to compile the Stored procedure if you have hit a size
limit, although it could be the query cost that is somehow behaving
differently and therefore hitting that limit (see sp_configure/ query
governor cost limit in Books online).

If you can run this through QA it seems most likely that you are not passing
the parameters incorrectly in your code, so try adding some debug
statements! If the procedure is as long as you say, it is probably worth
consider modularising it and splitting it into sub-procedures; you may also
be able to re-write the code to be more efficient. This may also help stop
recompilations.

John
"C Kirby" <ck****@mindspr ing.com> wrote in message
news:ps******** *************** *********@4ax.c om...

In SQL Server 2000, I've got a rather lengthy stored procedure, which
creates a lot of temporary tables as it processes down through a few
sets of data.
When testing it through Query Analyzer, it runs fine (a bit slow
though). But when I try to run it through the ade, it doesn't do
anything. It runs through the procedure in milliseconds but doesn't
seem to ever actually start it. If I change the calling code in the
ade VBA to refer to a different SP, it will call/run the different SP,
so I don't think its the way I call it.
Is there a limit to the number of lines a stored procedure can have,
or some other limit on memory or transactions?

[C Kirby]

Thanks for the help.. I do have NOCOUNT set to on, so I don't think
that is the problem.

I've tried to setup the debugging by using the IF @@ERROR <>0 ,
Rollback and return X.
I can't seem to get the front end to actually look at the return value
to tell if the sp ran successfully though, so something isn't quite
right with that.

As far as the parameters go, this sp doesn't use any, so I'm not
sending it any. Could this be why the front end isn't reading the
return parameter?

One question on splitting out the different functions into separate
stored procs. The very first thing the sp does is to create a temp
table holding the records to be manipulated. Right now it is named
#temp (or something like that). In order to reference this table from
another sp, should a use the double # ('##temp')?

On Tue, 9 Sep 2003 09:02:09 +0100, "John Bell"
<jb************ @hotmail.com> wrote:

Hi

You would not be able to compile the Stored procedure if you have hit a size
limit, although it could be the query cost that is somehow behaving
differently and therefore hitting that limit (see sp_configure/ query
governor cost limit in Books online).

If you can run this through QA it seems most likely that you are not passing
the parameters incorrectly in your code, so try adding some debug
statements! If the procedure is as long as you say, it is probably worth
consider modularising it and splitting it into sub-procedures; you may also
be able to re-write the code to be more efficient. This may also help stop
recompilations .

John
"C Kirby" <ck****@mindspr ing.com> wrote in message
news:ps******* *************** **********@4ax. com...

In SQL Server 2000, I've got a rather lengthy stored procedure, which
creates a lot of temporary tables as it processes down through a few
sets of data.
When testing it through Query Analyzer, it runs fine (a bit slow
though). But when I try to run it through the ade, it doesn't do
anything. It runs through the procedure in milliseconds but doesn't
seem to ever actually start it. If I change the calling code in the
ade VBA to refer to a different SP, it will call/run the different SP,
so I don't think its the way I call it.
Is there a limit to the number of lines a stored procedure can have,
or some other limit on memory or transactions?

[Dan Guzman]

> I've tried to setup the debugging by using the IF @@ERROR <>0 ,

Rollback and return X.
I can't seem to get the front end to actually look at the return value
to tell if the sp ran successfully though, so something isn't quite
right with that.

As far as the parameters go, this sp doesn't use any, so I'm not
sending it any. Could this be why the front end isn't reading the
return parameter?
The return value is essentially an output parameter. Does your
procedure return resultsets? If so, you may need to consume those
before the return value is available.
One question on splitting out the different functions into separate
stored procs. The very first thing the sp does is to create a temp
table holding the records to be manipulated. Right now it is named
#temp (or something like that). In order to reference this table from
another sp, should a use the double # ('##temp')?
The local temp table (#temp) is visible to the nested procs so you don't
need to resort to a global (##temp) table. An issue with global temp
tables is that you'll need to uniquely name them to handle concurrency.

--
Hope this helps.

Dan Guzman
SQL Server MVP
"C Kirby" <ck****@mindspr ing.com> wrote in message
news:4m******** *************** *********@4ax.c om... Thanks for the help.. I do have NOCOUNT set to on, so I don't think
that is the problem.

I've tried to setup the debugging by using the IF @@ERROR <>0 ,
Rollback and return X.
I can't seem to get the front end to actually look at the return value
to tell if the sp ran successfully though, so something isn't quite
right with that.

As far as the parameters go, this sp doesn't use any, so I'm not
sending it any. Could this be why the front end isn't reading the
return parameter?

One question on splitting out the different functions into separate
stored procs. The very first thing the sp does is to create a temp
table holding the records to be manipulated. Right now it is named
#temp (or something like that). In order to reference this table from
another sp, should a use the double # ('##temp')?

On Tue, 9 Sep 2003 09:02:09 +0100, "John Bell"
<jb************ @hotmail.com> wrote:

Hi

You would not be able to compile the Stored procedure if you have hit a sizelimit, although it could be the query cost that is somehow behaving
differently and therefore hitting that limit (see sp_configure/ query
governor cost limit in Books online).

If you can run this through QA it seems most likely that you are not passingthe parameters incorrectly in your code, so try adding some debug
statements! If the procedure is as long as you say, it is probably worthconsider modularising it and splitting it into sub-procedures; you may alsobe able to re-write the code to be more efficient. This may also help stoprecompilations .

John
"C Kirby" <ck****@mindspr ing.com> wrote in message
news:ps******* *************** **********@4ax. com...

In SQL Server 2000, I've got a rather lengthy stored procedure, which creates a lot of temporary tables as it processes down through a few sets of data.
When testing it through Query Analyzer, it runs fine (a bit slow
though). But when I try to run it through the ade, it doesn't do
anything. It runs through the procedure in milliseconds but doesn't seem to ever actually start it. If I change the calling code in the ade VBA to refer to a different SP, it will call/run the different SP, so I don't think its the way I call it.
Is there a limit to the number of lines a stored procedure can have, or some other limit on memory or transactions?

[Erland Sommarskog]

Dan Guzman (da*******@nosp am-earthlink.net) writes:

The return value is essentially an output parameter. Does your
procedure return resultsets? If so, you may need to consume those
before the return value is available.

To add to what Dan says here, it depends on whether you are using
client-side or server-side cursor. With client-side cursors you can
access the return value directly.

But we are a bit in the dark here, as we have not seen any of your
code, neither the ADO code, nor the SQL code.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[C Kirby]

You guys have *no* idea how much I appreciate the help!!

Here's the code that I am using to call the sp from the Access ade

'call stored procedure
docmd.hourglass true
Set com = New ADODB.Command
With com
.ActiveConnecti on = getadoconnectst ring("SM")
.CommandText = "qryBSTransPost "
.CommandType = adCmdStoredProc
.CommandTimeout = 0
.Execute , , adAsyncExecute

If .Parameters(0) <> 0 Then
'operation failed
MsgBox "Fail"
Else
DoCmd.Hourglass False
MsgBox "Transactio n Import Complete", vbOKOnly, "Brokerage
Transactions Imported"
End If

End With
Set com = Nothing

This is all going into a pre-exisiting software package that has the
getadoconnectio nstring function. Since the simple stored procs work
with this call, I'm going to say that the connection is ok.

Due to the length of the sp, I won't post it, but would be glad to let
anyone see it (even though it is *ugly*!!). It does not return any
records. It basically looks at a table, picks the records that meet a
certain criteria, creates a temp table to hold the Primary keys to
those records, then runs through a series of data manipulations using
more temp tables, then adds those created records into a couple of
different tables, then changes some values in the original records so
that they no longer meet the initial criteria.

This is the final section of the sp:
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN 11
END

COMMIT TRANSACTION
GO
Regardless of what actually happens with the sp when I call it from
the ade, the .parameter(0) value never triggers the 'fail' option..
On Wed, 10 Sep 2003 22:26:36 +0000 (UTC), Erland Sommarskog
<so****@algonet .se> wrote:

Dan Guzman (da*******@nosp am-earthlink.net) writes:

The return value is essentially an output parameter. Does your
procedure return resultsets? If so, you may need to consume those
before the return value is available.

To add to what Dan says here, it depends on whether you are using
client-side or server-side cursor. With client-side cursors you can
access the return value directly.

But we are a bit in the dark here, as we have not seen any of your
code, neither the ADO code, nor the SQL code.

[Dan Guzman]

Is there some reason you are using the adAsyncExecute option here? If
not, you might try removing the option from your Execute method.

It looks to me like your code isn't written to handle asynchronous proc
execution. The code is checking the return value even though the proc
may still be executing. The code probably works with your other procs
simply because they complete before you check the result.

Also, note @@ERROR is changed after every SQL statement so you need to
check it after each statement and perform error processing then. For
example:

BEGIN TRAN
INSERT INTO MyTable VALUES(1)
IF @@ERROR <> 0 GOTO ErrorHandler
INSERT INTO MyTable VALUES(2)
IF @@ERROR <> 0 GOTO ErrorHandler
COMMIT
RETURN 0

ErrorHandler:
IF @@TRANCOUNT > 0 ROLLBACK
RETURN 11
--
Hope this helps.

Dan Guzman
SQL Server MVP

"C Kirby" <ck****@mindspr ing.com> wrote in message
news:d2******** *************** *********@4ax.c om...

You guys have *no* idea how much I appreciate the help!!

Here's the code that I am using to call the sp from the Access ade

'call stored procedure
docmd.hourglass true
Set com = New ADODB.Command
With com
.ActiveConnecti on = getadoconnectst ring("SM")
.CommandText = "qryBSTransPost "
.CommandType = adCmdStoredProc
.CommandTimeout = 0
.Execute , , adAsyncExecute

If .Parameters(0) <> 0 Then
'operation failed
MsgBox "Fail"
Else
DoCmd.Hourglass False
MsgBox "Transactio n Import Complete", vbOKOnly, "Brokerage
Transactions Imported"
End If

End With
Set com = Nothing

This is all going into a pre-exisiting software package that has the
getadoconnectio nstring function. Since the simple stored procs work
with this call, I'm going to say that the connection is ok.

Due to the length of the sp, I won't post it, but would be glad to let
anyone see it (even though it is *ugly*!!). It does not return any
records. It basically looks at a table, picks the records that meet a
certain criteria, creates a temp table to hold the Primary keys to
those records, then runs through a series of data manipulations using
more temp tables, then adds those created records into a couple of
different tables, then changes some values in the original records so
that they no longer meet the initial criteria.

This is the final section of the sp:
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN 11
END

COMMIT TRANSACTION
GO
Regardless of what actually happens with the sp when I call it from
the ade, the .parameter(0) value never triggers the 'fail' option..
On Wed, 10 Sep 2003 22:26:36 +0000 (UTC), Erland Sommarskog
<so****@algonet .se> wrote:

Dan Guzman (da*******@nosp am-earthlink.net) writes:

The return value is essentially an output parameter. Does your
procedure return resultsets? If so, you may need to consume those
before the return value is available.

To add to what Dan says here, it depends on whether you are using
client-side or server-side cursor. With client-side cursors you can
access the return value directly.

But we are a bit in the dark here, as we have not seen any of your
code, neither the ADO code, nor the SQL code.

[C Kirby]

Looks like that was the problem, Dan! I removed the adAsync option
and the procedure is running!!!! Thanks for the help!!

I'm going to take a shot at splitting the the sp into a few sub
procedures and then see if I can get the return value to work..

Thanks for all the help from everybody!!!!!! !

On Thu, 11 Sep 2003 03:02:13 GMT, "Dan Guzman"
<da*******@nosp am-earthlink.net> wrote:

Is there some reason you are using the adAsyncExecute option here? If
not, you might try removing the option from your Execute method.

It looks to me like your code isn't written to handle asynchronous proc
execution. The code is checking the return value even though the proc
may still be executing. The code probably works with your other procs
simply because they complete before you check the result.

Also, note @@ERROR is changed after every SQL statement so you need to
check it after each statement and perform error processing then. For
example:

BEGIN TRAN
INSERT INTO MyTable VALUES(1)
IF @@ERROR <> 0 GOTO ErrorHandler
INSERT INTO MyTable VALUES(2)
IF @@ERROR <> 0 GOTO ErrorHandler
COMMIT
RETURN 0

ErrorHandler :
IF @@TRANCOUNT > 0 ROLLBACK
RETURN 11