I need to insert multiple rows in SQL 2005 table, so my code is

 
-SET &CURR_DATE= EDIT(&YYMD,'99999999');
-SET &BUYOUT_RISK_RATING = 33;
SQL
  DECLARE @JobNo VARCHAR(12), @PMEmail VARCHAR(50)
  DECLARE abc CURSOR
  FOR
    SELECT F2.[JOB NUMBER] AS JobNo, F1.[PROJECT MANAGER EMAIL] AS PMEmail
    FROM DW.VJOBACCOUNTBALANCESUMMARYDAILY_FACT F1, 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 DW.TALERTS_SENT))
  OPEN abc
  FETCH NEXT FROM abc INTO @JobNo, @PMEmail
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    INSERT INTO DW.TALERTS_SENT (Alert_Name, Job_Number, Date_Sent, Sent_To) 
	       VALUES ('Buyout-PM', @JobNo, GETDATE(), @PMEmail)
    FETCH NEXT FROM abc INTO @JobNo, @PMEmail
  END
  CLOSE abc
  DEALLOCATE abc
  GO
END
 

and I'm getting errors as described at http://techsupport.informationbuilders.com/sps/23032569.html
Is there a way to make it work with BEGIN and END? If not, how else I can make it work?
Thanks

As it states in the case you referenced in your post, this type of SQL is not supported in SQL pass-thru. It is also my understanding that you cannot 'stack' SQL statements like this in a single pass-thru statement like you could if you were creating a SQL Server stored procedure. Reference case #12282088. I would suggest actually creating a SQL Server stored procedure and calling it from your focexec or try using MODIFY or MAINTAIN.

Thank you for reply. Creating a SQL Server stored procedure the way I tried to do it is not going to work per case #23032569 (http://techsupport.informationbuilders.com/sps/23032569.html). Can you please provide some examples which show Inserting / Updating MULTIPLE records in LOOP using any of the methods you mentioned (creating a SQL Server stored procedure and calling it from your focexec or try using MODIFY or MAINTAIN). Thanks again.

Seva,

All you have to do is use the search facility above, try searching Focal Point for SQL INSERT and you will find several helpful topics.

T

Seva,

I'm also working with MSSQL.

I run in my reports procs with UPDATE, INSERT, DELETE.

I found that WebFOCUS begin transactions with
SET IMPLICIT TRANSACTIONS ON

I've made it work with
SET IMPLICIT TRANSACTIONS OFF

Also, I've changed settings of MSSQL adapter :
I've set AUTODISCONNECT = ON COMMAND.

I suppose thats not optimal but it's working.
I'm still in search for optimal settings.

Regards,
Alex

Still having trouble with inserting multiple rows. The code is

 -SET &TIME = EDIT(&TOD,'99:$99:$99')||'.000';
-SET &DAY = EDIT(&YYMD,'9999/99/999');
-SET &TS = &DAY||&TIME;
-TYPE "TS is " &TS   -* this line shows "TS is " 2007/10/08 10:50:55.000
ENGINE SQLMSS SET DEFAULT_CONNECTION DW_DEV_SV
SQL SQLMSS PREPARE SQLOUT FOR
  SELECT 'Buyout-PM' AS ALRTNM, RTRIM(F2.[JOB NUMBER]) AS JOBNUM, GETDATE() AS CURDATE, F1.[PROJECT MANAGER EMAIL] AS PMEMAIL
  FROM ...
  WHERE ...
END
TABLE FILE SQLOUT
  PRINT *
  ON TABLE HOLD AS #HLIST1 FORMAT SQLMSS
END
-RUN
-*-- Insert all the rows from the temporary table ----------
SQL 
  INSERT INTO DW.TALERTS_SENT (ALERT_NAME, JOB_NUMBER, DATE_SENT, SENT_TO)
  SELECT ALRTNM, JOBNUM, '&TS', PMEMAIL FROM #HLIST1
  ;
END
-RUN 

I'm getting errors like
(FOC428)TRANS 1 REJECTED FORMAT COL 43 FLD DATE_SENT
Buyout-PM6 20071008105055000test987@aol.com
What interesting is that if I replace line

 SELECT ALRTNM, JOBNUM, '&TS', PMEMAIL FROM #HLIST1

above with either

 VALUES ('Buyout-PM','987','&TS','test987@aol.com') 

or

 VALUES ('Buyout-PM','987','2007/10/07 21:41:14.000','test987@aol.com') 

then it works.
Looks to me that it doesn't like DATE_SENT format. In Master file I have

 FIELDNAME=DATE_SENT, ALIAS=Date_Sent, USAGE=HYYMDs, ACTUAL=HYYMDs, TITLE='Date Sent',$ 

Any ideas? Thanks.

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

Hi, Seva

I rewrote your example as:

SQL SQLMSS PREPARE SQLOUT FOR
  SELECT id, name , GETDATE() AS CURDATE
  FROM sysobjects
END

TABLE FILE SQLOUT
  PRINT *
  ON TABLE HOLD AS #HLIST1 FORMAT SQLMSS
END

-RUN

SQL 
	INSERT INTO HLIST_COPY (E01,E02,E03)
	SELECT E01,E02,E03 FROM #HLIST1	
END

1. I do not understand how you can even TABLE HOLD AS #HLIST1 FORMAT SQLMSS
MSSQL creates #-tables in tempdb, and I think that such command can be executed only if your default database is tempdb.
I get error: Microsoft OLE DB Provider for SQL Server: [42S02] Cannot create an index
: on '#HLIST1', because this table does not exist in database '....'.

2. INSERT
Your INSERT is in SQL, not in SQL SQLMSS. So it's FOCUS SQL.
And I see in docs: "The SQL Translator does not support subqueries, such as:
INSERT...INTO...SELECT...FROM..."

3. MSSQL #-tables, part2
AFAIK, WF does not hold connection by default to MSSQL, and MSSQL drops temp tables when session ends. So it must be dangerous to use them as hold-files (I can be wrong)

4. Ideas:

My way for WF+MSSQL is :
a) Incapsulate everything into SP and UDF (UDF is good, I use them extensively)
b) Run data modification procs in EX, reporting procs in EXEC

That's all my ideas at this moment.

I'm also interested in tips and tricks for MSSQL.