Great Plains should use Transaction Processing in SQL Server

This concerns Great Plains version 7.5 on MS SQL Server 2000 SP3.  Day before 
yesterday a brief electric power outage occurred exactly while a user was 
posting a batch of invoices in PM.  The database server and other network 
components were on UPS and did not fail in any way, but the workstation PC 
doing the posting was not on a UPS and it rebooted.  This left the invoice 
batch in a sort of "half posted" state.  In fact there was resulting damage 
in both the PM and GL areas of the database, because we have the system set 
up to post through in detail to GL.  We first attempted to fix this damage 
using Batch Recovery and Check Links and Reconcile, but that did not 
completely work.  Ultimately we had to fix the records in the database using 
SQL statements, and that did work, although it was a complex process.  Our 
concern at this point is that this should not happen again.  Based on what we 
saw in the database, it appears certain that the posting process is not 
wrapped in a BEGIN/COMMIT/ROLLBACK TRANSACTION type of processing.  Is this 
corrected in Great Plains 8.0 or is it going to be corrected in any future 
version of Great Plains?  We cannot put all of our workstation PC's on UPS, 
and even if we could, we think the same type of problem could occur if there 
was a sudden network outage at a critical moment.  We are frankly surprised 
that the Great Plains client program is not taking better advantage of 
transaction processing (not to mention stored procedures and integrity 
constraints) available on MS SQL Server for quite some time now.


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the 
suggestions with the most votes. To vote for this suggestion, click the "I 
Agree" button in the message pane. If you do not see the button, follow this 
link to open the suggestion in the Microsoft Web-based Newsreader and then 
click "I Agree" in the message pane.

http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=00ed50ba-d5b4-4d12-860b-4d32a305f60c&dg=microsoft.public.greatplains
0
Randolph1 (4)
5/19/2005 3:00:03 PM
greatplains 29623 articles. 6 followers. Follow

10 Replies
1046 Views

Similar Articles

[PageSpeed] 16

Hi Steve

What recovery model are you using for the SQL Databases?   Are you using 
Full Recovery for SQL Log backups?

-- 
Regards

James[MVP]
Visit MBS Blog Central

http://mbscentral.blogs.com


"Steven Randolph" <Steven Randolph@discussions.microsoft.com> wrote in 
message news:00ED50BA-D5B4-4D12-860B-4D32A305F60C@microsoft.com...
> This concerns Great Plains version 7.5 on MS SQL Server 2000 SP3.  Day 
> before
> yesterday a brief electric power outage occurred exactly while a user was
> posting a batch of invoices in PM.  The database server and other network
> components were on UPS and did not fail in any way, but the workstation PC
> doing the posting was not on a UPS and it rebooted.  This left the invoice
> batch in a sort of "half posted" state.  In fact there was resulting 
> damage
> in both the PM and GL areas of the database, because we have the system 
> set
> up to post through in detail to GL.  We first attempted to fix this damage
> using Batch Recovery and Check Links and Reconcile, but that did not
> completely work.  Ultimately we had to fix the records in the database 
> using
> SQL statements, and that did work, although it was a complex process.  Our
> concern at this point is that this should not happen again.  Based on what 
> we
> saw in the database, it appears certain that the posting process is not
> wrapped in a BEGIN/COMMIT/ROLLBACK TRANSACTION type of processing.  Is 
> this
> corrected in Great Plains 8.0 or is it going to be corrected in any future
> version of Great Plains?  We cannot put all of our workstation PC's on 
> UPS,
> and even if we could, we think the same type of problem could occur if 
> there
> was a sudden network outage at a critical moment.  We are frankly 
> surprised
> that the Great Plains client program is not taking better advantage of
> transaction processing (not to mention stored procedures and integrity
> constraints) available on MS SQL Server for quite some time now.
>
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow 
> this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=00ed50ba-d5b4-4d12-860b-4d32a305f60c&dg=microsoft.public.greatplains 


0
5/23/2005 2:19:11 PM
Dont know why you cannot put all your WS on UPS?? 

Our whole network is UPSed switches, routers, internet connection...which 
allows for the several power outages we experience due to the thunder storms 
we get spring, summer and fall...and poweroutages due to the snow and ice 
storms in the winter. At 60.00 per workstation it is a worthwhile 
investment...as it also protects the WS hardware.

Server is on a 1500, network on 500 and WS on 350-500s. It is a part of the 
basic WS hardware cost...and not only improves productivity ...but also 
protects the data from corruption\loss etc.

MHO of course

Tami

"Steven Randolph" wrote:

> This concerns Great Plains version 7.5 on MS SQL Server 2000 SP3.  Day before 
> yesterday a brief electric power outage occurred exactly while a user was 
> posting a batch of invoices in PM.  The database server and other network 
> components were on UPS and did not fail in any way, but the workstation PC 
> doing the posting was not on a UPS and it rebooted.  This left the invoice 
> batch in a sort of "half posted" state.  In fact there was resulting damage 
> in both the PM and GL areas of the database, because we have the system set 
> up to post through in detail to GL.  We first attempted to fix this damage 
> using Batch Recovery and Check Links and Reconcile, but that did not 
> completely work.  Ultimately we had to fix the records in the database using 
> SQL statements, and that did work, although it was a complex process.  Our 
> concern at this point is that this should not happen again.  Based on what we 
> saw in the database, it appears certain that the posting process is not 
> wrapped in a BEGIN/COMMIT/ROLLBACK TRANSACTION type of processing.  Is this 
> corrected in Great Plains 8.0 or is it going to be corrected in any future 
> version of Great Plains?  We cannot put all of our workstation PC's on UPS, 
> and even if we could, we think the same type of problem could occur if there 
> was a sudden network outage at a critical moment.  We are frankly surprised 
> that the Great Plains client program is not taking better advantage of 
> transaction processing (not to mention stored procedures and integrity 
> constraints) available on MS SQL Server for quite some time now.
> 
> 
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the 
> suggestions with the most votes. To vote for this suggestion, click the "I 
> Agree" button in the message pane. If you do not see the button, follow this 
> link to open the suggestion in the Microsoft Web-based Newsreader and then 
> click "I Agree" in the message pane.
> 
> http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=00ed50ba-d5b4-4d12-860b-4d32a305f60c&dg=microsoft.public.greatplains
0
TamiFarrelly (100)
5/26/2005 7:14:03 PM
Steven,

Great Plains does make use of database transactions in 7.5.  It has in 
Payables since version 6.0.  General Ledger has used transactions since the 
first release of SQL on Great Plains.  We don’t wrap the entire batch in a 
database transaction as that would lead to very bad performance.  Instead 
each accounting transaction in the batch is wrapped in a database 
transaction.  The batch is tracked to ensure that all accounting transactions 
are posted.  If the process is interrupted then several items occur to ensure 
that the batch can be continued from where it left off.  First, when your 
user logs back into the product it will see that a batch is in a posting 
state and it will transition this batch to an interrupted status and provide 
the user with a message that the batch can now be accessed from the batch 
recovery window.  The user should now proceed to the batch recovery window to 
continue the batch processing.  No data will ever be corrupted in this 
process and check links, Reconcile or your own SQL scripts should not be run. 
 If your batch did not transition properly to the batch recovery window or if 
it did not continue properly you should call support immediately as the 
process is encountering an unexpected error.

Thanks,
Scott

-- 
This posting is provided "AS IS" with no warranties and confers no rights.


"Steven Randolph" wrote:

> This concerns Great Plains version 7.5 on MS SQL Server 2000 SP3.  Day before 
> yesterday a brief electric power outage occurred exactly while a user was 
> posting a batch of invoices in PM.  The database server and other network 
> components were on UPS and did not fail in any way, but the workstation PC 
> doing the posting was not on a UPS and it rebooted.  This left the invoice 
> batch in a sort of "half posted" state.  In fact there was resulting damage 
> in both the PM and GL areas of the database, because we have the system set 
> up to post through in detail to GL.  We first attempted to fix this damage 
> using Batch Recovery and Check Links and Reconcile, but that did not 
> completely work.  Ultimately we had to fix the records in the database using 
> SQL statements, and that did work, although it was a complex process.  Our 
> concern at this point is that this should not happen again.  Based on what we 
> saw in the database, it appears certain that the posting process is not 
> wrapped in a BEGIN/COMMIT/ROLLBACK TRANSACTION type of processing.  Is this 
> corrected in Great Plains 8.0 or is it going to be corrected in any future 
> version of Great Plains?  We cannot put all of our workstation PC's on UPS, 
> and even if we could, we think the same type of problem could occur if there 
> was a sudden network outage at a critical moment.  We are frankly surprised 
> that the Great Plains client program is not taking better advantage of 
> transaction processing (not to mention stored procedures and integrity 
> constraints) available on MS SQL Server for quite some time now.
> 
> 
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the 
> suggestions with the most votes. To vote for this suggestion, click the "I 
> Agree" button in the message pane. If you do not see the button, follow this 
> link to open the suggestion in the Microsoft Web-based Newsreader and then 
> click "I Agree" in the message pane.
> 
> http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=00ed50ba-d5b4-4d12-860b-4d32a305f60c&dg=microsoft.public.greatplains
0
smcintyr (8)
5/26/2005 10:56:02 PM
We are very familiar with the Batch Recovery process, as well as the Check 
Links and Reconcile procedures.  As always, we tried those PRIOR to using our 
own SQL routines to fix the damage.  Batch Recovery and Check Links and 
Reconcile did NOT fix the damage.  Your statement "No data will ever be 
corrupted in this process" is thoroughly disproven by our actual experience, 
and is actually sort of ridiculous.  Your statement "the process is 
encountering an unexpected error" is obviously true, which is exactly my 
point.  Great Plains is not fault tolerant!  Network outages and PC hangs 
corrupt Great Plains data, but a well designed client server database system 
should prevent that from happening in the first place.  No special recovery 
process should be required at all.

"Scott McIntyre [MSFT]" wrote:

