Creating links between databases on different machines

Last year I worked on an interface between two systems to synchronize the data. It was specked out so that it was restricted to work with databases on the same machine or on different machines within a LAN, by specifying the machine name.

As it's been sold to a customer trying to connect two machines over the Internet by specifying an IP address. I've got to do a quick change to enable this.

Here's a quick list of situations to test in such a situation:

• server specified by machine name
• server specified by IP address
• databases on the same machine
• databases on different machines in the same domain in the same LAN
• databases on different machines in the same domain in the same WAN
• databases on different machines in different domains, but in the same LAN
• databases on different machines, connected over a VPN
• database on different machines, connected over the Internet
• connections using network/user authentication
• connections using specified a user name and password

Why does this matter?
Because connection strings may need to be different.

Checking for default constraints on SQL Server 2000 AND 2005 - CORRECTION!

Unfortunately there isn't a simple way to check for default constraints on a column which works on both SQL Server 2000 and 2005. To get around this problem, I created the following function:

CREATE FUNCTION DefaultConstraintExists(@SchemaAndTableName sysname, @Column sysname)
RETURNS varchar(1) AS
BEGIN
DECLARE @Result varchar(1);
DECLARE @columnId INT
--Try and get the columnID - only works on SQL Server 2000
SELECT @columnId = [info] FROM sysobjects
WHERE [xtype] = 'D'
AND [parent_obj] = OBJECT_ID(@SchemaAndTableName)
AND COL_NAME([parent_obj], [info]) = @Column
--If that failed, try and get it in a way that works on SQL Server 2005 (There is no way that works on both)
IF @columnId IS NULL
BEGIN
SELECT @columnId = COLUMNPROPERTY(OBJECT_ID(@SchemaAndTableName), @Column, 'ColumnId')
END
--Now see if the default constraint exists
IF EXISTS (Select * From sysobjects where xtype = 'D' and parent_obj = object_id(@SchemaAndTableName)
and col_name(parent_obj, @columnId) = @Column)
BEGIN
SET @Result = 'T'
END
ELSE
BEGIN
SET @Result = 'F'
END
RETURN @Result;
END
go

The above doesn't work properly on SQL Server 2005, it returns true if there are any constraints on the table, not just the desired field.
The following is simpler AND IT WORKS!

CREATE FUNCTION FDS.DefaultExists(@Schema varchar(10), @TableName varchar(100), @Column varchar(100))
RETURNS varchar(1) AS
BEGIN
DECLARE @Result varchar(1);
DECLARE @default varchar(255)

SELECT @default = COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @Schema
AND TABLE_NAME = @TableName
AND COLUMN_NAME = @Column

IF @default IS NULL
BEGIN
 SET @Result = 'F'
END
ELSE
BEGIN
 SET @Result = 'T'
END
RETURN @Result;
END
go

Excel & SQL Server date epochs

Just been caught out by this and so documenting it.

On Excel 2003
1 formatted as date = 1900-01-01

On SQL Server 2000
SELECT CONVERT(CHAR(8), CAST(1 AS DATETIME), 112) = 1900-01-02

Yes, the dates are out by a day.

Yes, this was very frustrating to discover.

EDSK ... you won't get rid of all bugs, add all the features or write all the programs

Every developer should know that you won't get rid of all bugs, add all the features or write all the programs you might want to.


Why is this important?

You need to set limits and prioritise.

There isn't enough time to do everything. So you need to focus on what's important and do that!


There isnt' the time to fix every bug. So fix the ones that cause the biggest problems or affect the most people (or whatever criteria you use to prioritse bugs) first.  By the time you've made those changes, circumstances may be different.

Make sure you're adding the feature which will bring the most benefit.  Not just the one that is easiest to code, or most fun.

Make sure you're creating a new program that is of use, doesn't already exist and will actually benefit others.
 


What do you do once you know this?
Prioritise!
Make sure you're doing something that is worth doing.
Don't do one thing if there's another that is more important.
Understand the costs and benefits of fixing the bug, adding the feature or creating a new program, before you write the code.

prematute optimization

Premature optimization is the root of all evil in programming.

—C.A.R. Hoare