Going to CouchConf 2011

My employer is sending me to CouchConf in NY this year. I will have both laptops with me at the conference and will be taking notes like a man possessed. Now I just need to find some time to set up CouchDB on my Ubuntu netbook before the trip.

More updates to come!

A (Huge) Needle In A Bigger Haystack

Microsoft's Sync Framework is a fragile creature, and as such doesn't like being abused with data. Today I ran into a problem with our app trying to sync a record that had a field of 8266 characters, which is over the (apparent) 8192 character limit. To simplify the problem of finding which record out of millions scattered across hundreds of tables with dozens of columns, I first hit google like any lazy coder.

This thread got me started by giving me a base for searching a single table for the longest column:
http://www.dbforums.com/microsoft-sql-server/1069468-possible-get-max-length-text-field.html

...but I needed more.

So I wrapped that thing in another cursor, and came up with this:

DECLARE @table SYSNAME, @field SYSNAME


DECLARE b_cursor CURSOR STATIC
FOR
SELECT name FROM sys.tables WHERE name not like '%tracking'  -- customize table SELECTion here
OPEN b_cursor
FETCH NEXT FROM b_cursor INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN


CREATE TABLE #Temp (TableName SYSNAME, ColumnName SYSNAME, MaxLength INT)


DECLARE a_cursor CURSOR STATIC
FOR
SELECT name FROM syscolumns WHERE id = object_id(@table) and name like '%history%' -- customize column SELECTion here
OPEN a_cursor


