Category Archives: SQL Server 2008

SQL Server Exception Handling by TRY…CATCH

Like C#, SQL Server also has an exception model to handle exceptions and errors that occurs in T-SQL statements. To handle exception in Sql Server we have TRY..CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH blocks. In Sql Server, against a Try block we can have only one CATCH block.

TRY..CATCH Syntax



  1.  BEGIN TRY



  2. --T-SQL statements



  3. --or T-SQL statement blocks



  4. END TRY



  5. BEGIN CATCH



  6. --T-SQL statements



  7. --or T-SQL statement blocks



  8. END CATCH




Error Functions used within CATCH block

  1. ERROR_NUMBER()

    This returns the error number and its value is same as for @@ERROR function.

  2. ERROR_LINE()

    This returns the line number of T-SQL statement that caused error.

  3. ERROR_SEVERITY()

    This returns the severity level of the error.

  4. ERROR_STATE()

    This returns the state number of the error.

  5. ERROR_PROCEDURE()

    This returns the name of the stored procedure or trigger where the error occurred.

  6. ERROR_MESSAGE()

    This returns the full text of error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Exception handling example



  1.  BEGIN TRY



  2. DECLARE @num INT, @msg varchar(200)



  3. ---- Divide by zero to generate Error



  4. SET @num = 5/0



  5. PRINT 'This will not execute'



  6. END TRY



  7. BEGIN CATCH



  8. PRINT 'Error occured that is'



  9. set @msg=(SELECT ERROR_MESSAGE())



  10. print @msg;



  11. END CATCH



  12. GO 







  1.  BEGIN TRY



  2. DECLARE @num INT



  3. ---- Divide by zero to generate Error



  4. SET @num = 5/0



  5. PRINT 'This will not execute'



  6. END TRY



  7. BEGIN CATCH



  8. SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;



  9. END CATCH;



  10. GO






Note

  1. A TRY..CATCH block combination catches all the errors that have a severity between 11 and 19.
  2. The CATCH block is executed only if there is an error occurs in T-SQL statements within TRY block otherwise the CATCH block is ignored.
  3. Each TRY block is associated with only one CATCH block and vice versa
  4. TRY and CATCH blocks can’t be separated with the GO statement. We need to put both TRY and CATCH blocks within the same batch.
  5. TRY..CATCH blocks can be used with transactions. We check the number of open transactions by using @@TRANCOUNT function in Sql Server.
  6. XACT_STATE function within the TRY..CATCH block can be used to check whether a open transaction is committed or not. It will return -1 if transaction is not committed else returns 1.

Rebuilding MS SQL Log File

There are situation where you need to rebuild your Microsoft SQL Server’s transaction log file. Today we discuss about such situations and how we can resolve it. In production environments where lot of data is being handled, the possibility of increasing the log file size we can’t control. We can recover the space by running the shrink command. But there will be situations even after running the shrink command the size will not reduce or the total database performance will not improve. One more situation is that log file is getting removed accidentally. So what can we do now? This is where we need to think about rebuilding the log file. DBCC REBUILD_LOG command can be handy to rebuild the SQL log file.
One thing we need to make sure before running this command is that some how we need to have the database exists in SQL Server. Setting the database to emergency recovery mode will solve this problem. Databases which lost its log file accidentally can be directly set to emergency recover mode, but for the first case (log file performance issue) we need to follow the below steps.

  • Stop SQL Server.
  • Delete Log file (.LDF) file.
  • Start the SQL Server


Since the log file got deleted if you try to access the database it will throw error, so don’t try to access still the time it got rebuild. Now we see how we can set the database to emergency recovery mode. Run the following commands to do so. You have to run these commands under master database.

USE Master
GO

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN
UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'DATABASE_NAME'
IF @@ROWCOUNT = 1
BEGIN COMMIT TRAN
RAISERROR('Emergency Mode Successfully Set', 0, 1)
END
ELSE
BEGIN ROLLBACK
RAISERROR('Setting Emergency Mode Failed', 16, 1)
END
GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

Once you have finished running the above commands start SQL Server. Again don’t forget one thing “Don’t try to access the database.”

Above commands changed the database to emergency mode. Now run the following commands to rebuild the log file.

DBCC REBUILD_LOG(''DATABASE_NAME'','C:\Database\logfilename.ldf')
ALTER DATABASE 'DATABASE_NAME' SET MULTI_USER
GO

