Dynamics Community Forum Thread Details

I have successfully installed Dynamics GP 10 but I am having problem to "Add sample database" using the GP Utilities. The process always stop at around 25% during the "Load Store Procedures" with the following error:

ERROR [Microsoft][SQL Native Client]COUNT field incorrect or syntax error
The following SQL statement produced an error:

create procedure [dbo].[seeRMAgeTrailBalance] @SORTBY char(15), @i_CUSTOMER_RS char(15) = '', @i_CUSTOMER_RE char(15) = '', @i_CUSTNAME_RS char(65) = '', @i_CUSTNAME_RE char(65) = '', @i_CUSTCLS_RS char(15) = '', @i_CUSTCLS_RE char(15) = '', @i_CUSTTYPE_RS char(21) = '', @i_CUSTTYPE_RE char(21) = '', @i_SLSPERSN_RS char(15) = '', @i_SLSPERSN_RE char(15) = '', @i_SLSTERRY_RS char(15) = '', @i_SLSTERRY_RE char(15) = '', @DETAIL tinyint = 0 as set nocount on declare @AGINGBKLBL1 char(15), @AGINGBKLBL2 char(15), @AGINGBKLBL3 char(15), @AGINGBKLBL4 char(15), @AGINGBKLBL5 char(15), @AGINGBKLBL6 char(15), @AGINGBKLBL7 char(15), @AGINGBKDY1 int, @AGINGBKDY2 int, @AGINGBKDY3 int, @AGINGBKDY4 int, @AGINGBKDY5 int, @AGINGBKDY6 int, @AGINGBKDY7 int, @MAXDAYS int, @MAXINDX tinyint, @AGEBY tinyint, @AGEUNAPPLDCR tinyint, @AGEDATE char(12), @CDATE char(12), @C_CUSTNMBR char(15), @C_RMDTYPAL tinyint, @C_DTDIFF int, @C_DOCDATE datetime, @C_DUEDATE datetime, @C_CURTRXAM numeric(19,5), @C_MY_DEX_ROW int set @AGINGBKLBL1 = '' set @AGINGBKLBL2 = '' set @AGINGBKLBL3 = '' set @AGINGBKLBL4 = '' set @AGINGBKLBL5 = '' set @AGINGBKLBL6 = '' set @AGINGBKLBL7 = '' set @AGINGBKDY1 = 0 set @AGINGBKDY2 = 0 set @AGINGBKDY3 = 0 set @AGINGBKDY4 = 0 set @AGINGBKDY5 = 0 set @AGINGBKDY6 = 0 set @AGINGBKDY7 = 0 set @CDATE = convert(char(12), getdate(), 101) select @AGINGBKLBL1 = RMPERDSC, @AGINGBKDY1 = RMPEREND from RM40201 where INDEX1 = 1 select @AGINGBKLBL2 = RMPERDSC, @AGINGBKDY2 = RMPEREND from RM40201 where INDEX1 = 2 select @AGINGBKLBL3 = RMPERDSC, @AGINGBKDY3 = RMPEREND from RM40201 where INDEX1 = 3 select @AGINGBKLBL4 = RMPERDSC, @AGINGBKDY4 = RMPEREND from RM40201 where INDEX1 = 4 select @AGINGBKLBL5 = RMPERDSC, @AGINGBKDY5 = RMPEREND from RM40201 where INDEX1 = 5 select @AGINGBKLBL6 = RMPERDSC, @AGINGBKDY6 = RMPEREND from RM40201 where INDEX1 = 6 select @AGINGBKLBL7 = RMPERDSC, @AGINGBKDY7 = RMPEREND from RM40201 where INDEX1 = 7 select @MAXDAYS = max(RMPEREND) from RM40201 select @MAXINDX = INDEX1 from RM40201 where RMPEREND = @MAXDAYS set @MAXINDX = @MAXINDX - 1 select @MAXDAYS = RMPEREND from RM40201 where INDEX1 = @MAXINDX select @AGEBY = AGEBY, @AGEUNAPPLDCR = AGEUNAPPLDCR from RM40101 If OBJECT_ID('tempdb..#RMOPENTRX') is NULL Begin create table #RMOPENTRX ( CUSTNMBR char(25) not null default '', DOCNUMBR char(21) not null default '', RMDTYPAL tinyint not null default 0, DOCABREV char(8) not null default '', DOCDATE datetime not null default '01/01/1900', GLPOSTDT datetime not null default '01/01/1900', DUEDATE datetime not null default '01/01/1900', ORTRXAMT numeric(19,5) not null default 0.00, CURTRXAM numeric(19,5) not null default 0.00, WROFAMNT numeric(19,5) not null default 0.00, DISTKNAM numeric(19,5) not null default 0.00, DISAVAMT numeric(19,5) not null default 0.00, DISAVTKN numeric(19,5) not null default 0.00, DISCDATE datetime not null default '01/01/1900', TRXDSCRN char(31) not null default '', CSPORNBR char(21) not null default '', SLPRSNID char(15) not null default '', SALSTERR char(15) not null default '', VOIDSTTS tinyint not null default 0, CURNCYID char(15) not null default '', PYMTRMID char(21) not null default '', SHIPMTHD char(15) not null default '', VOIDDATE datetime not null default '01/01/1900', CUSTNAME char(65) not null default '', CUSTCLAS char(15) not null default '', BALNCTYP tinyint not null default 0, LASTAGED datetime not null default '01/01/1900', USERDEF1 char(50) not null default '', CNTCPRSN char(65) not null default '', PHONE1 char(31) not null default '', CRLMTAMT numeric(19,5) not null default 0.00, CRDTSTRG char(200) not null default '', CRLMTPER tinyint not null default 0, CRLMTPAM numeric(19,5) not null default 0.00, NUMOFDYS int not null default 0, AGINGBK1 numeric(19,5) not null default 0.00, AGINGBK2 numeric(19,5) not null default 0.00, AGINGBK3 numeric(19,5) not null default 0.00, AGINGBK4 numeric(19,5) not null default 0.00, AGINGBK5 numeric(19,5) not null default 0.00, AGINGBK6 numeric(19,5) not null default 0.00, AGINGBK7 numeric(19,5) not null default 0.00, MY_DEX_ROW int Identity (1,1) ) End delete #RMOPENTRX If (@i_SLSPERSN_RE = 'þþþþþþþþþþþþþþþ' and @i_SLSTERRY_RE = 'þþþþþþþþþþþþþþþ') Begin insert into #RMOPENTRX (CUSTNMBR,DOCNUMBR,RMDTYPAL,DOCDATE,GLPOSTDT,DUEDATE,ORTRXAMT,CURTRXAM,WROFAMNT,DISTKNAM,DISAVAMT,DISAVTKN,DISCDATE,TRXDSCRN,CSPORNBR,SLPRSNID, VOIDSTTS,CURNCYID,PYMTRMID,SHIPMTHD,VOIDDATE, CUSTNAME,CUSTCLAS,BALNCTYP,USERDEF1,CNTCPRSN,PHONE1,CRLMTAMT,CRLMTPER,CRLMTPAM, CRDTSTRG) select a.CUSTNMBR,a.DOCNUMBR,a.RMDTYPAL,a.DOCDATE,a.GLPOSTDT,a.DUEDATE,a.ORTRXAMT,a.CURTRXAM,a.WROFAMNT,a.DISTKNAM,a.DISAVAMT,a.DISAVTKN,a.DISCDATE,a.TRXDSCRN,a.CSPORNBR,a.SLPRSNID, a.VOIDSTTS,a.CURNCYID,a.PYMTRMID,a.SHIPMTHD,a.VOIDDATE, b.CUSTNAME,b.CUSTCLAS,b.BALNCTYP,b.USERDEF1,b.CNTCPRSN,b.PHONE1,b.CRLMTAMT,b.CRLMTPER,b.CRLMTPAM, '$' + rtrim(cast(cast(b.CRLMTAMT as numeric(19,2)) as char(15))) + ' - Or if the Sum of Period ' + cast(b.CRLMTPER as char(1)) + ' and Beyond Exceeds ' + rtrim(cast(b.CRLMTPAM as char(15))) from RM20101 a, RM00101 b where a.CUSTNMBR = b.CUSTNMBR and a.CUSTNMBR between @i_CUSTOMER_RS and @i_CUSTOMER_RE and b.CUSTNAME between @i_CUSTNAME_RS and @i_CUSTNAME_RE and b.CUSTCLAS between @i_CUSTCLS_RS and @i_CUSTCLS_RE and b.USERDEF1 between @i_CUSTTYPE_RS and @i_CUSTTYPE_RE order by a.CUSTNMBR, a.DOCDATE ASC End Else Begin insert into #RMOPENTRX (CUSTNMBR,DOCNUMBR,RMDTYPAL,DOCDATE,GLPOSTDT,DUEDATE,ORTRXAMT,CURTRXAM,WROFAMNT,DISTKNAM,DISAVAMT,DISAVTKN,DISCDATE,TRXDSCRN,CSPORNBR,SLPRSNID, VOIDSTTS,CURNCYID,PYMTRMID,SHIPMTHD,VOIDDATE, CUSTNAME,CUSTCLAS,BALNCTYP,USERDEF1,CNTCPRSN,PHONE1,CRLMTAMT,CRLMTPER,CRLMTPAM, CRDTSTRG) select a.CUSTNMBR,a.DOCNUMBR,a.RMDTYPAL,a.DOCDATE,a.GLPOSTDT,a.DUEDATE,a.ORTRXAMT,a.CURTRXAM,a.WROFAMNT,a.DISTKNAM,a.DISAVAMT,a.DISAVTKN,a.DISCDATE,a.TRXDSCRN,a.CSPORNBR,a.SLPRSNID, a.VOIDSTTS,a.CURNCYID,a.PYMTRMID,a.SHIPMTHD,a.VOIDDATE, b.CUSTNAME,b.CUSTCLAS,b.BALNCTYP,b.USERDEF1,b.CNTCPRSN,b.PHONE1,b.CRLMTAMT,b.CRLMTPER,b.CRLMTPAM, '$' + rtrim(cast(cast(b.CRLMTAMT as numeric(19,2)) as char(15))) + ' - Or if the Sum of Period ' + cast(b.CRLMTPER as char(1)) + ' and Beyond Exceeds ' + rtrim(cast(b.CRLMTPAM as char(15))) from RM20101 a, RM00101 b, RM00301 c, RM00303 d where a.CUSTNMBR = b.CUSTNMBR and a.SLPRSNID = c.SLPRSNID and c.SALSTERR = d.SALSTERR and a.CUSTNMBR between @i_CUSTOMER_RS and @i_CUSTOMER_RE and b.CUSTNAME between @i_CUSTNAME_RS and @i_CUSTNAME_RE and b.CUSTCLAS between @i_CUSTCLS_RS and @i_CUSTCLS_RE and b.USERDEF1 between @i_CUSTTYPE_RS and @i_CUSTTYPE_RE and c.SLPRSNID between @i_SLSPERSN_RS and @i_SLSPERSN_RE and d.SALSTERR between @i_SLSTERRY_RS and @i_SLSTERRY_RE order by a.CUSTNMBR, a.DOCDATE ASC End update a set a.LASTAGED = b.LASTAGED from #RMOPENTRX a, RM00103 b where a.CUSTNMBR = b.CUSTNMBR update a set a.SLPRSNID = b.SLPRSNID from #RMOPENTRX a, RM00101 b where a.CUSTNMBR = b.CUSTNMBR update a set a.SALSTERR = b.SALSTERR from #RMOPENTRX a, RM00301 b where a.SLPRSNID = b.SLPRSNID update a set a.DOCABREV = b.DOCABREV from #RMOPENTRX a, RM40401 b where a.RMDTYPAL = b.RMDTYPAL update #RMOPENTRX set PHONE1 = '(' + substring(PHONE1,1,3) + ')' + substring(PHONE1,4,3) + '-' + substring(PHONE1,7,4) + ' Ext. ' + substring(PHONE1,11,4) DECLARE TRX_RANGE CURSOR FOR SELECT CUSTNMBR, RMDTYPAL, DOCDATE, DUEDATE, CURTRXAM, MY_DEX_ROW FROM #RMOPENTRX order by MY_DEX_ROW OPEN TRX_RANGE FETCH NEXT FROM TRX_RANGE INTO @C_CUSTNMBR, @C_RMDTYPAL, @C_DOCDATE, @C_DUEDATE, @C_CURTRXAM, @C_MY_DEX_ROW WHILE @@FETCH_STATUS = 0 Begin select @AGEDATE = LASTAGED from RM00103 where CUSTNMBR = @C_CUSTNMBR If @AGEBY = 1 Begin If (@C_DUEDATE > @AGEDATE) Begin set @C_DTDIFF = 0 End Else If (@AGEUNAPPLDCR = 0 and @C_RMDTYPAL >= 7) Begin set @C_DTDIFF = 0 End Else If (@AGEUNAPPLDCR = 1 and @C_DUEDATE = '01/01/1900' and @C_RMDTYPAL >= 7) Begin set @C_DTDIFF = 0 End Else Begin SELECT @C_DTDIFF = DATEDIFF(day, @C_DOCDATE, @AGEDATE) End End Else Begin If (@C_DUEDATE > @AGEDATE) Begin set @C_DTDIFF = 0 End Else If (@AGEUNAPPLDCR = 0 and @C_RMDTYPAL >= 7) Begin set @C_DTDIFF = 0 End Else If (@AGEUNAPPLDCR = 1 and @C_DUEDATE = '01/01/1900' and @C_RMDTYPAL >= 7) Begin set @C_DTDIFF = 0 End Else Begin SELECT @C_DTDIFF = DATEDIFF(day, @C_DUEDATE, @AGEDATE) End End If @C_RMDTYPAL >= 7 Begin set @C_CURTRXAM = @C_CURTRXAM * -1 End If (@C_DTDIFF = 0 ) Begin update #RMOPENTRX set AGINGBK1 = @C_CURTRXAM, NUMOFDYS = @C_DTDIFF where MY_DEX_ROW = @C_MY_DEX_ROW set @C_DTDIFF = 0 FETCH NEXT FROM TRX_RANGE INTO @C_CUSTNMBR, @C_RMDTYPAL, @C_DOCDATE, @C_DUEDATE, @C_CURTRXAM, @C_MY_DEX_ROW Continue End If ((@C_DTDIFF <= @AGINGBKDY2) or (@C_DTDIFF > @MAXDAYS and @AGINGBKDY2 > @MAXDAYS)) Begin update #RMOPENTRX set AGINGBK2 = @C_CURTRXAM, NUMOFDYS = @C_DTDIFF where MY_DEX_ROW = @C_MY_DEX_ROW set @C_DTDIFF = 0 FETCH NEXT FROM TRX_RANGE INTO @C_CUSTNMBR, @C_RMDTYPAL, @C_DOCDATE, @C_DUEDATE, @C_CURTRXAM, @C_MY_DEX_ROW Continue End If ((@C_DTDIFF <= @AGINGBKDY3) or (@C_DTDIFF > @MAXDAYS and @AGINGBKDY3 > @MAXDAYS)) Begin update #RMOPENTRX set AGINGBK3 = @C_CURTRXAM, NUMOFDYS = @C_DTDIFF where MY_DEX_ROW = @C_MY_DEX_ROW set @C_DTDIFF = 0 FETCH NEXT FROM TRX_RANGE INTO @C_CUSTNMBR, @C_RMDTYPAL, @C_DOCDATE, @C_DUEDATE, @C_CURTRXAM, @C_MY_DEX_ROW Continue End If ((@C_DTDIFF <= @AGINGBKDY4) or (@C_DTDIFF > @MAXDAYS and @AGINGBKDY4 > @MAXDAYS)) Begin update #RMOPENTRX set AGINGBK4 = @C_CURTRXAM, NUMOFDYS = @C_DTDIFF where MY_DEX_ROW = @C_MY_DEX_ROW set @C_DTDIFF = 0 FETCH NEXT FROM TRX_RANGE INTO @C_CUSTNMBR, @C_RMDTYPAL, @C_DOCDATE, @C_DUEDATE, @C_CURTRXAM, @C_MY_DEX_ROW Continue End If ((@C_DTDIFF <= @AGINGBKDY5) or (@C_DTDIFF > @MAXDAYS and @AGINGBKDY5 > @MAXDAYS)) Begin update #RMOPENTRX set AGINGBK5 = @C_CURTRXAM, NUMOFDYS = @C_DTDIFF where MY_DEX_ROW = @C_MY_DEX_ROW set @C_DTDIFF = 0 FETCH NEXT FROM TRX_RANGE INTO @C_CUSTNMBR, @C_RMDTYPAL, @C_DOCDATE, @C_DUEDATE, @C_CURTRXAM, @C_MY_DEX_ROW Continue End If ((@C_DTDIFF <= @AGINGBKDY6) or (@C_DTDIFF > @MAXDAYS and @AGINGBKDY6 > @MAXDAYS)) Begin update #RMOPENTRX set AGINGBK6 = @C_CURTRXAM, NUMOFDYS = @C_DTDIFF where MY_DEX_ROW = @C_MY_DEX_ROW set @C_DTDIFF = 0 FETCH NEXT FROM TRX_RANGE INTO @C_CUSTNMBR, @C_RMDTYPAL, @C_DOCDATE, @C_DUEDATE, @C_CURTRXAM, @C_MY_DEX_ROW Continue End Else Begin update #RMOPENTRX set AGINGBK7 = @C_CURTRXAM, NUMOFDYS = @C_DTDIFF where MY_DEX_ROW = @C_MY_DEX_ROW set @C_DTDIFF = 0 FETCH NEXT FROM TRX_RANGE INTO @C_CUSTNMBR, @C_RMDTYPAL, @C_DOCDATE, @C_DUEDATE, @C_CURTRXAM, @C_MY_DEX_ROW Continue End FETCH NEXT FROM TRX_RANGE INTO @C_CUSTNMBR, @C_RMDTYPAL, @C_DOCDATE, @C_DUEDATE, @C_CURTRXAM, @C_MY_DEX_ROW End CLOSE TRX_RANGE DEALLOCATE TRX_RANGE select * from #RMOPENTRX order by CUSTNMBR, RMDTYPAL

I have googled and read through all the relevant documentations but none provides the reason and solution.

There's nothing wrong with the syntax of the "seeRMAgeTrailBalance" procedure as it executed successfully in the SQL Server Management Studio Query Analyzer.

I tried installing Dynamics GP v10 on various test machines running WinXPPro as well as Windows 2003 Server (x86 & x64 with SP1, SP2 and SP3 variants) but none help resolving the problem.

I also tried linking Dynamics GP v10 with various SQL2005 and SQL2008 installations running on Windows 2003 Server (again with SP1, SP2 and the latest patches) but nothing help.

Please help and thank you!

Dynamics GP Utilities v10 problem with "adding sample database" due to COUNT field error

Helpful resources

News and Announcements

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Community AMA December 12th

Leaderboard

Featured topics

Product updates