Works on all other sql2022 servers but not on production. - must be dropped
-
HI
We have a small utility that is giving us a
"#table is missing from the system catalog. The object must be dropped and re-created before it can be used"
Driving me slowly crazy (short drive). Same utility, same user, same file, same everything. Works fine on our sql2022 dev, and stage servers. No errors. But gives the error you see below when utility is pointed at our prod server.
I've check all the obvious permissions and they are the same, dbowner, etc. Checked and granted view server state permission. Pretty sure it has to be environment / permission related as the DB's are all restored and it works there. Starting to wear a hole in the wall with my head. What else can I look for?
-
check if someone had DENY'ed create tables in TEMPDB- or even deny'ed other permissions that could cause the issue.
-
Hi Frederico
Yeah checked for denied .
Thanks tho!
-
Have you checked the NTFS permissions on the import file directory? It could be the the relevant service doesn't have access to it, and thus the import table isn't created.
-
Hi Dan
Yep checked access to file / share directory. Interesting that it works for many other files and it works fine when pointed to one of the dev/stage servers. So, pretty much ruled out file permissions.
If I run this:
select * from syscomments
where text like '%usp_drop_temp_ingenix_codes%'
SELECT * FROM sys.sql_modules
WHERE object_id = OBJECT_ID('[dbo].[temp_ingenix_codes]')
I get this:
CREATE PROCEDURE dbo.usp_drop_temp_ingenix_codes AS
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp_ingenix_codes]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN drop table [dbo].[temp_ingenix_codes] END
But not sure how helpful that is?
-
search for the content instead of the object id
SELECT *
FROM sys.sql_modules
WHERE definition like '%usp_drop_temp_ingenix_codes%'
and see what you get.I would also recreate the SP just in case its messed up.
-
thanks Fredirico
dropped and recreated proc
This is the result from the sysmodules:
all servers - prod and dev
1615258704 CREATE PROCEDURE [dbo].[usp_drop_temp_ingenix_codes] AS if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp_ingenix_codes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN drop table [dbo].[temp_ingenix_codes] END 0 0 0 0 0 0 NULL 0 0 0
Not sure where to go with this tho..
-
You need to put a ticket into Optum/Ingenix - most likely you have a version issue between the database and application.
With that said, the error you are getting references an actual temp table - the code you are showing is referencing a real table with a 'temp' name. The error is not occurring in that code - or it is happening somewhere later in the code than what you are showing.
I would also recommend letting Optum/Ingenix know that we now have DROP TABLE IF EXISTS which is a much cleaner way of checking for a table and dropping it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thanks Jeffery
Yeah we contacted the vendor who said they feel the issue is permission related.
The error is not occurring in that code - or it is happening somewhere later in the code than what you are showing.
Thinking the same things. But nothing in the logs nothing show on a trace. I do see timeout errors tho in my whoisactive database.
-
create an extended event session and log all the code being issued by the pc where you execute that app - that will give you full list of sql statements being executed by the app - and that should help you identify the issue.
- krypto69 wrote:
Thanks Jeffery
Yeah we contacted the vendor who said they feel the issue is permission related.
The error is not occurring in that code - or it is happening somewhere later in the code than what you are showing.
Thinking the same things. But nothing in the logs nothing show on a trace. I do see timeout errors tho in my whoisactive database.
If the vendor is stating it is a permissions issue - then they need to also provide the required permissions for the account running the application. In fact, they should be able to provide you with a script to make sure permissions are appropriately defined for the account that runs that application.
If not - then it probably isn't a permissions issue and they are just using that to ignore the problem, which is typical from my experience with that vendor.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
yeah guess they figure it works on all our other servers - the good old we dont know either must be permissions reply..
which i cant understand...feels like I'm missing something obvious but damn if I can find it.
-
SO...still working on this nightmare..
I found that if I run this in master:
SELECT *
FROM sys.sql_modules
WHERE definition like '%usp_drop_temp_ingenix_codes%'
On the broke server I get these two records:
538484997 CREATE PROCEDURE [dbo].[usp_drop_temp_ingenix_codes] AS if exists (SELECT * FROM sys.sql_modules WHERE definition like '%usp_drop_temp_ingenix_codes%') BEGIN drop table [dbo].[temp_ingenix_codes] END 1 1 0 0 0 0 NULL 0 0 0
554485054 CREATE PROCEDURE [dbo].[usp_drop_temp_ingenix_codes_ORIGINAL] AS if exists (SELECT * FROM sys.sql_modules WHERE definition like '%usp_drop_temp_ingenix_codes%') BEGIN drop table [dbo].[temp_ingenix_codes] END 1 1 0 0 0 0 NULL 0 0 0
On the non broke server I get no results.
I thought maybe I should delete the two records from the broke server but I get
Ad hoc updates to system catalogs are not allowed.
Probably stopping me from doing something stupid? or am I on to something here? Why would the records not be in the working servers master db? The error makes it seems like its trying to drop from there but can't. Or I'm not understanding something (likely)...
-
if you have them on sql_modules you will also have them as SP's - so a normal "drop procedure xxxx" on master will delete(drop) them.
(to be safe generate their definition before dropping)
no user code should ever be on master so I would check to see if other objects were created there incorrectly.
-
*UPDATE*
Thank you all so much for taking the time to help with this issue.
It turned out to be a trigger that gets installed by default now in dbwarden. Once I disabled the schema log change trigger - my application error went away.
Hopefully this helps someone else!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply