Cannot rename objects since SP3 failed | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Cannot rename objects since SP3 failed


Both Service Pack 3 and Service Pack 3a for SQL Server 2000 failed during installation on one of our servers, both times while running script "sp3_serv_uni.sql".
The server was previously running on Service Pack 2. The error message, as reported in sp3_serv_uni.out…..
Creating ‘system_function_schema.fn_get_sql’.
Msg 2714, Level 16, State 6, Server SPMSVR, Procedure fn_get_sql, Line 15
There is already an object named ‘fn_get_sql’ in the database. However, a query search on the master database reveals no such object exists:
select * from sysobjects where xtype = N’TF’
and uid = USER_ID(‘system_function_schema’) and name = ‘fn_get_sql’ I have read that some others have experienced similar problems with SP3, caused by .NET’s MDAC 2.8 This kind of problem was sorted in SP3a but did not solve my problem. Furthermore, it appears that, since the failed SP3 upgrade, I can no longer rename any database objects in user databases.
I can create and delete objects and perform any other administrative functions attempted so far, but cannot rename objects.
Enterprise Manager simply reports "Cannot rename object"
while Query Analyzer (in Northwind DB for example) reports:
Object ‘customers’ does not exist or is not a valid object for this operation. A DBCC checkdb on all my databases (system and user) reports no errors. Is it possible that some part of the failed SP3 upgrade script has set a system flag that was not rolled back ? Any help on either the SP3 problem or the object renaming problem would be appreciated.
http://www.databasejournal.com/features/mssql/article.php/2189761 on fn_get_sql information.
Try to review SQLSP.LOG for more information on SP3a patch application. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Are you running the upgrade logged in as ‘sa’ and is the owner of the objects that fail ‘dbo’ and not something else?
A little more background; Both the OS and SQL Server was recently reinstalled on this server after installing new hard disks (RAID 5 set, no problems encounted). The system databases were restored from backups and user databases were all re-attached from properly detached files. The backup, restore, detach and re-attach was performed under SP2, before and after the SQL Server re-installation respectively. Thereafter, SP3 was attempted and it failed. Now, things have just become a little stranger..
I’ve narrowed down the problem but I’ll bet it’s one of strangest things you folks will read about. On my one server right now…
All new database objects that get created by users in any role that has permissions to create objects, including the sa login and table imports via DTS, now have the following unusual characterstics: (1)Enterprise manager displays all newly created objects as System objects,
not User objects. (2)The ‘xtype’ column in the sysobjects table reports these objects as ‘U’ and not ‘S’ which compounds the mystery as to why Enterprise Manager displays them as system objects. (3)In the case of a table object:
From Enterprise Manager, I cannot drop the table, rename the table or rename the columns in the table, presumably because SQL server believes it to be a system table.
I can add and remove columns though. (4)From Query Analyzer, I have the same permissions/problems/restrictions as in Enterprise Manager, with the exception that I can drop the table using a ‘drop table’ statement. The objects display ownership as dbo which is normal.
As mentioned before, DBCC CheckDB reports no errors on any databases. Has anyone witnessed something so odd before ?
<br />Update on this situation.<br />I decided to restore the master database from 3 days ago which was the last backup I had that was prior to the SP3 failure.<br />This has fixed the strange problem I had with the system objects being created as default along with the renaming problems.<br /><br />I’m still without success on SP3 though, but I’m relieved to at least have my server functioning normaly in every other aspect again, under SP2.<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />
Any other information on SQLSP.LOG file for this SP3a. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
SP3a was applied (attempted) using the sa login.
The last line entries in SQLSP.log are:
—————————————-
12:01:43 Process Exit Code: (1)
12:01:45 Error running script: sp3_serv_uni.sql (1)
12:01:45 C:DOCUME~1ackupLOCALS~1TempSqlSetupBinscm.exe -Silent 1 -Action 6 -Service MSSQLSERVER
12:02:00 Process Exit Code: (0)
12:02:00 Action CleanUpInstall:
12:02:00 Installation Failed.
——————————————
In my case, this log entry does not provide any real clues beyond what is already known.
There are no other error or failure messages other than listed above.
As mentioned in my original post, I was able to narrow down the exact point of failure in sp3_serv_uni.sql:
The error message, as reported in sp3_serv_uni.out…..
——————————————————–
Creating ‘system_function_schema.fn_get_sql’.
Msg 2714, Level 16, State 6, Server SPMSVR, Procedure fn_get_sql, Line 15
There is already an object named ‘fn_get_sql’ in the database.
—————————————————-
The script itself (sp3_serv_uni.sql) even attempts to drop the object if it already exists:
———————————————–
if exists (select * from sysobjects
where xtype = N’TF’ and uid = USER_ID(‘system_function_schema’) and
name = ‘fn_get_sql’)
drop function system_function_schema.fn_get_sql
go
————————————————–
Running "drop function system_function_schema.fn_get_sql" on its own
returns a message that the object does not exist. It seems I’m in a Catch 22 situation. sp3_serv_uni.sql cannot drop an object if it does not exist and it cannot create the same object if it already exists.
system_function_schema.fn_get_sql seem to both ‘exist’ and ‘not exist’.
Right in this case I would suggest to approach MS PSS for a solution as it seems some issue with SP setup or so. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
A lot of times we need a change . While working on an existing database, we may need to change the database name and in some cases want to rename existing database objects. This can be done in a few seconds. In SQL Server this can be done in this manner :1. Renaming a database : The SQL Server command for renaming a database is : Syntax: EXEC sp_renamedb ‘oldName’, ‘newName’ ; eg: Suppose we have a database named "GPSTrainees" and we want to rename it to "KLMSTrainees" We can write : EXEC sp_renamedb ‘GPSTrainees’ , ‘KLMSTrainees’ ; However, though this command works on SQL Server 2000 and SQL Server 2005, it will not be supported in future versions of SQL Server. The new command that should be used for SQL Server 2005 and beyond is: ALTER DATABASE oldName MODIFY NAME = newName ; eg: ALTER DATABASE GPSTrainees MODIFY NAME=KLMSTrainees ; For more details plz logonhttp://www.mindfiresolutions.com/Renaming-Databse-Objects-859.php
You can get help from SQL moderator or just upgrade it to new version, you will get rid of this issue.Check it out it really works.
]]>