DBCC CHECKDB ('DATABASE_NAME')

A brand new transaction log file got created for your existing database from the scratch. You may lose some transactional integrity by using this method, but it is not really a big issue comparing to the situation you have.

Operand type clash: int is incompatible with date in MSSQL

While writing T-SQL, you might face a problem where a user defined values (like date, datetime) has to be concatenated along with main sql. But in this case while concatenating , since date value is obtained as 2010-2-1. But in case of sql this is a int and cannot be compared to a date field hence has to be denoted date by including single quotes but since T-SQL  is already a string, you might face some problem while writing query. Hence such special character has to be escaped and can be done as below:

This expression 12-4-2005 is a calculated int and the value is -1997. You should do like this instead '2005-04-12' with the ' before and after.

Using Stuff in MSSQL to insert string into another at specified position

STUFF (Transact-SQL)

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

STUFF() can be used to stuff a string into another string. It inserts the string at a given position, and deletes the number of characters specified from the original string.
Syntax

STUFF ( character_expression , start , length , replaceWith_expression )
Arguments

character_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.
start
Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.
length
Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression. length can be of type bigint.
replaceWith_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of character_expression beginning at start.
Return Types

Returns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.
Remarks

If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the start position is 0, a null value is returned. If the length to delete is longer than the first string, it is deleted to the first character in the first string.
An error is raised if the resulting value is larger than the maximum supported by the return type.
Examples

The following example returns a character string created by deleting three characters from the first string, abcdef, starting at position 2, at b, and inserting the second string at the deletion point.
SELECT STUFF('abcdef', 2, 3, 'ijklmn');
GO
Here is the result set.
--------- 
aijklmnef 

(1 row(s) affected)

Ranking or ordering in sql tables by partitioning

Consider we have table as follows:
Studentid
Subjectid
ObtainedScore
1
1
25
2
1
45
3
1
27
4
1
35
5
1
25
6
1
24
1
2
31
2
2
42
3
2
42
4
2
36
5
2
12
6
2
10
If we need to rank students based on their obtained score in each subject what would you write a query for. Among many way I’m illustrating following code for generating rank using row_number(), over and partition:
Select studetid, subjected, obtainedscore, row_number() over () partition by subjectid order by obtainedscore desc) as rank
Here partition groups the table by subjectid column and generates a rank by descending order of obtainedscore.

Output of this query would be as below

Studentid
Subjectid
ObtainedScore
rank
1
1
25
4
2
1
45
1
3
1
27
3
4
1
35
2
5
1
12
6
6
1
24
5
1
2
31
4
2
2
42
2
3
2
43
1
4
2
36
3
5
2
12
5
6
2
10
6


SQL SERVER – Difference Between EXEC and EXECUTE vs EXEC()

SQL SERVER – Difference Between EXEC and EXECUTE vs EXEC() – Use EXEC/EXECUTE for SP always

What is the difference between EXEC and EXECUTE?

They are the same. Both of them executes stored procedure when called as
EXEC sp_help
GO
EXECUTE sp_help
GO

I have seen enough times developer getting confused between EXEC and EXEC(). EXEC command executes stored procedure where as EXEC() function takes dynamic string as input and executes them.
EXEC('EXEC sp_help')
GO

Source:  http://blog.sqlauthority.com/2007/09/13/sql-server-difference-between-exec-and-execute-vs-exec-use-execexecute-for-sp-always/

Using Merge in SQL Server 2008 R2

MERGE INTO [TARGETTABLE] AS Target
    USING [SOURCETABLE] AS SOURCE
    ON
        –unique key filtering condition
        Target.COLUMN1=SOURCE.COLUMN1 AND
        Target.COLUMN2=SOURCE.COLUMN2 AND
        Target.COLUMN3 =SOURCE.COLUMN3 AND
        Target.COLUMN4 =SOURCE.COLUMN4
     WHEN MATCHED THEN
        –update or delete query
        UPDATE SET
            Target.ColumnA=SOURCE.ColumnA,
            Target.ColumnB=SOURCE.ColumnB,
            Target.ColumnC=SOURCE.ColumnC

    WHEN NOT MATCHED THEN
        –INSERT QUERY
        INSERT (COLUMNA, ColumnB, ColumnC, ColumnD)
        VALUES(
                        SOURCE.ColumnA,
                        SOURCE.ColumnB,
                        SOURCE.ColumnC,
                        SOURCE.ColumnD                    )
    ;