Gildner Solutions

Adding a unique constaint to a column that allows nulls

Filed under General

I have an integer column in one of my database tables that allows nulls.  What I want is for any value other than nulls to be unique.  Here's how I did it:

CREATE UNIQUE NONCLUSTERED INDEX idx_SupplierID_notnull
ON Brands(SupplierID)
WHERE SupplierID IS NOT NULL; 

Brands is my table and SupplierID is the column I want to be unique.

Entering a NULL value using SSMS

Filed under General

This happens every once in a while, I need to enter a NULL value when opening a table in SSMS.  I'm writing it here so I don't forget what it is.

Highlight the field and press

CTRL + 0  (zero)

Connecting to a remote development SQL Server 2008

Filed under General

I have a nice low-end Dell PowerEdge SC1430 server that I run out of my house to host some testing and development sites.  It's running Windows Server 2008 with SQL Server 2008 and is set up as a web server.  This has happen a few times now but I forget how to fix it every time, so I'm blogging about it.  I just reinstalled SQL 2008 because I botched it up somehow and it's sometimes easier to just start over.  After reininstalling and installing the server packs, I restored my databases and was back in action -- at least locally.  My IIS sites were running great.  So today I try to connect to my SQL Server 2008 instance through my laptop and SSMS and I'm shut out - connection failed.  I figure it's a firewall issue.  Nope, I already have port 1433 opened on both machines.  Some googling led to me a "allow remote connections" checkbox in the server instance properties.  Nope, already checked.  After an hour of racking my brain, I remembered one last thing -  enable the TCP/IP protocol on the SQL Server!

And bingo.  It works.

So, the next time I install a new SQL Server and need to allow remote TCP/IP access to it (through SSMS or a tool like SQL Compare), I'll take the following steps:

1.  Allow access to TCP port 1433 access on both the client and server machines through Windows Firewall (or whatever firewall that is used)

2.  In the properties of the server instance, under Connections, check the "Allow remote connections to this server":

3.  Enable TCP/IP access in the SQL Server Configuration Manager.

Hope this helps someone who was stuck like me.

Scott