Upgrade Woes, Take 2

I've been once again been put into a tough spot by our upgrade policy. Just recently I had to upgrade a client to a build not even a few weeks old in order to give them a bug fix. In case you're wondering why we didn't just patch the branch that they were on, it's because we don't generally do this for branches more than one or two months old. I think this is silly, considering that other ISVs will support a branch with patches and fixes for months or even years. I can't think of another software product released on such an insane build schedule as ours.

Anyway..

Today the customer called in (for the 4th time this week) with another interesting bug. After spending about an hour tweaking a stored proc thinking it was a change I had made two days ago, another guy came and told me that it's likely a code change he included in a couple of builds. It comes as a *complete* surprise that there might be bugs in a new, untested build. The fix, of course, is to put them on a newer build. This build has had even fewer minutes of testing. Any guesses on where this is going?

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