“Invalid object name ‘master.dbo.spt_values’.” This means your SQL Master DB is in a bad state. To fix: Error- Invalid object name ‘master.dbo.spt_values’. CD “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install” run u_tables.sql in master….
Using Integrated Security in connection strings should be either of the following: Integrated Security=SSPI or Integrated Security=false Not Integrated Security=true
declare @vcFK varchar(250), @vcTable varchar(250), @vcSP varchar(250), @vcView varchar(250), @vcFn varchar(250) -- ...drop all foreign key constraints select @vcFK = min(name) from sysobjects where type='F' while @vcFK is not null begin print 'Dropping FK constraint ' + @vcFK -- ...get name of table corresponding to the foreign key select @vcTable = S2.name from sysobjects S1 inner join sysconstraints C on S1.id = C.constid inner join sysobjects S2 on C.id = S2.id where S1.name = @vcFK and S1.type='F' exec ('alter table ' + @vcTable + ' drop constraint ' + @vcFK) select @vcFK = min(name) from sysobjects where type='F' and name > @vcFK end -- ...drop all tables select @vcTable = min(name) from sysobjects where type='U' and name not like 'dt%' while @vcTable is not null begin print 'Dropping table ' + @vcTable exec ('drop table ' + @vcTable) select @vcTable = min(name) from sysobjects where type='U' and name not like 'dt%' and name > @vcTable end -- ...drop all our stored procedures select @vcSP = min(name) from sysobjects where type='P' and (name like 'usp%') while @vcSP is not null begin print 'Dropping procedure ' + @vcSP exec ('drop procedure ' + @vcSP) select @vcSP = min(name) from sysobjects where type='P' and (name like 'usp%') and name > @vcSP end -- ...drop all views select @vcView = min(name) from sysobjects where type='V' and name like 'v%' while @vcView is not null begin print 'Dropping view ' + @vcView exec ('drop view ' + @vcView) select @vcView = min(name) from sysobjects where type='V' and name like 'v%' and name > @vcView end -- ...drop all functions select @vcFn = min(name) from sysobjects where type='FN' and name like 'udf%' while @vcFn is not null begin print 'Dropping function ' + @vcFn exec ('drop function ' + @vcFn) select @vcFn = min(name) from sysobjects where type='FN' and name like 'udf%' and name > @vcFn end
CREATE PROCEDURE AS BEGIN SET NOCOUNT ON BEGIN TRY --Do work END TRY BEGIN CATCH /* Note that catching and rethrowing an exception is a lossy operation. ERROR_PROCEDURE() etc will be reset. */ DECLARE @errorMessage NVARCHAR(4000); DECLARE @errorSeverity INT; DECLARE @errorState INT; SELECT @errorMessage = ERROR\_MESSAGE(), @errorSeverity = ERROR\_SEVERITY() , @errorState = ERROR\_STATE(); --Perform required recovery actions. RAISERROR ( @errorMessage, @errorSeverity, @errorState ); RETURN 1; END CATCH ...
Delete all tables EXEC sp_MSforeachtable @command1 = “DROP TABLE ?” Grant execute permissions to all stored procedures to a specific user `CREATE PROC grantexecutepermission( @UserName NVARCHAR(250)) AS DECLARE curse CURSOR FOR SELECT name FROM sysobjects WHERE TYPE = 'P'` OPEN curse DECLARE @proc VARCHAR(100) DECLARE @stmt NVARCHAR(200) FETCH NEXT FROM curse INTO @proc WHILE @@FETCH\_STATUS \= 0 BEGIN SET @stmt \= 'grant execute on ' + @proc + ' to ' + @UserName EXEC sp\_executesql @STMT PRINT @stmt FETCH NEXT FROM curse INTO @proc END CLOSE curse DEALLOCATE curse ...