Wednesday, February 29, 2012

Error 29506 when installing Microsoft SQL Server Management Studio Express on Windows Vista or Windows 7

When you are installing Microsoft SQL Server Management Studio Express on Windows Vista or Windows 7, you may receive this error message:
The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 29506.
This error message relates with permission issue (UAC) on Windows Vista and Windows 7. To solve this problem, you have to re-run the installer again using elevated command prompt by run the command prompt as administrator and execute the installer in there. If you are using 64-bit Windows, you should use the command prompt at C:\Windows\SysWOW64\cmd.exe instead. Otherwise, you can use the command prompt at C:\Windows\System32\cmd.exe.
  1. 32-bit Windows : Run elevated command prompt by right-click on Command Prompt in Start -> All Programs -> Accessories -> Command Prompt and select Run as administrator.
    Open Command Prompt as Administrator
  2. 64-bit Windows: Run the command prompt in C:\Windows\SysWOW64. Right-click on cmd.exe and select Run as administrator.
    Open Command Prompt as Administrator For 64-bit Windows
  3. On User Account Control, click Yes.
    User Account Control
  4. On Command Prompt (both 32-bit and 64-bit Windows), change path to directory where the installer file is and execute the file. By default, the file name for 32-bit version is SQLServer2005_SSMSEE.msi and 64-bit version isSQLServer2005_SSMSEE_x64.msi.
    Execute the installer file
  5. On Setup Wizard, follow the instruction as usual. There won’t be the error message this time.
    Microsoft SQL Server Management Studio Express Setup

Tuesday, February 28, 2012

How to check SQL Server Authentication Mode using T-SQL and SSMS

Many times developers want to put logic into their code or SSIS/DTS package to check the SQL Server authentication mode. How can this be done programmatically?

Before we get started, I want to cover the two ways that SQL Server authenticates logins. The two ways are:

  1. Windows Authentication Mode
  2. Windows and SQL Server Authentication Mode (Mixed Mode)
Windows Authentication Mode
In Windows authentication mode, we can only use Windows logins to connect to SQL Server. Windows Authentication utilizes the Kerberos security protocol .In enterprise environments, Windows login credentials are normally Active Directory domain credentials
Mixed Mode Authentication
In Mixed mode authentication, we can use either Windows authentication or SQL Server authentication to connect to SQL Server.
Windows Authentication Mode is much more secure than Mixed Mode. SQL Server Authentication is provided for backward compatibility only. Whenever possible use Windows Authentication.

Check Using SSMS
In SQL Server Management Studio Object Explorer, right-click on the server name, click Properties and go to Security page to check the SQL Server Authentication. In this case we can see that it is Windows Authentication mode.

Check Using xp_instance_regread
Using xp_instance_regread system procedure, we can read the registry value. SQL Server stores a "1" for Windows Authentication and a "2" for SQL Server authentication (Mixed Mode) in the windows registry. You can execute the below query to check the SQL Server Authentication.
DECLARE @AuthenticationMode INT EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',  N'LoginMode', @AuthenticationMode OUTPUT SELECT CASE @AuthenticationMode   WHEN 1 THEN 'Windows Authentication'  WHEN 2 THEN 'Windows and SQL Server Authentication'  ELSE 'Unknown' END as [Authentication Mode] 

Check Using Server Property
The Server Property function will return "1" for Windows authentication and "0" for Windows/SQL Authentication (Mixed Mode). It would be nice if these values were consistent from what is stored in the registry.
SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')  WHEN 1 THEN 'Windows Authentication'  WHEN 0 THEN 'Windows and SQL Server Authentication'  END as [Authentication Mode] 

Check Using xp_logininfo
Another option is to use xp_loginfo. This returns a value of "Windows NT Authentication" for Windows Authentication and "Mixed" for Windows/SQL Authentication (Mixed Mode).
EXEC master.sys.xp_loginconfig 'login mode' 

Monday, February 27, 2012

SQL Server Cursor Examples

ProblemIn my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing. I know cursors exist but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors? I assume Microsoft created them for a reason so they must have a place where they can be used in an efficient manner.
SolutionIn some circles cursors are never used, in others they are a last resort and in other groups they are used regularly. In each of these camps they have different reasons for their stand on cursor usage. Regardless of your stand on cursors they probably have a place in particular circumstances and not in others. So it boils down to your understanding of the coding technique then your understanding of the problem at hand to make a decision on whether or not cursor based processing is appropriate or not. To get started let's do the following:
  • Look at an example cursor
  • Break down the components of the cursor
  • Provide additional cursor examples
  • Analyze the pros and cons of cursor usage
