473,216 Members | 1,287 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

Limits to length of Stored Proc

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?
Jul 20 '05 #1
8 11222
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****@mindspring.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 pr

Jul 20 '05 #2
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****@mindspring.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?

Jul 20 '05 #3
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****@mindspring.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?


Jul 20 '05 #4
> 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****@mindspring.com> wrote in message
news:4m********************************@4ax.com... 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****@mindspring.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?

Jul 20 '05 #5
Dan Guzman (da*******@nospam-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
Jul 20 '05 #6
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
.ActiveConnection = getadoconnectstring("SM")
.CommandText = "qryBSTransPost"
.CommandType = adCmdStoredProc
.CommandTimeout = 0
.Execute , , adAsyncExecute

If .Parameters(0) <> 0 Then
'operation failed
MsgBox "Fail"
Else
DoCmd.Hourglass False
MsgBox "Transaction 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
getadoconnectionstring 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*******@nospam-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.


Jul 20 '05 #7
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****@mindspring.com> wrote in message
news:d2********************************@4ax.com...
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
.ActiveConnection = getadoconnectstring("SM")
.CommandText = "qryBSTransPost"
.CommandType = adCmdStoredProc
.CommandTimeout = 0
.Execute , , adAsyncExecute

If .Parameters(0) <> 0 Then
'operation failed
MsgBox "Fail"
Else
DoCmd.Hourglass False
MsgBox "Transaction 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
getadoconnectionstring 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*******@nospam-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.

Jul 20 '05 #8
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*******@nospam-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


Jul 20 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: June Moore | last post by:
Hi all, I have a stored procedure that return a resultset e.g. stored proc: get_employee_details select emp_id, emp_name, emp_salary, emp_position from empoloyee I would like to write...
9
by: Wolfgang Kreuzer | last post by:
Try hard to become familiar with T-SQL. Can anybodey tell me the best way to deal with set's provided by a stored procedure. Til yesterday I thougt trapping set in temp table using INSERT EXEC...
0
by: Dave Sisk | last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE...
5
by: Rhino | last post by:
This question relates to DB2 Version 6 on OS/390. Can a (COBOL) stored procedure on this platform do file I/O, i.e. write to a sequential file? I am trying to debug a stored procedure. As far...
2
by: Rhino | last post by:
I am getting an sqlcode of -927 when I execute SQL within a COBOL stored procedure in DB2 OS/390 Version 6 on OS/390. I have looked at the error message for that condition and tried everything I...
1
by: mike | last post by:
If I try and do a "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1" after I have run a stored procedure in DB2 version 7.2, I get the last generated Key before the CallableStatement was executed...
14
by: Roy | last post by:
Apologies for the cross-post, but this truly is a two-sided question. Given the option of creating Looping statements within a stored proc of sql server or in the code-behind of an .net webpage,...
0
by: balaji krishna | last post by:
Hi, I need to handle the return set from COBOL stored procedure from my invoking Java program. I do not know, how many rows the stored proc SQL fetches.I have declared the cursor in that proc, but i...
0
by: mirandacascade | last post by:
Questions toward the bottom of the post. Situation is this: 1) Access 97 2) SQL Server 2000 3) The Access app: a) sets up pass-thru query b) .SQL property of querydef is a string, the...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.