SQL SERVER – Fix: Error: 1505 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name and the index name

Here is another example where the error messages are very clear but often developers get confused with the message. I think the reason for the confusion is the word “Key” used in the error message. After I explained this to a developer who sent me the error he realize that it is about how we all interpret a same statement.

Following code will generate the error 1505.

-- Create Table
CREATE TABLE test (ID INT NOT NULL,
Col1 INT, Col2 VARCHAR(100))
GO
-- Populate Table
INSERT INTO test (ID, Col1, Col2)
SELECT 1, 1, 'First'
UNION ALL
SELECT 1, 2, 'Second' -- Duplicate ID col
UNION ALL
SELECT 3, 3, 'Third'
UNION ALL
SELECT 4, 4, 'Fourth'
GO

After creating a table, I am inserting same a key in the first column multiple times.

-- Now create PK on ID Col
ALTER TABLE test
ADD CONSTRAINT [PK_test]
PRIMARY KEY CLUSTERED
([ID] ASC)
GO

Now when I attempt to create a Primary Key on the column it gives us following error.

Msg 1505, Level 16, State 1, Line 2
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.test’ and the index name ‘PK_test’. The duplicate key value is (1).
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
The statement has been terminated.

Fix / Workaround / Solution:

In SQL Server Primary Key can’t have duplicate records as well can’t have a column which is nullable. In our case, you can notice that we are creating a primary key on the column ID which contains duplicate values of 1. The only way to create primary key on this column is to delete the duplicate row which exists. If your business logic does not allow to delete the duplicate row, this means that your column is not a good candidate for the Primary Key and you will have to either select another column or use a composite Primary Key (where you use multiple columns).

On Separate note, here is the blog post with video which explains how you can delete the duplicate row from the table: Select and Delete Duplicate Records – SQL in Sixty Seconds #036 – Video.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Fix Error: 8111 – Cannot define PRIMARY KEY constraint on nullable column in table – Error: 1750 – Could not create constraint. See previous errors

A very common error new developers receive when they begin with SQL Server and start playing with the keys. Let us first run following code which will generate an error 8111.

-- Create Table
CREATE TABLE test (ID INT, Col1 INT,
Col2 VARCHAR(100))
GO
-- Now create PK on ID Col
ALTER TABLE test
ADD  CONSTRAINT [PK_test]
PRIMARY KEY CLUSTERED
([ID] ASC)
GO

When you run above code it will give following error:

Msg 8111, Level 16, State 1, Line 2
Cannot define PRIMARY KEY constraint on nullable column in table ‘test’.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

Now let us see how we can fix the error.

Fix / Workaround / Solution:

The error message is very clear and explains the root cause of the error. When a user creates a primary key on any column, the column should not be nullable.

There are two ways to fix this error.

Method 1: Alter Column as NOT NULL (preferred)

This is the method is very popular as most of the time design of the database is already done so altering the column to not null is an easy thing to do.

-- Alter Column to not null
ALTER TABLE test
ALTER COLUMN ID INT NOT NULL
GO

Method 2: Create a table with the Column as NOT NULL

This method can be only useful, if you are aware that Primary Key needs to be NOT NULL. When you design your database or do database modeling at that time, you can keep this in mind and create a table with the column as not null. Additionally, you can also create Primary Key constraint when you create original table.

-- Create Table
CREATE TABLE test (ID INT NOT NULL,
Col1 INT, Col2 VARCHAR(100)
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED)
GO

You can read about more about creating a Primary Key over here: SQL SERVER – Create Primary Key with Specific Name when Creating Table.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix: Error: The conversion returned status value 2 and status text “The value could not be converted because of a potential loss of data.”. (SQL Server Import and Export Wizard)

Here is the question received from user – the email was long and had multiple request from reader to resolve this error.

Scenario:

The user was trying to import data from Excel to tables in SQL Server Database. Every time he attempted to import the data he faced following error. He tried using SSIS package as well using the Import Export Wizard (which creates an SSIS package under the hood as well) but he he kept on facing following error. He could not figure out the reason behind the error. I have modified the error to make it readable.

Error:

- Executing (Error)
Messages
Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column “Col1″ (18) to column “Col1″ (51). The conversion returned status value 2 and status text “The value could not be converted because of a potential loss of data.”.
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. [Error Detail]. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Data Conversion 0 – 0″ (39) failed with error code 0xC0209029 while processing input “Data Conversion Input” (40). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Solution:

Well, there can be many reasons for this error to show up but the major reason is that data type mismatch. I have observed that following two are the major reason for the error.

1) Null’s are not properly handled either in the destination database or during SSIS package creation. It is quite possible that the source contains a null database but the destination is not accepting the NULL data leading to build generate above error.

2) This is the most common issue I have seen in the industry where data types between source and destination does not match. For example, source column has varchar data and destination column have an int data type. This can easily generate above error. There are certain kind of datatypes which will automatically convert to another data type and will not generate the error but there are for incompatible datatypes which will generate above error.

The best practices is to make sure that you match your data types and their properties of the source and destination. If the datatypes of the source and destination are same, there is very less chance of occurrence of the above error.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix: Error : 402 The data types ntext and varchar are incompatible in the equal to operator

Some errors are very simple to understand but the solution of the same is not easy to figure out. Here is one of the similar errors where it clearly suggests where the problem is but does not tell what is the solution. Additionally, there are multiple solutions so developers often get confused with which one is correct and which one is not correct.

Let us first recreate scenario and understand where the problem is. Let us run following

USE Tempdb
GO
CREATE TABLE TestTable (ID INT, MyText NTEXT)
GO
SELECT ID, MyText
FROM TestTable
WHERE MyText = 'AnyText'
GO
DROP TABLE TestTable
GO

When you run above script it will give you following error.

Msg 402, Level 16, State 1, Line 1
The data types ntext and varchar are incompatible in the equal to operator.

One of the questions I often receive is that voucher is for sure compatible to equal to operator, then why does this error show up. Well, the answer is much simpler I think we have not understood the error message properly. Please see the image below. The next and varchar are not compatible when compared with each other using equal sign.

http://www.pinaldave.com/bimg/402error.jpg

Now let us change the data type on the right side of the string to nvarchar from varchar. To do that we will put N’ before the string.

USE Tempdb
GO
CREATE TABLE TestTable (ID INT, MyText NTEXT)
GO
SELECT ID, MyText
FROM TestTable
WHERE MyText = N'AnyText'
GO
DROP TABLE TestTable
GO

When you run above script it will give following error.

Msg 402, Level 16, State 1, Line 1
The data types ntext and nvarchar are incompatible in the equal to operator.

You can see that error message also suggests that now we are comparing next to nvarchar. Now as we have understood the error properly, let us see various solutions to the above problem.

Solution 1: Convert the data types to match with each other using CONVERT function.

Change the datatype of the MyText to nvarchar.

SELECT ID, MyText
FROM TestTable
WHERE CONVERT(NVARCHAR(MAX), MyText) = N'AnyText'
GO

Solution 2: Convert the data type of columns from NTEXT to NVARCHAR(MAX) (TEXT to VARCHAR(MAX)

ALTER TABLE TestTable
ALTER COLUMN MyText NVARCHAR(MAX)
GO

Now you can run the original query again and it will work fine.

Solution 3: Using LIKE command instead of Equal to command.

SELECT ID, MyText
FROM TestTable
WHERE MyText LIKE 'AnyText'
GO

Well, any of the three of the solutions will work. Here is my suggestion if you can change the column data type from ntext or text to nvarchar or varchar, you should follow that path as text and ntext datatypes are marked as deprecated. All developers any way to change the deprecated data types in future, it will be a good idea to change them right early.

If due to any reason you can not convert the original column use Solution 1 for temporary fix. Solution 3 is the not the best solution and use it as a last option. Did I miss any other method? If yes, please let me know and I will add the solution to original blog post with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix Error: Microsoft OLE DB Provider for SQL Server error ‘80040e07′ or Microsoft SQL Native Client error ‘80040e07′

I quite often receive questions where users are looking for solution to following error:

Microsoft OLE DB Provider for SQL Server error ‘80040e07′
Syntax error converting datetime from character string.

OR

Microsoft SQL Native Client error ‘80040e07′
Syntax error converting datetime from character string.

If you have ever faced above error – I have a very simple solution for you.

http://www.pinaldave.com/bimg/error-logo.jpgThe solution is being very check date which is inserted in the datetime column. This error often comes up when application or user is attempting to enter an incorrect date into the datetime field. Here is one of the examples – one of the reader was using classing ASP Application with OLE DB provider for SQL Server. When he tried to insert following script he faced above mentioned error.

INSERT INTO TestTable (ID, MyDate)
VALUES (1, '01-Septeber-2013')

The reason for the error was simple as he had misspelled September word. Upon correction of the word, he was able to successfully insert the value and error was not there. Incorrect values or the typo’s are not the only reason for this error. There can be issues with cast or convert as well. If you try to attempt following code using SQL Native Client or in your application you will also get similar errors.

SELECT CONVERT (datetime, '01-Septeber-2013', 112)

The reason here is very simple, any conversion attempt or any other kind of operation on incorrect date/time string can lead to the above error. If you not using embeded dynamic code in your application language but using attempting similar operation on incorrect datetime string you will get following error.

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

Remember: Check your values of the string when you are attempting to convert them to string – either there can be incorrect values or they may be incorrectly formatted.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix Visual Studio Error : Connections to SQL Server files (.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL

In one of the virtual environment while I was trying to add SQL Server Database (.mdf) file to asp.net project I encountered following error:

Connections to SQL Server files (.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: 

For a long time I am using SQL Server 2012 but this error was a bit interesting to me. I realize that there should not be any need of the SQL Server 2005 installation. I quickly figured out that I can remove this error if I do as mentioned below:

  • Open Microsoft Visual Studio
  • Select Tools >> Options >> Database Tools >> Data Connections
  • Enter the name of an installed instance in “SQL Server Instance Name” field.
  • Click OK

If you do not know the instance name, you can follow either of the options.

1) Use the command line sqlcmd utility

2) Using SQL Server Management Studio

Is there any other way to resolve this error?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Guest Post by Sandip Pani – SQL Server Statistics Name and Index Creation

Sometimes something very small or a common error which we observe in daily life teaches us new things. SQL Server Expert Sandip Pani (winner of Joes 2 Pros Contests) has come across similar experience. Sandip has written a guest post on an error he faced in his daily work. Sandip is working for QSI Healthcare as an Associate Technical Specialist and have more than 5 years of total experience. He blogs at SQLcommitted.com and contribute in various forums. His social media hands are LinkedIn, Facebook and Twitter.


Once I faced following error when I was working on performance tuning project and attempt to create an Index.

Mug 1913, Level 16, State 1, Line 1
The operation failed because an index or statistics with name ‘Ix_Table1_1′ already exists on table ‘Table1′.

The immediate reaction to the error was that I might have created that index earlier and when I researched it further I found the same as the index was indeed created two times. This totally makes sense. This can happen due to many reasons for example if the user is careless and executes the same code two times as well, when he attempts to create index without checking if there was index already on the object. However when I paid attention to the details of the error, I realize that error message also talks about statistics along with the index. I got curious if the same would happen if I attempt to create indexes with the same name as statistics already created. There are a few other questions also prompted in my mind. I decided to do a small demonstration of the subject and build following demonstration script.

The goal of my experiment is to find out the relation between statistics and the index. Statistics is one of the important input parameter for the optimizer during query optimization process. If the query is nontrivial then only optimizer uses statistics to perform a cost based optimization to select a plan. For accuracy and further learning I suggest to read MSDN.

Now let’s find out the relationship between index and statistics. We will do the experiment in two parts. i) Creating Index ii) Creating Statistics

We will be using the following T-SQL script for our example.

IF (OBJECT_ID('Table1') IS NOT NULL)
DROP TABLE Table1
GO
CREATE TABLE Table1
(Col1 INT NOT NULL,
Col2 VARCHAR(20) NOT NULL)
GO

We will be using following two queries to check if there are any index or statistics on our sample table Table1.

-- Details of Index
SELECT OBJECT_NAME(OBJECT_ID) AS TableName, Name AS IndexName, type_desc
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'table1'
GO
-- Details of Statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, Name AS StatisticsName
FROM sys.stats
WHERE OBJECT_NAME(OBJECT_ID) = 'table1'
GO

When I ran above two scripts on the table right after it was created it did not give us any result which was expected.

Now let us begin our test.

1) Create an index on the table

Create following index on the table.

CREATE NONCLUSTERED INDEX Ix_Table1_1 ON Table1(Col1)
GO

Now let us use above two scripts and see their results.

We can see that when we created index at the same time it created statistics also with the same name.

Before continuing to next set of demo – drop the table using following script and re-create the table using a script provided at the beginning of the table.

DROP TABLE table1
GO

2) Create a statistic on the table

Create following statistics on the table.

CREATE STATISTICS Ix_table1_1 ON Table1 (Col1)
GO

Now let us use above two scripts and see their results.

We can see that when we created statistics Index is not created. The behavior of this experiment is different from the earlier experiment.

Clean up the table setup using the following script:

DROP TABLE table1
GO

Above two experiments teach us very valuable lesson that when we create indexes, SQL Server generates the index and statistics (with the same name as the index name) together. Now due to the reason if we have already had statistics with the same name but not the index, it is quite possible that we will face the error to create the index even though there is no index with the same name.

A Quick Check

To validate that if we create statistics first and then index after that with the same name, it will throw an error let us run following script in SSMS. Make sure to drop the table and clean up our sample table at the end of the experiment.

-- Create sample table
CREATE TABLE TestTable
(Col1 INT NOT NULL,
Col2 VARCHAR(20) NOT NULL)
GO
-- Create Statistics
CREATE STATISTICS IX_TestTable_1 ON TestTable (Col1)
GO
-- Create Index
CREATE NONCLUSTERED INDEX IX_TestTable_1 ON TestTable(Col1)
GO
-- Check error
/*Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics with name 'IX_TestTable_1' already exists on table 'TestTable'.
*/
-- Clean up
DROP TABLE TestTable
GO

While creating index it will throw the following error as statistics with the same name is already created.

In simple words – when we create index the name of the index should be different from any of the existing indexes and statistics.

Click to Download Scripts

Reference: Pinal Dave (http://blog.SQLAuthority.com)