I have a daily report that I run which reports daily transactions by individuals on various tables. I would like to be able to schedule the report to run, gather the data and store it within a separate SQL database. How is this best done?

Thanks in advance

I haven't looked into it much, but I know there is a relatively new feature that allows for hold files to be stored in databases such as DB2, Oracle (I assume SQL Server also).
Your scheduled procedures could just create (or Modify) hold files which would be stored in separate databases.

I've had reasonable success with simply doing a hold file in a SQL format. There are a couple of caveats to be aware of:

• The connection that you are using needs to have rights to create a table and write to it.
  
• The table is created every time you do the HOLD. In other words, you need to have some mechanism to make sure the table doesn't exist beforehand because it will produce an error.
  

What I would recommend is creating a stored procedure or something that you can run before you do the hold that would drop the table (if it exists). If you need to get the data from somewhere else, then hold it to a focus file first, then connect to your destination database with a different connection/adapter, run the stored procedure, and then hold the focus file out as SQL. There are probably other ways to do this, but when I was playing around with it, that's what I formulated in my head. Your environment may have other constraints that warrant a different solution. I would definitely encourage you to play with those output types though.

quote:

or something t

As it is daily statistics I would like to be able to store the information without removing what info is already within the SQL table, so I can do historical trending etc...but that may come in handy down the road...

Well, I think what you'd do then is treat the table that gets created as a "work" table.

So as an alternative, don't run the proc before you create the table. Instead hold your file out to sql and then run a stored procedure afterwards that will move the data from the work table into your permanent table -- and then simply drop the work table when it's done.

Same basic principle, just slightly modified for keeping your data long-term.

Why not use a combination of TABLE and SQL passthru?

TABLE FILE filename
other stuff
ON TABLE HOLD AS TEMPSTATS FORMAT SQLMSS (or what ever it is)
END
-RUN
-IF &LINES EQ 0 THEN :End_It;

SQL SET ENGINE etc...
SQL
  UPDATE STATSFILE
  SELECT * FROM webfocus.TEMPSTATS
;
END
-RUN
-:End_It

You will have to correct the code above as it is not meant to be totally syntactically correct (I'm at home now and have no access to prove it) but it should give you an idea to begin with.

In fact exactly as Trav suggests above

T