PSTL 18.2.5 throws an error at the end of setup in GP 18.3.1245

(0) Share

Report

Posted on by Beat Bucher GP Gee...

28,021 Super User 2024 Season 1

Hi All,

I recently run into some weird error messages after adding a few new companies to a customer site which is running GP 18.3.1245.

The latest PSTL module was downloaded already for the previous companies and weirdly enough was showing version discrepancies between some of the older companies and the most recent ones..

This was pulled from the TA00860 table in DYNAMICS system DB.

The error messages I'm getting varies from company to company, in some cases the process for initialisation runs to the end, but throws a grant error (likely), though I've re-run the GRANT.SQL script against those companies..

In other companies, the process fails at 24% and then is stuck there and never completes.

right after when clicking on OK:

I re-installed the 18.2.5 PSTL chunk file and re-run the initializations of the objects in the companies (rebuild procs), but the error would come back anyway.

I'm wondering about that "smChangeAccountIndex2" stored procs that fails every time on some companies and can't get past it.. in some older companies, there is a bunch of SP's that are encrypted and seem to part of the PSTL setup, but with an older creation date.. I'm wondering if there isn't something missing from the most recent PSTL chunk file, and thus not created in the newer companies.. This GP instance was built back in 2020 under GP 18.2 and PSTL 18.2.1

After it got upgraded to GP 18.3, the PSTL was upgraded too, but it looks like that the encrypted SP's never got updated.. and in the new companies, are completely missing..

Thanks for any insights on validating this.

All responses (14)

Answers (1)

Suggested answer

Derek Albaugh on at

Like (0)

Report

RE: PSTL 18.2.5 throws an error at the end of setup in GP 18.3.1245

Interesting find and good to know......imagine you will save someone else who stumbles onto this issue.....always something you least suspect.

Thanks
Verified answer

Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at

Like (1)

Report

RE: PSTL 18.2.5 throws an error at the end of setup in GP 18.3.1245

Derek,

I think I found the culprit... the 2 ANSI settings in the ODBC setup.. Somehow those were checked off in the ODBC setup to connect to the SQL server, plus another one on the last tab (Perform Translation for character data). After I unchecked those options and restarted the GP client, running Rebuild Procs on a good company was finally going completely to the end without any warning message and it tooks also much longer to process, which I always found was too quick for my recalling..

Running the Rebuild Procs on a failing company (newly created) would do the same and complete the process without any errors.

This past thread put me on the right path:

community.dynamics.com/.../966787

Thanks for your time and support on this insidious bug..
Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at

Like (0)

Report

RE: PSTL 18.2.5 throws an error at the end of setup in GP 18.3.1245

The DEXSQL was captured while using the "Rebuild Procs" function in PSTL.. I don't have any other scripts to re-create objects, aside of missing SP's that are not encrypted.

I did re-add to the DYNAMICS DB the SP like "macGrantAll" that was missing after the clean-up and wasn't rebuilt yet.. Still PSTL would throw the error at the end of the process after rebuilding the SP's.

The fact that SQL throws this error about the max length of 128 seems to be an indicator related to the general SQL setup and I'll have to dig into that area.
Suggested answer

Derek Albaugh on at

Like (0)

Report

RE: PSTL 18.2.5 throws an error at the end of setup in GP 18.3.1245

This is when using the 'Rebuild Procs' or are you using scripts to manually add the objects?

Looking at a dexsql.log when I initialized PSTL on a new company database, the script looks the same as what you have, but I don't get any "Maximum length is 128" error.

use TWO declare @iStatus int, @O_SQL_Error_State int EXEC @iStatus = taRunProcedure"if exists (select * from sysobjects where id = object_id('dbo.smCustomerChanger1') and sysstat & 0xf = 4) begin drop procedure smCustomerChanger1 end ",' ', @O_SQL_Error_State

Rebuilding procs, I still don't see this error, nor could I find any mention of it, though it is definitely a SQL error, but odd as smCustomerChanger1 is nowhere near 28.
Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at

Like (0)

Report

RE: PSTL 18.2.5 throws an error at the end of setup in GP 18.3.1245

I might have a beginning of a hint on the problem.. maybe I'm just wrong. I re-ran the PSTL create objects on a company where it was ok, but still throws the error at the end about the grant macro not found.. and the code 103. It seems to throw this error quite often in the DEXSQL log I captured..

/* Date: 07/14/2021 Time: 12:07:00

SQLSTATE:(37000) Native Err:(103) stmt(160992896):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]The identifier that starts with 'if exists (select * from dbo.sysobjects where id = Object_id('dbo.taRMCustMstrPeriodSumDetail') and type = 'P') begin drop proc ' is too long. Maximum length is 128.*/

/* Date: 07/14/2021 Time: 12:07:00

stmt(160992896):*/

use CC declare @iStatus int, @O_SQL_Error_State int EXEC @iStatus = taRunProcedure"if exists (select * from dbo.sysobjects where id = Object_id('dbo.taRMCustMstrPeriodSum') and type = 'P') begin drop proc dbo.taRMCustMstrPeriodSum end ",'', @O_SQL_Error_State

/*

/* Date: 07/14/2021 Time: 12:07:00

SQLSTATE:(37000) Native Err:(103) stmt(160992896):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]The identifier that starts with 'if exists (select * from dbo.sysobjects where id = Object_id('dbo.taRMCustMstrPeriodSum') and type = 'P') begin drop proc dbo.ta' is too long. Maximum length is 128.*/

/*

/* Date: 07/14/2021 Time: 12:07:00

SQLSTATE:(00000) Native Err:(103) stmt(160992896):*/

*/

/* Date: 07/14/2021 Time: 12:06:50

stmt(160992896):*/

use CC declare @iStatus int, @O_SQL_Error_State int EXEC @iStatus = taRunProcedure"if exists (select * from sysobjects where id = object_id('dbo.smCustomerChanger1') and sysstat & 0xf = 4) begin drop procedure dbo.smCustomerChanger1 end ",'', @O_SQL_Error_State

/*

/* Date: 07/14/2021 Time: 12:06:50

SQLSTATE:(37000) Native Err:(103) stmt(160992896):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]The identifier that starts with 'if exists (select * from sysobjects where id = object_id('dbo.smCustomerChanger1') and sysstat & 0xf = 4) begin drop procedure d' is too long. Maximum length is 128.*/

/*

/* Date: 07/14/2021 Time: 12:06:50

SQLSTATE:(00000) Native Err:(103) stmt(160992896):*/

What is this max length of 128 ?
Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at

Like (0)

Report

RE: PSTL 18.2.5 throws an error at the end of setup in GP 18.3.1245

I just created a new company for another customer (unrelated) that is on GP 18.3.1200 and has PSTL 18.2.5 installed too..

The triggering of the PSTL setup in the new company generated 103 new SP's and 7 new tables in the company DB.

This confirms that the issue is somewhat with the GP setup on that other customer that prevents encrypted objects from being created and causes the PSTL setup to abort.
Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at

Like (0)

Report

RE: PSTL 18.2.5 throws an error at the end of setup in GP 18.3.1245

Derek,

The PSTL download link provided is the same version I'm already using..

I've been checking back to one of my own demo GP 18.3 environment which was setup using PSTL 18.2.5 too and it has all the objects from your list.. with exception of 2 tables in the company DB (TWO)

DROP TABLE WHPSETP

DROP TABLE WHPT001

and the related SP's:

DROP PROCEDURE zDP_WHPSETPF_1

DROP PROCEDURE zDP_WHPSETPL_1

DROP PROCEDURE zDP_WHPSETPN_1

DROP PROCEDURE zDP_WHPSETPSD

DROP PROCEDURE zDP_WHPSETPSI

DROP PROCEDURE zDP_WHPSETPSS_1

DROP PROCEDURE zDP_WHPT001F_1

DROP PROCEDURE zDP_WHPT001F_2

DROP PROCEDURE zDP_WHPT001L_1

DROP PROCEDURE zDP_WHPT001L_2

DROP PROCEDURE zDP_WHPT001N_1

DROP PROCEDURE zDP_WHPT001N_2

DROP PROCEDURE zDP_WHPT001SD

DROP PROCEDURE zDP_WHPT001SI

DROP PROCEDURE zDP_WHPT001SS_1

DROP PROCEDURE zDP_WHPT001SS_2

Maybe those objects belong to a module that isn't installed in GP..

On the DYNAMICS DB side, there is no objects missing from the script list..

for now I can't phantom as why PSTL would not be able to re-create the 'encrypted' SP's in either the System or company DB..

I'll do some more testing on my own 18.3 demo system, not wanting to risk the integrity of the customer setup which is already flaky on that side.
Suggested answer

Derek Albaugh on at

Like (0)

Report

RE: PSTL 18.2.5 throws an error at the end of setup in GP 18.3.1245

On my 18.3.1245 environment, I have the 5 TA_Parent.... procedures, taRunProcedure procedure, macGrantAll procedure and smUserWorkCopy1 procedure.

The same on my 18.3.1290 environment.

I'm fairly sure my 18.3.1245 is an upgrade from prior versions of GP 2018, 18.2 and 18.3, but not 100% at what version PSTL was first initialized and/or had procedures rebuilt.

With the encrypted procedures, I don't have scripts readily available either to manually create them.

I'm curious if the 18.3 version of PSTL from this site would resolve the issues you're seeing?

docs.microsoft.com/.../NOAM_PSTL_delta

Thanks
Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at

Like (0)

Report

RE: PSTL 18.2.5 throws an error at the end of setup in GP 18.3.1245

Here is another thing I noticed once the PSTL 18.2.5 was re-installed and the DYNAMICS..TA00860 had been wiped out, I went into various companies.. the old ones would run thru, but throw the pop-up about the grant script..

The newer companies would still all fail at 24%.

From the Scripts you posted here, the system DB script shows in SSMS that some objects are missing:

As well for the company DB script, there are several dozens of objects missing.. even a few zDP_'s at the end that I didn't capture here..

I'm wondering why all those objects are not created and what keeps them from being added to a company DB.. All sessions are executed under the 'sa' account and DYNSA is the owner of the GP companies..
Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at

Like (0)

Report

RE: PSTL 18.2.5 throws an error at the end of setup in GP 18.3.1245

Derek,

Tried them all.. first I'd remove all the PSTL objects from a non-working company and re-run the PSTL which would throw the usual error at 24% (object #28 in the run).

What I noticed this time is that there was a hidden pop-up behind the error message which I hadn't seen the previous times..

I tried to clean up all the PSTL objects from the DYNAMICS & the company DB's, installed the previous build 18.2.4, and it would fail exactly at the same place.. always stumbling on that "smChangeAccountIndex2", likely one of the 1st encrypted SP's for PSTL.

I even tried to run thru the add/remove program the re-installation of the PSTL, which then put back vers 18.2.1, which is incompatible with this version of GP.

After I got that removed and replaced as well by 18.2.5 again, it would run when I hit PSTL in GP and it would fail again with the same error as above..

It totally looks like that PSTL is unable to create some encrypted SP's.. I'm wondering if that couldn't be related to some SQL settings.. the server is running SQL 2016 SP2.