Example Cursor
Here is an example cursor from tip Simple script to backup all SQL Server databases where backups are issued in a serial manner:
DECLARE @name VARCHAR(50) -- database nameDECLARE @path VARCHAR(256) -- path for backup filesDECLARE @fileName VARCHAR(256) -- filename for backupDECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\'
nameFROM master.dbo.sysdatabasesWHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name
@fileName = @path + @name + '_' + @fileDate + '.BAK'

FETCH NEXT FROM db_cursor INTO @name END

db_cursor DEALLOCATE db_cursor
Cursor Components
Based on the example above, cursors include these components:
  • DECLARE statements - Declare variables used in the code block
  • SET\SELECT statements - Initialize the variables to a specific value
  • DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
    • NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.
  • OPEN statement - Open the cursor to begin data processing
  • FETCH NEXT statements - Assign the specific values from the cursor to the variables
    • NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
  • WHILE statement - Condition to begin and continue data processing
  • BEGIN...END statement - Start and end of the code block
    • NOTE - Based on the data processing multiple BEGIN...END statements can be used
  • Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
  • CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
  • DEALLOCATE statement - Destroys the cursor
Additional Cursor Examples
In the example above backups are issued via a cursor, check out these other tips that leverage cursor based logic:
Cursor Analysis
The analysis below is intended to serve as insight into various scenarios where cursor based logic may or may not be beneficial:
  • Online Transaction Processing (OLTP) - In most OLTP environments, SET based logic makes the most sense for short transactions. Our team has run into a third party application that uses cursors for all of its processing, which has caused issues, but this has been a rare occurrence. Typically, SET based logic is more than feasible and cursors are rarely needed.
  • Reporting - Based on the design of the reports and the underlying design, cursors are typically not needed. However, our team has run into reporting requirements where referential integrity does not exist on the underlying database and it is necessary to use a cursor to correctly calculate the reporting values. We have had the same experience when needing to aggregate data for downstream processes, a cursor based approach was quick to develop and performed in an acceptable manner to meet the need.
  • Serialized processing - If you have a need to complete a process in serialized manner, cursors are a viable option.
  • Administrative tasks - Many administrative tasks need to be executed in a serial manner, which fits nicely into cursor based logic, but other system based objects exist to fulfill the need. In some of those circumstances, cursors are used to complete the process.
  • Large data sets - With large data sets you could run into any one or more of the following:
    • Cursor based logic may not scale to meet the processing needs.
    • With large set based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues. As such, a cursor based approach may meet the need.
    • Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
    • If the data can be processed in a staging SQL Server database the impacts to the production environment are only when the final data is processed. All of the resources on the staging server can be used for the ETL processes then the final data can be imported.
    • SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.
    • Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a checkpoint or marking each row with the cursor. However, with a set based approach that may not be the case until an entire set of data is completed. As such, troubleshooting the row with the problem may be more difficult.
Cursor Alternatives
Below outlines alternatives to cursor based logic which could meet the same needs:
Next Steps
  • When you are faced with a data processing decision determine where you stand with SQL Server cursor usage. They may or may not have a place in your application or operational processes. There are many ways to complete a task, so using a cursor could be a reasonable alternative or not. You be the judge.
  • If you run into issues with another coding technique and need to get something done quickly, using a cursor may be a viable alternative. It may take longer to process the data, but the coding time might be much less. If you have a one time process or nightly processing, this could do the trick.
  • If cursors are shunned in your environment, be sure to select another viable alternative. Just be sure the process will not cause other issues. As an example, if a cursor is used and millions of rows are processed will this potentially flush all of the data from cache and cause further contention? Or with a large data set will the data be paged to disk or written to a temporary directory?
  • As you evaluate a cursor based approach versus other alternatives make a fair comparison of the techniques in terms of time, contention and resources needed. Hopefully these factors will drive you to the proper technique.

Saturday, February 25, 2012

Drop all Database Connections

Run the below script to Kill/Drop all Database Connections. It will run through all users currently connection to the specified database and kill their connection.

Use Master
Declare @dbname sysname
Set @dbname = 'name of database you want to drop connections from'
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Execute ('Kill ' + @spid) 
Select @spid = min(spid) from master.dbo.sysprocesses 
where dbid = db_id(@dbname) and spid > @spid

