T SQL | SQL with Manoj

SQL Error – The database principal owns a schema in the database, and cannot be dropped

October 10, 2015 1 comment

Have you ever faced this error while dropping a USER from a Database? Today while dropping a USER I faced this error and for a while I was not able to fix it. After checking here & there and searching online I found some clues and got the reason for this error.

–> Let’s check the reason behind it and how to fix it:

USE [AdventureWorks2014]
GO

DROP USER [hruser]
GO

You get following error:

Msg 15138, Level 16, State 1, Line 5
The database principal owns a schema in the database, and cannot be dropped.

The above error explains very well why the USER cannot be deleted. It’s because the USER is associated with a SCHEMA and has its ownership. Thus until and unless you assign another USER as the SCHEMA owner you cannot DROP this USER.

–> To check which SCHEMA this USER owns you can use either of these 2 methods:

Method #1: By simple Query:

SELECT *
FROM sys.schemas
WHERE principal_id = USER_ID ('hruser')
GO

name	schema_id	principal_id
Sales	9		5

Method #2: By GUI in SSMS: Right click on the user name under Database -> Security -> Users

–> So, all you need to do is change the ownership of the Schema from the USER that you want to delete to some other user like dbo, as done below:.

USE [AdventureWorks2014]
GO

ALTER AUTHORIZATION ON SCHEMA::[Sales] TO [dbo]
GO

If there are more SCHEMAs then issue ALTER AUTHORIZATION ON SCHEMA statement on those schemas also to change their owners.

–> Ok, now just re-run the DROP USER statement, it will drop the User.

USE [AdventureWorks2014]
GO

DROP USER [hruser]
GO

Categories: SQL Errors Tags: ALTER AUTHORIZATION ON SCHEMA, DROP USER

SQL Error – Cannot create index on view ‘vw_SomeView’ because the view is not schema bound.

October 5, 2015 Leave a comment

This error occurs when you try to create an Index on top of a View which is not created by using “WITH SCHEMABINDING” option.

When you create Index on top of a View they are called “Indexed View” or “Materialized View”.

–> Let’s check below:

use [AdventureWorks2014]
go

DROP VIEW IF EXISTS vw_Person
GO

CREATE VIEW vw_Person
AS
SELECT 
	[BusinessEntityID], 
	[FirstName], [MiddleName], [LastName], 
	[Suffix], 
	[EmailPromotion], 
	[ModifiedDate]
FROM [Person].[Person]
GO

CREATE UNIQUE CLUSTERED INDEX CIX_vw_Person ON [vw_Person]([BusinessEntityID])
GO

Msg 1939, Level 16, State 1, Line 18
Cannot create index on view ‘vw_Person’ because the view is not schema bound.

–> So to fix this issue you can ALTER the view definition by adding “WITH SCHEMABINDING” option with ALTER/CREATE VIEW statement as shown below.

Thus, creating an Index on a Schema bound view will not throw this error.

ALTER VIEW vw_Person
WITH SCHEMABINDING --<< here <<
AS
SELECT 
	[BusinessEntityID], 
	[FirstName], [MiddleName], [LastName], 
	[Suffix], 
	[EmailPromotion], 
	[ModifiedDate]
FROM [Person].[Person]
GO

CREATE UNIQUE CLUSTERED INDEX CIX_vw_Person ON [vw_Person]([BusinessEntityID])
GO

–> By adding this rule or restriction SQL Server just wants to make sure if an Index is created on a View, nobody can directly change the definition of Table(s) underneath the View. And if it needs a change then first the View need to be dropped and re-created after altering the tables (error shown below).

ALTER [Person].[Person]
DROP COLUMN [Suffix]

Msg 5074, Level 16, State 1, Line 32
The object ‘vw_Person’ is dependent on column ‘suffix’.
Msg 4922, Level 16, State 9, Line 32
ALTER TABLE DROP COLUMN suffix failed because one or more objects access this column.

Categories: Indexes, SQL Errors Tags: Indexed View, Materialized View, SQL Views

SQL Basics – Data Types in SQL Server (Video)

September 20, 2015 Leave a comment

–> Here is the notepad file I used in the above video for your reference:

– Numerics:

bit – 1, 0, NULL

tinyInt – 1 byte 0 to 255
smallInt – 2 bytes -32,767 to +32,767
int – 4 bytes -2,147,483,647 to +2,147,483,647
bigint – 8 bytes -9,223,372,036,854,775,808 to +9,223,372,036,854,775,808

decimal(p,s)/numeric(p,s) -10^38 +1 to +10^38 +1

smallMoney -214,748.3648 to +214,748.3648
money -922,337,203,685,477.5808 to +922,337,203,685,477.5808

– Flaoting point

float & real

– Date & Time (Temporal)

date – YYYY-MM-DD
time – hh:mm:ss[.nnnnnnn]
smallDatetime – YYYY-MM-DD hh:mm:ss
datetime – YYYY-MM-DD hh:mm:ss[.nnn]
datetime2 – YYYY-MM-DD hh:mm:ss[.nnnnnnn]

– Character Strings

char – 1 to 8000 chars, 8000 btyes
nchar – 1 to 4000 chars, 8000 btyes
varchar – 1 to 8000 chars, 8000 btyes
nvarchar – 1 to 4000 chars, 8000 btyes
[n]varchar(max) – 2GB

here prefix “n” is unicode to store international languages and take double space.

– binary Strings

binary – 1 to 8000 bytes
varbinary – 1 to 8000 bytes
varbinary(max) – 2GB
image – 2GB

– Other Datatypes

cursor
timestamp
xml
uniqueIdentifier – GUID
Spatial Types (geography, geometry)
sql_variant

… etc

Categories: SQL Basics Tags: DataType, SQL Server Data Types

SQL Error – Unable to generate a temporary class (result=1), error CVT1108, error CS1583

August 14, 2015 2 comments

Few days back I got an email from one of this blog reader mentioning that he was facing issues while Installing SQL Server, and was getting following error:

SQL Server Setup has encountered the following error:

Unable to generate a temporary class (result=1).
error CVT1108: cannot open C:\Users\DELL\AppData\Local\Temp\RESAC3F.tmp for writing
error CS1583: ‘c:\Users\DELL\AppData\Local\Temp\CSCAC2F.tmp’ is not a valid Win32 resource file

By the above error its evident that the user does not have sufficient rights to the Temporary location mentioned above “C:\Users\DELL\AppData\Local\Temp”.

As the SQL Server installer EXE is trying to extract the compressed files to this Temp location before actually installing SQL Server on your machine. But it looks like due to some reason the use account does not have access to this location, thus throwing error mentioned above.

–> Now the resolution for this is to, either:

1. Re-extract the contents to an another drive where you have full permission.

– or –

2. To set proper rights for the User on the Temp location. Go to the above Temp location and Right-click on the Temp folder, and select Properties, go to Securities tab. Under the “Groups or user names:” section just check if your user name is there. Select the user name and make sure it should have “Full Control” selected under the “Permissions for Administrators”. If it’s not then click on the Edit button and provide Full rights to the user account.

Categories: SQL Errors Tags: Install SQL Server, SQL Error

Difference between Index and Primary Key – MSDN TSQL forum

August 10, 2015 Leave a comment

–> Question:

What is the difference between the Index and the Primary Key?

–> My Answer:

In simple DBMS words:

– A Primary Key is a constraint or a Rule that makes sure to identify a table’s column uniquely and enforces it contains a value, ie. NOT NULL value.

– An Index on the other side is not a constraint, but helps you organize the table or selected columns to retrieve rows faster while querying with SELECT statement.

In SQL Server you can create only one Primary Key, and by-default it creates a Clustered Index on the table with the PK column as the Index key. But you can specify to create Non-Clustered Index with a PK also.

Indexes in SQL Server mainly are:

– Clustered Index

– Non Clustered

… you can specify them as unique or non-unique.

Other type of indexes are:

– ColumnStore

– Filtered

– XML

– Spatial

– Full Text

–> Another Answer by Erland:

A primary key is a logical concept. The primary key are the column(s) that serves to identify the rows.

An index is a physical concept and serves as a means to locate rows faster, but is not intended to define rules for the table. (But this is not true in practice, since some rules can only be defined through indexes, for instance filtered indexes.)

In SQL Server a primary key for a disk-based table is always implemented as an index. In a so-called memory-optimized table, the primary key can be implemnted as an index or as a hash.

–> Another Answer by CELKO:

PRIMARY KEY is define in the first chapter of the book on RDBMS you are too lazy to read. It is a subset of columns in the table which are all not null and unique in the table. An index is an access method used on the records of a physical file.

Many SQL products use indexes to implement keys; many do not (hashing is a better way for large DB products)

Check the video on Primary Keys: