Category Archives: SQL

Have you every received this message when you try to access Database Diagrams for SQL Server?

If so, there is a simple fix. You need to change to ownership of the database to a valid user on the computer that you are using like so:

EXEC sp_dbcmptlevel ‘<database-name>’, ’90’
go
ALTER AUTHORIZATION ON DATABASE::<database-name> TO “<valid-domain-and-user>”
go
use <database-name>
go
EXECUTE AS USER = N’dbo’ REVERT
go

Replace the text <database-name> with the name of the database that you can’t access the database diagrams for and also replace the text <valid-domain-and-user> with a valid entry such as: RUSS-LAPTOP\Russ.

This worked a treat for me and hopefully it will for you too. If it doesn’t then you can also try the following to check the compatibility level and the db owner

1. Right Click on your database, choose Properties
2. Goto the Options Page
3. In the Dropdown at right labeled “Compatibility Level” choose “SQL Server 2005(90)”
4. Goto the Files Page
5. Enter “sa” in the owner textbox
6. Press OK and retry

We’ve all done it. Changed our default database for our logon to a database in SQL Server other than master, then either detached or deleted the database some way down the line. Of course at this point your pretty much screwed as you can’t access much as your login no longer works.

To fix this when you open Management Studio press the Options button and change the Connect to Database option to master. Now, this doesn’t fix the problem, it merely allows you to then open up a New Query. In the query enter the following SQL, change the login name and execute it.

ALTER LOGIN TestLogin WITH DEFAULT_DATABASE = master

Change TestLogin for your own login, close Management Studio and re-open it, press the Options button and change the Connect to Database option back to <default>.

At this point you should be able to get back into SQL Server with the usual authority and you can now re-attach your database.

In the past I usually hand generated a quick bit of SQL to generate my INSERT statements from existing table data like so:

select
‘insert into dbo.Links values (‘ +
cast(exceedence_set_id as varchar(100)) + ‘, ‘ +
cast(seq as varchar(100)) + ‘, ‘ +
cast(link_id as varchar(100)) + ‘, ‘ +
cast(link_offset as varchar(100)) + ‘, ‘ +
‘false,’ +
cast(band_id as varchar(100)) +
‘)’
from dbo.Links

The only problem with this was that I had to hand add in the column name, handle null data etc… So, I thought I’d have a look to see if someone with loads more SQL experience than me had had a go at handling this all automatically. After a bit of searching on Google I found a handy procedure from Narayana Vyas Kondreddi. Put simply it can automatically generate your Insert statements for any table and also comes with a host of configurable parameters to tailor the output to your own needs. For example to generate the statements for table Links but ommit the column names because the data will be inserted into an identical table on another SQL Server instance, and to also ommit the identity column, call the procedure like so:

EXEC sp_generate_inserts ‘Links’, @include_column_list = 0, @ommit_identity = 1

It’s that simple….great piece of work by Vyas. Also check out the rest of his site as there’s plenty more SQL stuff available…

I currently get blob data from a SQL database but a collegue pointed out that I wasn’t making it easy for him to see how much data is returned from the query. After a bit of head-scratching he found the solution which as usual is not in the MSDN documentation.

Here’s an example of how to do it…
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
reader.Read(); // Get length of data by pass null as the byte array parameter
long dataLength = reader.GetBytes(0, 0, null, 0, 0);
Simple really….if only Microsoft would tell us this in the first place…!

I’ve been working quite a bit recently with a database that I have been constantly deleting from and re-adding data for testing an uploader. The uploader carries out hundreds of SQL statements and as such it has resulted in a rather large log file, which over time would get to the unmanageable size.

So I had a quick look for ways to reduce the log file size when I want to… In the end it’s simple Smile

DBCC SHRINKDATABASE(<database name>)

Simply put in the name of the database and execute it.

Here’s a handy bit of SQL that i’ve been using to reset the seed of an identity column for a table. I have often needed to do this when i’ve cleaned out a table, but have needed to start the identity entry back at 0 rather than it’s current seed value

DBCC CHECKIDENT (<table name>, RESEED, -1)
GO

Simply fill in the name of the table and execute it. This will reseed the identity to -1 meaning that when you next enter a new row it will start at 0.

Lastly, make sure you have the GO command between calls to DBCC or it won’t work 🙁