I have 2 databases (SIDW_DEV and RPTCTL_DEV). There is tAlerts_Sent table in
both databases. I need to Insert records into tAlerts_Sent table in RPTCTL_DEV
database from WebFocus procedure. If I run procedure manually from WebFocus
Developer Studio then it works great (it inserts records into tAlerts_Sent table
in RPTCTL_DEV database). If I use the same procedure for Dynamic Address
Distribution in ReportCaster (on Distribution tab in ReportCaster Schedule) then
it inserts records into tAlerts_Sent table in SIDW_DEV (wrong database). The
code is:

 ENGINE SQLMSS SET VARCHAR OFF
 -SET &CURR_DATE= EDIT(&YYMD,'99999999');
 -SET &BUYOUT_RISK_RATING = 33;
 JOIN VJOBACCOUNTBALANCESUMMARYDAILY_FACT.JOBID IN
 VJOBACCOUNTBALANCESUMMARYDAILY_FACT
 TO UNIQUE VJOB_DIM.JOBID IN VJOB_DIM AS J0
 END
 -* Using SQL engine to get dynamic list of burst values and email addresses
 ENGINE SQLMSS SET DEFAULT_CONNECTION DW_DEV_SV
 SQL SQLMSS PREPARE SQLOUT FOR
 SELECT F2.[JOB NUMBER] AS JOBNUM,F1.[PROJECT MANAGER EMAIL] AS PMEMAIL
 FROM SIDW_DEV.DW.VJOBACCOUNTBALANCESUMMARYDAILY_FACT F1, SIDW_DEV.DW.VJOB_DIM F2
 WHERE F1.JOBID = F2.JOBID
 AND F1.YYYYMMDD = &CURR_DATE
 AND F1.[BUYOUT RISK SCORE] >= &BUYOUT_RISK_RATING
 AND (
 (F2.[JOB NUMBER]+F1.[PROJECT MANAGER EMAIL] NOT IN
 (SELECT JOB_NUMBER+SENT_TO FROM RPTCTL_DEV.RPT.TALERTS_SENT))
 OR
 (F2.[JOB NUMBER]+F1.[PROJECT MANAGER EMAIL] IN
 (SELECT JOB_NUMBER+SENT_TO FROM RPTCTL_DEV.RPT.TALERTS_SENT WHERE
 DATEDIFF(day, DATE_SENT, GETDATE()) > 30))
 )
 END
 TABLE FILE SQLOUT
 PRINT *
 ON TABLE HOLD AS HFILE
 END
 -RUN
 -* Insert into tAlerts_Sent (should go to RPTCTL_DEV database)
 ENGINE SQLMSS SET DEFAULT_CONNECTION DW_DEV_SV
 SQL SQLMSS
 INSERT INTO RPTCTL_DEV.RPT.TALERTS_SENT (ALERT_NAME, JOB_NUMBER, DATE_SENT,
 SENT_TO)
 SELECT 'Buyout-PM', RTRIM(F2.[JOB NUMBER]), GETDATE(), F1.[PROJECT MANAGER
 EMAIL]
 FROM SIDW_DEV.DW.VJOBACCOUNTBALANCESUMMARYDAILY_FACT F1, SIDW_DEV.DW.VJOB_DIM F2
 WHERE F1.JOBID = F2.JOBID
 AND F1.YYYYMMDD = &CURR_DATE
 AND F1.[BUYOUT RISK SCORE] >= &BUYOUT_RISK_RATING
 AND (F2.[JOB NUMBER]+F1.[PROJECT MANAGER EMAIL] NOT IN
 (SELECT JOB_NUMBER+SENT_TO FROM RPTCTL_DEV.RPT.TALERTS_SENT))
 END
 -RUN
 -* Procedure for bursting (see page 3-23 in ReportCaster manual).
 SET ASNAMES=ON
 TABLE FILE HFILE
 PRINT JOBNUM AS 'VALUE'
 PMEMAIL AS 'DEST';
 ON TABLE PCHOLD
 END
 

I have 'Update' functionality as well and it is doing the same thing.
Any ideas why it is inserting records into different database when I use the
same procedure for Dynamic Address Distribution in ReportCaster?
Thanks.

From other posts I have helped out with, it appears if you do not set the SQL engine, WebFOCUS uses the WebFOCUS meta-data (ACX and/or MAS files) to access the tables when you use SQL. Is it possible that ReportCaster is pointing to a different set of meta-data?

I always set the SQL engine so that WebFOCUS does not use the WebFOCUS meta-data, this may solve the problem you're encountering with ReportCaster.

DB2

SET SQLENGINE=DB2

MS SQL Server

SET SQLENGINE=SQLMSS

Oracle

SET SQLENGINE=SQLORA

ODBC

SET SQLENGINE=SQLODBC

