Running a stored procedure and running out of memory problem.

Hi,

I'm looking for some guidance with a problem I'm having with a stored
procedure and running out of memory.
ASA ver 8.0.2.4122
What I'm trying to figure out is what elements within a stored procedure
could eat up the memory if done a repeated number of times or does my stored
procedure fail because my system lacks the appropriate amount of memory to
do the job.

I have a stored procedure that loops around based on the number of records
in a cursor, performs 'selects' within the loop to find other data based on
the cursor data, performs some calculations and finally updates the DB
depending on the final result.
The number of records in the cursor is estimated at a few million records,
so the stored procedure would need to loop around a few million times.
However, the system runs out of memory even before 200,000 let alone 1
million. Now the system I am running on has 1GB of physical memory, but I
think the availability is more like 250MB.


Below is part of the error log that is produced by ASA.


Part of the error log produced by iSQL
----------------------------
                              +-----------------+
                              |   Stack Trace   |
                              +-----------------+

Current thread: Database interface thread
java.lang.OutOfMemoryError
 <<no stack trace available>>



                                  +----------+
                                  |   ISQL   |
                                  +----------+

Thu Nov 11 01:51:36 EST 2004
ISQL version = 8.0.2, Build 4218
CommandLineOptions
  ConnectionInfo:
DSN=CRMDB;UID=dba;PWD=sql;DBN=CRMDB;<JDBCDRIVER>=com.sybase.jdbc2.jdbc.SybDr
iver
  Command tail: call asi_RunPricing(1)
  Initial SQL statement: (none)
  Execute initial SQL statement: No
  Use GUI: No
  Quiet mode is: Off
  Syntax check mode is: Off

ConnectionInfo for active connection:
DSN=CRMDB;UID=dba;PWD=sql;DBN=CRMDB;<JDBCDRIVER>=com.sybase.jdbc2.jdbc.SybDr
iver

*** A java.lang.OutOfMemoryError exception was caught while gathering
application information
  null
-----------------------------------------------


The actual stored procedure code is really long, so I'm not going to post
all of it, but the following is part real/pseudo code (mostly pseudo).


/*   declare statements */
/* cursor declare statement */
 declare cur_Customer dynamic scroll cursor for
select intm_listing.customer_number, intm_listing.material_number,
intm_product.material_class, intm_customer.tax_classification,
intm_product.tax1
  from intm_listing, intm_product, intm_customer
  where intm_listing.customer_number = intm_customer.customer_number
  and intm_listing.material_number = intm_product.material_id
  and intm_listing.valid_from <= current date and intm_listing.valid_to >=
current date
  union
  select intm_listing.customer_number, intm_listing.material_number,
intm_product.material_class, intm_customer.tax_classification,
intm_product.tax1
  from intm_listing, intm_product, intm_customer, (select a.customer_number
as cn1, b.customer_number as cn2 from intm_customer a left outer join
  (select distinct intm_listing.customer_number from intm_listing) b on
a.customer_number=b.customer_number and b.customer_number=null) d
  where intm_listing.customer_number = d.cn1
  and d.cn1=intm_customer.customer_number
  and intm_listing.material_number = intm_product.material_id
  and intm_listing.valid_from <= current date and intm_listing.valid_to >=
current date;

open cursor with hold
loop
    fetch first record from cursor

    find parent nodes for this particular customer

    /* Condition 1 */
    set some variables;
    perform select based on the variable values;
    perform calculation based on results of select;

    /* Condition 2 */
    set some variables;
    perform select based on the variable values;
    perform calculation based on results of select;

    /* Condition X (there are about 12 different conditions) */
    ....same as above....

    check if resultant value is the same was what is already in database
    if calculated value different from database, update database
    else if calculated value the same don't do anything
    else if calculated value doesn't exist, insert into database
    for every 50 records processed, perform a commit;
end loop
close cursor
commit


TIA for any help

tom



