Tag Archives: SQL Server

Check object existence on SQL Server using object_id function

This function will come handy to check existence of specific object in SQL Server. It commonly be used on deployment script to check if specific object need to be deployed had been exist, then further action would be assigned (ie. delete, copy and backup). It is also being used frequently on stored procedure with virtual table, to avoid if stored procedure would be stop at middle before virtual table being deleted.

Example of usage:

 1: if object_id('LedgerTransaction_Rate') is not null
 2:     drop view LedgerTransaction_Rate
 3: go
 4: 
 5: create view LedgerTransaction_Rate
 6: as
 7: select
 8:     L.*
 9:     , Year(TransDate) as TransYear
 10:     , Month(TransDate) as TransMonth
 11:     , datepart(qq, transdate) as TransQuarter
 12:     , dbo.ExchangeRate_get(C.CurrencyFunctional,
 13:         'Closing', L.TransDate) as ClosingRate
 14:     , dbo.ExchangeRate_get(C.CurrencyFunctional,
 15:          'MthAvg', L.TransDate) as AverageRate
 16: from
 17:     LedgerTransaction L
 18:     left join Company C on L.CompanyId=C.CompanyId
 19: go

SQL 2008 Error: “Saving changes is not permitted. The changes … require table to be recreated”

Today when I want to alter a database structure on my newly installed SQL Server 2008 R2, I am stopped by following error

“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”

So, I just following the advised solution written on the error to disable Prevent saving changes and it works fine again.

Screen Shot 2012-10-17 at 9.37.50 PM

Here are step I to disable the setting:

  1. From Microsoft SQL Server Management Studio, click menu Tools – Options
  2. Click Designers, it should be item no 6 on main options tree
  3. Uncheck “Prevent saving changes that require table re-creation”