Toad World® Forums

Automation- Append data to LocalDB table


#1

I have a simple automation script that runs and saves the output to a table in the LocalDB. In addition, I schedule this automation script to run weekly on Monday at 2am. Last week, the script ran fine. Today, the activity log states “A table and a snapshot cannot have the same name within one database in Local Storage.” Here is what the model looks like:

1778.Capture.png

In Execute_1, I have Export to Local Storage set to LocalDB, and selected the table name. The Overwrite existing table is unchecked.

Any thoughts?

Update: I noticed this issue is resolved in TDP 3.3.


#2

Appending to a table in Local Storage is something we added in Toad Data Point 3.3. You can get this feature in the current Beta or wait for the release at
the end of June.

You can only create a new table in Local Storage in Toad Data Point 3.2. This is what the error in the log file is stating.

Debbie

From: TDP_OWL [mailto:bounce-TDP_OWL@toadworld.com]

Sent: Monday, June 10, 2013 10:35 AM

To: toaddatapoint@toadworld.com

Subject: [Toad Data Point - Discussion Forum] Automation- Append data to LocalDB table

I have a simple automation script that runs and saves the output to a table in the LocalDB. In addition, I schedule this automation script to run weekly on Monday at 2am. Last week, the script
ran fine. Today, the activity log states “A table and a snapshot cannot have the same name within one database in Local Storage.” Here is what the model looks like:

in Execute_1, I have Export to Local Storage set to LocalDB, and selected the table name. The Overwrite existing table is unchecked.

Any thoughts?


#3

I am trying to the similar thing and the error that I am getting is: “Currently, export to Local Storage is not supported if query uses parameters.” Please advise


#4

Does you query have parameters? If yes - publishing such result set is not currently supported to Local Storage. There might be a workaround though. To figure out if such exists I need to know a bit more about your use case.

Thanks,

Igor.


#5

Thanks Igor for your quick response and yes my query currently has parameters. I am able to find the workaround however local storage would have made things easier for me hence wanted to check once. Are we planning to fix this issue in any upcoming versions?


#6

What I was trying to say - you still might be able to save such dataset to Local Storage. Try to use variable substitution instead of a parameter. E.g., you need to publish a result set of this query using Automation script:

SELECT * from REGION where REGION_ID = :ID

Try to use this instead:

SELECT * from REGION where REGION_ID = [tag:ID]#

Result set of this query can be saved to Local Storage.

Just remember that in this case variable value substitution will take place meaning you might be more careful about ID variable type.


#7

I see, this is interesting. Let me try this tomorrow and I will get back to you on this. Thanks for your help!


#8

I tried what you suggested and I am able to successfully export to Local storage using string, however I have a date in where clause and when I used my variable with #, it takes the value as To_date(‘04/30/2015 00:00:00.00’, ‘MM/DD/YYYY HH24:MI:SS.SS’) and thus throws an

Lookup Error - Oracle Database Error: ORA-01810: format code appears twice. Is there a way to avoid this? Please advise. Thank you!


#9

Yes, with dates it is a bit more complicated but still doable I believe. Can you please post your Automation script (tas file) along with your query (if you use linked files for sql in you script)? You can also send these directly to me (igor.manokhin@quest.com). I think I know what the problem is but having your files at hand will allow me to advise more promptly.

Thanks,

Igor.


#10

For time being, I am able to crack what I am looking for. Thanks for your help.

Do you have a couple of examples related to date or may be we can add some to the help for ease of everyone.

For e.g.: if we have a date column in ‘my_date’ variable, I see diff in the value when I use [tag:my]_date#, :my_date, etc. Is that how it should be?


#11

I was going to use your sql to give an example :slight_smile:

Yes, there are differences between using :ParamName and #ParamName# notations. In short - the first one is real parameter implementation when the server/client takes some part in, e.g., type casting. The 2nd one is just text substitution where it’s up to the user to provide valid formatting in case the type of ParamName is not string or integer.

Igor.


#12

Thanks Igor, this clarifies my doubt. :slight_smile: