mssql 2008 fix error Saving changes is not permitted …

Leave a comment

fix sql server error occur : “Saving changes is not permitted. The changes that 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”

answer : Tool bar of SQL Server 2008 choose Tools >> Options >> Designers
uncheck “Prevent saving changes that require table re-creation”

Advertisements

Creating Tricker when Insert Update

Leave a comment

— ================================================
— Template generated from Template Explorer using:
— Create Trigger (New Menu).SQL

— Use the Specify Values for Template Parameters
— command (Ctrl-Shift-M) to fill in the parameter
— values below.

— See additional Create Trigger templates for more
— examples of different Trigger statements.

— This block of comments will not be included in
— the definition of the function.
— ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        <Author,,Name>
— Create date: <Create Date,,>
— Description:    <Description,,>
— =============================================
CREATE TRIGGER BW_POI_SetCountryID
ON  BW_POI
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;

update bw_poi
set countryid = case
when i.country = ‘us’ or i.country = ‘usa’ or i.country like ‘united states%’ then 0
when i.country = ‘canada’ then 1
when i.country = ‘mexico’ or i.country = ‘mx’ then 2
when i.country = ‘australia’ then 3
when i.country = ‘thailand ‘ then 4
else null
end
from inserted i
inner join bw_poi p on i.[id] = p.[id]

END
GO

**************************select country, countryid, *
from bw_poi
where countryid is null and country <> ”

–update bw_poi set country = country

CASE WHEN in sql command

Leave a comment

SELECT Name, RatingID AS Rating,
   CASE RatingID
      WHEN ‘R’ THEN ‘Under 17 requires an adult.
      WHEN ‘X’ THEN ‘No one 17 and under.
      WHEN ‘NR’ THEN ‘Use discretion when renting.
      ELSE ‘OK to rent to minors.
   END AS Policy
FROM DVDs
ORDER BY Name;

backup restore sql server 2005

Leave a comment

RESTORE DATABASE PersonnelTestFixDept

FROM DISK = ‘C:\db1.bak’

WITH REPLACE