Board index » delphi » SQL Server Stored proc not running properly through Delphi

SQL Server Stored proc not running properly through Delphi

I have a SQL Server 6.5 stored proc I am trying to run through Delphi
(D3/NT4). The proc runs fine in the SQL enterprise manager, but when I try
to run it through my Delphi GUI, it gets part way through and then
mysteriously stops. I stuck in a bunch of statements that write status lines
to a separate table as the proc is executing, and these lines show it's
running just fine, until it just stops.

The result of the TStoredProc call in Delphi is 0, indicating that it is not
erroring out in any way. After running the proc through my GUI, I can go to
SQL trace, copy the command line that Delphi sent to SQL server, paste this
line into enterprise manager, execute it, and it runs fine. So the problem
cannot be a screwup with parameter values.

A colleague suggested calling the stored proc from Delphi using a SQL string
through a TQuery. I copied the command line mentioned above, prefixed it
with "EXECUTE", add it to the TQuery's SQL property, and then ExecSQL on it.
The same partial run and mysterious stop again occurs..

Does anyone have any idea what can be going on here? The stored proc is
pretty long and complicated, so including it here and expecting people to
examine it isn't really practical.

I would greatly appreciate and advice anyone can provide.

Steve C.

 

Re:SQL Server Stored proc not running properly through Delphi


Are you timing out?If it runs the same amount of time before stopping this
may be the culprit.

Try running it once from SQL-Enterprise Manager - time it. Set the timeout
value in the BDE to a time greater than the SQL-EM run [MAX QUERY TIME].
Then run it again via Delphi - time it. It should complete before the time
out occurs.

If not you may want to expand the timeout range to SQL-EM time + say 10mins
and if it bombs again then it's something else.

Doug.

Quote
Steve Champeau wrote in message <72holo$g...@forums.borland.com>...

>I have a SQL Server 6.5 stored proc I am trying to run through Delphi
>(D3/NT4). The proc runs fine in the SQL enterprise manager, but when I try
>to run it through my Delphi GUI, it gets part way through and then
>mysteriously stops. I stuck in a bunch of statements that write status
lines
>to a separate table as the proc is executing, and these lines show it's
>running just fine, until it just stops.

>The result of the TStoredProc call in Delphi is 0, indicating that it is
not
>erroring out in any way. After running the proc through my GUI, I can go to
>SQL trace, copy the command line that Delphi sent to SQL server, paste this
>line into enterprise manager, execute it, and it runs fine. So the problem
>cannot be a screwup with parameter values.

>A colleague suggested calling the stored proc from Delphi using a SQL
string
>through a TQuery. I copied the command line mentioned above, prefixed it
>with "EXECUTE", add it to the TQuery's SQL property, and then ExecSQL on
it.
>The same partial run and mysterious stop again occurs..

>Does anyone have any idea what can be going on here? The stored proc is
>pretty long and complicated, so including it here and expecting people to
>examine it isn't really practical.

>I would greatly appreciate and advice anyone can provide.

>Steve C.

Re:SQL Server Stored proc not running properly through Delphi


Thanks for the response, Doug.

Looking at the time out is a good idea, but not the problem here. My stored
proc, if given certain input parameters, can run for a very long time, so I
jacked up the [MAX QUERY TIME] to 3000. The test case I've been using
completes correctly in the enterprise manager in only 14 seconds, and {*word*99}s
out through Delphi in 4 seconds.

Steve C.

Quote
dp <.> wrote in message <72i15g$g...@forums.borland.com>...
>Are you timing out?If it runs the same amount of time before stopping this
>may be the culprit.

>Try running it once from SQL-Enterprise Manager - time it. Set the timeout
>value in the BDE to a time greater than the SQL-EM run [MAX QUERY TIME].
>Then run it again via Delphi - time it. It should complete before the time
>out occurs.

>If not you may want to expand the timeout range to SQL-EM time + say 10mins
>and if it bombs again then it's something else.

