RSS
 

Archive for the ‘SQL Server 2005/2008’ Category

Can’t Change Structure of existing table in SQL Server 2008

04 Apr

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

i got this error when i tried to change identity of table created in SQL server 2008, search and found that this error will be raised every time you will try to change the structure of the table so the solution is to disable certain feature by follow::–>

Click on the Tools drop down menu and selecting Options.  Under the Designers menu option, select the “Table and Database Designers” menu option and uncheck the “Prevent saving changes that require table re-creation” option.  After clicking OK you will be able to save the changes.

the following links describe the solution with screenshoot…..

http://www.sql-server-performance.com/faq/saving_changes_not_permitted_p1.aspx

and from Microsoft forums i found this post

  1. There are three transactions, one for each table. But it is really all one operation, and it’s not correct to commit before the foreign keys from Child to Parent has been re-added. Thus if the operation fails before the FK is re-added, you will run the database without the FK constraint.
  2. The script does not consider the possibility that an operation may fail with an error that aborts the transaction. If such an error occurs, the script will continue and perform the rest of the steps. I am told that the issue exists only if you run the generated script. If you run Save directly, the client will have control over the transaction and abort.
  3. Constraints that are reapplied are reapplied WITH NOCHECK, that is without verifying that they are valid. This means that SQL Server will mark them as “not trusted” and the optimizer will ignore them. In some situations this can have a serious impact on performance, for instance in partitioned views.
  4. In the scenario above, assume that you first made a change to Child then generated a script. Then you realised that you were changing the wrong table, so you closed Child without saving changes. Then you change Parent as above. If you now save changes, the changes to Child you discarded will now be carried out.
  5. As I think I’ve already mentionrf, the Table Designer only uses ALTER TABLE where SQL 6.5 would permit it. Which means that it would not use ALTER TABLE for a simple change like varchar(10) to varchar(20).

There may be more issues that I have forgotten. It is very clear that the people who wrote and designed this tool, did not understand what it means to make changes to a table. Certainly, it is not a  trivial matter, but changing tables in a production environment requires careful planning. Nevertheless, it’s frightening that MS for so long has included this toy tool in a program for managing enterprise databases.

Again, I strongly recommend that you do not use the Table Designer for implementing table changes, and if
you do, generate a script and review it carefully, and change the script to cater for the defects.

Wikio
Wikio
 
No Comments

Posted in SQL Server 2005/2008

 

Database Backup @ SQL Server 2005

18 Mar

The most common way to maintain duplicate copies of data is by using the backup capabilities built into SQL server, so at failure you have ability to recover from and get your database online again so let’s start review the different types of allowed backup in SQL Server 2005. ·

Full Backups

o Full back up is to capture all the data that is stored in the database.

o Always available, regardless of the recovery model you configure for a database.

o When you initiate a backup, the backup engine writes pages to the backup device without regard to the order of pages. Because the backup is not concerned with the precise ordering of pages, SQL server can open multiple threads to write data as fast as it can be accepted by the media

1. Lock the database, blocking all transactions.

2. Place a mark in the transaction log

3. Release the database lock

4. Back up all pages in the database

5. Lock the database, blocking all transactions

6. Place a mark in the transaction log

7. Release the database lock

8. Extract all transactions between the two log marks and append to the backup

o INIT parameter which is the most common, tells SQL server to overwrite anything in the backup device that might already exist before starting the backup operation.

· Differential Backups

o Captures all the extents that have changed since the last full backup.

o Reduce the number of transaction log backup that need to be restored.

o You can use differential backup along with a full backup. If a full backup does not exist, you can’t create differential backups.

o No matter what recovery model is specified for the database.

o An incremental backup captures any changes since the previous incremental backup. Therefore, restoring an incremental backup requires all other incremental backups. A differential backup always captures every extent that has changed since the last full backup. So each differential backup contains everything that any previous differential backup taken after a full backup contains.

Transaction Log Backups

o Perform transaction log backups only for databases you have configured to use the full or bulk-logged recovery model.

o Allowed only after a full backup has been performed.

o Contains only a subset of data and requires that you also have at least a full backup to recover the database. o It starts at the log sequence Number (LSN) at which the previous log backup completed. SQL server then backs up all subsequent transactions until the backup encounters an open transaction. After SQL server encounters an open transaction, the log backup completes. Any LSN that are backed up are then allowed to be removed from the transaction log, which enables the system to reuse log space. Note that a full backup is required before you can perform either a differential or a transaction log backup.

Wikio
Wikio
 
No Comments

Posted in SQL Server 2005/2008

 

Trigger…Have a Look Part 1

15 Mar
Today, i will go through trigger at database, i found that many developers don’t know about it so i determine to explain points about Triggers and how we can implement it in Microsoft SQL Server, but first what do you think about trigger, what is this word mean ?….. answer is so simple when you use computer and click on right button on mouse you will get menu on the screen, this is we called event –> event handler in other meaning when you press button (Event) then screen appeared on screen (Event Handler). if you understand this apply the same concept on database …… TRIGGERS IS EVENT HANDLER. let’s list the following points
  • A trigger is a specialized implementation of a Transact-SQL or CLR batch that automatically runs in response to an event within the database (i.e. run according to change to any object of database)
  • Triggers are specialized stored procedure that running run in response to certain event, but it is not having parameters and don’t return value and you can’t run it manually.
  • Difference between Triggers & Constraints is Triggers are similar to constraint but need more system overhead so it may lead to performance reduction.
  • Triggers application
    • Perform complex business rules validation
    • Cascading data modifications
    • Keep track of changes of every record (Audit)
  • Triggers Types
    • Data Manipulation Language Trigger (run when INSERT, UPDATE, or DELETE statements modify data in a specified table or view.)
    • Data Definition Language Trigger (which run in response to DDL events that occur on the server such as creating, altering, or dropping an object, are used for database administration tasks such as auditing and controlling object access)
  • DML Trigger
    • DML triggers are not stand-alone objects, and you can’t directly execute them. A DML trigger is attached to a specific table or view and defined for a particular event. When the event occurs, SQL Server automatically executes the code within the trigger, known as “firing the trigger.” The events that can cause a trigger to fire are INSERTING, UPDATE, and DELETE operations or you can make combinations of previous three types.
    • Triggers can update tables in other databases, and it is possible for triggers to span servers as well so don’t think the scope of trigger is limited to current database.
    • Trigger fire Mode
      • After
        • An AFTER trigger fires after SQL Server completes all actions successfully
        • You can define AFTER triggers only on tables
        • You can create any number of AFTER triggers on a view or table.
  • Instead of
    • causes SQL Server to execute the code in the trigger instead of the operation that caused the trigger to fire
    • You can create INSTEAD OF triggers on views and tables
    • You can define only one INSTEAD OF trigger for each INSERT, UPDATE, or DELETE event for a view or table
    • The most common usage is to use INSTEAD OF triggers on views to update multiple base tables through a view
    • If you have an INSTEAD OF trigger that will execute on a data INSERT, then the insertion will not take place.

You can’t use within trigger

  • Databases cannot be created, altered, dropped, backed up, or restored.
  • Structural changes cannot be made to the table that caused the trigger to fire, such as CREATE/ALTER/DROP INDEX, ALTER/DROP TABLE, and so on.

Nested triggers

For example, imagine you have Table A, which has a trigger on it to fire a modification within Table B, which in turn has a trigger on it that fires a modification within Table C. If a modification is made to Table A, then Table A’s trigger will fire, modifying the data in Table B, which will fire the trigger in Table B, thus modifying data in Table C. This nesting of triggers can go up to 32 triggers deep before you reach the limit set within SQL Server

Note that

SQL Server does not support triggers against system objects such as system tables and dynamic management views. Also, triggers will fire only in response to logged operations. Minimally logged operations such as TRUNCATE and WRITETEXT do not cause a trigger to fire.

Inserted & Deleted tables

Triggers have access to two special tables that are dynamically generated: INSERTED and DELETED. INSERTED and DELETED tables are visible only within a trigger and cannot be accessed by any other construct such as a stored procedure or function. The structure of the INSERTED and DELETED tables exactly matches the column definition of the table on which the trigger was created. Therefore, you can reference columns by using the same name as the table for which the trigger was defined.

The main thought that you must keep in mind when building a trigger is that a trigger fires after each record is flagged to be modified.

Waiting Me @ Trigger…Have a Look Part 2

Wikio
Wikio
 
No Comments

Posted in SQL Server 2005/2008

 
 
 

You need to log in to vote

The blog owner requires users to be logged in to be able to vote for this post.

Alternatively, if you do not have an account yet you can create one here.

Powered by Vote It Up

My Highlights powered by RoohIt (for WordPress)