> Steven,
> 
> Great Plains does make use of database transactions in 7.5.  It has in 
> Payables since version 6.0.  General Ledger has used transactions since the 
> first release of SQL on Great Plains.  We don’t wrap the entire batch in a 
> database transaction as that would lead to very bad performance.  Instead 
> each accounting transaction in the batch is wrapped in a database 
> transaction.  The batch is tracked to ensure that all accounting transactions 
> are posted.  If the process is interrupted then several items occur to ensure 
> that the batch can be continued from where it left off.  First, when your 
> user logs back into the product it will see that a batch is in a posting 
> state and it will transition this batch to an interrupted status and provide 
> the user with a message that the batch can now be accessed from the batch 
> recovery window.  The user should now proceed to the batch recovery window to 
> continue the batch processing.  No data will ever be corrupted in this 
> process and check links, Reconcile or your own SQL scripts should not be run. 
>  If your batch did not transition properly to the batch recovery window or if 
> it did not continue properly you should call support immediately as the 
> process is encountering an unexpected error.
> 
> Thanks,
> Scott
> 
> -- 
> This posting is provided "AS IS" with no warranties and confers no rights.
> 
> 
> "Steven Randolph" wrote:
> 
> > This concerns Great Plains version 7.5 on MS SQL Server 2000 SP3.  Day before 
> > yesterday a brief electric power outage occurred exactly while a user was 
> > posting a batch of invoices in PM.  The database server and other network 
> > components were on UPS and did not fail in any way, but the workstation PC 
> > doing the posting was not on a UPS and it rebooted.  This left the invoice 
> > batch in a sort of "half posted" state.  In fact there was resulting damage 
> > in both the PM and GL areas of the database, because we have the system set 
> > up to post through in detail to GL.  We first attempted to fix this damage 
> > using Batch Recovery and Check Links and Reconcile, but that did not 
> > completely work.  Ultimately we had to fix the records in the database using 
> > SQL statements, and that did work, although it was a complex process.  Our 
> > concern at this point is that this should not happen again.  Based on what we 
> > saw in the database, it appears certain that the posting process is not 
> > wrapped in a BEGIN/COMMIT/ROLLBACK TRANSACTION type of processing.  Is this 
> > corrected in Great Plains 8.0 or is it going to be corrected in any future 
> > version of Great Plains?  We cannot put all of our workstation PC's on UPS, 
> > and even if we could, we think the same type of problem could occur if there 
> > was a sudden network outage at a critical moment.  We are frankly surprised 
> > that the Great Plains client program is not taking better advantage of 
> > transaction processing (not to mention stored procedures and integrity 
> > constraints) available on MS SQL Server for quite some time now.
> > 
> > 
> > ----------------
> > This post is a suggestion for Microsoft, and Microsoft responds to the 
> > suggestions with the most votes. To vote for this suggestion, click the "I 
> > Agree" button in the message pane. If you do not see the button, follow this 
> > link to open the suggestion in the Microsoft Web-based Newsreader and then 
> > click "I Agree" in the message pane.
> > 
> > http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=00ed50ba-d5b4-4d12-860b-4d32a305f60c&dg=microsoft.public.greatplains
0
Randolph1 (4)
5/27/2005 11:11:02 AM
Obviously there is no *technical* reason why we cannot put all our 
workstations on a UPS.  Did it occur to you that there might be a financial 
reason?  We are a non profit charitable organization.

"Tami Farrelly" wrote:

> Dont know why you cannot put all your WS on UPS?? 
> 
> Our whole network is UPSed switches, routers, internet connection...which 
> allows for the several power outages we experience due to the thunder storms 
> we get spring, summer and fall...and poweroutages due to the snow and ice 
> storms in the winter. At 60.00 per workstation it is a worthwhile 
> investment...as it also protects the WS hardware.
> 
> Server is on a 1500, network on 500 and WS on 350-500s. It is a part of the 
> basic WS hardware cost...and not only improves productivity ...but also 
> protects the data from corruption\loss etc.
> 
> MHO of course
> 
> Tami
> 
> "Steven Randolph" wrote:
> 
> > This concerns Great Plains version 7.5 on MS SQL Server 2000 SP3.  Day before 
> > yesterday a brief electric power outage occurred exactly while a user was 
> > posting a batch of invoices in PM.  The database server and other network 
> > components were on UPS and did not fail in any way, but the workstation PC 
> > doing the posting was not on a UPS and it rebooted.  This left the invoice 
> > batch in a sort of "half posted" state.  In fact there was resulting damage 
> > in both the PM and GL areas of the database, because we have the system set 
> > up to post through in detail to GL.  We first attempted to fix this damage 
> > using Batch Recovery and Check Links and Reconcile, but that did not 
> > completely work.  Ultimately we had to fix the records in the database using 
> > SQL statements, and that did work, although it was a complex process.  Our 
> > concern at this point is that this should not happen again.  Based on what we 
> > saw in the database, it appears certain that the posting process is not 
> > wrapped in a BEGIN/COMMIT/ROLLBACK TRANSACTION type of processing.  Is this 
> > corrected in Great Plains 8.0 or is it going to be corrected in any future 
> > version of Great Plains?  We cannot put all of our workstation PC's on UPS, 
> > and even if we could, we think the same type of problem could occur if there 
> > was a sudden network outage at a critical moment.  We are frankly surprised 
> > that the Great Plains client program is not taking better advantage of 
> > transaction processing (not to mention stored procedures and integrity 
> > constraints) available on MS SQL Server for quite some time now.
> > 
> > 
> > ----------------
> > This post is a suggestion for Microsoft, and Microsoft responds to the 
> > suggestions with the most votes. To vote for this suggestion, click the "I 
> > Agree" button in the message pane. If you do not see the button, follow this 
> > link to open the suggestion in the Microsoft Web-based Newsreader and then 
> > click "I Agree" in the message pane.
> > 
> > http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=00ed50ba-d5b4-4d12-860b-4d32a305f60c&dg=microsoft.public.greatplains
0
5/27/2005 11:13:24 AM
Well...I guess it depends on the value of your data, the cost of down time 
and technical support due to these interuptions, the savings in hardware 
maintenence.....

MHO of course

Tami

"Steven Randolph" wrote:

> Obviously there is no *technical* reason why we cannot put all our 
> workstations on a UPS.  Did it occur to you that there might be a financial 
> reason?  We are a non profit charitable organization.
> 
> "Tami Farrelly" wrote:
> 
> > Dont know why you cannot put all your WS on UPS?? 
> > 
> > Our whole network is UPSed switches, routers, internet connection...which 
> > allows for the several power outages we experience due to the thunder storms 
> > we get spring, summer and fall...and poweroutages due to the snow and ice 
> > storms in the winter. At 60.00 per workstation it is a worthwhile 
> > investment...as it also protects the WS hardware.
> > 
> > Server is on a 1500, network on 500 and WS on 350-500s. It is a part of the 
> > basic WS hardware cost...and not only improves productivity ...but also 
> > protects the data from corruption\loss etc.
> > 
> > MHO of course
> > 
> > Tami
> > 
> > "Steven Randolph" wrote:
> > 
> > > This concerns Great Plains version 7.5 on MS SQL Server 2000 SP3.  Day before 
> > > yesterday a brief electric power outage occurred exactly while a user was 
> > > posting a batch of invoices in PM.  The database server and other network 
> > > components were on UPS and did not fail in any way, but the workstation PC 
> > > doing the posting was not on a UPS and it rebooted.  This left the invoice 
> > > batch in a sort of "half posted" state.  In fact there was resulting damage 
> > > in both the PM and GL areas of the database, because we have the system set 
> > > up to post through in detail to GL.  We first attempted to fix this damage 
> > > using Batch Recovery and Check Links and Reconcile, but that did not 
> > > completely work.  Ultimately we had to fix the records in the database using 
> > > SQL statements, and that did work, although it was a complex process.  Our 
> > > concern at this point is that this should not happen again.  Based on what we 
> > > saw in the database, it appears certain that the posting process is not 
> > > wrapped in a BEGIN/COMMIT/ROLLBACK TRANSACTION type of processing.  Is this 
> > > corrected in Great Plains 8.0 or is it going to be corrected in any future 
> > > version of Great Plains?  We cannot put all of our workstation PC's on UPS, 
> > > and even if we could, we think the same type of problem could occur if there 
> > > was a sudden network outage at a critical moment.  We are frankly surprised 
> > > that the Great Plains client program is not taking better advantage of 
> > > transaction processing (not to mention stored procedures and integrity 
> > > constraints) available on MS SQL Server for quite some time now.
> > > 
> > > 
> > > ----------------
> > > This post is a suggestion for Microsoft, and Microsoft responds to the 
> > > suggestions with the most votes. To vote for this suggestion, click the "I 
> > > Agree" button in the message pane. If you do not see the button, follow this 
> > > link to open the suggestion in the Microsoft Web-based Newsreader and then 
> > > click "I Agree" in the message pane.
> > > 
> > > http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=00ed50ba-d5b4-4d12-860b-4d32a305f60c&dg=microsoft.public.greatplains
0
TamiFarrelly (100)
5/27/2005 4:33:39 PM
Well.....I dont know of any database that would not corrupt with an 
interuptiion in processes like that. That is why you should use a UPS...maybe 
just on the WS that do these type of processes.

Also...you could do point in time restore...that is if your backup is 
properly configured.....

But then again...backup devices\media and software can be very 
expensive....so it just may not be just that high you your priorty list either


"Steven Randolph" wrote:

> We are very familiar with the Batch Recovery process, as well as the Check 
> Links and Reconcile procedures.  As always, we tried those PRIOR to using our 
> own SQL routines to fix the damage.  Batch Recovery and Check Links and 
> Reconcile did NOT fix the damage.  Your statement "No data will ever be 
> corrupted in this process" is thoroughly disproven by our actual experience, 
> and is actually sort of ridiculous.  Your statement "the process is 
> encountering an unexpected error" is obviously true, which is exactly my 
> point.  Great Plains is not fault tolerant!  Network outages and PC hangs 
> corrupt Great Plains data, but a well designed client server database system 
> should prevent that from happening in the first place.  No special recovery 
> process should be required at all.
> 
> "Scott McIntyre [MSFT]" wrote:
> 
> > Steven,
> > 
> > Great Plains does make use of database transactions in 7.5.  It has in 
> > Payables since version 6.0.  General Ledger has used transactions since the 
> > first release of SQL on Great Plains.  We don’t wrap the entire batch in a 
> > database transaction as that would lead to very bad performance.  Instead 
> > each accounting transaction in the batch is wrapped in a database 
> > transaction.  The batch is tracked to ensure that all accounting transactions 
> > are posted.  If the process is interrupted then several items occur to ensure 
> > that the batch can be continued from where it left off.  First, when your 
> > user logs back into the product it will see that a batch is in a posting 
> > state and it will transition this batch to an interrupted status and provide 
> > the user with a message that the batch can now be accessed from the batch 
> > recovery window.  The user should now proceed to the batch recovery window to 
> > continue the batch processing.  No data will ever be corrupted in this 
> > process and check links, Reconcile or your own SQL scripts should not be run. 
> >  If your batch did not transition properly to the batch recovery window or if 
> > it did not continue properly you should call support immediately as the 
> > process is encountering an unexpected error.
> > 
> > Thanks,
> > Scott
> > 
> > -- 
> > This posting is provided "AS IS" with no warranties and confers no rights.
> > 
> > 
> > "Steven Randolph" wrote:
> > 
> > > This concerns Great Plains version 7.5 on MS SQL Server 2000 SP3.  Day before 
> > > yesterday a brief electric power outage occurred exactly while a user was 
> > > posting a batch of invoices in PM.  The database server and other network 
> > > components were on UPS and did not fail in any way, but the workstation PC 
> > > doing the posting was not on a UPS and it rebooted.  This left the invoice 
> > > batch in a sort of "half posted" state.  In fact there was resulting damage 
> > > in both the PM and GL areas of the database, because we have the system set 
> > > up to post through in detail to GL.  We first attempted to fix this damage 
> > > using Batch Recovery and Check Links and Reconcile, but that did not 
> > > completely work.  Ultimately we had to fix the records in the database using 
> > > SQL statements, and that did work, although it was a complex process.  Our 
> > > concern at this point is that this should not happen again.  Based on what we 
> > > saw in the database, it appears certain that the posting process is not 
> > > wrapped in a BEGIN/COMMIT/ROLLBACK TRANSACTION type of processing.  Is this 
> > > corrected in Great Plains 8.0 or is it going to be corrected in any future 
> > > version of Great Plains?  We cannot put all of our workstation PC's on UPS, 
> > > and even if we could, we think the same type of problem could occur if there 
> > > was a sudden network outage at a critical moment.  We are frankly surprised 
> > > that the Great Plains client program is not taking better advantage of 
> > > transaction processing (not to mention stored procedures and integrity 
> > > constraints) available on MS SQL Server for quite some time now.
> > > 
> > > 
> > > ----------------
> > > This post is a suggestion for Microsoft, and Microsoft responds to the 
> > > suggestions with the most votes. To vote for this suggestion, click the "I 
> > > Agree" button in the message pane. If you do not see the button, follow this 
> > > link to open the suggestion in the Microsoft Web-based Newsreader and then 
> > > click "I Agree" in the message pane.
> > > 
> > > http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=00ed50ba-d5b4-4d12-860b-4d32a305f60c&dg=microsoft.public.greatplains
0
TamiFarrelly (100)
5/27/2005 8:59:48 PM
As Scott M noted, Great Plains does make use of database transactions in release 7.5. It has in 
Payables since version 6.0. General Ledger has used transactions since the first release of SQL on Great Plains.



This posting is provided "AS IS" with no warranties, and confers no rights.

<Steven Randolph> wrote:


This concerns Great Plains version 7.5 on MS SQL Server 2000 SP3.  Day before 
yesterday a brief electric power outage occurred exactly while a user was 
posting a batch of invoices in PM.  The database server and other network 
components were on UPS and did not fail in any way, but the workstation PC 
doing the posting was not on a UPS and it rebooted.  This left the invoice 
batch in a sort of "half posted" state.  In fact there was resulting damage 
in both the PM and GL areas of the database, because we have the system set 
up to post through in detail to GL.  We first attempted to fix this damage 
using Batch Recovery and Check Links and Reconcile, but that did not 
completely work.  Ultimately we had to fix the records in the database using 
SQL statements, and that did work, although it was a complex process.  Our 
concern at this point is that this should not happen again.  Based on what we 
saw in the database, it appears certain that the posting process is not 
wrapped in a BEGIN/COMMIT/ROLLBACK TRANSACTION type of processing.  Is this 
corrected in Great Plains 8.0 or is it going to be corrected in any future 
version of Great Plains?  We cannot put all of our workstation PC's on UPS, 
and even if we could, we think the same type of problem could occur if there 
was a sudden network outage at a critical moment.  We are frankly surprised 
that the Great Plains client program is not taking better advantage of 
transaction processing (not to mention stored procedures and integrity 
constraints) available on MS SQL Server for quite some time now.


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the 
suggestions with the most votes. To vote for this suggestion, click the "I 
Agree" button in the message pane. If you do not see the button, follow this 
link to open the suggestion in the Microsoft Web-based Newsreader and then 
click "I Agree" in the message pane.

http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=00ed50ba-d5b4-4d12-860b-4d32a305f60c&dg=microsoft.public.greatplains
0
cgust (108)
3/10/2006 5:18:30 PM
I completely agree on Steven's position on this problem. There is no possible 
explanation for not using transaction statements (begin transaction, commit 
or rollback). Checklinks and reconciliation utilities are to solve design 
problems. Improvement in hardware, network or user training can help, but 
don't address the problem roor cause. I'm surprised not to find more posts or 
recent activity about this.
Thanks!

"Char Gust (MSFT)" wrote:

> 
> As Scott M noted, Great Plains does make use of database transactions in release 7.5. It has in 
> Payables since version 6.0. General Ledger has used transactions since the first release of SQL on Great Plains.
> 
> 
> 
> This posting is provided "AS IS" with no warranties, and confers no rights.
> 
> <Steven Randolph> wrote:
> 
> 
> This concerns Great Plains version 7.5 on MS SQL Server 2000 SP3.  Day before 
> yesterday a brief electric power outage occurred exactly while a user was 
> posting a batch of invoices in PM.  The database server and other network 
> components were on UPS and did not fail in any way, but the workstation PC 
> doing the posting was not on a UPS and it rebooted.  This left the invoice 
> batch in a sort of "half posted" state.  In fact there was resulting damage 
> in both the PM and GL areas of the database, because we have the system set 
> up to post through in detail to GL.  We first attempted to fix this damage 
> using Batch Recovery and Check Links and Reconcile, but that did not 
> completely work.  Ultimately we had to fix the records in the database using 
> SQL statements, and that did work, although it was a complex process.  Our 
> concern at this point is that this should not happen again.  Based on what we 
> saw in the database, it appears certain that the posting process is not 
> wrapped in a BEGIN/COMMIT/ROLLBACK TRANSACTION type of processing.  Is this 
> corrected in Great Plains 8.0 or is it going to be corrected in any future 
> version of Great Plains?  We cannot put all of our workstation PC's on UPS, 
> and even if we could, we think the same type of problem could occur if there 
> was a sudden network outage at a critical moment.  We are frankly surprised 
> that the Great Plains client program is not taking better advantage of 
> transaction processing (not to mention stored procedures and integrity 
> constraints) available on MS SQL Server for quite some time now.
> 
> 
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the 
> suggestions with the most votes. To vote for this suggestion, click the "I 
> Agree" button in the message pane. If you do not see the button, follow this 
> link to open the suggestion in the Microsoft Web-based Newsreader and then 
> click "I Agree" in the message pane.
> 
> http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=00ed50ba-d5b4-4d12-860b-4d32a305f60c&dg=microsoft.public.greatplains
0
mhassel (1)
3/17/2009 5:19:04 PM
I came across this thread again after working to diagnose an issue we just 
discovered with duplicated transactions in our inventory & G/L subsystems.

For some reason (unidentified as of yet), the posting of our Receivings 
transactions occasionally fail and go into recovery. When reposted, the 
transactions in the Purchasing module are correct but those in the Inventory 
and G/L modules are duplicated. This is unacceptable!!! I can attest that GP 
does NOT recover cleanly from aborted transactions.

I'm not sure how to address this, other than by running queries periodically 
to look for duplicated transactions and doing whatever is needed to fix them. 
Like I don't have enough to do already!! :-)




-- 
Bud Cool
Microsoft Certified Business Management Solutions Specialist, GP 9.0 
Financials
Currently supporting: GP 9.0, SP2


"mhassel" wrote:

> I completely agree on Steven's position on this problem. There is no possible 
> explanation for not using transaction statements (begin transaction, commit 
> or rollback). Checklinks and reconciliation utilities are to solve design 
> problems. Improvement in hardware, network or user training can help, but 
> don't address the problem roor cause. I'm surprised not to find more posts or 
> recent activity about this.
> Thanks!
> 
> "Char Gust (MSFT)" wrote:
> 
> > 
> > As Scott M noted, Great Plains does make use of database transactions in release 7.5. It has in 
> > Payables since version 6.0. General Ledger has used transactions since the first release of SQL on Great Plains.
> > 
> > 
> > 
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > 
> > <Steven Randolph> wrote:
> > 
> > 
> > This concerns Great Plains version 7.5 on MS SQL Server 2000 SP3.  Day before 
> > yesterday a brief electric power outage occurred exactly while a user was 
> > posting a batch of invoices in PM.  The database server and other network 
> > components were on UPS and did not fail in any way, but the workstation PC 
> > doing the posting was not on a UPS and it rebooted.  This left the invoice 
> > batch in a sort of "half posted" state.  In fact there was resulting damage 
> > in both the PM and GL areas of the database, because we have the system set 
> > up to post through in detail to GL.  We first attempted to fix this damage 
> > using Batch Recovery and Check Links and Reconcile, but that did not 
> > completely work.  Ultimately we had to fix the records in the database using 
> > SQL statements, and that did work, although it was a complex process.  Our 
> > concern at this point is that this should not happen again.  Based on what we 
> > saw in the database, it appears certain that the posting process is not 
> > wrapped in a BEGIN/COMMIT/ROLLBACK TRANSACTION type of processing.  Is this 
> > corrected in Great Plains 8.0 or is it going to be corrected in any future 
> > version of Great Plains?  We cannot put all of our workstation PC's on UPS, 
> > and even if we could, we think the same type of problem could occur if there 
> > was a sudden network outage at a critical moment.  We are frankly surprised 
> > that the Great Plains client program is not taking better advantage of 
> > transaction processing (not to mention stored procedures and integrity 
> > constraints) available on MS SQL Server for quite some time now.
> > 
> > 
> > ----------------
> > This post is a suggestion for Microsoft, and Microsoft responds to the 
> > suggestions with the most votes. To vote for this suggestion, click the "I 
> > Agree" button in the message pane. If you do not see the button, follow this 
> > link to open the suggestion in the Microsoft Web-based Newsreader and then 
> > click "I Agree" in the message pane.
> > 
> > http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=00ed50ba-d5b4-4d12-860b-4d32a305f60c&dg=microsoft.public.greatplains
0
Bud (544)
11/6/2009 8:41:02 PM
Reply:

Similar Artilces:

Integration Manager upload to Great PLains
Hello, I am using the Integration Manager and uploading a text file to Great Plains and I am getting the following error. DOC 347 ERROR: Macro execution error, Dynamics.DEXTERITY_IM_MACRO_ABORTED (error 512): MoveTo line 1 scrollwin 'RM_Sales_Tax_Distribution_Scroll' field '(L) Account Number':'Account_Segment_Pool3' I am not sure what might be the problem Tony ...

CRM / SQL Reporting Co-Exist
We are trying to run Microsoft CRM and SQL Server Reporting on the same server. We have experiemented with several approaches but with no luck. Either SQL Server Reporting's Report Manager will not run with an error that session state in disenabled in ASP.Net or I get an error that there is a problem with the assembly Microsoft.CRM.Platform.Types. Does anyone know where I could find a document on how to make CRM and SQL Reporting co-exist on the same or server. It must be possible since many people run Small Business Server 2000 or 2003. Regards, George Vessels ...

Process all cells in a (user) selection
I am using this code to give me cell by cell access to a code defined selection; Dim aCell As Range For Each aCell In Sheet4.Range("A4:A34") ' Do Stuff Here Next aCell which processes every cell in the A4:A34 Range How do I do the same thing for a selection drawn by the user before pressing my 'Process' button ? Thanks On 05 May 2010 11:10:21 GMT, Isis <isissoft@NOSPAMbtinternet.com> wrote: >I am using this code to give me cell by cell access to a code defined >selection; > >Dim aCell As Range >For Each aCell In S...

last 4 wk average using calculated pivotitem
Hi, I am trying to create a pivot table/chart from data that is listed b week. So my table has "week" as the row field and "total X" as th column field. I want the chart to display the total for each week as bar (the easy part) and also to graph the average of the last fou weeks as a line on top of the bar graph. I have tried to figure ou how to add a calculated pivot item but I can't seem to get it to wor right. So for each week there would be a second almost subtotal lik entry that calculates the average for the previous four weeks (if ther aren't four previo...

Sending email from exchange server to Win2k server on same domain
This has been bugging me, I have a win2K box with smtp installed, I want to allow users to send messages from exchange to the win2k server where they will be processed by some scripts. I have never tried this before and I am not sure of: 1. what is the address of the server (servername@domain.com?) 2. how should the exchange account/address be configured Any advice gratefully received Thanks Mark Mark Davey wrote: > This has been bugging me, I have a win2K box with smtp installed, I want to > allow users to send messages from exchange to the win2k server where they > will be proc...

SQL Back-end / Access Front-end using linked tables????
I have a backend that contains tables that I pull Driver information, Customer information , etc. (these are MAS 200 accounting software tables that are exported nightly to the SQL backend). I also have on the backend tables that I push information to; information that we enter on the forms located on the front end. I might not have this set- up correctly, but I'm linking all tables on the back end to the Access database on the front end. When trying to set up an Auto Lookup off of a query I've created in Access it says I need to go into the table change Data Type to the Lookup wizard,...

Second SMTP Server
I have two Exchange servers in the same domain. They are in two separate admin groups. They are on two separate subnets. Inter-Organizational mail works fine. They have one routing group connector between the two. These two servers are on opposite sides of the globe. Server 1 is in the U.S. and sends Internet addresses SMTP directly to its destination just fine. Server 2 is in Europe, and will not send Internet addressed SMTP mail at all. The mail just sits in a queue, and says retry. This appears to be a two fold problem. 1. Server 2 does not send the Internet addressed SMTP mail to S...

Using a VBscript to copy/delete/move Outlook messages
I need to write a VBscript to: - open my Outlook (2000 or XP) mailbox - read the list of the folders in either the Exchange Mailbox and in local ..pst files - open each folder and subfolders - read information about messages (i.e. sender, recipient, creation date, and so on) - move, copy or delete messages based on the above information Can anybody please provide a pointer to a sample I can start from? Regards Marius ...

Using XP Briefcase with money file
I put a copy of my money file into the laptop briefcase and I am able to keep them in sych for a while. For some reason I get locked out, the computer knows the file has been updated but it will not let the desk top file update the laptop file, I get the following message; "The source file can not be opened" "Check to see if the other program has the file opened, and close the file before updating the briefcase." Other files update just fine. When I get this message, I can't even drag a new copy of the Money file to my briefcase, I have to rename the file an...

Automatically Purging Transaction Logs
Hello, I am running Exchange Server 2003 and am looking for a method to aoutomatically purge transaction log files after succesful back up. Can anyone give me a good method of doing this? Thanks in advance. A successful backup automatically purges the transaction logs (if you're using Exchange-aware backup sw) -- Bharat Suneja MCSE, MCT -------------------------------- "Hunter" <Hunter@discussions.microsoft.com> wrote in message news:4DF7D5C6-5422-4FAB-BC45-E9284E8F0CB9@microsoft.com... > Hello, > > I am running Exchange Server 2003 and am looking for a ...

Automatically redirecting profiles to a new exchange server?
OK old exchange server was SERVEROLD and new one is SERVERNEW (for arguments sake). The pc has lots of different profiles on it and I dont wanna manually change each profile to reflect the new server. Running Outlook 2002 is there a way to automatically do this? I believe I can use a prf file created using Custom Wizards from the Office Resource Kit and deploy this to do this for me. Can anyone give any more detailed info on how this can be done? Thanks <carlosvenegas@jellybeandesign.co.uk> wrote in message news:1132785375.328184.274520@g47g2000cwa.googlegroups.com... > OK ol...

Exchange Features Tab in ADUC when using Windows 2000 SP3
Hi, I would like to run Active Directory Users and Computers with Exchange 2003 Admin tools I'm limited to Windows 2000 Prof/SP3 Everything works,except from the Exchange Features tab. When selecting this tab, ADUC hangs. Any ideas. (I have to stay on SP3!) br TN Terminal Services is always my first choice. On Wed, 13 Apr 2005 23:35:22 +0200, "newsMS" <t_n@trashcan.dk> wrote: >Hi, > >I would like to run Active Directory Users and Computers with Exchange 2003 >Admin tools >I'm limited to Windows 2000 Prof/SP3 > >Everything works,except...

mail server
There was a problem logging onto your mail server. Your Password was rejected. Account: 'shawmail.ed.shawcable.net', Server: 'shawmail.ed.shawcable.net', Protocol: POP3, Server Response: '-ERR [AUTH] Authentication failed', Port: 110, Secure(SSL): No, Server Error: 0x800CCC90, Error Number: 0x800CCC92 That is what i recieve after setting up a new identity. A box pops up with my username and a password, but i don't know what the password is or why my account will not authorize. why wont it work?? This is on Outlook Express-making a new identity "Soph...

Using a variable for a chart limit
Since I got instant help on my last query, is there any way to use a cell reference as an axis minimum or maximum in format axis? It seems impossible, but there is a lot of experience out there. Thanks in advance. -- Vince F Hi, See Jon's information http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html Cheers Andy Vince F wrote: > Since I got instant help on my last query, is there any way to use a cell > reference as an axis minimum or maximum in format axis? It seems impossible, > but there is a lot of experience out there. Thanks in advance. -- Andy Pop...

How do I make door hangers using publisher?
Is this even possible? Mary Sauer has a website with a generous collection of goodies including door hangers. Take a look at just above George Washington's portrait (way down the page) at, http://msauer.mvps.org/publisher_projects2.htm -- Don - Publisher 2000� Vancouver, USA "medinapie" <medinapie@discussions.microsoft.com> wrote in message news:986B8199-C9B5-46CE-8F2F-C772BE5C21BC@microsoft.com... > Is this even possible? I have a sample on my web page. Scroll down -- on the left. http://msauer.mvps.org/graphics.htm Some cute ones here that you could mod...

using program to record audio comment and compress audio to MP3
I can use VBA to record an audio comment into a Word file using Selection.InlineShapes.AddOLEObject ClassType:="SoundRec", FileName:="", _ LinkToFile:=False, DisplayAsIcon:=True, IconFileName:= _ "C:\WINDOWS\system32\sndrec32.exe", IconIndex:=0, IconLabel:="Wave Sound" BUT but by default the file is stored as a PCM at 44kHz and 16 bit stereo whcih is about 200kB per second Sound recorder (which is used to do the recording) allows me to manually convert this to a MP3 and a much smaller file e.g. 3kB per second Is there any way I can aut...

Can I use oulook from home and have access at work?
When I set up my outlook I lose all my inbox messages online and would not be able to read my email from work. Is there a way to have my inbox available on outlook and remain online? Just to clarify, are you asking how to set up Outlook at home to receive email from work? If that's your question, you need to ask your IT Admin or Exchange Admin if you can use OWA and how that's done. That wouldn't have email coming into your Outlook account but it is a form of Outlook, Outlook Web Access. This may or may not be allowed. Your IT staff would of course have all kinds of securi...

Script to audit windows 2003 servers
I am looking for a powershell script which we allow me to audit my physical servers which are in a specific network. Is this possible to do. The information I need for the server is as following: Model number - amount of RAM - amount and speed of CPU's - local storage - amount of nics - amount of HBA - installed OS And the subnet the servers are in is 172.16.40.0/21. Is there a way I can run one script to get all this information. It will of course require remote access to these servers. Also the servers are all windows 2003 SP2. An...

how do I find the equation of a line using a scatter plot?
I am taking a chem lab and right now we are studying rate laws using kenetics. we had to graph the results of our experiment and then find the equation of the line. such as y=mx+b but i cant fiugre out how to do it. or if i am supposed to put a trend line in the graph, but if that is what I am supposed to do how do I find the equation of it? First: to make trendline. Right click on the data in the chart; use Add trendline; specify Linear; on Options tab, check shoe equation Secondly:To get slope and intercept: Let's say you x-values are in A2:A11 and y-values in B2:B11 for slope us...

Line Item comments in SOP Transaction in Sales Line Item Smartlist
The Sales Line Item smartlist, comment fields (Comment 1, Comment 2...) seems to be picking up comment from the transaction level comment text rather than line item comment text entered in Sales Item detail entry screen. Seems illogical to me. It should pick comment text from Line item details rather than transaction level comments. Any Ideas? What version? It seems fine in 8.0. VG "Samit" wrote: > The Sales Line Item smartlist, comment fields (Comment 1, Comment 2...) > seems to be picking up comment from the transaction level comment text > rather than line item com...

Using Rept function
I want to repeat one cells entry somewhere else in my (same) worksheet and have used the Rept function successfully. However, ifthe 1st cell (e.g. A3 is in date format like 12/12/2003, when I use the Rept A3,1 function in another cell (e.g.cell R3), it returns the date as a number (37967). What I want is for the date to be repeated exactly as is (12/12/2003 or whatever date is typed). Can anyone help please Don't use the REPT function. Just enter =A3 and apply the date format you want using Format, Cells if you do not automatically get it. -- Jim Rech Excel MVP You can't unless ...

Schema validation using Visual Studio .NET 2003
Hi, All Iam trying to do is validate a group of schemas which inturn references an external XSD schema(tried putting both in local webserver/as part of project),Iam getting errors This is what Iam doing 1. Open a blank Biztalk solution 2. Add all the schemas to validate 3. Validate Schema Am not sure what Iam doing wrong.Any help would be appreciated Thanks Raj ...

Exchange and SQL
Hi Everyone, I am wondering if it is a good idea to have Exchange 2000 server and SQL 2000 server on the same machine? Also, should I keep that Windows 2000 server machine a member server or promote to a DC? Any suggestions would be greatly appreciated. Thanks Vu I think ideally the best bet is to keep the box as a member server and just use it for exchange. Unless of course you're running SBS in which case you get all 3 on the same machine :) "Nu2Exchange" <Nu2Exchange@discussions.microsoft.com> wrote in message news:8ED0E610-967C-4093-A88B-5FA4971DAEA6@micro...

How to use Or in txrbox
Hi Working in a relational db in Access 2003 I have a continuous form (MyForm). In Header section of the form I have an unbound txtbox control to enter words separated by Or. After entering the word(s) user click a button to trigger searching the word(s). My form is based on Allen's Browne search example. Thus searching controls are in Header and founded records must be displayed in form2 rather than in Details of the Myform. On Mon, 26 Apr 2010 13:20:02 -0700, sebastico <sebastico@discussions.microsoft.com> wrote: >Hi >Working in a relational db in Access 2003 I...

SQL Set Commision
I have two tables (here is a simplification of the problem). One table has: OrderID, Amount, SplitID, CustomerID. Sample OrderID Amount SplitID CustomerID 1 $100.00 1 A 2 $40.00 1 B 3 $100.00 1 A 4 $250.00 1 A 5 $100.00 2 A 6 $150.00 2 B For Customer A, the Total for Customer A SplitID 1, the total is: $450.00 ($100 + $100 + $250) The second Table has SplitID, SplitN, Amount, Percent. Sample SplitID SplitN Amount % 1 1 $0.00 0% 1 2 $50.00 5% 1 3 $100.00 10% 1 4 $200.00 15% 2 1 $0.00 0% 2 2 $100.00 5% I use the second table to c...