Database Diagram Support Objects cannot be Installed … no valid owner

When trying to create ER diagram for a newly published database (from SSDT) I have been getting the following error message:

Capture

 

There are different ways to resolve this issue.

 

I used the following code to give user SA the ownership of the database.

USE [my_db_name]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Helpful links from StackOverflow are:

http://stackoverflow.com/a/26402194

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

http://stackoverflow.com/a/2043693

Advertisements

How to Get Only File Name from Full File Path

In our database we normally save the full path of files (i.e. photos, drawings etc.).  On many occasions I have to show only the actual file name.  Previously I was using PATINDEX and REVERSE for that, and I was also checking to see if the number of ‘\’s in the full path was zero or not.  I have now found a better way of doing this job.

SELECT
RIGHT('\' + MYFILEPATH, CHARINDEX('\', REVERSE(MYFILEPATH)) - 1)
FROM MYTABLE

How to Concatenate Columns To CSV String?

In our Property table we have addresses in different fields.  A full address field is used for the ease of report generation.  This field is a concatenation of the different address fields.  Sometimes the fields are NULL or empty.  I use the following code to create CSV string:

select
STUFF(
coalesce(',',nullif(PROPNUM,''),'')
+ coalesce(','nullif(STREETDSC,''),'')
+ coalesce(','nullif([LOCALITY1],''),'')
+ coalesce(',',nullif([LOCALITY2],''),'')
+ coalesce(',',nullif([POSTCODE],''),'')
from tmppropertyimport

I am using COALESCE and NULLIF for this purpose.

In short COALESCE returns the first non-null expression from its parameter(s).

NULLIFF returns a null value if the two specified expressions are equal.

Lets look at how it works.

NULLIF(PROPNUM, ‘’) – returns PROPNUM if not null or not empty.  Returns NULL if PROPNUM is  NULL or empty.

‘,’ + NULLIF(PROPNUM, ‘’) – returns “,PROPNUM” if PROPNUM has data.  Returns NULL if PROPNUM is null or empty.

The result is CSV string beginning with a comma.  I get rid of that comma using STUFF(string,1,1,’’).

Thanks for this goes to Stackoverflow post:

http://stackoverflow.com/a/5235783

Casting UniqueIdentifier to Varchar

I had the following code which gave an error message:

BEGIN TRAN

declare @machienid varchar
set @machienid = cast(NEWID() as varchar)
select @machienid

ROLLBACK

 

The error message was:

Msg 8170, Level 16, State 2, Line 4
Insufficient result space to convert uniqueidentifier value to char.

The default length for varchar is 30, and there is a chance of data overflow.  UniqueIdentifer has length of 36 characters.  So by specifying the length of the varchar variable we can overcome this issue.

Thanks goes to Pinal Dave’s excellent blog about SQL:

SQL SERVER – FIX: ERROR: 8170 Insufficient result space to convert uniqueidentifier value to char

How to Get List of All Foreign Keys

As part of my project to transfer our legacy database to SQL Azure I have been playing with our existing index list.  Dropping and creating new keys and index is pain in the backside as there are lots of foreign key constraints here and there.

As a quick get-around I have decided to remove all foreign keys (don’t worry! This is just for my exercise.  I will definitely put them back later, at least some of them.  This will also give me the chance to check where a key is required or if the database is missing any vital key).  I have used to following script to create a dynamic script to drop all FK constraints.

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id

 

Thanks to Jimmy Bogards blog post about Viewing all foreign key constraints in SQL Server.

And, I think Jimmy has borrowed the idea from Pinal Dave’s excellent SQL Authority Blog.