September 13, 2021 at 5:46 pm
SQL SERVER 2016 (13.0.5026.0)
NOTE: The reference SQL code is not something I/We created but was setup by Prophix when they implemented the system for us. I am only responsible for trouble-shooting it when things fail, not for the T-SQL code it uses.
We have a B2B app named Prophix that drops an existing table in the DB and re-creates it via SELECT * INTO . The App uses a SQL Login that is a member of the DBO database role so it has the access needed to drop and re-create the table. Recently its been failing and the app is recording the error msg
Native SQL process was failed. Cannot drop the table 'vWAM_PROPHIX_GL_TOTALS', because it does not exist or you do not have permission.
Is this the kind of error that would be captured in the default trace? I've looked and have been unable to locate anything in the default trace for this but I don't know if this is something the default race would normally capture. I believe that when this fails its not b/c of the error the app lists but if this is something the default trace should record then I can use that prove that the error msg is wrong and that something else is preventing the code from running.
Thanks
Kindest Regards,
Just say No to Facebook!September 13, 2021 at 7:29 pm
I am not certain on the default trace, but I think you could set up an XE session to capture that.
How is the tool running the drop script? I am wondering if MAYBE it is accurate and the object doesn't exist OR possibly it lacks permissions? Permissions is unlikely as the user is dbo, but could be something to test on a test instance to confirm that that user can drop the table. I am not familiar with the PROPHIX table naming pattern, but since it starts with a "v", I am wondering if MAYBE it is a view and they are using DROP TABLE?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 13, 2021 at 8:27 pm
Did you check the table exists ? Does the DROP have a WHERE EXISTS clause ?
Maybe there was an error at some point after the table got dropped, and never got recreated.
September 15, 2021 at 9:56 am
This was removed by the editor as SPAM
October 27, 2021 at 8:10 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply