I was having problems allowing some of my co-workers connect to the instance of SQL Server on my machine. Of course I was aware of the ‘Allow remote connections to this server’ option so I checked that first. It was already on, so I checked out Google to see what I could find.
After fiddling about with firewalls and ports, it turns out that I didn’t have the TCP/IP protocol enabled within the SQL Server Configuration Manager tool. This meant network based communication with any databases on my SQL Server instance was impossible.
As soon as I enabled this protocol and restarted the instance (via SQL Server Services section above) incoming connections were possible. Of course your problem may be caused by something else so check out Daniel Walzenbach’s article on MSDN: How to enable remote connections in SQL server 2008? It helped me a lot.
Perhaps your scripting rollout and rollback DDL SQL changes and you want to check for instance if a default constraint exists before attemping to drop it and its parent column.
Most schema checks can be done using a collection of information schema views which SQL Server has built in. To check for example the existence of columns you can query the information schema view for columns with a query which looks something like:
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourtablename' AND COLUMN_NAME = 'yourcolumnname')
however none of the information schema views have anything in them for checking for a named default constraint. For that you need to use something like:
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE TYPE_DESC = 'DEFAULT_CONSTRAINT' AND NAME = 'yourconstraintname')
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE TYPE = 'D' AND NAME = 'yourconstraintname')