I am trying to use Modify to update a sql table by first holding a file and doing the modify. I am getting this error:

 (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 42000
 : Microsoft OLE DB Provider for SQL Server: [42000] Syntax error or access
 :  violation
 (FOC1740) EXECUTE ERROR  : ABS_FORECAST_ADJUSTMENT
 0 TRANSACTIONS:         TOTAL =     1  ACCEPTED=     1  REJECTED=     0
 SEGMENTS:             INPUT =     0  UPDATED =     0  DELETED =     0

 

 ON TABLE HOLD AS HFRCST FORMAT ALPHA
END
-RUN

MODIFY FILE ABS-FORECAST_ADJUSTMENT
FIXFORM FROM HFRCST
MATCH COMPANY CUSTOMER
ON MATCH UPDATE SALES_FORECAST_MO1 SALES_FORECAST_MO2 SALES_FORECAST_MO3 SALES_FORECAST_MO4 SALES_FORECAST_MO5 SALES_FORECAST_MO6 SALES_FORECAST_MO7 SALES_FORECAST_MO8 SALES_FORECAST_MO9 SALES_FORECAST_MO10 SALES_FORECAST_MO11 SALES_FORECAST_MO12 LAST_CHANGE_DATE LAST_CHANGE_USER
DATA ON HFRCST
END
-RUN 

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

Don,

What is the correct file name ?

ABS_FORECAST_ADJUSTMENT, or

ABS-FORECAST_ADJUSTMENT

quote:

Syntax error or access violation

Do you have update access to the table?

according to the dba I do have update ability, but I will verify that again with him.

Donald, a possible cause is that the update is on a single line,
I do not know if Modify supports long lines as well as WebFOCUS now does.

Try recoding it to go over multiple lines which is what we did in the old days.

ON MATCH UPDATE SALES_FORECAST_MO1 SALES_FORECAST_MO2 SALES_FORECAST_MO3
ON MATCH UPDATE SALES_FORECAST_MO4 SALES_FORECAST_MO5 SALES_FORECAST_MO6
ON MATCH UPDATE SALES_FORECAST_MO10 SALES_FORECAST_MO11 SALES_FORECAST_MO12
ON MATCH UPDATE LAST_CHANGE_DATE LAST_CHANGE_USER

Donald

You should not ask the web master if YOU have access to the database but if the application (IBI) has access.

Am I missing the point, but don't you have to add WRITE=YES to the access file to allow update to the DB.

I tried updating just three fields to prove that its not a length issue.

 SET TRACEOFF = ALL
 SET TRACEON=STMTRACE
 SET TRACEON = SQLAGGR//CLIENT
 SET TRACEON = STMTRACE//CLIENT
 SET TRACEON = STMTRACE/2/CLIENT
 SET TRACEUSER = ON
 -RUN
 DEFINE FILE MISTRAL-FS03_V01
 MONTH1/A20 = '1';
 MONTH2/A20 = ' ';
 MONTH3/A20 = ' ';
 MONTH4/A20 = ' ';
 MONTH5/A20 = ' ';
 MONTH6/A20 = ' ';
 MONTH7/A20 = ' ';
 MONTH8/A20 = ' ';
 MONTH9/A20 = ' ';
 MONTH10/A20 = ' ';
 MONTH11/A20 = ' ';
 MONTH12/A20 = ' ';
 COMPANY/A2 = 'H' || 'H';
 CUSTOMER/A6 = 'BA06';
 END
 -RUN
 TABLE FILE MISTRAL-FS03_V01
 PRINT DST.MONTH1 AS SALES_FORECAST_MO1
 DST.MONTH2 AS SALES_FORECAST_MO2
 DST.MONTH3 AS SALES_FORECAST_MO3
 DST.MONTH4 AS SALES_FORECAST_MO4
 DST.MONTH5 AS SALES_FORECAST_MO5
 DST.MONTH6 AS SALES_FORECAST_MO6
 DST.MONTH7 AS SALES_FORECAST_MO7
 DST.MONTH8 AS SALES_FORECAST_MO8
 DST.MONTH9 AS SALES_FORECAST_MO9
 DST.MONTH10 AS SALES_FORECAST_MO10
 DST.MONTH11 AS SALES_FORECAST_MO11
 DST.MONTH12 AS SALES_FORECAST_MO12
 DST.COMPANY AS COMPANY
 DST.CUSTOMER AS CUSTOMER
 COMPUTE LAST_CHANGE_DATE/HYYMDs = HGETC(10, 'HYYMDm');
 COMPUTE LAST_CHANGE_USER/A10 = 'N642553';
 WHERE ( CO EQ 'H' )AND ( DIV1NM EQ 'IBU' ) AND (CUST =  'BA06')  ;
 ON TABLE HOLD AS HFRCST FORMAT ALPHA
 END
 -RUN
 14.28.22 BR  (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 14.28.22 BR  (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
 14.28.22 AE    SELECT T1."CO",T1."DIV1NM",T1."CUST" FROM
 14.28.22 AE   AMISTRAL_PROD.FS03_V01 T1 WHERE (T1."CUST" = 'BA06') AND
 14.28.22 AE   (T1."DIV1NM" = 'IBU') AND (T1."CO" = 'H');
 0 NUMBER OF RECORDS IN TABLE=       61  LINES=     61
 MODIFY FILE ABS-FORECAST_ADJUSTMENT
 FIXFORM FROM HFRCST
 MATCH COMPANY CUSTOMER
 ON MATCH UPDATE SALES_FORECAST_MO1 LAST_CHANGE_DATE LAST_CHANGE_USER
 DATA ON HFRCST
 END
 -RUN
 14.28.22 AE   SELECT T1."Company",T1."Customer",T1."YearMonth",
 14.28.22 AE   T1."PartNumber",T1."Orig_Forecast_M1",T1."Orig_Forecast_M2",
 14.28.22 AE   T1."Orig_Forecast_M3",T1."Orig_Forecast_M4",
 14.28.22 AE   T1."Orig_Forecast_M5",T1."Orig_Forecast_M6",
 14.28.22 AE   T1."Orig_Forecast_M7",T1."Orig_Forecast_M8",
 14.28.22 AE   T1."Orig_Forecast_M9",T1."Orig_Forecast_M10",
 14.28.22 AE   T1."Orig_Forecast_M11",T1."Orig_Forecast_M12",
 14.28.22 AE   T1."Sales_Forecast_Mo1",T1."Sales_Forecast_Mo2",
 14.28.22 AE   T1."Sales_Forecast_Mo3",T1."Sales_Forecast_Mo4",
 14.28.22 AE   T1."Sales_Forecast_Mo5",T1."Sales_Forecast_Mo6",
 14.28.22 AE   T1."Sales_Forecast_Mo7",T1."Sales_Forecast_Mo8",
 14.28.22 AE   T1."Sales_Forecast_Mo9",T1."Sales_Forecast_Mo10",
 14.28.22 AE   T1."Sales_Forecast_Mo11",T1."Sales_Forecast_Mo12",
 14.28.22 AE   T1."CSR_Forecast_Mo1",T1."CSR_Forecast_Mo2",
 14.28.22 AE   T1."CSR_Forecast_Mo3",T1."CSR_Forecast_Mo4",
 14.28.22 AE   T1."CSR_Forecast_Mo5",T1."CSR_Forecast_Mo6",
 14.28.22 AE   T1."CSR_Forecast_Mo7",T1."CSR_Forecast_Mo8",
 14.28.22 AE   T1."CSR_Forecast_Mo9",T1."CSR_Forecast_Mo10",
 14.28.22 AE   T1."CSR_Forecast_Mo11",T1."CSR_Forecast_Mo12",T1."CSR_Comments",
 14.28.22 AE   T1."Sales_Comments",T1."Status",T1."Status_Error_Message",
 14.28.22 AE   T1."Registration_date",T1."Registration_user",
 14.28.22 AE   T1."Last_Change_Date",T1."Last_Change_User",T1."Submit_Date",
 14.28.22 AE   T1."Submit_User" FROM absdb_prod.dbo.Forecast_Adjustment T1
 14.28.22 AE   WHERE (T1."Company" = ?) AND (T1."Customer" = ?) ORDER BY
 14.28.22 AE   T1."Company"  ASC,T1."Customer"  ASC,T1."YearMonth"  ASC,
 14.28.22 AE   T1."PartNumber"  ASC;
 14.28.22 AE   UPDATE absdb_prod.dbo.Forecast_Adjustment SET  WHERE ("Company"
 14.28.22 AE   = ?) AND ("Customer" = ?) AND ("YearMonth" = ?) AND
 14.28.22 AE   ("PartNumber" = ?);
 (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 42000
 : Microsoft OLE DB Provider for SQL Server: [42000] Syntax error or access
 :  violation
 (FOC1740) EXECUTE ERROR  : ABS_FORECAST_ADJUSTMENT
 0 TRANSACTIONS:         TOTAL =     1  ACCEPTED=     1  REJECTED=     0
 SEGMENTS:             INPUT =     0  UPDATED =     0  DELETED =     0



 

Donald, two things

1. What User is being used to access SQL Server from WebFOCUS?

2. Are you able to copy the UPDATE SQL, change the ? to values and run it against SQL Server with the User above?