SQL SERVER – Fix – Error 1759 Computed column in table is not allowed to be used in another computed-column definition

This is a very common error with beginning developers when they start working with computed columns.

When a new developer finds a joy of computed column, it is very natural to use the same computed column for other columns. However, a computed column cannot be used in another computed column or it will give an error.

Here is a simple reproduction of the scenario.

-- Create table
CREATE TABLE Table1 (Col1 INT, Col2 AS Col1*10);

Now we will add another computed column Col3 based of Col2 (which is computed column itself).

-- Create Column
ALTER TABLE Table1
ADD Col3 AS Col2*10;

When we execute above statement, it will give us following error.

Msg 1759, Level 16, State 0, Line 2
Computed column ‘Col2′ in table ‘Table1′ is not allowed to be used in another computed-column definition.

This is because we can not use computed columns in another computed column. This kind of referencing is not allowed in SQL Server.

Workaround:

Here is a quick workaround. Our end goal is to create another column which is multiply of Col2 by 10. Now Col2 is Col1 multiplied by 10. That means Col3 is Col1 multiplied by 100. Let us create a new column which is Col1 multiplied by 100.

-- Create Column
ALTER TABLE Table1
ADD Col3 AS Col1*100;

That’s it! We are done.

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

About these ads

SQL SERVER – Error – Resolution – Could not allocate space for object in database because the filegroup is full

Just a day ago on my local box I received following error.

Could not allocate space for object ‘<object_name>’ in database ‘<database_name>’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I was a bit surprised as this was not a production server or even real server. It was my laptop with SQL Server installed in it for testing and presentation purpose. I was a bit shocked and decided to figure out what is wrong with my SQL Server. Just like everyone I searched online and I end up having few solutions, however, after a while I realized then what exactly I was missing. However, I encourage all of you to read various steps I took to reach to a solution to this problem.

Solution / Fix :

Step 1: I checked if my Hard Drive has more space available.

If your Hard Drive is full, please empty out any unnecessary stuff. This may not directly fix your problem, but it is a necessary exercise to do before you do Step 2.

Step 2: Delete unnecessary object from your file group.

If your primary filegroup (in most cases) or filegroup which has generated this error is restricted to fix size, you may get this error. You have two workarounds here.

Step 2a: Delete objects from your filegroup which you do not need. You can move objects to another filegroup if you have multiple filegroups.

Step 2b: Increase the fixed size of your primary filegroup.

Step 3: Check your SQL Server version.

SQL Server Express version 2008 and earlier had the limitation of 4GB maximum Database size. SQL Server Express 2008 R2 and onwards have limitation 10GB maximum database size.

In my case, I was running SQL Server Express version, hence I had faced above error. I moved my database to standard edition and everything went fine.

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

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)

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)