truncate command in ODBC

DSguru2B · Post by **DSguru2B** » Wed Dec 27, 2006 1:55 pm

The truncate ghost is messing with you.

Jokes apart, try removing the trailing semicolon. See what happens. I wouldnt be surprised if it works.

samsuf2002 · Post by **samsuf2002** » Wed Dec 27, 2006 2:00 pm

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????????

DSguru2B · Post by **DSguru2B** » Wed Dec 27, 2006 2:04 pm

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.

narasimha · Post by **narasimha** » Wed Dec 27, 2006 2:05 pm

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!

DSguru2B · Post by **DSguru2B** » Wed Dec 27, 2006 2:06 pm

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

us1aslam1us · Post by **us1aslam1us** » Wed Dec 27, 2006 2:38 pm

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

Sam

samsuf2002 · Post by **samsuf2002** » Thu Dec 28, 2006 8:50 am

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]

DSguru2B · Post by **DSguru2B** » Thu Dec 28, 2006 8:53 am

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

samsuf2002 · Post by **samsuf2002** » Thu Dec 28, 2006 9:05 am

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.

DSguru2B · Post by **DSguru2B** » Thu Dec 28, 2006 9:14 am

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

samsuf2002 · Post by **samsuf2002** » Thu Dec 28, 2006 9:50 am

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

DSguru2B · Post by **DSguru2B** » Thu Dec 28, 2006 9:58 am

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

jdmiceli · Post by **jdmiceli** » Thu Dec 28, 2006 4:31 pm

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!