Fmtonly ON when calling SP - mssql

questions
1

Hi all,

MSSQL driver

I have starge problem, I am calling SP from Clarion as usual using prop:sql

mytable{:prop:sql} = 'Call MySP'

usually Clarion SQL driver convert it to and executed in sql as

exec MySP

and it works fine.

But sometime it generate something like this:

SET FMTONLY On EXEC MySP SET FMTONLY OFF

so somehow it adds SET FMTONLY before and after EXEC (in one line) and prop:sql returns error 90.

Why?? Who are triggering SET FMTONLY ??

my Sp defined as this:

CREATE PROCEDURE mySP (	
	--@ReturnCode smallint output
) AS
BEGIN
	Declare
	@ReturnCode smallint
	
	set @ReturnCode = -1
		
	select @ReturnCode
	
END

so it returns some value using SELECT and we can read it by
NEXT(mytable)

best regards,
Guennadi

2

Hi,
is it mytable{PROP:SQL} = ‘Call MySP’ ?

3

sure!!! otherwise I will have compile error…
will edit original post

1 Like
4

Have you tried
MyTable{Prop:SQL} = ‘CALL MySP()’
Just a guess.

5

Rick,
As I mentioned it works fine!!! So syntax not a problem. Problem is that somehow / sometime it generates
SET FMTONLY On EXEC MySP SET FMTONLY OFF

instead of just
EXEC MySP

6

I had this situation today but it was nothing to do with a stored procedure.
Do you ever deal with different databases within the same application? In my case I think it was related to switching the db owner at a not cool point. The driver was not happy and threw an error “object not found” type of error back. Looking at the sql profiler I saw the exact same FMTONLY thing just after it attempted my SELECT statement.
I fixed the code to not switch the db owner and everyone is happy again.

7

It is always the same table and the connection!!

I have checked different scenarios and realized that Clarion (?? Or sql) generate this surrounding statements only if slq object doesn’t exist . So in my case somehow MySP stored procedure looks like doesn’t exist.

I am looking for the reason why… Because logic in the clarion code is very difficult and probably we have some issue like closing tables or disconnection etc…

8

Maybe the reason it seems to not exist is a permissions issue.
Also, what DataType is the first field in the table MyTable ? Is it numeric so that no CW type conversion is happening as you are returning -1

1 Like
9

What is the definition of myTable ?
In my case I was using s non-threaded variable for the Driver/Owner string. Changing these to be threaded, as per the documentation, caused my problem to go away.