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 Disable The Cross X Button in Winform

I had this requirement to to not allow user to close a windows form by clicking the top-right cross button.  This can be done by checking the FormClosing Event.

 

private void DataCheckInspection_FormClosing(object sender, FormClosingEventArgs e)
{
    if (e.CloseReason == CloseReason.UserClosing)
    {
        e.Cancel = true;
    }
}

TSQL-How to Contract Numbers

I was trying to contract numbers from “1” to “1st”, 2 to “2nd” etc.

This amazing solution from Brad Schulz at

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/6d176328-908c-4b00-8768-eb71b7c57e7b

did the job for me.

SELECT CAST(Number AS VARCHAR(10))
      +CASE 
         WHEN Number%10=1 AND Number%100<>11 THEN 'st'
         WHEN Number%10=2 AND Number%100<>12 THEN 'nd'
         WHEN Number%10=3 AND Number%100<>13 THEN 'rd'
         ELSE 'th' 
       END
FROM master..spt_values
WHERE type='P' 

Reset SA password for SQL box

My old box was changed to different domain and I could not use Windows authetication to log into the SQL server there as “sysadmin”. I forgot the SA user password as well. After some search I found the following solutions

Option 1 :

If there is any other SQL Server Login that is a member of sysadmin role, you can log in using that account and reset the password of SQL Server.

Option 2 :

If there is any other Windows Login that is a member of Windows Admin Group, log in using that account. Start SQL Server in Single User Mode as described here : SQL SERVER – Start SQL Server Instance in Single User Mode.

Create a new login and give it sysadmin permission.

Note : If you have SQL Server Agent enabled, it starts before SQL Server service. If you have enabled SQL Server in a single user mode, it will connect it first, so it is recommended to turn that off before attempting any of the above options.

I used the second option and was able to reset SA’s password.

Start SQL server in “single user” mode.

To start SQL Server in single user mode is very simple procedure as displayed below.

Go to SQL Server Configuration Manager and click on SQL Server 2005 Services. Click on desired SQL Server instance and right click go to properties. On the Advance table enter param ‘-m;‘ before existing params in Startup Parameters box.

clip_image001

Make sure that you entered semi-comma after -m. Once that is completed, restart SQL Server services to take this in effect. Once this is done, now you will be only able to connect SQL Server using sqlcmd.

Make sure to remove newly added params after required work is completed to restart it in multi user mode.

Now I was able to login to the SQL server with windows authentication.

I change the password for user SA.

Thanks to links from:

SQL SERVER – Forgot the Password of Username SA

SQL SERVER – Start SQL Server Instance in Single User Mode

Have Multiline Strings in VS

I frequently face situation where a string variable has a long length, so long that I have to scroll the screen left to right to see it.  What helps in this situation is to have breaks in that string so that the full string can be seen without having to scroll left-right.

Using “@” before the string’s value helps in this case.  For example,

string mysql = "select table1.col1,table1.col2,table1.col3, table2.column3, table2.col4, table2.col4 from table1 join table2 on table1.col1 = table2.col2"

can be written as:

string mysql = @"select table1.col1,table1.col2,table1.col3
, table2.column3, table2.col4, table2.col4
from table1 join table2
on table1.col1 = table2.col2"