Just a day before I was working on some query and faced this error. As soon as I receive this, I realized that what I had done wrong and what I needed to fix. The suggestion demonstrated in the error message is to the point and accurate.

Here is the complete error I received when I had used ISNULL function to int value and in case of the null value, I wanted to display current date time.

Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

Before we fix the error, let us recreate this error.

-- Create table
CREATE TABLE #TestTable (ItemID INT, IsAvailable INT)
-- Insert into table
INSERT INTO #TestTable (ItemID, IsAvailable)
SELECT 1, NULL
UNION ALL
SELECT 2, 1
UNION ALL
SELECT 3, 1
UNION ALL
SELECT 4, NULL
UNION ALL
SELECT 5, 1
GO

Now let us run following T-SQL script which should throw an error for us.

-- Following will throw error
SELECT ItemID, ISNULL(IsAvailable, GETDATE()) AvailableNow
FROM #TestTable
GO

Above T-SQL script will give following error:

Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

Now we can overcome above error by using following three workarounds.

Solution / Workaround:

-- Fix 1
SELECT ItemID, ISNULL(CONVERT(DATETIME, IsAvailable), GETDATE()) AvailableNow
FROM #TestTable
GO
-- Fix 2
SELECT ItemID, COALESCE(IsAvailable, GETDATE()) AvailableNow
FROM #TestTable
GO
-- Fix 3
SELECT ItemID, CASE WHEN IsAvailable IS NULL THEN GETDATE() ELSE IsAvailable END AvailableNow
FROM #TestTable
GO

Now above three examples will fix the problem. However, there is one small problem here. When I use either ISNULL or COALSECE the value IsAvailable which is INT now is converted to the datetime and display value of year 1900.

Now you can see even though we found workaround it has small issue – this is better than the error which we were getting before, but still not accurate.

Here is the one more solution which will overcome the incorrect data issue described above.

-- Fix 4
SELECT ItemID, CASE WHEN IsAvailable IS NULL THEN CONVERT(VARCHAR(26),GETDATE()) ELSE CONVERT(VARCHAR(26), IsAvailable) END AvailableNow
FROM #TestTable
GO

Here is the screenshot of the resultset.

Well, let me know if there is any better solution and I will gladly include it here with due credit.

Run following command to clean up.

-- clean up
DROP TABLE #TestTable
GO

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

Here is the interesting error one of my friend faced and it took me 15 minutes go over various code to identify the real issue. When we see a simple example in demonstration or a blog, we often think that SQL is a much simpler language and it looks easy. However, in reality it is often more complex than we think. I was dealing with the Stored Procedure which is which had 10000 lines of the code and there were many different views and functions used in it. The worst part was my friend can’t share his original code as it is owned by his company and I am just trying to help him externally. Well, here is the error he was facing.

Msg 1060, Level 15, State 1, Line 3
The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer.

We worked together to identify the code which was generating the error. The code was as follows.

SET @param = 100
 SELECT TOP (@param) *
 FROM Sales.SalesOrderDetail sod

Well, looking at the code everything looked normal. However, the error was still happening. It is common practice for developers to declare all the variables on the top of the page and later use them in the code. After few more attempts my friend was able to find the code where the variable @param was delcared. Now here is the simplified complete code.

DECLARE @param FLOAT
 SET @param = 100
 SELECT TOP (@param) *
 FROM Sales.SalesOrderDetail sod

In this case the @param was declared as a float and that was the reason why the error was showing up. As error suggests the param passed in the TOP clause should be only Integer. In this case even though the value assigned to @param is INT the data type of the @param is FLOAT and that is why it is not accepted and it throws an error.

Here is the code which will fix the error.

DECLARE @param INT
SET @param = 100
SELECT TOP (@param) *
FROM Sales.SalesOrderDetail sod

After changing the datatype of the @param the error was resolved.

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

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
)

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
)

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.

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
)