Wednesday, June 13, 2018

Unable to ALTER SQL Server database from single user to multi user

When i try to alter database from single user to multi user i got facing below error

Transaction (Process ID 233) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205)

Solution :



USE [MyDatabase]
GO
SET DEADLOCK_PRIORITY LOW
GO
ALTER DATABASE MyDatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Drop DEFAULT Constraint in SQL Server


Syntax to drop existing default constraint from a table

USE [DatabaseName]

GO

ALTER TABLE [Table_Name]
DROP CONSTRAINT [DefaultConstraintName]


Below is the example to drop default constrain from orderqty table


USE [MyDatabase]

GO

ALTER TABLE [dbo].[orderqty]
DROP CONSTRAINT [CK__orderqty__qty__628FA481]


Add Default constraint for existing table in SQL Server

Below is the syntax for adding default constraint for an existing table



ALTER TABLE tbl_Name
                       ADD CONSTRAINT default_columnName
                                         DEFAULT value FOR columnNmae




example :
Below is the query to create DEFAULT constraint on qty column when the orderqty table already created



ALTER TABLE orderqty
ADD CONSTRAINT default_qty
DEFAULT 1 FOR qty;


We created default constraint and we did not inserted value for qty field using INSERT statement in below example but "1" value inserted for qty


Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)


Problem

Cannot drop the user 'dbo'.

Rename failed for User 'dbo'.  (Microsoft.SqlServer.Smo)

Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)





Solution 

USE My_Database; 
ALTER AUTHORIZATION ON My_Database::DATABASE_NAME TO sa

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...