Thank you for reply. We use MS SQL 2005 and you can see that I have in code

 ENGINE SQLMSS SET DEFAULT_CONNECTION DW_DEV_SV 

, so I assume this should take care of setting SQL Engine. I also have code

 SEGNAME=TALERTS_SENT, TABLENAME=RPTCTL_DEV.RPT.tAlerts_Sent, 
   CONNECTION=DW_DEV_SV, KEYS=4, $ 

in access file talerts_sent.acx and I have

 FILENAME=TALERTS_SENT, SUFFIX=SQLMSS  , $
  SEGMENT=TALERTS_SENT, SEGTYPE=S0, $
    FIELDNAME=ALERT_NAME, ALIAS=Alert_Name, USAGE=A30V, ACTUAL=A30V, TITLE='Alert Name',$
    FIELDNAME=JOB_NUMBER, ALIAS=Job_Number, USAGE=A12, ACTUAL=A12V, TITLE='Job ID',$
    FIELDNAME=DATE_SENT, ALIAS=Date_Sent, USAGE=HYYMDs, ACTUAL=HYYMDs, TITLE='Date Sent',$
    FIELDNAME=SENT_TO, ALIAS=Sent_To, USAGE=A30, ACTUAL=A30V, TITLE='Sent To',$ 

in Master file talerts_sent.mas
Any other ideas?
Thanks

quote:

ENGINE SQLMSS SET DEFAULT_CONNECTION DW_DEV_SV

most likely does not set the SQL engine, it sets the connection.

SET SQLENGINE=SQLMSS

sets the engine.

Wouldn't hurt to give it a try.

Are you running DevStudio on the same box as Report Caster? Not usually done this way, but possible. Just trying to rule out the possibility that you have db connections set up two different ways i.e. DW_DEV_SU is defined one way for the server DevStudio uses and a different way on the server ReportCaster uses.

Francis, I tried your suggestion and inserted

 SET SQLENGINE=SQLMSS 

right before line

 ENGINE SQLMSS SET DEFAULT_CONNECTION DW_DEV_SV 

, but it is doing the same thing.

Darin,
I'm running DevStudio and ReportCaster on different boxes. There are 3 boxes total. WebFocus DevStudio is installed on my development machine (#1). Web Server, WebFocus Client and WebFocus Reporting Server are on another machine (#2). SQL 2005 is on another machine (#3). I can see DW_DEV_SV connection properties on the 2nd machine at WebFocus Server Adapters screen. Are you saying I can setup/define separate connection on the server ReportCaster uses? How can I check what connection ReportCaster uses?
Thanks

Perhaps ReportCaster is having trouble reading/updating two different databases in one SQL statement. Is it possible for you to separate the SELECT from the INSERT - create two SQL statements with a HOLD file in between?

I do have a fex that does something similar to yours, though I've never run it via ReportCaster.

SET SQLENGINE = SQLMSS
SQL SET SERVER MSCRMUAT
-RUN

...

SQL INSERT MSCRM.dbo.ListBase
  (LISTID, CREATEDON, PURPOSE, LOCKSTATUS, LISTNAME, OWNINGBUSINESSUNIT,
   CREATEDFROMCODE, IGNOREINACTIVELISTMEMBERS, MODIFIEDBY, DESCRIPTION,
   STATUSCODE, DELETIONSTATECODE, MEMBERTYPE, DONOTSENDONOPTOUT,
   MEMBERCOUNT, CREATEDBY, MODIFIEDON, STATECODE, OWNINGUSER )
  SELECT LISTID, CREATEDON, PURPOSE, LOCKSTATUS, LISTNAME, OWNINGBUSINESSUNIT,
   CREATEDFROMCODE, IGNOREINACTIVELISTMEMBERS, MODIFIEDBY, DESCRIPTION,
   STATUSCODE, DELETIONSTATECODE, MEMBERTYPE, DONOTSENDONOPTOUT,
   MEMBERCOUNT, CREATEDBY, MODIFIEDON, STATECODE, OWNINGUSER
  FROM CRMEXT.dbo.ListBaseWork
  WHERE LISTID = '&NEWGUID'
END
-RUN

This is exactly what I was thinking about and I was trying to avoid doing it in one SQL statement. However, I was getting other errors when I did it with HOLD file in between - please see
https://forums.informationbuilders.com/eve/forums/a/tpc/...181078052#6181078052
Thanks

If it works in DevStudio but not in ReportCaster, I would simplify the SQL using the least number of columns - while still working in DevStudio but getting the error in ReportCsster, and open a case with IB.

I assume the fex is in an App folder. Have you tried running it as a self-service fex (outside of DevStudio) by running it from the IB supplied WebFOCUS page:

http://server:port/ibi_html/

(Things may be behaving differently in DevStudio)

