Cannot Remove an Invalid Object
-
Some time ago, probably in SQL 7.0, the following stored procedure was created, (I generalized the names):
USE [DbName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[#mytest] AS
CREATE TABLE #mytest(ColumnName nvarchar(50))
INSERT #mytest
SELECT [TableName].ColumnName FROM [TableName]
SELECT * FROM #mytest
DROP TABLE #mytestI suspect someone was probably trying to learn about temporary tables.
Now, fast forward to today with SQL 2016. Over the years I have tried to get rid of it with no luck. It was not causing any problems so I went on about my business. However, now I am trying to cleanup the database for source control and it needs to go. In mssms #mytest is listed as a stored procedure. It is not listed in the tempdb Temporary Tables section.
I have tried the following things:
DROP PROCEDURE [dbo].[#mytest] - Error: Cannot drop the procedure '#mytest', because it does not exist or you do not have permission.
Right click on the stored procedure name and select Delete - Error: Drop failed for StoredProcedure '[dbo].[#mytest]'.
Right click on the stored procedure name and select Rename - Error: Unable to rename mytest. (objectexplorer). Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.
DROP TABLE [dbo].[#mytest] - Error: Cannot drop the table '#mytest', because it does not exist or you do not have permission.
The problem seems to be that a stored procedure is named like a temporary table and has become orphaned. There's got to be a way to manually cleanup the bad records.
Any ideas will greatly appreciated!
-
My guess with this is not a problem with the stored procedure naming but more with your permissions.
I just executed the following against a SQL Server 2016 instance and it worked without any problems:
CREATE PROCEDURE [dbo].[#test1]
AS
SELECT 1 AS value
INTO #test2
SELECT *
FROM [#test2]
DROP TABLE [#test2]
GO
DROP PROCEDURE [dbo].[#test1]From what I can see, this should be doing essentially the same thing as what you are doing and I got no errors and the object dropped successfully.
My first step would be to validate your permissions on the database and ensure that you have the appropriate permissions to drop procedures.
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. -
A temporary stored procedure is created the same way a temporary table is created - it is a temporary object available for that connection and located in tempdb.
When the connection is terminated the procedure will be dropped automatically. If it is not dropped, then it will be dropped when SQL Server is restarted because tempdb is rebuilt every time SQL Server is restarted.
Where are you seeing this procedure - is it in one of the user databases or is it in the tempdb database? If it is in the tempdb database - then it is either being created by some other process, or someone has added that temp procedure to the model database.
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 -
It is showing up under Stored Procedures in one of our databases, not the tempdb database. This object has remained in existence through our server upgrades to SQL 2005, SQL 2008, and SQL 2016. My source control process requires an error free environment but it keeps finding this one invalid object.
-
Oh that is a challenging one then. I was just trying to figure out how to reproduce that.
As a thought, I am wondering if there might be some character before the # (such as a space or a non-printable character). As a thought, if you SELECT from sys.procedures on that database, does it show up in the list?
And if you run the following:
SELECT
CAST([name] AS VARBINARY(MAX))
, CAST('#mytest' AS VARBINARY(MAX))
FROM[sys].[procedures]
WHERE[name] LIKE '#mytest';on that database, do both columns match? I am wondering if MAYBE some character got put in front of the # (such as a NULL or start of text or something) and it needs to be added back in.
If you look at the VARBINARY for #mytest you get 0x236D7974657374. Taking that and breaking it up by 2 digits per character, we can see that 23 is the first character and in ASCII, HEX 23 is DEC 35 which is the # character. 6D corresponds with m and so on.
I may be way out to lunch here, but my hope is that I am close (or possibly right) in which case fixing it shouldn't be too hard to do I think. Not 100% positive, but one of those "fingers crossed" type things.
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. -
Can you show us the name you see in that database?
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 -
Had not thought about that road. Here is what that script produced:
CAST([name] AS VARBINARY(MAX)) = 0x23006D0079007400650073007400
CAST('#mytest' AS VARBINARY(MAX)) = 0x236D7974657374
Other than the extra zeros they match.
-
the object shows up in the sys.procedures view as #mytest.
I would also like to add that I am logged in as a sysadmin.
The extra "00" is added due to the NVARCHAR datatype. So it does appear that there are no hidden characters. BTW, that object was created 9/15/1999. LOL
-
Ok I am completely stumped on this one. I was trying to re-create the object using different flavors of dynamic SQL and casting and converting hoping I could trick SQL into creating the object, but no such luck.
I can think of some crazy ideas on how to fix this error but I do NOT recommend these... but it might work. First, so we don't impact the live system, take a backup of the database, restore the backup with a new name then detach the database. This way we have the MDF and LDF files. Next, open the MDF file in a hex edior and search for the value 23006D0079007400650073007400 (the value found in sys.procedures). Everywhere you find it, change the 23 to 6D (NOTE this is assuming you have no procedure already existing named mmytest in the database. If you do, pick a different letter to change that to). It MAY be in the database in multiple locations. Once you have changed it everywhere, save the file and re-attach the database (again with the new name so you don't break the current one) and if you check sys.procedures, it should now be named mmytest instead of #mytest. Now you should be able to drop it, and then run DBCC CHECKDB on it to make sure nothing is horribly broken. If it was successful, detach the old database and rename the modified one to the new name and you may be good to go.
I say this is not recommended as modifying a database file via a hex editor is never advised. It might work, but might also have some unexpected surprises after doing this. My opinion, this sort of change should be fine as long as you change all occurrences of the string to the same value. That is, not have one changed to 6D and another changed to 6E. Another approach you could do which would be slow and painful would be to script out all of the objects and users and write them to a new database and then transfer the actual data over. Basically script out the database and recreate it but skip creating that #mytest stored procedure (or don't... it should be created in tempdb and disappear when the session is closed with the new SQL version) and once that script finishes, swap the names of the databases. Depending on the size of the database and the number of tables, this may be a large and annoying task though.
With all of my advice above, I would recommend to do this on a TEST system first and then test the crap out of everything. And the reason I don't recommend just removing the 2300 is that changing the file size and the location of the bits will likley corrupt the whole thing. When working with a hex editor on a non-text file, it is ALWAYS safer to just change existing values without adding or removing any data. When you are done, the file size should be identical to before you started.
OR, a different approach, reach out to Microsoft. There are some pretty smart developers out there who would probably enjoy tackling a problem like this. You ran into a case that shouldn't be possible yet is. And I expect you are not the first to find it, you are just the first who decided that they need to fix it. And if this is a situation that Microsoft hasn't run into before, it could cause a problem with a future update for you (and potentially others).
EDIT - never mind...hex editing the stored procedure name looks like it likely won't work due to "incorrect checksum". Doing some testing on my end to see if I can replicate the problem (via hex editing and setting changes)...
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. - Brian Alger wrote:
Had not thought about that road. Here is what that script produced:
CAST([name] AS VARBINARY(MAX)) = 0x23006D0079007400650073007400
CAST('#mytest' AS VARBINARY(MAX)) = 0x236D7974657374
Other than the extra zeros they match.
These are not the same - if you reverse this:
Select cast(0x23006D0079007400650073007400 As varchar(30))
, cast(0x236D7974657374 As varchar(30))The first one returns # only - the second one returns #mytest. Those extra 00 characters are embedded NUL characters in the string - and that will not match.
What I would do at this point is get the name and schema from sys.procedures and put them in variables, then create dynamic SQL that uses those variables to build the drop statement and execute the dynamic SQL. Warning: put this in a transaction and rollback to validate the code - this way you can make sure it only deletes the one procedure and nothing else.
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 - Jeffrey Williams wrote:Brian Alger wrote:
Had not thought about that road. Here is what that script produced:
CAST([name] AS VARBINARY(MAX)) = 0x23006D0079007400650073007400
CAST('#mytest' AS VARBINARY(MAX)) = 0x236D7974657374
Other than the extra zeros they match.
These are not the same - if you reverse this:
Select cast(0x23006D0079007400650073007400 As varchar(30))
, cast(0x236D7974657374 As varchar(30))The first one returns # only - the second one returns #mytest. Those extra 00 characters are embedded NUL characters in the string - and that will not match.
What I would do at this point is get the name and schema from sys.procedures and put them in variables, then create dynamic SQL that uses those variables to build the drop statement and execute the dynamic SQL. Warning: put this in a transaction and rollback to validate the code - this way you can make sure it only deletes the one procedure and nothing else.
If you run that but cast it as NVARCHAR it works though. CASTING as VARCHAR expects 2 bytes per character, NVARCHAR does 4 bytes per character.
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. -
So was able to replicate the issue!
My steps were to first create the database, create the stored procedure named mmytest. Next, changed Page Verify to NONE, took the database offline, loaded up the MDF file in a hex editor (I used HxD as it was free), searched for 6d006d0079007400650073007400 and replaced it with 23006d0079007400650073007400 (I had 3 cases where this value popped up in my sample database) and hit save. Next, brought the database online which was successful, ran DBCC CHECKDB(corruptiontest, repair_rebuild) so I wouldn't get any consistency errors from checkdb. Finally, ran SELECT * FROM sys.procedures to get the above screenshot. CHECKDB says everything is good. At this point, I can turn page verify back to checksum and I get no errors and I still get the above in the database.
So, to summarize a "fix" for this:
1 - take a backup of the database JUST IN CASE and run CHECKDB just to make sure nothing else is broken that MAY get fixed (or made worse) by running future steps. IF CHECKDB comes back with something bad, fix that first.
2 - set PAGE VERIFY to NONE
3 - take database offline
4 - load up the MDF file in a hex editor
5 - search in the HEX for the value you are looking for and replace it with something that makes sense and is easy to find in the database (ie not any non-printable character)
6 - save the MDF
7 - bring the database online
8 - run DBCC CHECKDB(<database>, REPAIR_REBUILD)
9 - change PAGE VERIFY back to CHECKSUM
10 - drop the stored procedure
I would still do this on a TEST system first so you can do some additional validation that nothing is damaged beyond repair from this. My testing on my sample (empty) database made this quick and painless. YMMV.
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. -
if the database itself isn't on the terabyte size level why not just create an empty db and migrate all objects to it EXCEPT that one? then simple backup and restore with old name and problem solved.
-
Thank you all for the time and effort put into trying to understand and fix this issue. I will see what I can get past my boss and play with these ideas over the next few weeks in my development environment. We have a 2 week shut down coming in August. I hope to finally fix it then. Thanks Again!
-
What I would strongly encourage you to do is get a test instance set up so you can try out the different methods suggested. The main reason to get a test instance up is so you can do a trial run of a fix and document it. If you decide to go with the migrate and swap approach, it would be good to know how long that takes to complete and decide how you are going to migrate the data (SSIS or TSQL)
Depending on how frequently the data changes, you may benefit from making an SSIS package that just copies the changed data over. If that package works, you could use the test instance to get a version that is just with today's data, and then when you are ready to go live, you have a much smaller data set to move over.
My last comment - the "hex editor" method would be my "last resort" method. Editing any binary file (or at least not plain text) in a hex editor without advice from someone who knows the system inside and out is likely to introduce problems down the line. While the hex editor approach worked for me on my test database, it doesn't mean that it is the right solution in your case. But this is another case where testing is critical.
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.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply