TRUNCATE TABLE Fails but Table DOES Exist

[JohnBates]

This is a weird one (most of mine are

The table does exist and the owner is dbo.

Server: Msg 4701, Level 11, State 1, Procedure procFile_Output_Debit_forBryant_Append, Line 22
Could not truncate table 'dbo.tmpFileExtract_Debit_forBryant' because this table does not exist in database 'bryant'.


Thanks, John

 

[gmmastros]

I see that the table name starts with 'tmp'. Is this a SQL Server temp table declared with # in front of it?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JohnBates]

Thanks George.

No, the developers kind of misname these tables. It is a permanent table.

John

 

[ArtieChoke]

Does the account that is running the procedure have permission to truncate the table or even access it?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[JohnBates]

Thanks ArtieChoke.

I considered permissions as a possible cause.

But I have sys admin rights and I get the same error when I attempt to truncate the table but I can truncate other tables successfully.

John

 

[Katy44]

what happens when you select count(*) from the table?
(I don't know if I can help this just sounds a really interesting one!)

 

[JohnBates]

We can close this one out.

I deleted the table, recreated it. Now the sp that performs the TRUNCATE TABLE truncates successfully.

(I think somehow a system table got messed up to cause SQL Server to not "know" this table existed)

Thanks to all who made suggestions.
John

 

[scoutant]

You prefaced the name with dbo, so perhaps the developer that created it didn't create it as dbo.tmpFileExtract_Debit_forBryant, but rather as
developer.tmpFileExtract_Debit_forBryant. When you drop and recreate the table, it will recreate as dbo since you are a sysadmin.