Hi,I'm having trouble with what seems the simplest of tasks.

-SET &ECHO = ALL;
SET TEMPERASE = ON
SQL SQLMSS SET SERVER CHSPLUS
SET SQLENGINE = SQLMSS
SQL SQLMSS SET USER TESTSQLUSERS
-RUN
SQL SQLMSS EX SPQUERYPHONE '%&PHONENUMBER|%';
TABLE FILE SQLOUT
PRINT *
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT &WFFMT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *.............

Because of the way Excel formats data as it pleases (turns what you want alpha to numeric), I'm trying to create an interim file to add characters to the front of the data. I can't get anything to work. I've tried creating a hold file and defining the new field I want. I've tried to save the file so I can mess with it outside of Focus. I continually get errors that the field I'm trying to redefine doesn't exist or the file I'm trying to create can't be found.

Can someone actually adjust the above code for me to make it 1. save to hold or as file, redefine a field, and print fields; 2. simply save as c:\file.xls.

My problems seem to be a function of executing stored procedures (I think).

Thanks for any help!

The problem may be that you're missing the ON TABLE HOLD statement. Try adding

TABLE ON TABLE HOLD AS SQLOUT
END

Extract from the document, "iWay Data Adapter Administration for UNIX,Windows, OpenVMS, OS/400, OS/390 and z/OS Version 5 Release 2.3"

http://documentation.informationbuilders.com/masterinde...tadapt_admin_523.pdf

or

http://tinyurl.com/4ctov

quote:

Calling a Microsoft SQL Server Stored Procedure Using SQL Passthru
Microsoft SQL Server stored procedures are supported via SQL Passthru. These procedures
need to be developed within Microsoft SQL Server using the CREATE PROCEDURE
command.
Example Calling a Stored Procedure
The supported syntax to call a stored procedure is shown below.
ENGINE SQLMSS
EX SAMPLE PARM1,PARM2,PARM3...;
TABLE ON TABLE PCHOLD
END
The server supports invocation of stored procedures written according to the following
rules:
� Only scalar input parameters are allowed, but not required.
� Output parameters are not allowed, except for optional cursor type parameters.
� A cursor used for output parameters must be defined with:
� The TYPE statement in a PROCEDURE.
� An associated record layout of the answer set to be returned.
� The cursor must be opened in the procedure.
� No fetching is allowed from the output parameter cursors in the stored procedure. The
Data Adapter for Microsoft SQL Server fetches the answer set.
� Any error messages must be issued using the RAISE APPLICATION ERROR method.

Like so?
SQL SQLMSS SET SERVER CHSPLUS
SET SQLENGINE = SQLMSS
SQL SQLMSS SET USER TESTSQLUSERS
-RUN
SQL SQLMSS EX SPQUERYPHONE '%&PHONENUMBER|%';
TABLE FILE SQLOUT
PRINT *
TABLE ON TABLE HOLD AS SQLOUT2
END
DEFINE FILE SQLOUT2
CLAIMNO/A24= '#'|CLAIMNUMBER;
END
TABLE FILE SQLOUT2
PRINT CLAIMNO

Gets these errors:
0 ERROR AT OR NEAR LINE 3 IN PROCEDURE SQLOUT FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: TABLE
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
0 ERROR AT OR NEAR LINE 18 IN PROCEDURE __T00000FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: CLAIMNUMBER
0 ERROR AT OR NEAR LINE 21 IN PROCEDURE __T00000FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: CLAIMNO
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

Maybe like so:

SQL SQLMSS SET SERVER CHSPLUS
SET SQLENGINE = SQLMSS
SQL SQLMSS SET USER TESTSQLUSERS
-RUN
SQL SQLMSS EX SPQUERYPHONE '%&PHONENUMBER|%';
TABLE ON TABLE HOLD AS SQLOUT
END

DEFINE FILE SQLOUT
CLAIMNO/A24= '#'|CLAIMNUMBER;
END
TABLE FILE SQLOUT
PRINT CLAIMNO
END

This actually creates the HOLD file for the SQL procedure:

TABLE ON TABLE HOLD AS SQLOUT
END

I copied your code to avoid typos. I've deleted any other extraneous format stuff...just the pure code.

Errors received are:
0 ERROR AT OR NEAR LINE 1 IN PROCEDURE SQLOUT FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: @@@@0007
BYPASSING TO END OF COMMAND
0 ERROR AT OR NEAR LINE 12 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND
0 ERROR AT OR NEAR LINE 15 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

Thanks for your persistent help. I am new with WebFocus and feel jinxed at times.

Lisa

use like this following your embedded sql - make sure you end the sql with ;(semi-colon)


TABLE FILE SQLOUT
PRINT TRNCODE
ON TABLE SET ASNAMES ON
ON TABLE HOLD FORMAT ALPHA AS DETBAICD
END

also the file name for hold file should not be more than 8 chars.

Vipul

Thanks to you both. Still get pretty much the same errors. I've finally opened a case with Tech Support.

Your problem may be that WF cannot find the stored procedure. If the connection_attributes do not identify a default database, then WF doesn't know what db to search. Try adding the database name to the prefix of the sp call. I'm doing as follows:

SQL SQLMSS
EX ESSDEMO.dbo.dumpEdbbas '&IN'
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TMPOUT
END
-*
-RUN

TABLE FILE TMPOUT
PRINT *
END

And its all good. If I remove the 'ESSDEMO' (the database name) in the procedure call, then I get an error about table names not found.

Thanks everyone! What it was is too simple. Upper/Lower case. I don't think SQL cares about case but the hold file produced in WF had ClaimNumber. I was using CLAIMNUMBER. There's a big DUH. The fex is working fine.

However, does anyone know how to set to mixed text. I am not within report painter..just editing .fex as text.

Select "View/Options..." from the menu when editting. Check "Mixed Case".

And I thought I'd looked everywhere!!
Thanks much.
Lisa.