0
Thomas
11/11/2004 3:55:31 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

3 Replies
744 Views

Similar Articles

[PageSpeed] 35

This may be tied to some limitation of JConnect ...
try running it with the JDBC-ODBC bridge selected
(which in 8.0.2 is not the Sun one but our iAnywhere
 JDBC driver that is now the default and recommended
 JDBC driver in ASA 9).

Also feel free to compare this against one of the latest
8.0.2/.3 ebfs (only the client side is required for the test)
or 9.0.1 (the Developer Edition is freely downloadable)

Also JConnect 5.5 has numerous ebfs available for it.  Last
time I checked EDS#13 (which would make it something
like version 5.5.0.13 if JConnect followed the standard numbering
system that ASA does).  Those ebfs are available at

    http://downloads.sybase.com/swd/base.do

(Note you will need to increase the timeframe to at least 9 months
  to get the

    "JConnect for JDBC"

 Product Family to show up on that page since it has been a little while
 since it rolled out an ebf for that ... Date: Apr 30, 2004 for ESD#13)




"Thomas" <thomas@someplace.net> wrote in message 
news:41938b73$1@forums-1-dub...
> Hi,
>
> I'm looking for some guidance with a problem I'm having with a stored
> procedure and running out of memory.
> ASA ver 8.0.2.4122
> What I'm trying to figure out is what elements within a stored procedure
> could eat up the memory if done a repeated number of times or does my 
> stored
> procedure fail because my system lacks the appropriate amount of memory to
> do the job.
>
> I have a stored procedure that loops around based on the number of records
> in a cursor, performs 'selects' within the loop to find other data based 
> on
> the cursor data, performs some calculations and finally updates the DB
> depending on the final result.
> The number of records in the cursor is estimated at a few million records,
> so the stored procedure would need to loop around a few million times.
> However, the system runs out of memory even before 200,000 let alone 1
> million. Now the system I am running on has 1GB of physical memory, but I
> think the availability is more like 250MB.
>
>
> Below is part of the error log that is produced by ASA.
>
>
> Part of the error log produced by iSQL
> ----------------------------
>                              +-----------------+
>                              |   Stack Trace   |
>                              +-----------------+
>
> Current thread: Database interface thread
> java.lang.OutOfMemoryError
> <<no stack trace available>>
>
>
>
>                                  +----------+
>                                  |   ISQL   |
>                                  +----------+
>
> Thu Nov 11 01:51:36 EST 2004
> ISQL version = 8.0.2, Build 4218
> CommandLineOptions
>  ConnectionInfo:
> DSN=CRMDB;UID=dba;PWD=sql;DBN=CRMDB;<JDBCDRIVER>=com.sybase.jdbc2.jdbc.SybDr
> iver
>  Command tail: call asi_RunPricing(1)
>  Initial SQL statement: (none)
>  Execute initial SQL statement: No
>  Use GUI: No
>  Quiet mode is: Off
>  Syntax check mode is: Off
>
> ConnectionInfo for active connection:
> DSN=CRMDB;UID=dba;PWD=sql;DBN=CRMDB;<JDBCDRIVER>=com.sybase.jdbc2.jdbc.SybDr
> iver
>
> *** A java.lang.OutOfMemoryError exception was caught while gathering
> application information
>  null
> -----------------------------------------------
>
>
> The actual stored procedure code is really long, so I'm not going to post
> all of it, but the following is part real/pseudo code (mostly pseudo).
>
>
> /*   declare statements */
> /* cursor declare statement */
> declare cur_Customer dynamic scroll cursor for
> select intm_listing.customer_number, intm_listing.material_number,
> intm_product.material_class, intm_customer.tax_classification,
> intm_product.tax1
>  from intm_listing, intm_product, intm_customer
>  where intm_listing.customer_number = intm_customer.customer_number
>  and intm_listing.material_number = intm_product.material_id
>  and intm_listing.valid_from <= current date and intm_listing.valid_to >=
> current date
>  union
>  select intm_listing.customer_number, intm_listing.material_number,
> intm_product.material_class, intm_customer.tax_classification,
> intm_product.tax1
>  from intm_listing, intm_product, intm_customer, (select a.customer_number
> as cn1, b.customer_number as cn2 from intm_customer a left outer join
>  (select distinct intm_listing.customer_number from intm_listing) b on
> a.customer_number=b.customer_number and b.customer_number=null) d
>  where intm_listing.customer_number = d.cn1
>  and d.cn1=intm_customer.customer_number
>  and intm_listing.material_number = intm_product.material_id
>  and intm_listing.valid_from <= current date and intm_listing.valid_to >=
> current date;
>
> open cursor with hold
> loop
>    fetch first record from cursor
>
>    find parent nodes for this particular customer
>
>    /* Condition 1 */
>    set some variables;
>    perform select based on the variable values;
>    perform calculation based on results of select;
>
>    /* Condition 2 */
>    set some variables;
>    perform select based on the variable values;
>    perform calculation based on results of select;
>
>    /* Condition X (there are about 12 different conditions) */
>    ....same as above....
>
>    check if resultant value is the same was what is already in database
>    if calculated value different from database, update database
>    else if calculated value the same don't do anything
>    else if calculated value doesn't exist, insert into database
>    for every 50 records processed, perform a commit;
> end loop
> close cursor
> commit
>
>
> TIA for any help
>
> tom
>
>
> 


0
Nick
11/11/2004 4:51:29 PM
Are you referring to the -datasource flag for dbisql?
If so, do I just type '-datasource "JDBC-ODBC"'?

Thanks,

Tom

"Nick Elson" <no_spam_nicelson@sybase.com> wrote in message
news:4193988d$1@forums-2-dub...
> This may be tied to some limitation of JConnect ...
> try running it with the JDBC-ODBC bridge selected
> (which in 8.0.2 is not the Sun one but our iAnywhere
>  JDBC driver that is now the default and recommended
>  JDBC driver in ASA 9).
>
> Also feel free to compare this against one of the latest
> 8.0.2/.3 ebfs (only the client side is required for the test)
> or 9.0.1 (the Developer Edition is freely downloadable)
>
> Also JConnect 5.5 has numerous ebfs available for it.  Last
> time I checked EDS#13 (which would make it something
> like version 5.5.0.13 if JConnect followed the standard numbering
> system that ASA does).  Those ebfs are available at
>
>     http://downloads.sybase.com/swd/base.do
>
> (Note you will need to increase the timeframe to at least 9 months
>   to get the
>
>     "JConnect for JDBC"
>
>  Product Family to show up on that page since it has been a little while
>  since it rolled out an ebf for that ... Date: Apr 30, 2004 for ESD#13)
>
>
>
>
> "Thomas" <thomas@someplace.net> wrote in message
> news:41938b73$1@forums-1-dub...
> > Hi,
> >
> > I'm looking for some guidance with a problem I'm having with a stored
> > procedure and running out of memory.
> > ASA ver 8.0.2.4122
> > What I'm trying to figure out is what elements within a stored procedure
> > could eat up the memory if done a repeated number of times or does my
> > stored
> > procedure fail because my system lacks the appropriate amount of memory
to
> > do the job.
> >
> > I have a stored procedure that loops around based on the number of
records
> > in a cursor, performs 'selects' within the loop to find other data based
> > on
> > the cursor data, performs some calculations and finally updates the DB
> > depending on the final result.
> > The number of records in the cursor is estimated at a few million
records,
> > so the stored procedure would need to loop around a few million times.
> > However, the system runs out of memory even before 200,000 let alone 1
> > million. Now the system I am running on has 1GB of physical memory, but
I
> > think the availability is more like 250MB.
> >
> >
> > Below is part of the error log that is produced by ASA.
> >
> >
> > Part of the error log produced by iSQL
> > ----------------------------
> >                              +-----------------+
> >                              |   Stack Trace   |
> >                              +-----------------+
> >
> > Current thread: Database interface thread
> > java.lang.OutOfMemoryError
> > <<no stack trace available>>
> >
> >
> >
> >                                  +----------+
> >                                  |   ISQL   |
> >                                  +----------+
> >
> > Thu Nov 11 01:51:36 EST 2004
> > ISQL version = 8.0.2, Build 4218
> > CommandLineOptions
> >  ConnectionInfo:
> >
DSN=CRMDB;UID=dba;PWD=sql;DBN=CRMDB;<JDBCDRIVER>=com.sybase.jdbc2.jdbc.SybDr
> > iver
> >  Command tail: call asi_RunPricing(1)
> >  Initial SQL statement: (none)
> >  Execute initial SQL statement: No
> >  Use GUI: No
> >  Quiet mode is: Off
> >  Syntax check mode is: Off
> >
> > ConnectionInfo for active connection:
> >
DSN=CRMDB;UID=dba;PWD=sql;DBN=CRMDB;<JDBCDRIVER>=com.sybase.jdbc2.jdbc.SybDr
> > iver
> >
> > *** A java.lang.OutOfMemoryError exception was caught while gathering
> > application information
> >  null
> > -----------------------------------------------
> >
> >
> > The actual stored procedure code is really long, so I'm not going to
post
> > all of it, but the following is part real/pseudo code (mostly pseudo).
> >
> >
> > /*   declare statements */
> > /* cursor declare statement */
> > declare cur_Customer dynamic scroll cursor for
> > select intm_listing.customer_number, intm_listing.material_number,
> > intm_product.material_class, intm_customer.tax_classification,
> > intm_product.tax1
> >  from intm_listing, intm_product, intm_customer
> >  where intm_listing.customer_number = intm_customer.customer_number
> >  and intm_listing.material_number = intm_product.material_id
> >  and intm_listing.valid_from <= current date and intm_listing.valid_to
>=
> > current date
> >  union
> >  select intm_listing.customer_number, intm_listing.material_number,
> > intm_product.material_class, intm_customer.tax_classification,
> > intm_product.tax1
> >  from intm_listing, intm_product, intm_customer, (select
a.customer_number
> > as cn1, b.customer_number as cn2 from intm_customer a left outer join
> >  (select distinct intm_listing.customer_number from intm_listing) b on
> > a.customer_number=b.customer_number and b.customer_number=null) d
> >  where intm_listing.customer_number = d.cn1
> >  and d.cn1=intm_customer.customer_number
> >  and intm_listing.material_number = intm_product.material_id
> >  and intm_listing.valid_from <= current date and intm_listing.valid_to
>=
> > current date;
> >
> > open cursor with hold
> > loop
> >    fetch first record from cursor
> >
> >    find parent nodes for this particular customer
> >
> >    /* Condition 1 */
> >    set some variables;
> >    perform select based on the variable values;
> >    perform calculation based on results of select;
> >
> >    /* Condition 2 */
> >    set some variables;
> >    perform select based on the variable values;
> >    perform calculation based on results of select;
> >
> >    /* Condition X (there are about 12 different conditions) */
> >    ....same as above....
> >
> >    check if resultant value is the same was what is already in database
> >    if calculated value different from database, update database
> >    else if calculated value the same don't do anything
> >    else if calculated value doesn't exist, insert into database
> >    for every 50 records processed, perform a commit;
> > end loop
> > close cursor
> > commit
> >
> >
> > TIA for any help
> >
> > tom
> >
> >
> >
>
>


0
Thomas
11/11/2004 9:35:44 PM
I believe you just use

     -ODBC -c "UID=...;PID=...;ENG=...;DBF...;LINKS=TCPIP{HOST=...};....."


"Thomas" <thomas@someplace.net> wrote in message 
news:4193db2b$1@forums-2-dub...
> Are you referring to the -datasource flag for dbisql?
> If so, do I just type '-datasource "JDBC-ODBC"'?
>
> Thanks,
>
> Tom
>
> "Nick Elson" <no_spam_nicelson@sybase.com> wrote in message
> news:4193988d$1@forums-2-dub...
>> This may be tied to some limitation of JConnect ...
>> try running it with the JDBC-ODBC bridge selected
>> (which in 8.0.2 is not the Sun one but our iAnywhere
>>  JDBC driver that is now the default and recommended
>>  JDBC driver in ASA 9).
>>
>> Also feel free to compare this against one of the latest
>> 8.0.2/.3 ebfs (only the client side is required for the test)
>> or 9.0.1 (the Developer Edition is freely downloadable)
>>
>> Also JConnect 5.5 has numerous ebfs available for it.  Last
>> time I checked EDS#13 (which would make it something
>> like version 5.5.0.13 if JConnect followed the standard numbering
>> system that ASA does).  Those ebfs are available at
>>
>>     http://downloads.sybase.com/swd/base.do
>>
>> (Note you will need to increase the timeframe to at least 9 months
>>   to get the
>>
>>     "JConnect for JDBC"
>>
>>  Product Family to show up on that page since it has been a little while
>>  since it rolled out an ebf for that ... Date: Apr 30, 2004 for ESD#13)
>>
>>
>>
>>
>> "Thomas" <thomas@someplace.net> wrote in message
>> news:41938b73$1@forums-1-dub...
>> > Hi,
>> >
>> > I'm looking for some guidance with a problem I'm having with a stored
>> > procedure and running out of memory.
>> > ASA ver 8.0.2.4122
>> > What I'm trying to figure out is what elements within a stored 
>> > procedure
>> > could eat up the memory if done a repeated number of times or does my
>> > stored
>> > procedure fail because my system lacks the appropriate amount of memory
> to
>> > do the job.
>> >
>> > I have a stored procedure that loops around based on the number of
> records
>> > in a cursor, performs 'selects' within the loop to find other data 
>> > based
>> > on
>> > the cursor data, performs some calculations and finally updates the DB
>> > depending on the final result.
>> > The number of records in the cursor is estimated at a few million
> records,
>> > so the stored procedure would need to loop around a few million times.
>> > However, the system runs out of memory even before 200,000 let alone 1
>> > million. Now the system I am running on has 1GB of physical memory, but
> I
>> > think the availability is more like 250MB.
>> >
>> >
>> > Below is part of the error log that is produced by ASA.
>> >
>> >
>> > Part of the error log produced by iSQL
>> > ----------------------------
>> >                              +-----------------+
>> >                              |   Stack Trace   |
>> >                              +-----------------+
>> >
>> > Current thread: Database interface thread
>> > java.lang.OutOfMemoryError
>> > <<no stack trace available>>
>> >
>> >
>> >
>> >                                  +----------+
>> >                                  |   ISQL   |
>> >                                  +----------+
>> >
>> > Thu Nov 11 01:51:36 EST 2004
>> > ISQL version = 8.0.2, Build 4218
>> > CommandLineOptions
>> >  ConnectionInfo:
>> >
> DSN=CRMDB;UID=dba;PWD=sql;DBN=CRMDB;<JDBCDRIVER>=com.sybase.jdbc2.jdbc.SybDr
>> > iver
>> >  Command tail: call asi_RunPricing(1)
>> >  Initial SQL statement: (none)
>> >  Execute initial SQL statement: No
>> >  Use GUI: No
>> >  Quiet mode is: Off
>> >  Syntax check mode is: Off
>> >
>> > ConnectionInfo for active connection:
>> >
> DSN=CRMDB;UID=dba;PWD=sql;DBN=CRMDB;<JDBCDRIVER>=com.sybase.jdbc2.jdbc.SybDr
>> > iver
>> >
>> > *** A java.lang.OutOfMemoryError exception was caught while gathering
>> > application information
>> >  null
>> > -----------------------------------------------
>> >
>> >
>> > The actual stored procedure code is really long, so I'm not going to
> post
>> > all of it, but the following is part real/pseudo code (mostly pseudo).
>> >
>> >
>> > /*   declare statements */
>> > /* cursor declare statement */
>> > declare cur_Customer dynamic scroll cursor for
>> > select intm_listing.customer_number, intm_listing.material_number,
>> > intm_product.material_class, intm_customer.tax_classification,
>> > intm_product.tax1
>> >  from intm_listing, intm_product, intm_customer
>> >  where intm_listing.customer_number = intm_customer.customer_number
>> >  and intm_listing.material_number = intm_product.material_id
>> >  and intm_listing.valid_from <= current date and intm_listing.valid_to
>>=
>> > current date
>> >  union
>> >  select intm_listing.customer_number, intm_listing.material_number,
>> > intm_product.material_class, intm_customer.tax_classification,
>> > intm_product.tax1
>> >  from intm_listing, intm_product, intm_customer, (select
> a.customer_number
>> > as cn1, b.customer_number as cn2 from intm_customer a left outer join
>> >  (select distinct intm_listing.customer_number from intm_listing) b on
>> > a.customer_number=b.customer_number and b.customer_number=null) d
>> >  where intm_listing.customer_number = d.cn1
>> >  and d.cn1=intm_customer.customer_number
>> >  and intm_listing.material_number = intm_product.material_id
>> >  and intm_listing.valid_from <= current date and intm_listing.valid_to
>>=
>> > current date;
>> >
>> > open cursor with hold
>> > loop
>> >    fetch first record from cursor
>> >
>> >    find parent nodes for this particular customer
>> >
>> >    /* Condition 1 */
>> >    set some variables;
>> >    perform select based on the variable values;
>> >    perform calculation based on results of select;
>> >
>> >    /* Condition 2 */
>> >    set some variables;
>> >    perform select based on the variable values;
>> >    perform calculation based on results of select;
>> >
>> >    /* Condition X (there are about 12 different conditions) */
>> >    ....same as above....
>> >
>> >    check if resultant value is the same was what is already in database
>> >    if calculated value different from database, update database
>> >    else if calculated value the same don't do anything
>> >    else if calculated value doesn't exist, insert into database
>> >    for every 50 records processed, perform a commit;
>> > end loop
>> > close cursor
>> > commit
>> >
>> >
>> > TIA for any help
>> >
>> > tom
>> >
>> >
>> >
>>
>>
>
> 


0
Nick
11/12/2004 12:03:25 AM
Reply:

Similar Artilces:

Running Running Running
Name: John Harris Email: jkharrisatmindspringdotcom Product: Firefox Summary: Running Running Running Comments: Why, does Firefox, even when mimimized, still takes up to 20, 30, 40% (or more) of my CPU on my Windows XP Pro computer. It seems to just happily chew up cycles. How do I get it to do nothing while minimized. Especially a problem with virtual machines. Thanks, John Harris Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.3) Gecko/2008092417 Firefox/3.0.3 (.NET CLR 3.5.30729) From URL: http://hendrix.mozilla.org/ Note to readers...