FETCH NEXT FROM a_cursor INTO @field
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp
EXEC ('SELECT ''' + @table + ''', ''' + @field + ''', max(len(' + @field + ')) FROM ' + @table )
FETCH NEXT FROM a_cursor INTO @field
END


CLOSE a_cursor
DEALLOCATE a_cursor


SELECT * FROM #Temp


DROP TABLE #Temp


FETCH NEXT FROM b_cursor INTO @table
END
CLOSE b_cursor
DEALLOCATE b_cursor


--DROP TABLE #Temp


-- select from a table to see the record with the largest field
SELECT * FROM (SELECT Id, len(History) AS len FROM WorkOrders) AS a ORDER BY len DESC

The guts of it is the EXEC statement that queries each column of each table. Note that I've put in comments where you can customize the queries to filter tables and columns by name, etc. At the bottom is a query that shows how to query a table for records and sort by the record size. Emjoy

** Note: If it complains about the table #Temp already existing, just drop it by running the commented drop statement towards the end.

NoSQL

Yes, it was just a matter of time before I was bitten by the NoSQL bug. What's curious is that I've been a long-time SQL proponent (for my young age) and I think I've managed to grok it over the years. However, the more I deal with relational data in tightly constrained schemas, the more ornery this model seems to be. Just the other day I was dealing with some entity relationships in our project. It's fairly basic and important stuff, like Users that have Roles that have Rights, etc. Thanks to the constraints, adding a few users and roles for the testers turned into an all day chore. Once I had finished with it, I handed it off to another guy who would update the whole entity model along with my data. This caused problems with yet more constraints to other entities. In the end, the whole thing needed to be scrapped as too complex, and the test data never got updated.

I've always just accepted the inherent incompatibility between relational databases and object-oriented languages. Now that I've seen another way of doing it I must admit that I'm rather hooked on a "hash in the sky" so to speak. Why does the data store need to care about every minutia in my data types and properties? It would be one thing if I routinely used a fixed number of strictly typed properties in all my objects, but I don't. I usually have at least some optional properties many of which are intentionally ambiguously typed. Given that, why would I choose a data layer that ties me down so drastically?

Query Dynamics GP Configuration

Often in the course of a customer implementation I've found myself thinking "Why don't they keep a list of their important GP setup?" I know the answer to that, but that's another post. I'd really like something like this:

System	SQL Server:	9.00.1399.06, RTM, Developer Edition (64-bit)
TWO	eConnect Version:	10.0.0.0
TWO	Company Name:	Fabrikam, Inc.
TWO	Sitename:	Two , Inc
TWO	Multi-currency:	True
TWO	Functional Currency:	Z-US$
TWO	Functional Currency Format:	#,##0.00
TWO	Functional Currency Decimals:	2
TWO	Default Site:	NORTH
TWO	Multiple Bins:	True
TWO	Qty Shortage Option:	Back Order Balance
TWO	Extended Pricing:	N\A
TWO	SOP Setup Options: Auto-Assign Lot Nums:	False
TWO	SOP Setup Options: Auto-Assign Serial Nums:	True
TWO	Prices Not Required In Price List:	True
TWO	Pickup Ship Methods:	PICKUP,
TWO	Deliver Ship Methods:	COURIER, DROP SHIP, EXPRESS MAIL, OVERNIGHT, LOCAL DELIVERY, MAIL, TWO DAY, GROUND, INTERNATIONAL, NEXT DAY,
TWO	"Use separate Fulfillment Process" Checked:	FUL, SPECORD, ORDMAN,
TWO	"Use separate Fulfillment Process" Un-Checked:	PHNORD, REPORD, STDORD, RMAORD, DISTRIBUTION,

Well, I sat down and wrote a SQL script to show me exactly that! Here it is; hope you find it helpful too.

use TWO


DECLARE @dbname char(30)
set @dbname = DB_NAME()


DECLARE @spGPSettings TABLE (
DBName char(30),
Config char(50),
Value char(500)
);

declare @tempstr varchar(1024)

--DECLARE @sqlstr char(500)
--set @sqlstr = 'use ' + @dbname + ''

-- SQL Server Version
insert into @spGPSettings
SELECT 'System', Config='SQL Server:', Value=rtrim(convert(char, SERVERPROPERTY('productversion'))) + ', ' + rtrim(convert(char, SERVERPROPERTY ('productlevel'))) + ', ' + rtrim(convert(char, SERVERPROPERTY ('edition')))

-- eConnect
declare @spTempTable table (DatabaseName char(20), Version char(20), CompanyName char(50));
insert into @spTempTable
exec DYNAMICS..taeConnectVersionInfoDYNAMICS
insert into @spGPSettings
select @dbname, Config='eConnect Version:', Value=convert(char, Version) from @spTempTable where DatabaseName = @dbname
insert into @spGPSettings
select @dbname, Config='Company Name:', Value=convert(char, CompanyName) from @spTempTable where DatabaseName = @dbname

-- company
insert into @spGPSettings
select @dbname, Config='Sitename:', Value=isnull(rtrim(SITENAME), '') from DYNAMICS..SY03500

-- Multi Currency
if (select count(*) from CM00100 where CURNCYID <> '')>0 begin
insert into @spGPSettings
select @dbname, Config='Multi-currency:', Value='True'
end else if (select count(*) from CM00100 where CURNCYID <> '') = 0 begin
insert into @spGPSettings
select @dbname, Config='Multi-currency:', Value='False'
end

-- Currency Setup
insert into @spGPSettings
select
@dbname, Config='Functional Currency:',
Value=rtrim(isnull(funcCurr.CURNCYID, ''))
from MC40000 as currSetup (nolock)
left join DYNAMICS..MC40200 as funcCurr (nolock) on funcCurr.CURNCYID=currSetup.FUNLCURR

insert into @spGPSettings
select
@dbname, Config='Functional Currency Format:',
Value=cast(replace(rtrim('#,##0 ' + substring('00000',1, isnull((select top 1 DECPLCUR-1 from DYNAMICS..MC40200 as a join MC40000 as b on a.CURRNIDX=b.FUNCRIDX), 2))), ' ', '.') as varchar(15))
from MC40000 as currSetup (nolock)
left join DYNAMICS..MC40200 as funcCurr (nolock) on funcCurr.CURNCYID=currSetup.FUNLCURR

insert into @spGPSettings
select
@dbname, Config='Functional Currency Decimals:',
Value=cast(isnull(funcCurr.DECPLCUR ,3)-1 as char)
from MC40000 as currSetup (nolock)
left join DYNAMICS..MC40200 as funcCurr (nolock) on funcCurr.CURNCYID=currSetup.FUNLCURR

-- default warehouse
insert into @spGPSettings
select @dbname, Config='Default Site:', Value=LOCNCODE from SOP40100


-- multiple bins
insert into @spGPSettings
select @dbname, Config='Multiple Bins:', Value=case ENABLEMULTIBIN when 1 then 'True' else 'False' end from IV40100

-- Qty Shortage Option
insert into @spGPSettings
select @dbname, Config='Qty Shortage Option:', Value=case DFQTYSOP when 1 then 'Override Shortage' when 2 then 'Sell Balance' when 3 then 'Back Order Balance' when 4 then 'Back Order All' when 5 then 'Cancel Balance' when 6 then 'Cancel All' when 7 then 'Distribute' end from SOP40100

-- Extended Price (?)
if (select count(EXPRINST) from DYNAMICS..SY05501 where CMPANYID=(select CMPANYID from DYNAMICS..SY01500 (nolock) where INTERID=@dbname))>0 begin
insert into @spGPSettings
select @dbname, Config='Extended Pricing:', Value='Installed'
end else if (select count(EXPRINST) from DYNAMICS..SY05501 where CMPANYID=(select CMPANYID from DYNAMICS..SY01500 (nolock) where INTERID=@dbname))=0 begin
insert into @spGPSettings
select @dbname, Config='Extended Pricing:', Value='N\A'
end

-- sop setup: auto-assign l/n, s/n
insert into @spGPSettings
select @dbname, Config='SOP Setup Options: Auto-Assign Lot Nums:', Value=case SOPALLOW_3 when 1 then 'True' else 'False' end from SOP40100
insert into @spGPSettings
select @dbname, Config='SOP Setup Options: Auto-Assign Serial Nums:', Value=case SOPALLOW_2 when 1 then 'True' else 'False' end from SOP40100

-- Prices_Not_Required_In_Pr
insert into @spGPSettings
select @dbname, Config='Prices Not Required In Price List:', Value=case Prices_Not_Required_In_Pr when 1 then 'True' else 'False' end from SOP40100 (nolock)

-- Shipping methods
set @tempstr = ''
select @tempstr = coalesce(@tempstr, '', '') + rtrim(SHIPMTHD) + ', '
from SY03000 where SHIPTYPE = 0
insert into @spGPSettings
select @dbname, Config='Pickup Ship Methods:', Value=@tempstr
--
set @tempstr = ''
select @tempstr = coalesce(@tempstr, '', '') + rtrim(SHIPMTHD) + ', '
from SY03000 where SHIPTYPE = 1
insert into @spGPSettings
select @dbname, Config='Deliver Ship Methods:', Value=@tempstr

-- docs with separate fulfillment process
set @tempstr = ''
SELECT @tempstr = coalesce(@tempstr, '', '') + rtrim(DOCID) + ', '
FROM TWO.dbo.SOP40200 WITH ( NOLOCK) WHERE SOPTYPE = 2 and USPFULPR = 1
insert into @spGPSettings
select @dbname, Config='"Use separate Fulfillment Process" Checked:', Value=substring(@tempstr, 0, 500)
--
set @tempstr = ''
SELECT @tempstr = coalesce(@tempstr, '', '') + rtrim(DOCID) + ', '
FROM TWO.dbo.SOP40200 WITH ( NOLOCK) WHERE SOPTYPE = 2 and USPFULPR = 0
insert into @spGPSettings
select @dbname, Config='"Use separate Fulfillment Process" Un-Checked:', Value=substring(@tempstr, 0, 500)

select * from @spGPSettings

Upgrade Woes

First blog post! My goal is to make this an informative and occasionally amusing account of the perils and pitfalls of web and database programming. I work at a small software company where I do a bit of everything, so what better source of inspiration is there?

Just recently, a client of ours requested a software update to address a certain issue. The update went smoothly enough, with the biggest step being the update to their database. We have a utility that automates a Red Gate compare and updates based on differences. Usually this works smoothly and requires only a bit of coaxing once in a while. The only problem is that while the update seemed normal, it isn't smart enough to notice changes in the old version so it overwrites any existing database object with the new version.

Soon, the "bug reports" started rolling in...

To make a long story short, I can't help but wonder if there is a better way to extend a data layer with customer-specific changes. We already have a mechanism which which we extend a few key stored procedures, but it's only a 'if exists run XYZ' call to an extender proc. These extender procedures are ignored during an update and are thus safe from overwriting, but they are of somewhat limited value for coupling in additional functionality. I have begun looking at borrowing a few pages from the OO playbook for this problem. Specifically, I think some good'ole polymorphism would allow customers to extend our product in a safe way.