Enjoy everyday in our life

It’s a very common scenario for developer run into “out of space” on C drive because the Model database default location was set at “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA”.

Therefore, there are few occasion that I was in need of moving the datafiles from C drive to my D drive which is more expandable and have lots more space for my huge database.  Sure, you can definitely do a backup and then create new database in your D drive and then restore your backup file into that new database! However, what if you just want to get it done quick?

Here are few easy steps for you to move the datafiles from one location to another location; example here is we want to move a database called “test1″:

1. Backup your database (just in case anything go wrong, this is extremely important if you have valuable data)
2. Make database offline or make sure there’s no active connection on the database you plan to move the data files.
3. Run this sp_detach_db script in query management studio:
   EXEC sp_detach_db 'test1', 'true'

4. Move the datafiles for test1 - “test1.mdf” and “test1_log.ldf” from “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA” to “D:\MSSQL\DATA” folder on file system
5. Run this sp_attach_db script:
   EXEC sp_attach_db @dbname = N'test1',
   @filename1 = N'D:\MSSQL\DATA\test1.mdf',
   @filename2 = N'D:\MSSQL\DATA\test1_log.ldf'

6. Finish! Now you can use your test1 db just like before!

Keep in mind that you will need access to the database server file system and you will need to know the location of your database and have sysadmin role in the database.
Hope this help!

• Read More »

It took me a while to figure this out so I’m going to blog about this.  Note that this article is only for SQL Server users.

This code snippet below is not very useful if you just look at it without understanding what I used it for; so here is some background.

I have a big stored procedure, RefreshTables, which runs 15 other stored procedures to populate data and they are wrapped by a transaction in RefreshTables SP. I also have a ExecuteDataPopulation SP which calls the 15 stored procedures and log the result for the execution. Therefore, if one out of the 15 procedure failed, all of the affected data is rolled back. At the same time, I would like to populate the before row count and after row count for each tables that I execute data population stored procedure on.  Therefore, I can’t do any DDL during the big stored procedure (so nothing is committed) and I will have table name changing on each individual data population process. See the code below, it takes a dynamic sql (table name changing) and returns the value that dynamic sql generated:


declare @rowcount int, @sql nvarchar(1000), @tablename nvarchar(100)
set @tablename = 'users'
set @sql='select @rc = count(*) from '+@tablename

select 'give me value back!', @rowcount

By using this in my Audit procedure ExecuteDataPopulation, I now can get the before and after row count! I know there is an easier way to get row count using:

create table #tmp (rowcount int);

insert into #tmp
exec('select count(*) from users');

However I cannot use it like this since I do not want to have any DDL happen during my transaction, it commits everything which happened before the DDL!!!

Hope this will be helpful to someone who is trying to figure out how to get return value out of your dynamic sql.

• Read More »

I am currently working on all 3 major databases now:  SQL Server and Oracle for my 9 to 5 job; MySQL for personal use.  One day one of my friends asked me, “How do you limit row count to only show the first 10 rows in Oracle?”  And I quickly answered him “Oh… add limit 10 at the end”.  Right after I said that, I realized that it does not work for Oracle because it is a MySQL syntax!

Here is a table for quick comparison for the syntax difference for SQL Server, Oracle and MySQL.  All of these examples do the exact same thing: return 10 rows from a result set.

I personally like MySQL syntax the best! It is easy to understand and  easy to remember and ofcourse makes total sense!

• Read More »