When to use STUFF instead of REPLACE – SQL Server

The STUFF function in SQL Server ‘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.

REPLACE on the other hand replaces all occurrences of a specified string value with another string value.

The STUFF function is useful when you are not sure of the characters to replace, but you do know the position of the character. I saw an excellent implementation of this function overhere by GMastros and Even. Here’s the code with a practical usage of STUFF:

CREATE FUNCTION [dbo].[fn_StripCharacters](    @String NVARCHAR(MAX),    @MatchExpression VARCHAR(255))RETURNS NVARCHAR(MAX)ASBEGIN    SET @MatchExpression =  '%['+@MatchExpression+']%'    WHILE PatIndex(@MatchExpression, @String) > 0        SET @String = STUFF(@String, PatIndex(@MatchExpression,@String), 1, '')    RETURN @StringENDSELECT dbo.fn_StripCharacters('a1$%s#22d&39#f4$', '^a-z')as OnlyAlphabets

Repairing Error 5235 when the DBCC command fails in MS SQL Server 2008 database

In MS SQL Server, the DBCC command is used to perform checks on the databases and repair the errors, if any. Also, there are several repair clauses that can be used with this command to repair different types of corruption cases. However, there are some types of errors that are not repaired even by this command. In such cases, the most optimum SQL recovery would be made possible by using a third-party MDF file recoverysoftware.

For instance, consider that you are using the DBCC command on an MS SQL Server 2008 database. However, the process terminates abruptly and could not get completed. An error message is displayed, in the process, which is:

“[EMERGENCY] DBCC DBCC_COMMAND_DETAILS executed by USER_NAME terminated abnormally due to error state ERROR_STATE. Elapsed time: HOURS hours MINUTES minutes SECONDS seconds.”

Cause:

The aforementioned summary message appears whenever the DBCC command faces some problems during its execution. The reasons for failure at each state are:

• State 0: There is fatal metadata corruption in the database, which is causing the issue.

• State 1: This state was cancelled because of an internal check failure.

• State 2: This state failed as the basic checks for the system tables has failed.

• State 3: This state failed as the database failed to restart after the transaction log rebuilding.

• State 4: An access violation has occurred during this state.

• State 5: An unknown error has occurred that has made the DBCC command to end abruptly.

Resolution:

For each of the aforementioned problems in every state, you can perform the following recovery acts:

• Restore from backup: If the problem has occurred during the State 0, State 2, or State 3, then you should restore the database from its backup. However, before this you must ensure that the backup is updated and clean.

• Contact MS Customer Service and Support (CSS): If the problem has occurred in the State 1 or State 4, then you should contact Microsoft Customer Service and Support (CSS) for all kind of assistance.

• Rerun the DBCC command: If the problem has occurred in the State 5, then you should try to run the command again. However, if the resolution does not work then you should contact CSS in this case as well.

If the problem does not get fixed, then there is a need of using a third-party MS SQL Server recovery software to recover MDF files. These tools use highly advanced and sophisticated algorithms to scan the original database without modifying it.

What to do when the Error 8910 arises in an MS SQL Server 2000 database

An extent in the SQL Server databases is a collection of eight consecutive pages, whose details are stored in an IAM (Index Allocation Map) page. An IAM page stores the details of an object and index with the information of the previous and next objects as well. However, sometimes the IAM definitions get corrupt due to issues like header corruption, virus infections, damaged hardware parts, etc. For such cases, you can adopt corrective measures to rectify the issue. If none of the measures works, then there is a need of using a professional MDF file repair software to perform MDF recovery.

Looking at a practical situation in which you encounter the following error while working on an MS SQL Server 2000 database:

“Page P_ID in database ID DB_ID is allocated to both object ID O_ID1, index ID I_ID1, and object ID O_ID2, index ID I_ID2.”

Cause:

This problem has appeared as the P_ID page is allocated to two different objects, which is incorrect.

Resolution:

This error can be addressed using the following resolving techniques:

• Fixing the hardware-related issues: Usually it has been observed that such errors are caused due to problems in the hardware parts. For this issue, you should perform the following methods to recover SQL database:

  • Checking the error logs for errors: You should check the SQL Server error logs and analyze if some error has occurred because of any hardware-related problem. If yes, then try to fix the problem.

  • Running hardware diagnostics: You should run the hardware diagnostics and check if any error arises while doing so. If yes, then try to correct any such errors.

  • Swapping the hardware components: You should try to swap the hardware parts to see if the problem appears or not. If the problem does not appear after doing so, then the issue is solved.

• Using the database backup: If the hardware parts are perfectly in order, then you should check the status of the database backup. If it is updated, then you can use it by replacing the database with the backup.

• Running DBCC CHECKDB: You can also use the DBCC CHECKDB utility with the suggested repair clause to fix the damaged database.

These methods should bring normalcy to the SQL database failing which you should use a third-party MS SQL Server recovery software. Rich user interface, non-destructive scanning algorithms, and the ability to recover MDF file objects are some of the features of these SQL recovery tools.

Convert VarChar to Currency in SQL Server

To convert a varchar to currency, the recommended practice is to do the formatting in the front end.

However if that option is not available to you, you can use the following T-SQL code to do the formatting. In the code shown below, we are converting varchar into US Dollar currency.

DECLARE @t TABLE(amount decimal(12,2)) INSERT INTO @t SELECT 27450 union all SELECT 2841.2 union all SELECT 8786723.62 union all SELECT 8723SELECT amount,'$'+ CONVERT(varchar(100),CAST(amount as money),1) as converted_amountFROM @t

Note : The value must be converted to money datatype before the formatting

Check if Database Exists In SQL Server – Different ways

A very frequently asked question is how to to check if a Database exists in SQL Server. Here are some different ways.

The following code will be common for all the methods:

DECLARE @db_name varchar(100)SET @db_name='master'

Method 1: Use sys.sysdatabases view

IF EXISTS(SELECT * FROM sys.sysdatabases where name=@db_name)PRINT 'The database exists'elsePRINT 'The database does not exist'

Method 2: Use sysdatabases system table from master database

IF EXISTS(SELECT * FROM master..sysdatabases WHERE name=@db_name)PRINT 'The database exists'elseprint 'The database does not exist'

Method 3: Using of sp_msforeachdb

--If you dont get a message, the database doesn't existDECLARE @sql varchar(1000)SET @sql='if ''?''='''+@db_name+''' print ''the database exists'''EXEC sp_msforeachdb @sql 

Method 4: Using sp_msforeachdb with information_schema.schemata

--If you dont get a message, the database doesn't existDECLARE @sql varchar(1000)SET @sql='if exists(select * from ?.information_schema.schemata wherecatalog_name='''+@db_name+''') print ''the database exists'''EXEC sp_msforeachdb @sql