Because you are using SQL Passthru, the .acx file is not being used.

If you have access to the global edasprof.prf for both of the servers, the one DS talks to and the one RC talks to, can you post the connection string for those data bases in this thread?

I have a sneaky suspicion that is where the problem is.

Francis,
I opened case with IB Tech support already.
The fex is in Applications\[module name here]\Procedures folder.
I just tried to run it as self-service fex and it did everything right (inserted records to tAlerts_Sent table in RPTCTL_DEV database).
To summarize what we know now - it works great if I run procedure manually from WebFocus Developer Studio or if I run it as self-service fex; it works wrong if I use the same procedure for Dynamic Address
Distribution in ReportCaster.

Ginny, here is info about edasprof.prf:

 edasprof.prf on WebFocus Server (where ReportCaster is installed/running)

D:\ibi\srv71\wfs\etc\edasprof.prf 

-*********************************************************
-* Profile generated on 07 March 2006 at 09:35:59
-*********************************************************
-*
-*
APP PATH ibisamp baseapp test module1a module1b_dev_sv module1b_dev
-*
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES SWINERTON NT_MIS2/webfocus,C9F3757740D19162;SI_CMS811
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES IBI_DW JDEDEV\BI/rcaster,F020E0EB29B9F073;IBI_DW
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES DW CORPBI2/SIDW,39841A07E55E70E0
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES DW_DEV CORPBI2/DevDWReader,C9F3757740D19162;SIDW_DEV
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES DW_PROD CORPBI2/ProdDWReader,C9F3757740D19162;SIDW_PROD
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES DW_DEV_SV SVAYSBAN-SV/DevDWReader,C9F3757740D19162
-*
-*

edasprof.prf on machine where WebFocus Dev Studio installed
C:\ibi\DevStudio71\srv71\wfs\etc\edasprof.prf

-*********************************************************
-* Profile generated on 02 February 2007 at 14:55:00
-*********************************************************
-*
-*
APP PATH IBISAMP
-*
 

I'm agree with Ginny - if you use SQL Passtru, master-files are not using.
SQL is not translated and passed as you wrote.
Your SQL all written with three-part names.

As I see your databases are on the same server :
ENGINE SQLMSS SET CONNECTION_ATTRIBUTES DW_DEV_SV SVAYSBAN-SV/DevDWReader,C9F3757740D19162
(you use only this connection)

Have you tried SQL Profiler traces?

I suppose that only place where things can get wrong is the JOIN.

Your code seems fine though the wf JOIN is redundant.

You code a server connection so explicit mentions of databases should be honoured. (I think this would be true even if you coded a database connection with the database appearing in the connection after the semicolon).

The only avenues I can go down are:

Rcaster may have a different server to ds or self serve. Check in the caster settings on about tab 3.

Rcaster performs editing on your code in that it removes your output statements and replaces them with its own.
eg
ON TABLE PCHOLD

Stick an &ECHO=ON in the proc and see what its doing just in case its ripped out some vital code.

I have always been told that SQL passthru will run code on the SQL engine of your choice EXACTLY as it would if you ran it on the native system.

wf clearly does not do this but is not explicit in what it does.