>Doug.

>Steve Champeau wrote in message <72holo$g...@forums.borland.com>...

>>I have a SQL Server 6.5 stored proc I am trying to run through Delphi
>>(D3/NT4). The proc runs fine in the SQL enterprise manager, but when I try
>>to run it through my Delphi GUI, it gets part way through and then
>>mysteriously stops. I stuck in a bunch of statements that write status
>lines
>>to a separate table as the proc is executing, and these lines show it's
>>running just fine, until it just stops.

>>The result of the TStoredProc call in Delphi is 0, indicating that it is
>not
>>erroring out in any way. After running the proc through my GUI, I can go
to
>>SQL trace, copy the command line that Delphi sent to SQL server, paste
this
>>line into enterprise manager, execute it, and it runs fine. So the problem
>>cannot be a screwup with parameter values.

>>A colleague suggested calling the stored proc from Delphi using a SQL
>string
>>through a TQuery. I copied the command line mentioned above, prefixed it
>>with "EXECUTE", add it to the TQuery's SQL property, and then ExecSQL on
>it.
>>The same partial run and mysterious stop again occurs..

>>Does anyone have any idea what can be going on here? The stored proc is
>>pretty long and complicated, so including it here and expecting people to
>>examine it isn't really practical.

>>I would greatly appreciate and advice anyone can provide.

>>Steve C.

Re:SQL Server Stored proc not running properly through Delphi


Quote
>I have a SQL Server 6.5 stored proc I am trying to run through Delphi
>(D3/NT4). The proc runs fine in the SQL enterprise manager, but when I try
>to run it through my Delphi GUI, it gets part way through and then
>mysteriously stops.

What do you mean by "stops?"  Does it hang?  Does it return prematurely?
How big is the code?  Does it make sense to post it (or at least the code
that seems to cause the problem)?

-- Mark

Re:SQL Server Stored proc not running properly through Delphi


Hi Steve

I had similar problems when I was trying to pass a String variable in Delphi
to a CHAR variable in SQL.

I never figured out the why, I simply stopped using CHAR variables and
replaced them with VARCHAR's.

If this is not the probelm then you will have to provide some more
information.

What are the input parameters?
Are there any output variables?

The Stored Procedure may be complicated but it would be useful to see what
parameters you are trying to pass.

EDWIN HARRELL
Systems Architect
iDOiNK Software
edwin.harr...@idoink.com

Re:SQL Server Stored proc not running properly through Delphi


I believe this is because a CHAR column pads the data value to fill the
column with CHAR's until it reaches the width of the column definition.

In MS SQL, I believe the char used is '@' to pad with..

So when doing evaluations on something on a field, you would always have
to consider the blank spaces in the column.

Might that have been the issue?

Rkr

Quote
Edwin Harrell wrote:

> Hi Steve

> I had similar problems when I was trying to pass a String variable in Delphi
> to a CHAR variable in SQL.

> I never figured out the why, I simply stopped using CHAR variables and
> replaced them with VARCHAR's.

> If this is not the probelm then you will have to provide some more
> information.

> What are the input parameters?
> Are there any output variables?

> The Stored Procedure may be complicated but it would be useful to see what
> parameters you are trying to pass.

> EDWIN HARRELL
> Systems Architect
> iDOiNK Software
> edwin.harr...@idoink.com

--
                   \|||/
                   /'^'\
                  ( 0 0 )
--------------oOOO--(_)--OOOo--------------
. Reid Roman                              .
. Delphi Programmer / Analyst             .
. TVisualBasic:=class(None)               .
. May the Source be With You              .
-------------------------------------------
. Auto-By-Tel (http://www.autobytel.com)  .
. Irvine, CA U.S.A                        .
. E-Mail : rkroman (at) pacbell (dot) net .
. or reidr (at) autobytel (dot) com       .
-------------------------------------------

Other Threads