MYSQL – Could not Drop Object [Content] (‘Cannot delete or update a parent row: a foreign key constraint fails’, 1217) DROP DATABASE DatabaseName

If you are using MySQL following, you must have faced following error when you want to drop a table or database. This error happens when we try to drop a table or database which has a foreign key constraint associated with another table, schema or database.

Could not Drop Object [Content] (‘Cannot delete or update a parent row: a foreign key constraint fails’, 1217) DROP DATABASE DatabaseName

Here is a simple workaround to this problem. Disable the keys right before you drop a table or a database. Remember to practice this on the database which you are confident to drop it.

SET FOREIGN_KEY_CHECKS=0;
DROP DATABASE humanresources;
DROP TABLE MyTable;
SET FOREIGN_KEY_CHECKS=1;

Let me know if you are using any method for this purpose.

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

About these ads

SQL SERVER – Fix – Login failed for user ‘username’. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

Earlier this week, I was on vacation and did not check my emails for three days and when I returned, I found six different emails from 4 different people asking for the same problem.

When they attempted to login using their application to SQL Server they faced following error:

Login failed for user 'username'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

This is quite a common error and can happen when incorrect username is communicated to SQL Server. The fix is to provide correct username. This can also happen if you are attempting to login to SQL Server with SQL Username but it is only configured to connect to Windows Login. You can quickly fix this error by following steps listed below.

Solution/Workaround/Fix:

Open SQL Server Management Studio and right click on Server Node and select Properties

Now go to Security Tab and go to Server Authentical Area. Select SQL Server and Windows Authentication Mode.

You should be able to login with your SQL Server username. If you are not able to login with your SQL Server username after following above steps, it is quite possible your username is incorrect, please check again.

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

SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database – SSMS

This is a follow up of the blog post I have posted on error 3154 few years ago. I have received quite a few emails that how we can fix the same error with the help of SQL Server Management Studio. In this blog post, I will explain how we can do the same with SSMS. Here is the error code:

Error 3154: The backup set holds a backup of a database other than the existing database.

You can fix that with the help of T-SQL with the following command. 

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\BackupAdventureworks.bak'
WITH REPLACE

If you want to do the same with SSMS, you can follow the steps here:

Step 1: Create a database with your preferred name. (In our case AdventureWorks)

Step 2: Write click on the database and click on Tasks >> Restore >> Database…

Step 3: On the restore screen go to third selection of Options. Now select the checkbox “Overwrite the existing database (WITH REPLACE)”

Step 4: Click OK. It should successfully restore the database.

Note: When you restore a database WITH REPLACE it will overwrite the old database.

Relevant Blog Post:

FIX : Error 3154: The backup set holds a backup of a database other than the existing database

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

SQL SERVER – Fix : Error: 217 Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

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

Click to Download Scripts

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

SQL SERVER – Fix – Error: 1060 The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer

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)

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)

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)