Friday, February 24, 2012

How to generate Quartile ranking

Execute the following SQL Server T-SQL example script in Management Studio Query Editor to create a Quartile ranking of the sales staff of AdventureWorks Cycles.

-- SQL Quartile - ntile - NTILE(4) - SQL ranking functions
-- SQL inner join - SQL format money
USE AdventureWorks2008; 

SELECT SalesStaff = p.LastName + ', ' + p.FirstName, 
       NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile', 
       YTDSalesAmount = '$' + convert(VARCHAR,s.SalesYTD,1), 
       State = sp.StateProvinceCode 
FROM   Sales.SalesPerson s 
       INNER JOIN Person.Person p 
         ON s.BusinessEntityID = p.BusinessEntityID 
       INNER JOIN Person.Address a 
         ON a.AddressID = p.BusinessEntityID 
       INNER JOIN Person.StateProvince sp 
         ON sp.StateProvinceID = a.StateProvinceID 
       AND SalesYTD > 0; 

SalesStaff     Quartile YTDSalesAmount    City       State
Mitchell, Linda   1     $5,200,475.23     Issaquah    WA 
Pak, Jae          1     $5,015,682.38     Renton      WA 
Blythe, Michael   1     $4,557,045.05     Issaquah    WA 
Carson, Jillian   1     $3,857,163.63     Issaquah    WA 
Varkey, Ranjit    2     $3,827,950.24     Renton      WA 
Campbell, David   2     $3,587,378.43     Renton      WA 
Saraiva, José     2     $3,189,356.25     Renton      WA 
Ito, Shu          3     $3,018,725.49     Renton      WA 
Reiter, Tsvi      3     $2,811,012.72     Issaquah    WA 
Valdez, Rachel    3     $2,241,204.04     Renton      WA 
Mensa-Annan, Tete 4     $1,931,620.18     Renton      WA 
Vargas, Garrett   4     $1,764,938.99     Issaquah    WA 
Tsoflias, Lynn    4     $1,758,385.93     Renton      WA


Thursday, February 23, 2012

When were the statistics for a table last updated?

The below query returns the statistics for a table last updated. It makes use of the sys.indexes and sys.tables catalog views, along with the STATS_DATE() function, to retrieve the date that each index was last updated for every user table in the current database.
SELECT AS Table_Name, AS Index_Name,
i.type_desc AS Index_Type,
dex_id) AS Date_Updated --,sp.rows --if you want t
STATS_DATE(i.object_id,i.i no know how many rows unrem this FROM
ject_id = i.object_id JOIN sys.partitions sp ON t.object_id = sp.
sys.indexes i JOIN sys.tables t ON t.o bobject_id and i.index_id = sp.index_id --new WHERE i.type > 0 and --if you want to see heap rem this
sp.rows > 0 ORDER BY ASC, i.type_desc ASC, ASC
This little query can be useful in troubleshooting and diagnosing performance-related issues. Sometimes, it's as simple as outdated statistics.

Wednesday, February 22, 2012

Change SQL Server Password

There are many method through which you can change SQL Server Password.

Method 1
You can use Query windows of Management Studio to change password. 
Open SQL Server Management Studio, Open New Query Window and type the below command and press Execute:

Alter Login [sa] With Default_Database=[Master]
Use [Master]
ALTER Login [sa] With Password = N'Change Password' Must_Change

After executing this 'Change Password' is set for your sa account.

Method 2
You can use Command Prompt to change Password.

Open Command Prompt (Start - Run - cmd) and type the below Command:

Osql –S <Server Name> –E 

  1. EXEC sp_password NULL, ’<Password>’, ’sa’
  2. GO
<Server Name> is the name of the SQL Server and <Password> is the password you want to set.

Method 3
You can also change Password by Windows Authentication. Open SQL Server Management Studio. Login to Management Studio with Windows Authentication. Expand Server - Security - Logins. Now double click on sa login. Here type a new Password, Confirm Password, and click on Finish. Restart SQL Server. Now you can login to SQL Server with new sa account password.

Tuesday, February 21, 2012

The backup set holds a backup of a database other than the existing ‘dbName’ database

Sometime when you try to restore the backup file on an existing database and receive the following error message:
Restore failed for Server ‘SQL Server name‘. (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘dbName‘ database. (Microsoft.SqlServer.Smo)
Above error message indicates that you are going to restore a database on another database which are not the same database. For example, you have backup Northwind database and try to restore the backup to AdventureWorks database, the error will occur.
You can use the overwrite the existing database option while you’re restoring to solve the problem.
On Restore Database, select Options tab on the left menu. Then, check Overwrite the existing database on Restore options.
Now Restore the database. and problem is solved.
You can also delete the database and again restore that.

Monday, February 20, 2012

SQL 2008 shrink log file size with no_log alternative

Microsoft SQL Server 2008 does not allow you to truncate the transaction log using the no_log syntax.

Use the below code to shrink the log file for your database


SQL 2008 returns a table of shrinkfile related results plus the following confirmation:

(1 row(s) affected)
DBCC execution completed. If DBCC printed error
messages, contact your system administrator.

Saturday, February 18, 2012

Configuring SQL Server to listen on alternative Port

If you need SQL Server to listen on some alternative port or to share additional port for remote access, please follow these directions:

1) Login to your server using Terminal Services Client
2) Go to Start -> Programs -> SQL Server -> Server Network Utility
3) Under Enabled Protocols, select TCP/IP and go to properties
4) In the default port field, enter “1433, 2512” without the quotes. This will force SQL Server to listen on both port 1433 and 2512. 
If you would like to have SQL listen on a different port, simply enter additional port numbers in a comma delimited format.
5) Now restart the SQL Server service so that it can rebind it’s network connections.

Friday, February 17, 2012

String comparision

There are various method through which we can compare string.

SELECT AccountName FROM AccountMaster WHERE AccountName='REKHA'

SELECT AccountName from AccountMaster WHERE AccountName LIKE '%REKHA%'

SELECT AccountName from AccountMaster WHERE AccountName LIKE '%_REKHA_%'

SELECT AccountName from AccountMaster WHERE AccountName LIKE '%[OO]_%'
SELECT * FROM AccountMaster

SELECT * FROM AccountMaster
SELECT * FROM AccountMaster WHERE AccountName IN('SANJAY','REKHA', 'NIKITA')

SELECT AccountName FROM AccountMaster WHERE AccountName LIKE 'S%[NJ]%_Y'

This query searches BETWEEN Start TO End withing a string.

SELECT AccountName FROM AccountMaster WHERE AccountName LIKE 'S____Y'

This one will validate the format of a string.  As shown it will return "Valid".  Change the "8" to an "A" and you get "Invalid":
Declare @string VarChar(50) = '741ZVM543'
SELECT CASE WHEN @string LIKE '[0-9][0-9][A-Z][A-Z][0-9][0-9][0-9]' THEN 'Valid' ELSE 'Invalid' END

This one will find the first upper-case character in a string (it returns "7"):
Declare @string VarChar(50) = 'white Chem'
SELECT PatIndex('%[A-Z]%',@string collate Latin1_General_BIN)

Thursday, February 16, 2012

Finding the details of Backup File without restoring it

Sometime we may have to find details of a SQL backup file without restoring it. Some of the common requirements are:
  1. When the backup Started and Finished.
  2. Is the backup file is good.
  3. Who had taken this backup.
  4.  What is the type of backup.
  5. Is backup Compressed
  6. Database Creation Date
  7. SQL Server Version
  8. From which system backup was taken
  9. Database Collation
  10. Recovery Model
We can get all the information wsing below query:



Column name
Description for SQL Server backup sets
Backup set name.
Backup set description.
Backup type:
1 = Database
2 = Transaction log
4 = File
5 = Differential database
6 = Differential file
7 = Partial
8 = Differential partial
Expiration date for the backup set.
Whether the backup set is compressed using software-based compression:
0 = No
1 = Yes
User name that performed the backup operation.
Name of the server that wrote the backup set.
Name of the database that was backed up.
Version of the database from which the backup was created.
Size of the backup, in bytes.
Date and time the database was created.
Date and time that the backup operation began.
Date and time that the backup operation finished.
Compatibility level setting of the database from which the backup was created.
1 = Snapshot backup.
1 = Backup contains backup checksums.
1 = Database was read-only when backed up.
1 = Database was single-user when backed up.
1 = Database was damaged when backed up, but the backup operation was requested to continue despite errors.
1 = Backup contains backup checksums.
1 = Backup taken with NORECOVERY; the database was taken offline by backup.
1 = A copy-only backup.
A copy-only backup does not impact the overall backup and restore procedures for the database
Recovery model for the Database, one of: