Steve Jobs One Last Thing

Recently I watched this great documentary about Steve Jobs: One Last Thing. Great to see his story from different angles. He sure did change the world for many people.

You can view the one hour documentary free on Youtube or directly here:

Have fun watching this video,

How to change database primary key field with a foreign key constraint

Today I needed to increase a varchar database from 2 position to 50 positions. At first I thought this would be easy, just make a change script like this:

   1: ALTER TABLE tb_Categories ALTER COLUMN Code varchar(50) not null

After running in SSMS I got two error messages back:

Msg 5074, Level 16, State 1, Line 1
The object 'FK_CategoryCode' is dependent on column 'Code'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Code failed because one or more objects access this column.

Oops.. The Code field is used as a primary key here and referenced by another table tb_Tips. Luckily the database protects data loss by checking the constraints on a table before allowing any changes. (Btw, my database is for storing user tips (tb_Tips) and are categorized within groups via tb_Categories.).

Since I want to increase from 2 to 50 characters, I don’t get any data loss, so I really want to change my field with keeping my data in the tables. After some thinking, I produced a rough plan:

  • Temporarily remove the foreign key constraint in tb_Tips
  • Temporarily remove the primary key constraint in tb_Categories
  • Now the tb_Categories.Code field is key constraints free: upgrade the Code field from 2 to 50 characters
  • Upgrade the reference field in tb_Tips
  • Add primary key constraint for tb_Categories
  • Add foreign key constraint for tb_Tips
  • Done! (and check the data in the tables)

So here is the script:

   1: DECLARE @constraintName varchar(255),@table nvarchar(50), @sql nvarchar(255), @constraintpattern nvarchar(10)
   2: set @table = 'tb_Tips' 
   3: set @constraintpattern = '%categ%'
   4:  
   5: --Remove foreign key constraint for tb_Tips
   6: select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table
   7: select @constraintName = CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table and CONSTRAINT_NAME like @constraintpattern and CONSTRAINT_TYPE = 'FOREIGN KEY'
   8: select @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + @constraintName
   9: exec sp_executesql @sql
  10:  
  11: --Remove primary key constraint for table tb_Categories
  12: set @table = 'tb_Categories' 
  13: select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table
  14: select @constraintName = CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table and CONSTRAINT_NAME like @constraintpattern and CONSTRAINT_TYPE = 'PRIMARY KEY'
  15: select @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + @constraintName
  16: --DEBUG: select @sql
  17: exec sp_executesql @sql
  18:  
  19: -- Extend primary key field for table tb_Categories
  20: ALTER TABLE tb_Categories ALTER COLUMN Code varchar(50) not null
  21:  
  22: -- Extend foreign key field for table tb_Tips
  23: ALTER TABLE tb_Tips ALTER COLUMN Category_ref varchar(50)
  24:  
  25: -- Add primary key for table tb_Categories
  26: ALTER TABLE tb_Categories WITH NOCHECK 
  27: ADD CONSTRAINT PK_CategoeryCode PRIMARY KEY CLUSTERED (Code)
  28: WITH (FILLFACTOR = 75, PAD_INDEX = ON)
  29:  
  30: -- Add foreign key for table tb_Tips
  31: ALTER TABLE tb_Tips WITH NOCHECK 
  32: ADD CONSTRAINT FK_CategoryCode FOREIGN KEY (Category_ref)
  33:    REFERENCES tb_Categories (Code)

Note 1: I query the INFORMATION_SCHEMA.TABLE_CONSTRAINTS system table to retrieve the EXACT name of the constraint. Since I need to run this script also on other database servers, the script should be figure out the name itself.

Note 2 / Note to self: next time always use a integer column with auto increment.

I came across the website of Jon Galloway, which has a brilliant T-SQL script to remove all constraints from a single table (a bit too much for me in the current situation):

   1: -- t-sql scriptlet to drop all constraints on a table
   2: DECLARE @database nvarchar(50)
   3: DECLARE @table nvarchar(50)
   4:  
   5: set @database = 'dotnetnuke'
   6: set @table = 'tabs'
   7:  
   8: DECLARE @sql nvarchar(255)
   9: WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
  10: BEGIN
  11:     select    @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
  12:     from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
  13:     where    constraint_catalog = @database and 
  14:             table_name = @table
  15:     exec    sp_executesql @sql
  16: END

 

Windows 8 keyboard combinations

The new version of Windows has some new keyboard combinations for the new features. Of course there are a lot of sites that have complete downloadable cheat sheets for these kind of things. I just create a simple list to start with to keep the learning curve acceptable Smile

 

New key combinations for Windows 8

WinKey = Start screen or previously opened application

WinKey + C = Open charms

WinKey +. = Moves opened application to left or right side

WinKey + Tab = Open application overview on the LHS

WinKey + Q = Search application

WinKey + X = Start screen power menu

WinKey + W = Search in configuration settings

WinKey + Z = Show options in Metro-app and start screen

WinKey + F1 = Windows Help

Most important of the existing combinations are still available in Windows 8:

WinKey + D = Back to desktop

WinKey + E = Open Windows Explorer

WinKey + F = Open search

WinKey + R = Open the Run command

WinKey + P = Open the settings for a Projector or a 2nd monitor

WinKey + L = Lock the PC

 

Have fun,

Installing Windows 8 CP with Virtual Box

Today I took some time to install Windows 8 Consumer Preview and run it within Virtual Box. During installation I wrote down some notes of what I encountered during setup. Here is my simplified list of steps to achieve a working Windows 8.

You can download the Win8 Consumer Preview from the Microsoft site as a ISO image.

1. Install Virtual Box + Extension Pack.
2. Create new virtual machine with OS Windows 8.
3. Choose 2048 MB internal memory for 64 bit version (You can use 1024 for 32 bit).
4. Create Virtual Disk Image with dynamically allocated setting & 20 GB HDD space.
5. After create the disk image, mount the ISO as a virtual CD/DVD station. You can use the 'host' setting when you have burned the image to DVD.
6. Other settings that need to be checked:
- PAE/NX (tab System / Processor)
- IO APIC (tab Motherboard)
- VT-x/AMD-V (tab Acceleration)
- Nested paging (tab Acceleration)
7. Use the Windows 8 consumer preview activation key: DNJXJ-7XBW8-2378T-X22TX-BKG7J
8. Choose 'Custom: install Windows only (advanced)'
9. Proceed installation procedure as you used to do. At the end you can choose to use your Windows Live account or not. I didn't want to use a Microsoft account, I chose to create a local account: admin/admin.
10. After installation it's smart to run VBoxWindowsAdditions.exe for installing the Guest Additions. It's useful for drivers and sharing folder with the host.

Hope this helps,

Easy way to clear your SQL server database

Sometimes you want to make a clean start with your SQL server database since it’s filled up with old demo data. We all now that you can delete or truncate your tables easily by executing some T-SQL statements. When you only have a couple of tables and no complex foreign key dependencies you can do it by hand. When you database is larger and for more complex it can be a time consuming task and it’s different for each database schema.

The following script makes it really easy (beware: select the correct database!).

 1: ------------------------------------------------------------------------------------
 2: ---- NOTE: !!! DOUBLE CHECK SELECTED DATABASE !!!! ----
 3: ---- FIRST TRY RUNNING WITH TRANSACTIONS ON, LATER REPLACE ROLLBACK WITH COMMIT ----
 4: ------------------------------------------------------------------------------------
 5: begin transaction
 6:  
 7: -- disable all constraints
 8: EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
 9:  
 10: -- delete data in all tables
 11: EXEC sp_MSForEachTable "DELETE FROM ?"
 12:  
 13: -- enable all constraints
 14: exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
 15:  
 16: -- some of the tables have identity columns we may want to reseed them
 17: EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"
 18:  
 19: rollback transaction
 20: --commit transaction

Note: I have the code surrounded by a transaction that performs a rollback by default. Just to make sure that you don´t run this script accidentally on the wrong database. You can download the script by using the link below

clear_all_database_tables.sql (1.54 kb)  

Hope this can save some time for you too,