Error: ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column ” cannot be added to non-empty table ” because it does not satisfy these conditions.
Description: Try below statement on an existing table which contains records.
ALTER TABLE table1
ADD col1 BIT NOT NULL
Above error will be shown, because SQL statement not saying what value should be added or updated for existing records. When we say NOT NULL, we must provide a value for the existing fields.
Solution: Add a DEFAULT value or “Allow Null”.
ALTER TABLE table1
ADD col1 BIT DEFAULT 0 NOT NULL
ALTER TABLE table1
ADD col1 BIT NULL
Error: Specified cast is not valid. (SqlManagerUI)
Description: While trying to restore a database using SQL Server Management Studio, this error dialogue box pops up. When we go for detailed error, there is nothing much helps us to identify the root cause of the problem. Before restoring we should follow below steps.
1. Make sure the source and target database server version is same or not.
2. Check whether the backup database copy is not corrupted.
RESTORE VERIFYONLY FROM DISK = N’C:\Backup\12345test.bak’
3. If using FTP transfer softwares to move the backup files, make sure to set the FTP transfer mode to BINARY from ASCII.
Error: Cannot show requested dialog (SqlMgmt). A required operation could not be completed. You must be a member of the sysadmin role to perform this operation. (SqlManagerUI)
Description: Again another connectivity issue where the user name using having no permission or rights to complete the operation. Or, we can say the user is a normal or standard user.
Solution: Select and right-click Logins from Security node, from Properties window select Server Roles and select sysadmin check box.
Error: System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ” database. (Microsoft.SqlServer.Smo)
Description: While creating or restoring a database from an old backup of another server, we get this error in SQL Server Management Studio. That means there is difference in target and source database.
Solution: Couple or more ways to restore the database.
- Delete the database, add a new database with same name as source database name. Then try restoring it.
- Using command RESTORE DATABASE followed by WITH REPLACE.
RESTORE DATABASE MyDB FROM DISK = 'E:\MyDB.bak' WITH REPLACE
3. Right click on Database, choose Restore then Database. Go to Options tab and choose Overwrite the existing database (WITH REPLACE).
Error: User does not have permission to perform this action.
Description: When trying to run Transact-SQL statement like DROP, GRANT, EXEC etc for which the logged in user do not have the permission to perform or execute this statement, the exception throws up. Sometimes while trying to access database system objects, the exception throws up.
Solution: First check whether the log in using to connect and perform database activities exists or not. Open SQL Server Management studio, under Security, Logins node add the user and map the database also. Or, if the user already exists then GRANT required permission’s.
Error: Column ” does not belong to table ”.
Description: As error says that the column name trying to use does not exists or not part of the table.
Solution: Check the column exists in table or not. Check the column name used is correct.
Error: Incorrect syntax near the keyword ‘top’
Description: Syntax error.
Incorrect syntax near the keyword ‘top’
select col top 1 * from table1
Incorrect syntax near the keyword ‘from’
select top 1 from table1
Solution: Correct the syntax.
select top 1 * from table1