DSXchange

The truncate ghost is messing with you.
Jokes apart, try removing the trailing semicolon. See what happens. I wouldnt be surprised if it works.

DSguru i tried removing the semi colon still it is showing as invalid command , as mentioned earlier DELETE command is working fine but it will take more time than TRUNCATE .........my aim is to increase the performance of the job. i dnt know how it worked when i ran with the sample rec????????

Let me ask you this, can you execute a stored procedure from the OPEN command fine ???
If yes then just create a small stored procedure that truncates that table. Three or four lines of code for the stored procedure. Thats it. Atleast you dont have to worry about when it will/will not work and why.

samsuf2002 wrote:i dnt know how it worked when i ran with the
sample rec????????

Just as a check can you try again with some sample records?
Then we can blame it on the volume!

Its not the volume. Because the OPEN statement gets executed before the first record gets inserted. Something else is going on.

Paste the command in code here. And try to run as suggested by Narasimha.

Sam

i tested with sample data again and still it is giving this fatal

main_program: Fatal Error: Invalid open command : TRUNCATE TABLE ERR_TIER_BILLING_SUMMARY ;

i also tried without semicolon i dint work. I think i should try using the stored procedure as suggested by DSguru but not sure whether OPEN COMMAND works to execute the STORE PROC.[/code]

I see a space before the trailing semicolon. Is that the case even in the your actual job ???

i even tried with that i.e, with space and without space. i just read an online post which informs that TRUNCATE cannot be used in ODBC stage ,the option of TRUNCATE can be used in ORACLE stage . So , therefore i should end up using DELETE command.

Avoid Delete. It will take for ever. Plus all that logging. Dba will scream too. Make a stored procedure out of it.

Thanks DSguru for all your help i will with store proc and let u guys know its result. One question can i run a script for that in before job sub routine i am using LINUX.

Thanks

Sure you can. Build the script. Make sure its working from command line. Then provide its fully qualified path in ExecSH. Your done.

Hi all,

Just a quick thought (or two) on this:

1. Are there any foreign keys pointing to the table? If so, SQL Server doesn't allow truncate against a table that is referenced by FK's.

2. Instead of a semi-colon, have you used the tried and true GO keyword? I realize it's ancient and hokey, but it still works well as far as I know. So the command would look something like 'TRUNCATE TABLE ERR_TIER_BILLING_SUMMARY GO' (without the quotes)

I make no warrantees or guarantees either quick suggestion will work, but it's something I haven't seen written yet.

Bestest!