I am using the following code in WebFOCUS 7.7.03. &FOCREL in development returned R727703B, production returned M727703D.

-SET &DocId = '12345678901';
-SET &MessageType = 'A01';
-DEFAULT &HistDate = '';
-DEFAULT &HistRandame = '';

SET SQLENGINE = SQLMSS
ENGINE SQLMSS SET DEFAULT_CONNECTION CORR
SQL SQLMSS
EX dbo.usp_UpdateEMRData '&DocId', '&MessageType', '&HistDate', '&HistRandom';

-*Read data from sql into temporary file
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HDATA FORMAT ALPHA
END

TABLE FILE HDATA
PRINT *
END

In my development environment this works 100% of the time. In my production environment this works 0% of the time. The stored procedure inserts data into 4 different tables and then returns 5 or 6 fields that I need to use to create a flat file. The sql stored procedure gets executed in both environments and the data is added to the database. However TABLE FILE SQL OUT returns 0 records in production, 1 record in development. I suspect this is a configuration issue, but have not idea where to start.

Executing the stored procedure directly in ssms always produces the output. I have tried executing my fex from both the console and within my maintain application, with the same results.

Any assistance would be appreciated.

This message has been edited. Last edited by: Deana,

-SET &DocId = '12345678901';
-SET &MessageType = 'A01';
-DEFAULT &HistDate = '';
-DEFAULT &HistRandame = '';

SET SQLENGINE = SQLMSS
ENGINE SQLMSS SET DEFAULT_CONNECTION CORR
SQL SQLMSS
EX dbo.usp_UpdateEMRData '&DocId', '&MessageType', '&HistDate', '&HistRandom';
END

-*Read data from sql into temporary file
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HDATA FORMAT ALPHA
END

TABLE FILE HDATA
PRINT *
END

Starting with the obvious, do you have an adapter configured on your production server named CORR?

T

quote:

SET SQLENGINE = SQLMSS
ENGINE SQLMSS SET DEFAULT_CONNECTION CORR
SQL SQLMSS
EX dbo.usp_UpdateEMRData '&DocId', '&MessageType', '&HistDate', '&HistRandom';
END

Yes Tom.. You are right. The END plays the trick here. I have faced similar issue when executing a stored proc from WF and END played the trick.

Tony A... Yes, the connection is named correctly. (Thank you though, as often times it is the obvious that gets me).

Tom Flynn and Ramkumar... adding END after the EX statement did not fix the problem. It did however lead me to the solution as it printed out the master for the sqlout file. My first 4 fields returned were varchar(1200) in sql and therefore converted to text. I moved the text fields to the end of the line and moved the end statement prior to the EX statement (which is where I had it originally and had removed during my debugging). After I moved the end, I received FOC1400 and FOC1407. A previous post recommended adding ENGINE SQLMSS SET CONVERSION LONGCHAR ALPHA.

So the solution was to part.
ADD 'ENGINE SQLMSS SET CONVERSION LONGCHAR ALPHA' before the EX statement.
Move the text fields to the end of the output file.

Thank you very much for your assistance!