Recyle Error Log

May 23, 2011 Comments off

If you not often restart your server then your SQL Server error log files get filled up and it will take more time to load on your SSMS and sometime SSMS tale long time to respond

Let see how we can recycle the Error log. SQL Server Error log will be automatically recycle when you recycle the SQL Server or the Server .however we will not be recycle or reboot the server very often.

SQL Server has DBCC Error log command to automatically Recycle the Error Log. however we cannot run this manually so, we need to create a Automatic job to Recycle the SQL Server Error Log.

To Configure the Error Log we need to Run the following command on SQL Server 2005 or Later.

xp_instance_regwriteN’HKEY_LOCAL_MACHINE’,N’Software\Microsoft\MSSQLServer\MSSQLServer’,N’NumErrorLogs’, REG_DWORD, 6

We can limit the number of error log file by specifying number from 6 – 99 so we can have maximum of 99 files.

Categories: SQL General

Importing Excel to SQL: The value violated the integrity constraints

December 21, 2010 Comments off

Another interesting thing happened today when i tried to import the  data from Excel to SQL Database i got an error that the value violated the integrity constraints. The error message as like below.

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0×80004005.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80004005 Description: “Unspecified error”.
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task: There was an error with input column “Showtext” (52) on input “Destination Input” (42). The column status returned was: “The value violated the integrity constraints for the column.”.
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The “input “Destination Input” (42)” failed because error code 0xC020907D occurred, and the error row disposition on “input “Destination Input” (42)” specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Problem Cause

When you try to import the excel to sql using ssms ,ssms create the automatic sql script to create a table. in which few of the data types (bit,etc) will not allow nullable value into the column that is the reason it is causing the above error.


In my case from my excel data there are many null values or the empty values are there so i need to change the column to allow null values. after that i try to import the data it worked fine for me.

Categories: SQL Programming

Saving Changes is not permitted,when Modified the table using SSMS

December 20, 2010 Comments off

When i tried to modified the table structure using SSMS in SQL 2008 SSMS is not allowing me to modify the table i got the warring message that,Saving Changes is not permitted.


This is the good thing SSMS  has the facility to prevent the changes on the table design. because using query analyzer in all the version if you want to modify the table structure  it will drop and recreate the table, if you have very huge table and you want to modify the table structure using the GUI it will take huge time to recreate the table. this will create a production outage too.

How to resolve this . Go to > Tools >Options>table and database designer > uncheck the prevent and saving table recreation.

The following  screenshot will give the clear idea.

Note :  By Default this option will be enabled so user will not be able to modify the table .Please do not uncheck this option on production environment  use script to modify them.

Categories: SQL Programming

Dedicated administrator connections are not supported. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

December 9, 2010 Comments off

When trying to open DAC using SSMS you will get Dedicated administrator connections are not supported. (Microsoft.SqlServer.Management.SqlStudio.Explorer). if you get this error message then you are trying to open DAC connection using the Object explorer. Only one DAC connection can be opened so we will not be able to use object explorer to open DAC. 

There are two ways to open DAC connection

1.SSMS>New>Database Engine Query > ADMIN:Servername (on the server name)

2.SQLCMD -S Servername -E -A

Categories: SQL General

Rename SQL Server HOST Name on Stand-Alone Server

November 13, 2010 4 comments

When we change the SQL Server host name, we don’t need to reinstall the SQL Server to take the new name to affect.

once you change the SQL Server HOST name on Stand-Alone machine Replication,Mirroring,Wont work so,before rename the SQL Server Host Name need to considered the following.

  1.   If your server is involved replication,Logshipping then, you need to re-configure the replication ,log shipping otherwise it wont work
  2.  If you have mirroring configured for 2005 and later version you need to turn off mirroring and need to re-establish the mirroring
  3.  If the server is cluster then need to follow the different steps
  4.  If you have reporting service configured on the server after the rename the service wont be available for more information please Click Microsoft Guide

Steps to be followed.

  1. SELECT  @@SERVERNAME  – Give you the Existing server Name present in the metadata. it will be the old server name.

Remove the Old Server Name from the Metadata.

2.  Sp_dropserver ‘Old_Server_Name’

Add the renamed server name

3. Sp_Addserver ‘New_server_Name’ , ‘Local’

if you don’t Specify the ‘Local’ then You will Get @@servername as NULL.

4. Restart the SQL Services.

5. SELECT @@SERVERNAME    – Will give you the modified Server Name in the Step No.3

 Note : This Solution is Only for the Stand-Alone Machine

Categories: SQL General

What is DeadLock?

September 30, 2010 1 comment

When the two user processes have locks on separate objects and each user wants a lock on the other’s process then deadlock will occur.

For EX: User 1 has Lock on object “History” whereas User 2 has Lock on “HistoryDetails” and User 2 wants Lock on “History” and user 1 wants Lock on “HistoryDetails”.   such case SQL Server will end up with deadlock by choosing the user who will be a deadlock victim.

When the Lock Manager detected Deadlock by using deadlock detection algorithm the following things will happen by SQL Server once the Deadlock detected.
                1. Lock manager will choose one of the user process as a deadlock victim 
                2.Lock Manager will initiate 1205 error message to the client
                3.Lock Manager will kill the Victim Process and free up the resources and allow the other  process to continue.

Categories: SQL General

Get every new post delivered to your Inbox.

%d bloggers like this: