SQL SERVER – Fix : Error – sqljdbc_auth.dll Issue – com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed

It seems that Sundays are marked for strange errors. Every Sunday I come across something interesting and different to post. This time I receive error from one of JDBC driver users. He sent me a message that he is not able to connect to SQL Server and he is facing the following error while connecting to SQL Server. He has already checked firewall and TCP/IP settings and still he is facing the errors.

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: “Connection refused: connect. Verify the connection properties. “

I am not a Java person or do not have a lot of experience with the JDBC. I had to spend lots of time .NET so this error was new to me. I decided to get online with him using remote admin and we tried to figure out what is going on with his server. We used internet extensively to figure out the problem. After a while we end up with a Microsoft Document. This document explains how to build connection string and as a good solution to the error mentioned above.

Solution / Workaround / Resolution:

To fix above error the sqljdbc_auth.dll file has to be installed in the following location:

<installation directory>\sqljdbc_<version>\<language>\auth\

You can find the Microsoft JDBC Driver 4.0 for SQL Server from here.

If above information does not resolve your error I suggest you to use refer Microsoft Document as it it contains additional solutions.

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

About these ads

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)

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)