Archive

Archive for the ‘Error Messages’ Category

Understanding MERGE Transact SQL Statement

July 5, 2011 1 comment

By using “Merge”, you can perform insert, delete and update on a table in a single SQL statement. This helps in synchronizing the tables as well.

However while using merge there are several points that you need to keep in mind. Few

  • In a “Merge” statement, a “When Matched” clause with a search condition cannot appear after a ‘When Matched’ clause with no search condition.
  • A “Merge” statement must be terminated by a semi-colon (;).
  • An action of type ‘DELETE’ is not allowed in the ‘WHEN NOT MATCHED’ clause of a MERGE statement.
  • At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.
  • Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints.

Few examples of merge are as given below.

Read more…

 

 

SQL Interview Questions – Basics 2

October 8, 2010 Leave a comment

What are the advantages and disadvantages of Surrogate Key ?

Pros:

  1. Business Logic is not in the keys.
  2. Small 4-byte key (the surrogate key will most likely be an integer and SQL Server for example requires only 4 bytes to store it, if a bigint, then 8 bytes).
  3. Joins are very fast.
  4. No locking contentions because of unique constraint (this refers to the waits that get developed when two sessions are trying to insert the same unique business key) as the surrogates get generated by the DB and are cached – very scalable.

Cons:

  1. An additional index is needed.  In SQL Server, the PK constraint will always creates a unique index, in Oracle, if an index already exists, PK creation will use that index for uniqueness enforcement (not a con in Oracle).
  2. Cannot be used as a search key.
  3. If it is database controlled, for products that support multiple databases, different implementations are needed, example: identity in SS2k, before triggers and sequences in Oracle, identity/sequence in DB2 UDB.
  4. Always requires a join when browsing the child table(s).

 

What are  identity columns?

An Identity column is a column (field ) in a database table that is made up of values generated by the database.

Continue Reading …

Script to List Linked Servers

October 7, 2010 Leave a comment

In a large server , it is very common to create high number of linked server connections. it might be required ( not very often) to use this list in the SPs or other objetcs created. Below is a simple query / script that can provide the list of linked servers used.

Continue Reading ..

Renaming MS SQL Server database

February 8, 2010 2 comments

Renaming a database is not done very often. However, I have done it myself many times.  this can be done in two ways. The common method used for rename is by using ” Alter Database ” script. An example is as shown below.

ALTER DATABASE OldDbName MODIFY NAME = NewDbName

The above script will rename the database “OldDbName” with new name “NewDbName”. Another way of acheiving the same is by using the stored Procedure “SP_RENAMEDB” The syntax is as below

EXEC SP_RENAMEDB 'OldDbName' , 'NewDbName'

“Alter” is preferred over the SP_RenameDB as the SP will be deprecated in the future version of  SQL Server.

SQL Server might throw an error while renaming a database as below.

This is due to the existing conections to the database. to overcome this error,

1.you need to drop all the connections to the database.  Refer to my previous blog for more info on this.https://awesomesql.wordpress.com/2010/02/08/script-to-drop-all-connections-to-a-database/

2. You can set the database to single user mode . refer the link for more info on this. https://awesomesql.wordpress.com/2009/08/04/changing-the-collation-of-a-database/

Change the system error messages with custom messages

December 30, 2009 1 comment

This post discusses about ways to change the system messages with custom messages . This is not widely used, as it is not advisable to change these messages.  However there is nothing wrong in knowing it. :) 

We can change the Custom messages by using system pocs “sp_dropmessage” and “sp_addmessage “. 

I executed a query as given in the picture below. 

After this I used the script below to change the system message. The proc “sp_dropmessage” is used to drop the already existing message and  “sp_addmessage ” to add a new one.  

  

IF EXISTS (SELECT 1 FROM sys.messages WHERE message_id = 100097) BEGIN 

EXEC sp_dropmessage 100097  

END 

GO 

EXEC sp_addmessage @msgnum = 100097, @severity = 16, @msgtext = ‘SP %s : My new text message!’  

 

The message was changed as shown in the picture. 

Now, can we do the same using the script below ? 

update  SYS.MESSAGES 

 set  text = ‘SP %s : My new text message!’ 

 WHERE MESSAGE_ID = 100097 

try it yourself

Connect to SQL Server 2008 from SSMS for 2005

September 3, 2009 Leave a comment

Many people have asked me this in the past. I used the SP3 released for SQL Server 2005. Install this and you should be able  to connect to sql server 2008 from SSMS of 2005. The service pack is available for download in the microsoft site or click here for download.

Rest of the configuration is done in the same way, as you configure to connect to SQL server 2005.

Categories: Error Messages, SQL Server