Errors upgrading SQL Server 2005 to SQL Server 2008

I have an existing SQL Server 2005 database which I would like to use in SQL Server 2008 database.

Everyone knows there are 2 ways to do this. Attach a database ( you need mdf and ldf file ) or Restore from a backup file (thus you need .bak file)

As much as I tried attaching the database, I could not update my login ID in the database (using the conventional sp_change_users_login ‘Auto_Fix’, ‘MyLoginName’)

So, I tried restoring the database. Steps followed are from here

I hit an error at –Restore Database step

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing ‘MyDatabaseName’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Which brings me to this link

So the fix for me is

USE master
ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE MyDatabaseName
FROM DISK = ‘C:\MyDatabaseName.bak’
WITH MOVE ‘YourMDFLogicalName’ TO ‘D:\DataYourMDFFile.mdf’,
REPLACE

If it doesn’t break, don’t change it

I noticed something wasn’t quite right in one of the database settings. Sure, it won’t be obvious and it will never get spotted on the surface if everything is entered via the system, but if you were to manually enter via database, sooner or later some error is going to happen. I was thinking of fixing it, but then I remembered a colleague once said, “If it doesn’t break, don’t change it” (it might become worse after changing it)

Which was what happened to my VS2005, which was running pretty slow and I decided to ‘clean’ the registry manager, till I realised that ‘cleaning’ meant removing most of the keys (which meant that the program was really stupid to identify which keys should be removed and which shouldn’t). Sad to say, Resharper could not work on my VS2005 and also made my VS weird. (Eg I cannot add a new file in a project)

VS2003 went through the whole clean-up-and-reinstall process, but since I was mainly working on VS2005 and the reinstall usually takes up a lot of time I had to do without Resharper for sometime. Strangely, I managed to install Resharper again the other day after months without it (It did not work previously, clean-and-install, contacting Helpdesk and etc)

Drive detected in Device Manager but not shown in My Computer

My pendrive does not get automatically displayed in my work PC (XP) but it is displayed in the device manager.

Easy way out

Click Start. Right click My Computer > Manage. Select Disk Management. You should see your drive there. Right click and Change Drive Letter and Paths.

Agile Business Analysis

I had the privilege yesterday to attend the Agile Business Analysis talk by Ellen Gottesdiener, the one hour session for those who were unable to attend the ongoing Software Development Conference.

It was interesting being amongst the Business Analysts, learning what they did, how Agile fits into the business perspective and something that I took away with me that day, how one should test their system (verification & validation)

I look forward to attend more sessions such as these.

For those interested, please take a look at Australia IIBA they are certainly encouraging more BA’s to join them.

Bushfire assistance – Technical Assistance needed

 As you know, many families have been severely impacted by the recent bushfires in Victoria. A great deal of people have been left homeless and displaced, and as various aid agencies’ efforts to provide immediate assistance and relief gain traction, thoughts are now turning to ‘what next’. One simple tool that can assist families to start picking up the pieces of their lives is access to the internet, along with various other tools such as printers, scanners copiers and fax machines. To meet this need, Melbourne organisation Infoxchange aims to establish Technology Resource Hubs in locations affected by the fires, and are also working to connect people in affected areas who have a technology need with people who are able to help out. In order to successfully scale their activities, Infoxchange are seeking of donations of both equipment and time. Which is where you come in If you have technology skills, and can volunteer some time to go out onsite to assist with installing machines and configuring networking and internet connections, it’d be great if you could please register at the link below. If you can’t physically help, but have serviceable PCs or laptops laying around that could be pressed into service as internet browsing systems, or if you have any networking gear that’s surplus to requirements, then please also contact Infoxchange via the link below – they’d be pleased to accept your donations.

http://www.bushfirerelief.infoxchange.net.au/

Visual Studio 2005 Keyboard Environment, missing Step Into F11

I’m not sure if I’m the only one, I recently used another user profile on VS2005, needless to say all the environment settings were reset and I realized that my favourite Step Into button no longer exists, and the keyboard F11 no longer works. Only using Step Over F10 isn’t very favourable. 

http://msdn.microsoft.com/en-us/library/bb245788(VS.80).aspx

 

Anyway, this is what I did.

Tools > Import and Export Settings

Import selected environment settings

Yes, save my current settings

Default Settings > General Development Settings

(Description: Configures the environment to closely match VS .NET 2003 to provide an experience that is already familiar to you. Select this collection of settings if you develop in more than one programming language)

 

Someone else has blogged about the menu settings different for C# and VB, so he adds/changes the C# environment into the menu to get the Step Into button

http://blogs.vertigo.com/personal/keithc/Blog/archive/2007/07/20/missing-menu-options-in-visual-studio-2005.aspx

Drop a default constraint without knowing the name

This is a very nice script written by Rob Farley

http://msmvps.com/blogs/robfarley/archive/2007/11/26/two-ways-to-find-drop-a-default-constraint-without-knowing-its-name.aspx

declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
set @table_name = N’Department’
set @col_name = N’ModifiedDate’

select t.name, c.name, d.name, d.definition
from sys.tables t
    join
    sys.default_constraints d
        on d.parent_object_id = t.object_id
    join
    sys.columns c
        on c.object_id = t.object_id
        and c.column_id = d.parent_column_id
where t.name = @table_name
and c.name = @col_name

Twitter subscriptions in WordPress

I was trying to figure how to use my (dead)  Twitter account. Figured it would be rather useful if I had some RSS subscriptions published. As you probably know by now, Twitter is not a click-and-drag widget yet (as of now) on the WordPress Design.

Found this, looks like it worked.

http://en.forums.wordpress.com/topic/twitter-updates-not-displaying?replies=29

SQL Server Change Ownership

Bill Graziano  wrote a very useful script here. 

SELECT 'EXEC(''sp_changeobjectowner @objname = '''''+
  ltrim(u.name) + '.' + ltrim(s.name) + ''''''
  + ', @newowner = dbo'')'
FROM  sysobjects s,
      sysusers u
WHERE s.uid = u.uid
AND   u.name <> 'dbo'
AND   xtype in ('V', 'P', 'U')
AND   u.name not like 'INFORMATION%'
order by s.name
This query finds every view, stored procedure and user table in the database not owned by the dbo and converts ownership to the dbo.

SQL Server 2000 vs SQL Server 2005 queries

This gives an example of SQL queries between SQL Server 2000  and SQL Server 2005

http://www.mssqltips.com/tip.asp?tip=1015

Bumped into this while looking for  IGNORE_DUP_KEY

 

Also realized you can’t put this as part of a table creation unlike SQL Server 2005.