Sage 100

I'm just wondering if anyone could shed some light on this question, does the reinitialize data files do anything other than delete the SQL table and recreate it? I'm attempting to automate a resolution that requires me to reinitialize two work files for a premium client, but I cant seem to get the utility to let me select them using VBS (so this would also be another valid option). I can however run SQL queries using VBS very easily.

------------------------------
Jeremy Fox
Velosio LLC
------------------------------

"delete from tablename" should do it... I use SQLCMD for executing SQL from outside of Sage.  (As always, use direct data edits with extreme caution and test thoroughly before running Live).
Work tables should always be blank though... they're created as temporary tables in tempdb.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Jeremy - Can I ask which KB article resolution you are trying to automate? To answer your question in a word: YES. E.g. When Reinit'ing A/R Cash Receipts data entry tables, when you select AR_CashReceiptsDetail it will update AR_OpenInvoice to zero out the PaidToday column where PaidToday > 0 (which happens in the normal course of Cash Receipts Entry). Same thing with AP_OpenInvoice when you select AP_CheckDetail.

I'm not sure if you were trying to do this anyway with your VBS but it's better to use sy_reinitialize_bus with BOI to accomplish your task, as long as you know what you are doing and have tested and re-tested your solution. This way you don't have to worry about the Updates on ancillary tables and your code doesn't have to veer outside the object (no direct T-SQL required). I posted a response on how to do this a loooong time ago so maybe you'll find it with a search (try "sy_reinitialize_bus" )

About the WRK tables the base tables are empty. The ones with the Session ID appended to the table name have the data in them but in Premium those are created in TempDB like Kevin mentioned.

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA
------------------------------

@Alnoor Cassim honestly I'd prefer the boi approach, but I'm just not sure how to actually pull it off for this conversation.  KB76175 (and 69387 but i have that part worked out) the issue I'm having is with the Reinitialize of the IM_TransactionRegisterWrk and IM_transactionRestart. I honestly assumed reinitliazing files would be in the same vain of a rebuild sort/key files which previously was saying we couldn't really do directly by scripting. (i have pulled that off with autohotkey though).  

now I expect that the next question is why it's needed regularly or why its needed in premium, the client is integrating with Pallet Track using VI scripts so I suspect that its somehow an issue with the "timing". though the short answer is roughly once every two months I need to "rebuild" the tables using the method Sage prescribes for premium and then reinitializeing those two and it works like a charm.

------------------------------
Jeremy Fox
Velosio LLC
------------------------------

Original Message:
Sent: 03-13-2019 01:19
From: Alnoor Cassim
Subject: Sage 100 Premium reinitialize Data Files

Jeremy - Can I ask which KB article resolution you are trying to automate? To answer your question in a word: YES. E.g. When Reinit'ing A/R Cash Receipts data entry tables, when you select AR_CashReceiptsDetail it will update AR_OpenInvoice to zero out the PaidToday column where PaidToday > 0 (which happens in the normal course of Cash Receipts Entry). Same thing with AP_OpenInvoice when you select AP_CheckDetail.

I'm not sure if you were trying to do this anyway with your VBS but it's better to use sy_reinitialize_bus with BOI to accomplish your task, as long as you know what you are doing and have tested and re-tested your solution. This way you don't have to worry about the Updates on ancillary tables and your code doesn't have to veer outside the object (no direct T-SQL required). I posted a response on how to do this a loooong time ago so maybe you'll find it with a search (try "sy_reinitialize_bus" )

About the WRK tables the base tables are empty. The ones with the Session ID appended to the table name have the data in them but in Premium those are created in TempDB like Kevin mentioned.

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA
------------------------------

Thanks Jeremy for the response and sharing those articles. I looked at the How To Rebuild Sage 100 Premium Tables article and wow if I was still at Sage I would have approached that differently. It doesn't address the normal root cause of that issue. I'm very pro-Premium / SQL (even sat as a panelist on the MotM SQL presentation too with @Steve Iwanowski  , @Robert White , and @Jim Woodhead ) but my first approach always leans towards a solution that is platform agnostic that could work on Premium, Advanced, and Standard. It sounds to me like this could be the classic case of the dictionary not being in alignment with the physical table. If that is true, there is a command you can run for that (a 1-liner) and for Premium additional steps could be added. 