Clearly amper substitution occurs, but then Francis seems to imply that a general SQL statement will use information in the .ACX and .MAS to further modify the query. (I can't get this to work)

I have had probs with -- type comments as well.

What are the rules? We should be told.

Hi,

Just try to force the sql context by specifying the database before the table name in the insert:
like databasename.owner.tablename

Majid,

I've also thought about it.
But I see
SIDW_DEV.DW.VJOBACCOUNTBALANCESUMMARYDAILY_FACT
SIDW_DEV.DW.VJOB_DIM
RPTCTL_DEV.RPT.TALERTS_SENT

It's all three-part.

The only thing that seems strange - is

JOIN VJOBACCOUNTBALANCESUMMARYDAILY_FACT.JOBID IN
 VJOBACCOUNTBALANCESUMMARYDAILY_FACT
 TO UNIQUE VJOB_DIM.JOBID IN VJOB_DIM AS J0
 END

John, in Crazy DB2 Error? I discussed running SQL without and with WF synonyms. It seems to me that if you do not set the SQL ENGINE, WF looks for WF synonyms/metadata - I'm not sure if only the acx file is used or if both acx and mas are used. In the first example in the Crazy DB2 Error? posting, I use the DB2 SQL ENGINE and specify the db-name.table-name (BASEL.TIME_D). In the second example I do not use the DB2 SQL engine and specify the WF synonym name (BASEL_TIME_D). I don't think using the general SQL is the desirable option.

just a thought, add dbo.?

quote:

ENGINE SQLMSS SET DEFAULT_CONNECTION DW_DEV_SV
SQL SQLMSS
INSERT INTO DBO.RPTCTL_DEV.RPT.TALERTS_SENT (ALERT_NAME, JOB_NUMBER, DATE_SENT,
SENT_TO)
SELECT 'Buyout-PM', RTRIM(F2.[JOB NUMBER]), GETDATE(), F1.[PROJECT MANAGER
EMAIL]
FROM SIDW_DEV.DW.VJOBACCOUNTBALANCESUMMARYDAILY_FACT F1, SIDW_DEV.DW.VJOB_DIM F2
WHERE F1.JOBID = F2.JOBID
AND F1.YYYYMMDD = &CURR_DATE
AND F1.[BUYOUT RISK SCORE] >= &BUYOUT_RISK_RATING
AND (F2.[JOB NUMBER]+F1.[PROJECT MANAGER EMAIL] NOT IN
(SELECT JOB_NUMBER+SENT_TO FROM RPTCTL_DEV.RPT.TALERTS_SENT))
END

Hi Francis

I did manage to get SQL against a focus master to work. (You can't use the ALIAS in an SQL request)

It takes the SQL and translates to the dialect or retrieval commands

ie SQL for an XFOCUS file gets translated to TABLE FILE
SQL for Oracle db gets translated to Oracle SQL dialect etc etc.

The great thing about this is that it respects the formatting in the master file so you dont have to do masses of defines.

You can use the limited subset of SQL but possibly you might find something that is easier to do via SQL than table file.

This must be how IWAY works in that the master files are used to give the fellow who is coding an embedded sql statement a single view of the data regardless of where it is located and he just has to write his sql statement doing what he wants with the data. (I checked and it quite happily translates a join between ORA (ACTIVITY) and MSS (MAXIMO_WORKORDER).

SQL 
SELECT 
T1.ACT_ACTIVITY_SEQ
FROM ACTIVITY T1 INNER JOIN MAXIMO_WORKORDER T2  ON T2.OUTLABCOST = T1.ACT_ACTIVITY_SEQ  
WHERE ACT_ACTIVITY_SEQ IN 
(SELECT AOC_ACTIVITY_SEQ
FROM ACTIVITY_OBJECT_CONNECTION
);
TABLE 
ON TABLE HOLD 
END
-RUN

Translates to

 Name     Level Description                       Set  Comp.ID
 STMTRACE  1    SQL/MDX Generated Statement Trace  Y     AE
 SQLTRANS  3    SQLTRANS Memory Dump               Y     BL
 SQLAGGR   1    SQL Aggregation Trace              Y     BR
 17.06.13 BR  (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 17.06.13 BR  (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
 17.06.13 AE    SELECT T1."ACTIVITY_SEQ" FROM
 17.06.13 AE   IFSAPP.ACTIVITY_OBJECT_CONNECTION T1;
 ...RETRIEVAL KILLED
 1
 0 NUMBER OF RECORDS IN TABLE=        0  LINES=      0
 0
 17.06.13 BR  (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 17.06.13 BR  (FOC2599) NON-SQL SEGMENT IN HIERARCHY (OTHER INTERFACE PRESENT)
 17.06.13 AE    SELECT T1."ACTIVITY_SEQ" FROM IFSAPP.ACTIVITY T1;
 17.06.13 BR  (FOC2598) FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL  : WHERE exp
 17.06.13 BR  (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 17.06.13 BR  (FOC2599) NON-SQL SEGMENT IN HIERARCHY (OTHER INTERFACE PRESENT)
 17.06.13 AE    SELECT T2."outlabcost" FROM dbo.workorder T2 WHERE
 17.06.13 AE   (T2."outlabcost" = ?);
 ...RETRIEVAL KILLED

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

Have you tried placing DW_DEV_SV at the top of the EDASPROF

Fran

Coming back to the question in hand

SQL SQLMSS PREPARE SQLOUT FOR

Is equivalent to

SET SQLENGINE = SQLMSS
SQL PREPARE SQLOUT FOR 

But is

SQL SET SERVER DW_DEV_SV

The same as

ENGINE SQLMSS SET DEFAULT_CONNECTION DW_DEV_SV

?

John, thanks for the interesting feedback. Cheers, Francis.

This might help: Issuing a commit after connection change

Certainly you do not need to issue the second

ENGINE SQLMSS SET DEFAULT_CONNECTION DW_DEV_SV

So try without that since then there is effectively no change in default connection.

quote:

But is

SQL SET SERVER DW_DEV_SV

The same as

ENGINE SQLMSS SET DEFAULT_CONNECTION DW_DEV_SV

?

Yes it is. They are different ways of saying the same thing.

MSS SQL Settings

quote:

Originally posted by Francis Mariani:
John, thanks for the interesting feedback. Cheers, Francis.

No, Thank You, and Focal Point for raising the topic in the first place.