sqlexec() cause foxpro to crash

[BlackDice]

I'm using sqlexec in a number of places, but when I use it on a certain stored procedure, VFP just crashes. no messages, no nothing; just completely disappears. Anyone know what this is? I'm using a sql server backend. I call other stored procedures in the same app, and not a problem. I call sqlexec() 3 times in the same function, and the other two calls don't have a problem. I even switched the text to call a different sproc there, and it works fine. It only fails when I call this one specific sproc. I also tried this exact same call in sql Query Analyzer and it works. This is the call:

exec pr_AddWork 126

"

that fails. If I use:

"exec pr_AddPerson 126

"
it goes through just fine.

BlackDice

http://www.stillwaterexpress.com

 

[Mike Lewis]

BlackDice,

More information please:

- What version of VFP are you using?

- How many result sets would you expect the sproc to return?

- Is there anything unusual about the sproc itself?

- You say you are calling SQLEXEC() three times in the same function. Have you tried removing it from the function, and just calling it on its own (to isolate the possibility that there is other code in your function that is causing the problem)>

- You say that VFP "just completely disappears". Is it still visible in the Windows Task Manager?

Mike






__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[BlackDice]

1. vfp9

2. didn't think about that. It does return a lot of result sets in the Query Analyzer window

3. No

4. Not Yet

5. No



BlackDice

http://www.stillwaterexpress.com

 

[Mike Lewis]

When you say a "large number of result sets", are you talking about four or five, or 10 or 20, or hundreds?

I don't know if there is a limit on the number of result sets within VFP (I would guess not), but if you can temporarily amend the sproc so that it only returns one or two, that would at least eliminate that possibility.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[BlackDice]

WAY in the hundreds. This sproc creates a cursor, that in turn calls 4 or five other sprocs and the cursor may have about 100-200 iterations. But I think result sets are being returned for all the sprocs it calls also which are simple select statements that are only returning 1 or 2 rows. I'm now trying to find a way for SQL Server to not return any output, because i don't actually need any cursors or anything returned.

BlackDice

http://www.stillwaterexpress.com

 

[Mike Lewis]

I think you're on the right track. In any case, it sounds like there's an awful lot of overhead in that sproc. Processing a SQL Server-type cursor is inefficient at the best of times, and if it is doing 100 - 200 iterations, plus any possible locking that might be going on behind the scenes .... it will all be very slow and clumsy.

If nothing else, I would have thought there was a risk of the connection timing out, or of the server going into a deadly embrace. That shouldn't cause VFP to crash, of course, but it does sound like a situation to avoid if possible.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[BlackDice]

yeah, the person who wrote the sproc put values into most of the variables by using SELECT's. I'm changing that now by using SET's . Also a lot of the smaller sprocs ended with SELECT statements so that they would return a cursor. I'm modifying that so that a result set won't be returned if you don't want one, simply by putting in a parameter that defaults to 1 so it won't break any existing code that uses that sproc and expects a result set.

BlackDice

http://www.stillwaterexpress.com

 

[Mike Lewis]

Simplifying the sprocs will do no harm, and might solve the original problem. Let us know if it makes a difference.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)