Remote Stored Procedures from SQL pass through

[JoaoPinto]

Hi,

I am trying to call a stored procedure from SQL in webfocus.

My code is as follows

************************************************************

REMOTE DEST=********

-MRNOEDIT BEGIN
-REMOTE BEGIN

SET SQLENGINE=SQLMSS
SQL SQLMSS
USE PEDAHR
EXEC getWeek;

TABLE FILE SQLOUT
PRINT *
ON TABLE PCHOLD AS SQLHOLD
END

-REMOTE END
-MRNOEDIT END
************************************************************

It isn't working. Has anyone ever tried to do this before?

Thanks,

 

[GrSw]

Try to change the code:

...
SET SQLENGINE=SQLMSS

SQL SQLMSS
USE PEDAHR
END

SQL SQLMSS
EX getWeek;
TABLE FILE SQLOUT
PRINT *
ON TABLE PCHOLD AS SQLHOLD
END
...

Hope this helps
Grzegorz

 

[JoaoPinto]

No, It's telling me that it can't find the stored procedure.
Yet if I execute the code

USE PEDAHR
EXEC getWeek

in Query analyser it works. And obviously I can see the stored procedure has been created. Is it because I am using a remote session? Do I need to put something in front of the stored procedure name?

 

[JoaoPinto]

Interesting development. I need to add the name of the database and a user with permission to the front of the procedure

i.e

EXEC PEDAHR.PEDATSAdmin.getWeek

But I still get an error saying the Table SQLHOLD cannot be read. I think this is to do with the positioning of the ; . Any ideas?

Thanks,


Code:
***********************************************************
REMOTE DEST=CI414R45

-MRNOEDIT BEGIN
-REMOTE BEGIN

SET SQLENGINE=SQLMSS
SQL SQLMSS
EXEC PEDAHR.PEDATSAdmin.getWeek;
TABLE
ON TABLE PCHOLD AS SQLHOLD
END

-REMOTE END
-MRNOEDIT END

TABLE FILE SQLHOLD
PRINT *
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *

TYPE=REPORT, GRID=OFF, JUSTIFY=CENTER,$
TYPE=HEADING, COLOR=BLUE, FONT='ARIAL',
STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=TITLE, FONT='ARIAL', COLOR=BLUE,
SIZE=7, BACKCOLOR=RGB(252 253 209), JUSTIFY=CENTER,$
TYPE=DATA, FONT='ARIAL',
SIZE=7, BACKCOLOR=RGB(252 253 209), JUSTIFY=CENTER,$
ENDSTYLE
END
***********************************************************

 

[GrSw]

Try to use syntax with SQLOUT, and then PCHOLD. Maybe it works in this case.

What is strange - The syntax: EXEC dbname.owner.procedure;
does not work for me from within WebFOCUS (WF 5.2 on Windows).

When I try Exec Northwind.dbo.TEST1 from Query Analyser, it works.

Using the following WebFOCUS, code:

SQL SQLMSS
EXEC Northwind.dbo.TEST1;
END

I just receive: "EDA no data" message, while:

SQL SQLMSS
 USE Northwind
END
SQL SQLMSS
 EX TEST1;
 TABLE FILE SQLOUT
 PRINT *
END

returns the results as expected.

Hope this helps
Grzegorz

 

[JoaoPinto]

What sort of permissions have you set up on your table? Also are you doing a remote pass through?

Thanks for your help.

 

[GrSw]

I have configured MS SQL data adapter with the 'sa' account.
I tested the syntax on the local adapter, i.e. without the -REMOTE commands.

 

[GrSw]

One another test (local):

SQL SQLMSS 
EX Northwind.dbo.TEST1;
TABLE FILE SQLOUT
PRINT *
END

works as expected, but whith EXEC instead of EX it results in 'EDA no data'.

 

[JoaoPinto]

I have managed to get the below code to work as expected.

************************************************************
REMOTE DEST=CI414R45

-MRNOEDIT BEGIN
-REMOTE BEGIN

SET SQLENGINE=SQLMSS
SQL SQLMSS
EX PEDAHR.PEDATSAdmin.getWeek;
TABLE
ON TABLE PCHOLD AS SQLHOLD
END

-REMOTE END
-MRNOEDIT END

TABLE FILE SQLHOLD
PRINT *
END
************************************************************

The 2 sticking points are that as you say above you need to use EX instead of EXEC or EXECUTE (strange??) and also when using a remote pass through you need to declare a PCHOLD file as above to a normal HOLD file or letting the SQL print on it's own.

The mystical world of webfocus strikes again.

Thanks a lot for your help.

 

[focwizard]

To respond to your sticking points:

1. The keyword 'EX' indicates to WebFOCUS, that you're invoking a stored procedure. Every relational database indicates that a stored procedure is to be run in a different manner. We chose EX as a 'common' indicator

2. The reason for the PCHOLD is twofold. First, the TABLE indicates that the stored procedure is returning and answer set. Second, the PCHOLD indicates that the answer set is to be sent to the client, and NOT to remain on the server, where the procedure was executed.

 

[SJK]

You need to hold the file, so try this


;


TABLE
ON TABLE HOLD AS SQLOUT Sanford Jay Kadish (Sandy)
TEKINSIGHT -skadish@tekinsight.com
9897 Merlin Drive East
Jacksonville, FL 32257
(904) 730-2271