Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
balousta
Starting Member
2 Posts |
 Posted - 2009-03-08 : 15:17:02
|
I have a recored in sql 2008 server table sysobjects for a store procedure that not physically exist in the data base. If any one could help me in reorg sysobjects table to clean this record.What happen I have a dotnetnuke web site that I wont to upgrade, part of the upgrade there is a sql script that iterate all objects in the sysobject stable and modify the permission. One of the store procedure in the sysobjects table is not exist, i check the store procedure via the sql manager and it's not exist, the upgrade script is aborted with an error, her is the message i got.System.Data.SqlClient.SqlException: Cannot find the object 'NTForums_GetForumsByGroup2', because it does not exist or you do not have permission. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText) at DotNetNuke.Data.SqlDataProvider.GrantStoredProceduresPermission(String Permission, String LoginOrRole)if exists (select * from dbo.sysusers where name='TAWFEEQ') begin declare @exec nvarchar(2000) declare @name varchar(150) declare sp_cursor cursor for select o.name as name from dbo.sysobjects o where ( OBJECTPROPERTY(o.id, N'IsProcedure') = 1 or OBJECTPROPERTY(o.id, N'IsExtendedProc') = 1 or OBJECTPROPERTY(o.id, N'IsReplProc') = 1 ) and OBJECTPROPERTY(o.id, N'IsMSShipped') = 0 and o.name not like N'#%%' and (left(o.name,len('')) = '' or left(o.name,7) = 'aspnet_') open sp_cursor fetch sp_cursor into @name while @@fetch_status >= 0 begin select @exec = 'grant EXECUTE on ' + @name + ' to [TAWFEEQ]' execute (@exec) fetch sp_cursor into @name end deallocate sp_cursor end  |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2009-03-09 : 09:31:20
|
| Can you manually delete the non-existent SP before the upgrade? If not you may need to physically remove the record from the underlying system catalog (sys.sysobjects is a view in 2005 and 2008). What version are you trying to upgrade from? I'm hoping 2000 as that's the easiest to fix...Paul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)SQL Server MVP, Contributing Editor of TechNet MagazineAuthor of SQL 2005 DBCC CHECKDB/repair codeAuthor & Instructor of Microsoft Certified Master - Database course |
 |
|
|
balousta
Starting Member
2 Posts |
Posted - 2009-03-09 : 12:58:21
|
| Thank you PaulI could not delete the none-existing SP. I found the catalog and here what I have tried and the error message i gotdelete from sys.all_objects where name = 'NTForums_GetForumsByGroup2'Msg 259, Level 16, State 1, Line 1Ad hoc updates to system catalogs are not allowed.The version I use is 2005 |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2009-03-09 : 15:32:08
|
| ok - see this blog post, but proceed at your own risk: [url]http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Using-the-SQL-2005-Dedicated-Admin-Connection-to-fix-Msg-8992-corrupt-system-tables.aspx[/url]Paul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)SQL Server MVP, Contributing Editor of TechNet MagazineAuthor of SQL 2005 DBCC CHECKDB/repair codeAuthor & Instructor of Microsoft Certified Master - Database course |
|
|
|
|
|
|
|
|
Record in sysobjects table for not existing object