Division By Zero Error

This problem is actually quite easy to solve, using this: NULLIF(Divisor,0) NULLIF will check whether the Divisor is equal to zero. If it is, then it specifies the value to be NULL therefore it won’t trigger the SQL Server error message. More info: http://msdn.microsoft.com/en-us/library/ms177562.aspx

Read Full Post »

How To Find The SQL Server Port Number

SQL Server 2005/2008: Click open the SQL Server Configuration Manager > SQL Server 2005 Network Configuration> Protocols for MSSQLSERVER On the right panel, double click TCP/IP > IP addresses tab. SQL Server 2000 : Go to SQL Server Client Network Utility, select the instance, make sure you’re on the General tab, select TCP/IP and click [...]

Read Full Post »

How To Fix A Suspect Database – SQL Server 2005

I was running a resource-intensive reindexing script when my CPU froze for about 15 mins without any sign of “thawing”. Being an impatient idiot, I reached for the power button to reboot. Back to the SSMS….. aaaand the database has gone suspect, GREAT! *with sarcasm* Well, since this is a non-production database, there’s no issue [...]

Read Full Post »

Uninstall SQL Server 2008

This is a bit tricky! I was trying to uninstall the SQL Server 2008 on my local server. Buuut… I couldn’t find the remove option. Add/Remove Program? Not an option, I might end up with nasty, dirty registry and corrupted installation. Setup.exe? Strangely doesn’t have a remove option (this is SQL2008 RTM). So I asked [...]

Read Full Post »

Disappearing Task Manager Tabs

Is your Task Manager looking like this? Been wondering where the tabs are? Just double click on the area pointed by the arrow. Look who’s back!

Read Full Post »

Installing Adventureworks Database

If you’re as silly as me and you’ve been wondering where the hell the Adventureworks database is after you run the msi file, look here. My light bulb was clicked recently and I found the solution here: http://msdn.microsoft.com/en-us/library/aa992075.aspx In a nutshell, here’s what you have to do (after you downloaded and ran the file): 1. [...]

Read Full Post »

Change Default Schema In SQL Server 2005/2008

I recently found out that some schemas in my SQL2005 box are not of the default dbo (this is apart from some pre-determined schemas that we have). This seems to happen when I run sp_adduser to add a login as a user of a particular database. CREATE LOGIN [DOMAIN\mylogin] FROM WINDOWS GO USE MyTestDB GO [...]

Read Full Post »

Unlock Login in SQL 2005/2008

Without changing password: ALTER LOGIN [login] WITH CHECK POLICY = OFF; ALTER LOGIN [login] WITH CHECK POLICY = ON; GO With password change: ALTER LOGIN [login] WITH password =’*****’ UNLOCK; GO http://msdn.microsoft.com/en-us/library/ms189828.aspx

Read Full Post »

Replication Monitor For Non-Admin

It’s a good news that SQL Server 2005 comes with a fixed database role replmonitor to allow non-admin staff (developers etc) to view the Replication Monitor. To set it up: 1. CREATE USER username in the distribution database 2. Assign replmonitor database role to the username Your developers etc should be able to check out [...]

Read Full Post »

How to convert datetime data type

I must admit I dislike the SQL datetime data type – it’s long, ugly and not always readable. So I totally understand why some people will go a distance to make this type easier for them (to read, that is). Well, the good news is there is a way to convert datetime format to other [...]

Read Full Post »