How about this - Even if you don't use / need it, I'll post a sample of how ​​​​to do this "rebuild" (with no T-SQL) and how to do the BOI / VBS version of this Error 47 fix (which also has no T-SQL and can deal with the Wrk and Restart tables), but in return I would ask you a favor of sorts. If you can accept my contact request, I'll explain it there.​​

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA
------------------------------

Original Message:
Sent: 03-14-2019 13:17
From: Jeremy Fox
Subject: Sage 100 Premium reinitialize Data Files

@Alnoor Cassim honestly I'd prefer the boi approach, but I'm just not sure how to actually pull it off for this conversation.  KB76175 (and 69387 but i have that part worked out) the issue I'm having is with the Reinitialize of the IM_TransactionRegisterWrk and IM_transactionRestart. I honestly assumed reinitliazing files would be in the same vain of a rebuild sort/key files which previously was saying we couldn't really do directly by scripting. (i have pulled that off with autohotkey though).

now I expect that the next question is why it's needed regularly or why its needed in premium, the client is integrating with Pallet Track using VI scripts so I suspect that its somehow an issue with the "timing". though the short answer is roughly once every two months I need to "rebuild" the tables using the method Sage prescribes for premium and then reinitializeing those two and it works like a charm.

------------------------------
Jeremy Fox
Velosio LLC
------------------------------


Original Message:
Sent: 03-13-2019 01:19
From: Alnoor Cassim
Subject: Sage 100 Premium reinitialize Data Files

Jeremy - Can I ask which KB article resolution you are trying to automate? To answer your question in a word: YES. E.g. When Reinit'ing A/R Cash Receipts data entry tables, when you select AR_CashReceiptsDetail it will update AR_OpenInvoice to zero out the PaidToday column where PaidToday > 0 (which happens in the normal course of Cash Receipts Entry). Same thing with AP_OpenInvoice when you select AP_CheckDetail.

I'm not sure if you were trying to do this anyway with your VBS but it's better to use sy_reinitialize_bus with BOI to accomplish your task, as long as you know what you are doing and have tested and re-tested your solution. This way you don't have to worry about the Updates on ancillary tables and your code doesn't have to veer outside the object (no direct T-SQL required). I posted a response on how to do this a loooong time ago so maybe you'll find it with a search (try "sy_reinitialize_bus" )

About the WRK tables the base tables are empty. The ones with the Session ID appended to the table name have the data in them but in Premium those are created in TempDB like Kevin mentioned.

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA

That Sage Rebuild method looks iffy to me.  If you drop and re-create the table, you'll lose your indexes (and any custom triggers).  And if the data is corrupt but valid-enough to still accept the data type, it'll just copy the corruption back in.  If it's too corrupt, you'll lose the whole record (maybe this is how ProvideX already works?).    Not sure how much you really gain with that approach...

------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
------------------------------

Original Message:
Sent: 03-14-2019 14:53
From: Alnoor Cassim
Subject: Sage 100 Premium reinitialize Data Files

Thanks Jeremy for the response and sharing those articles. I looked at the How To Rebuild Sage 100 Premium Tables article and wow if I was still at Sage I would have approached that differently. It is not a bad approach, creative in fact but I think it could also address the normal root cause of that issue. I'm very pro-Premium / SQL (even sat as a panelist on the MotM SQL presentation too with @Steve Iwanowski  , @Robert White , and @Jim Woodhead ) but my first approach always leans towards a solution that is platform agnostic that could work on Premium, Advanced, and Standard. It sounds to me like this could be the classic case of the dictionary not being in alignment with the physical table. If that is true, there is a command you can run for that (a 1-liner) and for Premium additional steps could be added.

How about this - Even if you don't use / need it, I'll post a sample of how ​​​​to do this "rebuild" (with no T-SQL) and how to do the BOI / VBS version of this Error 47 fix (which also has no T-SQL and can deal with the Wrk and Restart tables), but in return I would ask you a favor of sorts. If you can accept my contact request, I'll explain it there.​​

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA
------------------------------


Original Message:
Sent: 03-14-2019 13:17
From: Jeremy Fox
Subject: Sage 100 Premium reinitialize Data Files

@Alnoor Cassim honestly I'd prefer the boi approach, but I'm just not sure how to actually pull it off for this conversation.  KB76175 (and 69387 but i have that part worked out) the issue I'm having is with the Reinitialize of the IM_TransactionRegisterWrk and IM_transactionRestart. I honestly assumed reinitliazing files would be in the same vain of a rebuild sort/key files which previously was saying we couldn't really do directly by scripting. (i have pulled that off with autohotkey though).

now I expect that the next question is why it's needed regularly or why its needed in premium, the client is integrating with Pallet Track using VI scripts so I suspect that its somehow an issue with the "timing". though the short answer is roughly once every two months I need to "rebuild" the tables using the method Sage prescribes for premium and then reinitializeing those two and it works like a charm.

------------------------------
Jeremy Fox
Velosio LLC


Original Message:
Sent: 03-13-2019 01:19
From: Alnoor Cassim
Subject: Sage 100 Premium reinitialize Data Files

Jeremy - Can I ask which KB article resolution you are trying to automate? To answer your question in a word: YES. E.g. When Reinit'ing A/R Cash Receipts data entry tables, when you select AR_CashReceiptsDetail it will update AR_OpenInvoice to zero out the PaidToday column where PaidToday > 0 (which happens in the normal course of Cash Receipts Entry). Same thing with AP_OpenInvoice when you select AP_CheckDetail.

I'm not sure if you were trying to do this anyway with your VBS but it's better to use sy_reinitialize_bus with BOI to accomplish your task, as long as you know what you are doing and have tested and re-tested your solution. This way you don't have to worry about the Updates on ancillary tables and your code doesn't have to veer outside the object (no direct T-SQL required). I posted a response on how to do this a loooong time ago so maybe you'll find it with a search (try "sy_reinitialize_bus" )

About the WRK tables the base tables are empty. The ones with the Session ID appended to the table name have the data in them but in Premium those are created in TempDB like Kevin mentioned.

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA

More than iffy Steve. This article was definitely not ready to publish because it wasn't verified by someone who knows Premium/SQL. You're completely right about losing the indexes! That is not just an inconvenience or loss of some speed issue. The 1st index is a primary key index (clustered too), meaning without that KPRIMARY, whenever Sage attempts to UPDATE an existing row / record on the table, it will instead INSERT and create a new one, and now you have a duplicate record problem. 

Using the CreateTable() method via BOI or even SYZCON will create / update the table according to the dictionary which does include the indexes. There is another way too to effect that too. AND you're right about losing the user defined triggers! And yes agree about the data corruption just being copied back in. 

Originally thought primary underlying cause would be dictionary not aligning to physical table. E.g. In past at least, with Premium adding UDFs through UDF Maint when there are 100+ company codes, will not add the UDF columns to the table after X number of companies have been processed where X is around 100.

 BUT in re-thinking this from what you said, if Support is seeing these "hard errors" go away with this resolution which removes indexes, that leads us to believe the indexes simply need to be rebuilt or reorganized, meaning what they should have said INSTEAD is setup a Database Maintenance Plan and run it! (and schedule it to keep running every X days)

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA
------------------------------

Original Message:
Sent: 03-14-2019 16:30
From: Steve Iwanowski
Subject: Sage 100 Premium reinitialize Data Files

That Sage Rebuild method looks iffy to me.  If you drop and re-create the table, you'll lose your indexes (and any custom triggers).  And if the data is corrupt but valid-enough to still accept the data type, it'll just copy the corruption back in.  If it's too corrupt, you'll lose the whole record (maybe this is how ProvideX already works?).    Not sure how much you really gain with that approach...

------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
------------------------------


Original Message:
Sent: 03-14-2019 14:53
From: Alnoor Cassim
Subject: Sage 100 Premium reinitialize Data Files

Thanks Jeremy for the response and sharing those articles. I looked at the How To Rebuild Sage 100 Premium Tables article and wow if I was still at Sage I would have approached that differently. It is not a bad approach, creative in fact but I think it could also address the normal root cause of that issue. I'm very pro-Premium / SQL (even sat as a panelist on the MotM SQL presentation too with @Steve Iwanowski  , @Robert White , and @Jim Woodhead ) but my first approach always leans towards a solution that is platform agnostic that could work on Premium, Advanced, and Standard. It sounds to me like this could be the classic case of the dictionary not being in alignment with the physical table. If that is true, there is a command you can run for that (a 1-liner) and for Premium additional steps could be added.

How about this - Even if you don't use / need it, I'll post a sample of how ​​​​to do this "rebuild" (with no T-SQL) and how to do the BOI / VBS version of this Error 47 fix (which also has no T-SQL and can deal with the Wrk and Restart tables), but in return I would ask you a favor of sorts. If you can accept my contact request, I'll explain it there.​​

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA


Original Message:
Sent: 03-14-2019 13:17
From: Jeremy Fox
Subject: Sage 100 Premium reinitialize Data Files

@Alnoor Cassim honestly I'd prefer the boi approach, but I'm just not sure how to actually pull it off for this conversation.  KB76175 (and 69387 but i have that part worked out) the issue I'm having is with the Reinitialize of the IM_TransactionRegisterWrk and IM_transactionRestart. I honestly assumed reinitliazing files would be in the same vain of a rebuild sort/key files which previously was saying we couldn't really do directly by scripting. (i have pulled that off with autohotkey though).

now I expect that the next question is why it's needed regularly or why its needed in premium, the client is integrating with Pallet Track using VI scripts so I suspect that its somehow an issue with the "timing". though the short answer is roughly once every two months I need to "rebuild" the tables using the method Sage prescribes for premium and then reinitializeing those two and it works like a charm.

------------------------------
Jeremy Fox
Velosio LLC


Original Message:
Sent: 03-13-2019 01:19
From: Alnoor Cassim
Subject: Sage 100 Premium reinitialize Data Files

Jeremy - Can I ask which KB article resolution you are trying to automate? To answer your question in a word: YES. E.g. When Reinit'ing A/R Cash Receipts data entry tables, when you select AR_CashReceiptsDetail it will update AR_OpenInvoice to zero out the PaidToday column where PaidToday > 0 (which happens in the normal course of Cash Receipts Entry). Same thing with AP_OpenInvoice when you select AP_CheckDetail.

I'm not sure if you were trying to do this anyway with your VBS but it's better to use sy_reinitialize_bus with BOI to accomplish your task, as long as you know what you are doing and have tested and re-tested your solution. This way you don't have to worry about the Updates on ancillary tables and your code doesn't have to veer outside the object (no direct T-SQL required). I posted a response on how to do this a loooong time ago so maybe you'll find it with a search (try "sy_reinitialize_bus" )

About the WRK tables the base tables are empty. The ones with the Session ID appended to the table name have the data in them but in Premium those are created in TempDB like Kevin mentioned.

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA

I use Ola's scripts to build my maintenance plans.  Here's the Indexing options:

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
------------------------------

Original Message:
Sent: 03-14-2019 17:17
From: Alnoor Cassim
Subject: Sage 100 Premium reinitialize Data Files

More than iffy Steve. This article was definitely not ready to publish because it wasn't verified by someone who knows Premium/SQL. You're completely right about losing the indexes! That is not just an inconvenience or loss of some speed issue. The 1st index is a primary key index (clustered too), meaning without that KPRIMARY, whenever Sage attempts to UPDATE an existing row / record on the table, it will instead INSERT and create a new one, and now you have a duplicate record problem.

Using the CreateTable() method via BOI or even SYZCON will create / update the table according to the dictionary which does include the indexes. There is another way too to effect that too. AND you're right about losing the user defined triggers! And yes agree about the data corruption just being copied back in.

Originally thought primary underlying cause would be dictionary not aligning to physical table. E.g. In past at least, with Premium adding UDFs through UDF Maint when there are 100+ company codes, will not add the UDF columns to the table after X number of companies have been processed where X is around 100.

 BUT in re-thinking this from what you said, if Support is seeing these "hard errors" go away with this resolution which removes indexes, that leads us to believe the indexes simply need to be rebuilt or reorganized, meaning what they should have said INSTEAD is setup a Database Maintenance Plan and run it! (and schedule it to keep running every X days)

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA
------------------------------


Original Message:
Sent: 03-14-2019 16:30
From: Steve Iwanowski
Subject: Sage 100 Premium reinitialize Data Files

That Sage Rebuild method looks iffy to me.  If you drop and re-create the table, you'll lose your indexes (and any custom triggers).  And if the data is corrupt but valid-enough to still accept the data type, it'll just copy the corruption back in.  If it's too corrupt, you'll lose the whole record (maybe this is how ProvideX already works?).    Not sure how much you really gain with that approach...

------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯


Original Message:
Sent: 03-14-2019 14:53
From: Alnoor Cassim
Subject: Sage 100 Premium reinitialize Data Files

Thanks Jeremy for the response and sharing those articles. I looked at the How To Rebuild Sage 100 Premium Tables article and wow if I was still at Sage I would have approached that differently. It is not a bad approach, creative in fact but I think it could also address the normal root cause of that issue. I'm very pro-Premium / SQL (even sat as a panelist on the MotM SQL presentation too with @Steve Iwanowski  , @Robert White , and @Jim Woodhead ) but my first approach always leans towards a solution that is platform agnostic that could work on Premium, Advanced, and Standard. It sounds to me like this could be the classic case of the dictionary not being in alignment with the physical table. If that is true, there is a command you can run for that (a 1-liner) and for Premium additional steps could be added.

How about this - Even if you don't use / need it, I'll post a sample of how ​​​​to do this "rebuild" (with no T-SQL) and how to do the BOI / VBS version of this Error 47 fix (which also has no T-SQL and can deal with the Wrk and Restart tables), but in return I would ask you a favor of sorts. If you can accept my contact request, I'll explain it there.​​

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA


Original Message:
Sent: 03-14-2019 13:17
From: Jeremy Fox
Subject: Sage 100 Premium reinitialize Data Files

@Alnoor Cassim honestly I'd prefer the boi approach, but I'm just not sure how to actually pull it off for this conversation.  KB76175 (and 69387 but i have that part worked out) the issue I'm having is with the Reinitialize of the IM_TransactionRegisterWrk and IM_transactionRestart. I honestly assumed reinitliazing files would be in the same vain of a rebuild sort/key files which previously was saying we couldn't really do directly by scripting. (i have pulled that off with autohotkey though).

now I expect that the next question is why it's needed regularly or why its needed in premium, the client is integrating with Pallet Track using VI scripts so I suspect that its somehow an issue with the "timing". though the short answer is roughly once every two months I need to "rebuild" the tables using the method Sage prescribes for premium and then reinitializeing those two and it works like a charm.

------------------------------
Jeremy Fox
Velosio LLC


Original Message:
Sent: 03-13-2019 01:19
From: Alnoor Cassim
Subject: Sage 100 Premium reinitialize Data Files

Jeremy - Can I ask which KB article resolution you are trying to automate? To answer your question in a word: YES. E.g. When Reinit'ing A/R Cash Receipts data entry tables, when you select AR_CashReceiptsDetail it will update AR_OpenInvoice to zero out the PaidToday column where PaidToday > 0 (which happens in the normal course of Cash Receipts Entry). Same thing with AP_OpenInvoice when you select AP_CheckDetail.

I'm not sure if you were trying to do this anyway with your VBS but it's better to use sy_reinitialize_bus with BOI to accomplish your task, as long as you know what you are doing and have tested and re-tested your solution. This way you don't have to worry about the Updates on ancillary tables and your code doesn't have to veer outside the object (no direct T-SQL required). I posted a response on how to do this a loooong time ago so maybe you'll find it with a search (try "sy_reinitialize_bus" )

About the WRK tables the base tables are empty. The ones with the Session ID appended to the table name have the data in them but in Premium those are created in TempDB like Kevin mentioned.

------------------------------
Alnoor Cassim

Free Agent Developer and Consultant
CallForHelp.biz
Email: alnoor@callforhelp.biz
Orange County, CA