Closed-
Hi I wanted to know if anyone knows as to why if you drop and recreate a table in the database WF shows no data. But if I truncate and reload the table I do get data output.

The reason for the drop and recreate was that we need the refreshes to be quick and often. And so I was using temp table to hold the data and then just swapping the temp tables for the actual table. Where as truncating and reloading the tables makes it so that there is a window of about 2 min. when there is no data.
Using WF 7.6

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

Not quite sure what you mean by "drop and recreate" as opposed to "truncate and reload." The answer may also depend on the database you're using, but you have not listed that on your profile.

Going by standard WF definitions, "drop and recreate" would never have any data. All that does is drop the table, and initialize a new, empty table based on the Master File for that table. There is no data loaded into any table as part of this process.

There is no "truncate and reload" option in WF so you must be referring to database operations, in which case you ARE manually reloading data into the table from some specified location using database tools outside of WF (or via SQL passthru).

Not quite sure what you mean by "drop and recreate" as opposed to "truncate and reload." The answer may also depend on the database you're using, but you have not listed that on your profile.

Going by standard WF definitions, "drop and recreate" would never have any data. All that does is drop the table, and initialize a new, empty table based on the Master File for that table. There is no data loaded into any table as part of this process.

There is no "truncate and reload" option in WF so you must be referring to database operations, in which case you ARE manually reloading data into the table from some specified location using database tools outside of WF (or via SQL passthru).

Regards,

Darin


Sorry for the confusion. What I am doing is using a SSIS package to load data into a SQL Server. WF is only being used to report the data. So the two senerios are
1) drop the table and then recreate the table and copy the data from the temp table.
2) Truncate the table and then reload the table.

The truncate or drop table and the loading of the data is done in the SSIS package.

My question is just why is it when we do a truncate and reload the data, WF is fine except for the time it takes for the table to reload.
but when I drop the table and recreate it even when there is data in the table WF returns no data.

Hope this clears it up a little. If not let me know.

Thanks in advance for the help.

quote:

1) drop the table and then recreate the table and copy the data from the temp table.

I guess I'm still not understanding this process. When you "copy the data from the temp table" I assume that this is still a database operation. Either way, neither of these have to do with WF processes, correct?

If WF comes back without errors and says there is no data, then there is no data (assuming that you aren't using any selection criteria to eliminate records.) Otherwise, there would be some SQL error returned (table or record locked, open cursor error, table not found, no select privilege, etc.) or a FOCUS error returned.

P.S. No need to include the entire content of the previous post. We can scroll to see them if necessary.

Let's try this.

You have a table in the database called cars.

If you run "select * from cars" you get 10 records.
If then you look at a report in WF you get 10 records.

If you Truncate the table cars and then reload the data(out side of WF).

If you run "select * from cars" you get 10 records.
If then you look at a report in WF you get 10 records.

If you drop the table cars and then create the table cars and reload the data(out side of WF).

If you run "select * from cars" you get 10 records.
If then you look at a report in WF you get 0 records.

Inderjit:
You said in your original post that you replace the 'temp' tables for the actual table. How is that being done? At that point, are you refreshing the AFD?
Later you said you do a drop table...create table... reload data. If you do this without the temp table swapping process, does WF return data?

Diptesh,
No I am not refreshing the AFD.

If you look at my last post. I am trying to refresh the data mart and then have WF show the current data.

Inderjit:
Try this. Load your new data into a temp table. Drop the actual table. Swap the temp table for the actual table. Recreate MFD and AFD. Run a report. Do you get data?

There is no need to refresh the mfd and the afd if the file is named the same, has the same layout, and resides in the same location.

You may want to turn traces on to see of there is actually any attempt at data retrieval. Also make sure the DBMS error messages are turned on so you can see if any error is returned. I think it is ERRORTYPE DBMS.

As far as I can tell, you're not doing anything out of the ordinary and we do the same type of thing periodically with our database tables, but do not experience the problem you are having.