Running a stored procedure within a stored procedure
I have a stored procedure (A), which I need to call from another stored procedure (B). The data pulled from (B) is then inserted into a temporary table. The problem I am having is the stored procedure is in a loop, and it only runs once. I need it to run each time the statement loops. For example: In the cursor I have, there are 3 rows, so the statement should return 3 rows. However, only 1 row is returned, it looks like the stored procedure is only one once. What do I have to do to get it to loop?? DECLARE dCURSOR CURSOR FOR select ID, value1, value2 from table; OPEN dCU...

Running stored procedure within a stored procedure
I have a custom built users table for storing some values and I am also utilizing the aspnet_Users table. I want to delete a user from my users tables then execute the aspnet_Users_DeleteUser sproc and pass into the stored procedure the username of the user to delete because the DeleteUser method requires this. When I execute the command from within my asp.net web application I get the exception below. Both values are being obtained from the asp.net application and are represented in my DAL that is also below. Any thoughts as to why I am receiving this exception? Thanks. Procedure or functi...

Problem running stored Procedure
Hi Guys & Gals I'm having problems running a stored procedure, I'm getting an error that I don't understand. My procedure is this: ALTER PROC sp_get_allowed_growers @GrowerList varchar(500) AS BEGIN SET NOCOUNT ON DECLARE @SQL varchar(600) SET @SQL = 'SELECT nu_code, nu_description, nu_master FROM nursery WHERE nu_master IN (' + @GrowerList + ') ORDER BY nu_code ASC' EXEC(@SQL) END GO and the code I'm using to execute the procedure is this: public DataSet GetGrowers(string Username) { System.Text.StringBuilder ...

stored procedure. The c# code run stored procedure.
 CREATE PROCEDURE GetThreadMessages @iThreadID int AS SELECT message_id, thread_id, user_id, first_names, last_name, email, subject, body, date_submitted, category_name, category_id, last_edited FROM message_view WHERE thread_id = @iThreadID ORDER BY date_submitted asc For update ---------------------------------------------------------------------------------------- CREATE PROCEDURE UpdateThreadMessages @iThreadID int @name varchar(50) AS UPDATE message_view SET first_names = @name FROM message_view ...

SR6 is running, and running, and running...
I started it last night at about 1130pm, now its 905 am. It seems to be stuck at 95% on the first partition, with 12 minutes left, and 4 hours 21 minutes elapsed, which I know is bologna. The SMART information has all dissapeared, except for the EEC stuff. Dynastat is not running, but it shows data moving across at a normal speed. I'm not totally sure it's hung up, but it may be taking it's time because it won't move the files in the current area, due to them all being marked as system/hidden. I hope it moves on. Torrance "Torrance Bell" <st...

Long running stored procedure problem.
I have a client that is using PB software that we have developed . He is having a problem with a long running procedure called from PB bombing out. The procedure returns no results and is called using dynamic sql. Depending on the volume of data the procedure can take a long time to run. The procedure runs fine if there is not much data but if the data volume increases the app crashes and takes the server with it. If the procedure is run directly from ISQL on the same machine it works fine. Any ideas? One thought was transaction log filling up etc. but wouldn't this be the same...

memory constrains to running a stored procedure?
Hi, I have a stored procedure runs about 10 minutes on one server. When I move my work to a different server, it runs for 2 hours and is suspended after filling the temdb. One difference between the two servers is the size of tempdb: the old one has 30G and the new one has 800MB. Could that be the only reason? Are there other memory constrains to running a stored procedure? Here is the part of the stored procedure that deals with a large table, about 250K rows with each row of 42 length declare @updateDttm smalldatetime select @updateDttm = getdate() declare @refDat...

problem while running a stored procedure from powerbuilder
Hi, When I run a Sybase stored procedure from PowerBuilder, I get an error message as "'The parameter of type 111 did not have a valid value". What is this parameter type 111? Can anyone tell me the reason for this error and how should I resolve the same? Thanks, Nataraj PS: Sybase version is 12.5 and PowerBuilder version is 8.0.3 > Hi, > > When I run a Sybase stored procedure from PowerBuilder, I > get an error message as "'The parameter of type 111 did > not have a valid value". What is this parameter type 111? Type 111 is pro...

Why running stored procedure piece by piece is much faster than running it as a whole
I have encountered a very strange problem in writing a stored procedure. Here is what happened. I wrote a lengthy stored procedure and it will take approximately 2 minutes to finish running this procedure. In order to figure out why this procedure is slow, I copy all the codes into SQL Advantage, break it into 2 pieces and run it piece by piece. Surprisingly first piece finishes within 5 seconds, and second piece finishes within 10 seconds. I then run these two pieces together, and it will still take approximately 2 minutes to finish. Can anyone tell me how this can happen? ...

Problems Running Stored Procedures under DB2 v5
We are currently using PB 6.5 and DB2/OS 390 5.1 along with of the CAE 5.2. So far, we have not had any luck calling a stored procedure from PB. Is there any documentation out there on the specifics for coding a SP from PB for DB2 and are there any known bugs or tricks that must be done in order to make SP's work? We have been able to successfully invoke the SP from a mainframe COBOL program. We have been able to invoke and execute the SP from PB but, PB always reports an error and no input/output parms are exchanged between PB and the SP. We have not been able to retrieve re...

Problem running stored procedure from gridview delete button
Hi everyone, I am currenty using ASP.NET 2 with Visual Studio 2005 (c#) and sqlexpress 2005. I have a problem running a stored procedure from a gridview. This SPROC is used for UPDATE and DELETE command. The update procedure worked fine (checked database and data is there). But when I click on the delete button inside the gridview ,with or without DataKeyNames, I get the following errors: 1. With 'Period' DataKeyNames specified produces error message 'Procedure or Function 'sProc' has too many arguments specified'. When I run debug, e.Values["Period"].ToString() returns nu...

Problem running Site Kit: "Could not find stored procedure 'site_GetSiteSettings"
I am trying to get the Personal Site Kit up and running, but come across errors when I run the Login.aspx. The error is " Could not find stored procedure 'site_GetSiteSettings'. Also, when I run the application code/site settings manager, it breaks at: Using reader As SqlDataReader = command.ExecuteReader()   Just wondering if anyone else has ran into these problems and what they did to get around them. Thanks!!   Seems like you have this stored procedure in your code but not in your database. Please double check your database make sure it is there. Sue's edream - www...

How to run a sql stored in a variable inside a stored procedure ?
Hi, I am having a situation that needs a sql to be run in a stored procedure, stored in a local variable. Basically the where clause is constructed based on certain criteria. If anybody knows a workaround for this please let me know. Thanks in advance Ganesh Salem After adding your servername to sysservers database and creating a remote account, you can use remote procedure calls to the same server itself. For more information on creating a remote user account and adding your servername to sysservers system table please see your software documentation. Gokhan DEMIR ...

Web resources about - Running a stored procedure and running out of memory problem. - sybase.sqlanywhere.general

Parliamentary procedure - Wikipedia, the free encyclopedia
... of the House of Commons of the Parliament of the United Kingdom , from which it derives its name. In the United States, parliamentary procedure ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

NFL security procedures in question after rappelling protesters
Two protesters with sophisticated gear disrupted the Panthers-Colts game in Charlotte Monday night, bringing into question security at the stadium ...

'Painless' dental cavity procedure regrows tooth enamel
... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...

Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'
Kim Zolciak Shares Glam Post-Heart Surgery Selfie, Calls Procedure 'a Success'

Wall Street Regulator (FINRA): Procedures, Recordkeeping, Education Key For Compliant Social Media
Meeting regulatory requirements when using social media requires proper procedures, recordkeeping solutions, sufficient resources to monitor ...

US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners

Concession: Ryan promises Freedom Caucus he’ll delay discussion of reforming procedure for removing the ...
Compromise. Like I said this morning, it sounds like they did a little horse-trading at their summit. Ryan wants them to give up their right ...

Penis Enlargement Procedure Kills Man, Botched Procedure Lands Woman In Prison
... a 38-year-old bartender with no medical training at all who told him she could medically enhance the size of his penis at home. But the procedure ...

Resources last updated: 12/3/2015 6:16:15 PM