Results of SQL Server Stored Procedure Call

[rummage]

Hi,

I am executing two different SQL Stored procedures from WebFOcus. I want to get the results of both the stored procedures in one output report file. How do I do that? Here is my code

SQL SQLMSS

EX GOA01_SQL.dbo.spgoa_GetSummaryDataA
'30A882AA-D6F7-4A34-8F71-4157C6CEDE27',0;

TABLE FILE SQLOUT
PRINT *
END

SQL SQLMSS

EX GOA01_SQL.dbo.spgoa_GetSummaryData
'30A882AA-D6F7-4A34-8F71-4157C6CEDE27';

TABLE FILE SQLOUT
PRINT *

END


Here I am using two 'TABLE FILE SQLOUT' statements to get two different report file outputs. If I just use one 'TABLE FILE SQLOUT', it does not work. Please help me in this.

Thanks in advance.

Thambaiya

 

[FOCMAN]

Try This :

FILEDEF MYSQLOUT DISK D:\TEMP\MYSQLOUT.FTM
SQL SQLMSS

EX GOA01_SQL.dbo.spgoa_GetSummaryDataA
'30A882AA-D6F7-4A34-8F71-4157C6CEDE27',0;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS MYSQLOUT
END

FILEDEF MYSQLOUT DISK D:\TEMP\MYSQLOUT.FTM (APPEND
SQL SQLMSS

EX GOA01_SQL.dbo.spgoa_GetSummaryData
'30A882AA-D6F7-4A34-8F71-4157C6CEDE27';

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS MYSQLOUT
END

TABLE FILE MYSQLOUT
PRINT *
END

The caveat is the FILDEF to a permanent directory must be unique to each request otherwise 2 users hitting the enter
key at the same time could overwrite each others. There are techniques from IBI to prevent this.

UNIX and MVS platforms have similar allocations.

 

[rummage]

Thanks FOCMAN for the answer. I forgot to tell you one crucial information. The resluting columns from the two stored procedures are different. The output that I am getting from your code is giving me the columns of the first stored procedure 'spgoa_GetSummaryDataA' for the outputs of both the procedures. (Please note the names of the stored procedures are different - 'spgoa_GetSummaryDataA and 'spgoa_GetSummaryData)What should I do to get two different sets of columns of data to get appended to one file. Please advice me.

Thanks for all the help.

Thambaiya

 

[FOCMAN]

Try this :

FILEDEF MYSQLOUT DISK D:\TEMP\MYSQLOUT.FTM
SQL SQLMSS

EX GOA01_SQL.dbo.spgoa_GetSummaryDataA
'30A882AA-D6F7-4A34-8F71-4157C6CEDE27',0;
TABLE FILE SQLOUT
PRINT *
ON TABLE SAVE AS MYSQLOUT
END

FILEDEF MYSQLOUT DISK D:\TEMP\MYSQLOUT.FTM (APPEND
SQL SQLMSS

EX GOA01_SQL.dbo.spgoa_GetSummaryData
'30A882AA-D6F7-4A34-8F71-4157C6CEDE27';

TABLE FILE SQLOUT
PRINT *
ON TABLE SAVE AS MYSQLOUT
END


The result will be just a save file with all items from both
stored procs in it. HOLD creates a MFD (schema) of your answerset but being your requests return different data you can only create this output file with 2 different formats in it.

 

[rummage]

Hi FOCUSER,

Now, I am getting the two rows. But I do not get the column names for both the result sets. That is simply I get two rows of data - one for each SQL stored procedure.I do not get the column headings for even a single procedure. Moreover, the report text file is also not opening to the user. It simply gets saved as a text file in the path that we provided. How do I resolve this?

Thanks
Thambaiya

 

[FOCMAN]

Try this :

FILEDEF MYSQLOUT DISK D:\TEMP\MYSQLOUT.HTM
SQL SQLMSS

EX GOA01_SQL.dbo.spgoa_GetSummaryDataA
'30A882AA-D6F7-4A34-8F71-4157C6CEDE27',0;
TABLE FILE SQLOUT
PRINT *
ON TABLE SAVE AS MYSQLOUT FORMAT HTML
END

FILEDEF MYSQLOUT DISK D:\TEMP\MYSQLOUT.HTM (APPEND
SQL SQLMSS

EX GOA01_SQL.dbo.spgoa_GetSummaryData
'30A882AA-D6F7-4A34-8F71-4157C6CEDE27';

TABLE FILE SQLOUT
PRINT *
ON TABLE SAVE AS MYSQLOUT FORMAT HTML
END

This creates HTML Output of all data with column headings.

If you change 'FORMAT HTML' to 'FORMAT WP' you will get more
conventional report look.

 

[rummage]

Thanks a lot FOCMAN. It worked great.

Thambaiya