How to resolve a sales order processing posting interruption in Microsoft Dynamics GP

August 30, 2007 at 5:55 pm | Posted in GP Support, Undocumented Features | Leave a comment

INTRODUCTION

This step-by-step article describes how to resolve a sales order processing posting interruption when you use Sales Order Processing in Microsoft Dynamics GP 10.0, in Microsoft Dynamics GP 9.0, in Microsoft Business Solutions – Great Plains 8.0, in Microsoft Business Solutions – Great Plains 7.5, in Microsoft Great Plains Dynamics 7.0, in Microsoft Great Plains eEnterprise 7.0, in Great Plains 6.0 Dynamics, or in Great Plains 6.0 eEnterprise.

Back to the top Back to the top

MORE INFORMATION

Locate the transaction

Verify in which tables the transaction appears. To do this, follow these steps:

1. Verify Sales Order Processing. To do this, use the one of the following methods.

• Method 1On the Inquiry menu, point to Sales, and then click Sales Documents. Determine whether the transaction appears under Unposted, under History, or under both.
• Method 2In Microsoft SQL Query Analyzer, run the following scripts against the Company database.

select * from SOP10100 where SOPNUMBE = 'xxx' select * from SOP10200 where SOPNUMBE = 'xxx' select * from SOP30200 where SOPNUMBE = 'xxx' select * from SOP30300 where SOPNUMBE = 'xxx'

Note In this script, xxx represents the actual invoice number.
2. Verify Receivable Management. To do this, use the appropriate method.

• On the Inquiry menu, point to Sales, and then click Transaction by Customer. Verify the transactions exists and the status of the transaction.
• Using Query Analyzer, run the following scripts against the Company database.

select * from RM10301 where DOCNUMBR = 'xxx' select * from RM20101 where DOCNUMBR = 'xxx'

Note In this script, xxx represents the actual invoice number.
3. Verify the General Ledger using by using one of the following method.

• Method 1On the Inquiry menu, point to Financials, and then click Detail Inquiry. Determine whether the transaction exists. If the transaction does not exist, go to Method 2.
• Method 2On the Transactions menu, point to Financials, and then click Batches. Determine whether a batch exists for the transaction.
• Method 3In Query Analyzer, run the following script against the company database.

select * from GL10000 where DTAControlNum = 'xxx' select * from GL20000 where ORCTRNUM = 'xxx'
4. Determine whether the inventory was reduced. To do this, use the one of the following methods.

• Method 1On the Inquiry menu, point to Inventory, and then click Item Transaction. Determine whether the transaction exists.
• Method 2In Query Analyzer, run the following script against the company database. 

select * from IV10200 where RCPTNMBR = 'xxx'

Note In this script, xxx represents the actual invoice number.

Back to the top Back to the top

Correct the transaction

After you verify the location of the SOP transaction, correct the transaction. To do this, use the following methods if the listed conditions apply.

Method 1

Follow these steps if the following conditions are true:

• The transaction is posted to the General Ledger.
• The inventory is reduced.
• The transaction has been posted to Receivables Management.
• The transaction is in the Sales Order Processing Work and History tables.
1. Click Start, point to Microsoft SQL Server 2005, and then click SQL Server Management Server. If you are using Microsoft SQL Server 2000, click Start, point to Microsoft SQL Server, and then click Query Analyzer.
2. In the database list, click the company database.
3. Click Query
4. Copy the following query to the Query window. Then click Execute: 

delete SOP10100 where SOPNUMBE = 'xxx' delete SOP10200 where SOPNUMBE = 'xxx'

Note In this query, xxx represents the actual invoice number.

Back to the top Back to the top

Method 2

Follow these steps if the following conditions are true:

• The inventory is reduced.
• The transaction has not been posted on the General Ledger.
• The transaction has been posted to Receivables Management.
• The transaction is in Sales Order Processing work and history.
1. On the Transactions menu, point to Financial, and then click General.
2. Enter a journal entry for the accounts that were used in the SOP transaction.Note In this entry, you cannot drill back to the SOP transaction.
3. Click Start, point to Microsoft SQL Server 2005, and then click SQL Server Management Server. If you are using SQL 2000, click Start, point to Microsoft SQL Server, and then click Query Analyzer.
4. In the database list, click the company database.
5. Click Query.
6. Run the following query against the company database.

delete SOP10100 where SOPNUMBE = 'xxx' delete SOP10200 where SOPNUMBE = 'xxx'

Note In this query, xxx represents the actual invoice number.

Back to the top Back to the top

Method 3

Follow these steps if the following conditions are true:

• The inventory is reduced.
• The transaction has been posted to General Ledger.
• The transaction has not been posted to Receivables Management.
• The transaction appears in Sales Order Processing work tables and history tables.
1. On the Transactions menu, point to Sales, click Transaction Entry, and then and enter an invoice for the SOP transaction that was originally entered. Post to General Ledger, and then delete the batch that is created. This action prevents the invoice from updating the General Ledger two times.Note In this entry, you cannot drill back to the SOP transaction.
2. Click Start, point to Microsoft SQL Server 2005, and then click SQL Server Management Server. If you are using SQL 2000, click Start, point to Microsoft SQL Server, and then click Query Analyzer.
3. In the database list, click the company database.
4. Click Query.
5. Run the following query against the company database.

delete SOP10100 where SOPNUMBE = 'xxx' delete SOP10200 where SOPNUMBE = 'xxx'

Note In this query, xxx represents the actual invoice number.

Back to the top Back to the top

Method 4

Follow these steps if the following conditions are true:

• The inventory is reduced.
• The transaction has not posted to the General Ledger.
• The transaction has not posted to Receivables Management.
• The transaction appears in Sales Order Processing work tables and history tables.
1. On the Transactions menu, point to Financial, and then click General.
2. Enter a journal entry for the cost of goods sold and for the inventory accounts that would have been affected by the SOP transaction.Note In this entry, you cannot drill back to the SOP transaction.
3. Click Start, point to Microsoft SQL Server 2005, and then click SQL Server Management Server. If you are using SQL 2000, click Start, point to Microsoft SQL Server, and then click Query Analyzer.
4. In the database list, click the company database.
5. Click Query
6. Run the following query against the company database. 

delete SOP10100 where SOPNUMBE = 'xxx' delete SOP10200 where SOPNUMBE = 'xxx'

Note In this query, xxx represents the actual invoice number.
7. On the Transactions menu, point to Sales, and then click Transaction Entry.
8. Enter an invoice for the SOP transaction that was originally entered. You can post this batch through the General Ledger.Note In this entry, you cannot drill back to the SOP transaction.

Back to the top Back to the top

Method 5

Follow these steps if the following conditions are true:

• The inventory is not reduced.
• The transaction has posted to the General Ledger.
• The transaction has posted to Receivables Management.
• The transaction appears in Sales Order Processing work tables and history tables.

Resolution 1

1. On the Transaction menu, point to Sales, click Posted Transactions, and then void the receivables invoice that was created.
2. If the journal entry has posted to the General Ledger, Point to Financial on the Transaction menu, and then click General.
3. Enter a reversing journal entry to reverse the original journal entry that was created from the SOP transaction.
4. If the journal entry is still in a batch in the GL, point to Financial on the Transaction menu, and then click Batches. Delete the record from the batch.
5. Click Start, point to Microsoft SQL Server 2005, and then click SQL Server Management Server. If you are using SQL 2000, click Start, point to Microsoft SQL Server, and then click Query Analyzer.
6. In the database list, click the company database.
7. Click Query.
8. Run the following query against the company database.

delete SOP30200 where SOPNUMBE = 'xxx' delete SOP30300 where SOPNUMBE = 'xxx'

Note In this query, xxx represents the actual invoice number.

Resolution 2

1. On the Transaction menu, point to Inventory, click Transaction Entry, and then enter an inventory adjustment to take the quantity out of Inventory. Do not post this to the General Ledger.Note In this entry, you cannot drill back to the SOP transaction.
2. Click Start, point to Microsoft SQL Server 2005, and then click SQL Server Management Server. If you are using SQL 2000, click Start, point to Microsoft SQL Server, and then click Query Analyzer.
3. In the database list, click the company database.
4. Click Query.
5. Run the following query against the company database. 

delete SOP10100 where SOPNUMBE = 'xxx' delete SOP10200 where SOPNUMBE = 'xxx'

Note In this query, xxx represents the actual invoice number.

Back to the top Back to the top

Method 6

Follow these steps if the following conditions are true:

• The inventory is not reduced.
• The transaction has not posted to the General Ledger.
• The transaction has posted to Receivables Management.
• The transaction appears in Sales Order Processing work tables and history tables.
1. On the Transaction menu, point to Sales, click Posted Transactions, and then void the receivables invoice that was created.
2. Click Start, point to Microsoft SQL Server 2005, and then click SQL Server Management Server. If you are using SQL 2000, click Start, point to Microsoft SQL Server, and then click Query Analyzer.
3. In the database list, click the company database.
4. Click Query.
5. Run the following query against the company database.

delete SOP30200 where SOPNUMBE = 'xxx' delete SOP30300 where SOPNUMBE = 'xxx'

Note In this query, xxx represents the actual invoice number.
6. On the Transaction menu, point to Sales, and then click Sales Batches.
7. Post the batch again.

Back to the top Back to the top

Method 7

Follow these steps if the following conditions are true:

• The inventory is not reduced.
• The transaction has posted to the General Ledger.
• The transaction has not posted to Receivables Management.
• The transaction appears in Sales Order Processing work tables and history tables.

Resolution 1

1. If the journal entry has posted to the General Ledger, click Transaction, point to Financial, and then click General.
2. Enter a reversing journal entry to reverse the original journal entry that was created from the SOP transaction.
3. If the journal entry is still in a batch in the General Ledger, click Transaction, point to Financial, and then click Batches. Delete the record from the batch.
4. Click Start, point to Microsoft SQL Server 2005, and then click SQL Server Management Server. If you are using SQL 2000, click Start, point to Microsoft SQL Server, and then click Query Analyzer.
5. In the database list, click the company database.
6. Click Query.
7. Run the following query against the company database.

delete SOP30200 where SOPNUMBE = 'xxx' delete SOP30300 where SOPNUMBE = 'xxx'

Note In this query, xxx represents the actual invoice number.
8. On the Transaction menu, point to Sales, and then click Sales Batches.
9. Post the batch again.

Resolution 2

1. On the Transaction menu, point to Inventory, and then click Transaction Entry.
2. Enter an inventory adjustment to take the quantity out of Inventory. Do not post this adjustment to the General Ledger.Note In this entry, you cannot drill back to the SOP transaction.
3. On the Transactions menu, point to Sales, and then click Transaction Entry.
4. Enter an invoice for the SOP transaction that was originally entered. Post to the General Ledger. Delete the batch that is created to prevent the invoice from updating the General Ledger two times.Note In this entry, you cannot drill back to the SOP transaction.
5. Click Start, point to Microsoft SQL Server 2005, and then click SQL Server Management Server. If you are using SQL 2000, click Start, point to Microsoft SQL Server, and then click Query Analyzer.
6. In the database list, click the company database.
7. Click Query.
8. Run the following query against the company database.

delete SOP10100 where SOPNUMBE = 'xxx' delete SOP10200 where SOPNUMBE = 'xxx'

Note In this query, xxx represents the actual invoice number.

Back to the top Back to the top

Method 8

Follow these steps if the following conditions are true:

• The inventory is not reduced.
• The transaction has not posted to the General Ledger.
• The transaction has not posted to Receivables Management.
• The transaction appears in Sales Order Processing work and history tables.
1. Click Start, point to Microsoft SQL Server 2005, and then click SQL Server Management Server. If you are using SQL 2000, click Start, point to Microsoft SQL Server, and then click Query Analyzer.
2. In the database list, click the company database.
3. Click Query.
4. Run the following query against the company database.

delete SOP30200 where SOPNUMBE = 'xxx' delete SOP30300 where SOPNUMBE = 'xxx'

Note In this query, xxx represents the actual invoice number.
5. On the Transaction menu, point to Sales, click Sales Batches.
6. Post the batch again.

Back to the top Back to the top

Desbloqueos en Great Plains

August 9, 2007 at 12:39 pm | Posted in GP Support | Leave a comment

En circunstancias poco frecuentes, Microsoft Great Plains puede producir registros inusuales en las respuestas. Pero aprender a solucionarlo es sencillo.

Cuando surge un problema de operaciones, como un usuario o un proceso por lotes bloqueado, hay que solucionarlo rápidamente. Afortunadamente, el remedio para estos problemas sólo requiere unos pasos sencillos, comenta Andrew Karasev, principal responsable de tecnología en Alba Spectrum Technologies USA, una compañía de personalización de Microsoft® Business Solutions—Great Plains® CRM ubicada en Chicago.

Problemas de desconexión de usuarios

Según Karasev, Great Plains Certified Master, no es raro que un usuario cierre el equipo de forma inadecuada, descuidando los procesos metódicos de cierre de sesión de Great Plains. Cuando eso sucede, el día siguiente puede comenzar con el pie contrario. “Si un usuario cierra sesión en su equipo de esa manera, Great Plains no tiene ningún comando de cierre de sesión del usuario”, comenta Karasev. “Por lo que si el usuario intenta iniciar sesión al día siguiente, obtiene un mensaje de error indicando que ya está conectado”.

La solución, explica Karasev, es realizar los pasos siguientes para eliminar el estado de sesión iniciada procedente de la sesión anterior:

  1. En Microsoft SQL ServerTM, abra SQL Query AnalyzerTM.
  2. Cambie a la base de datos de DYNAMICS y ejecute la secuencia de comandos siguiente: DELETE ACTIVITY WHERE USERID='[inserte aquí el Id. de usuario]’.
  3. Si hay más de un usuario registrado con este problema, elimine los usuarios de Great Plains en Sistema > Actividad usuario > Eliminar usuario.
  4. Si desea trabajar a nivel de archivos, solicite a todos los usuarios de Great Plains que cierren la sesión.
  5. Localice el archivo raíz de DYNAMICS en el servidor donde tenga su estructura de archivos de Great Plains.
  6. En la carpeta SYSTEM de DYNAMICS, elimine los archivos ACTIVITY.*.

Lotes bloqueados

Los lotes, como un grupo de cuentas de proveedores, pueden quedar bloqueados cuando un usuario ha enviado el lote y a continuación el sistema se bloquea. Al igual que con el atasco de usuario por el estado de inicio de sesión residual, puede desbloquear el lote con unos pasos sencillos.

  1. Cierre la sesión de Great Plains.
  2. Vuelva a iniciar sesión utilizando el mismo Id. de usuario.
  3. Great Plains mostrará un mensaje preguntando si desea recuperar el lote enviado.

Con eso debería solucionarse el problema. Sin embargo, de no ser así, intente el procedimiento siguiente:

  1. En Microsoft SQL Server, el registro de lotes se almacena en la tabla Cabecera de Lote: SY00500 en la base de datos de la compañía. Localice y borre los campos siguientes: BCHSTTUS y MKDTOPST. Puede que en algunos casos también tenga que eliminar el registro de SY00800 de la base de datos de DYNAMICS.
  2. En Pervasive o c-tree, instale el controlador ODBC correspondiente y “conecte” las tablas: SY00500, SY00800 con Microsoft Access. (Use la técnica de tablas vinculadas).
  3. Ahora ya debería poder procesar sus lotes con normalidad.

Si proporciona soporte técnico para Great Plains, debe aprender estos dos trucos para ahorrar tiempo y hacer que los usuarios sean tan productivos como sea posible.

You receive a “The stored procedure createSQLTmpTable returned the following results, DBMS: 12 Great Plains: 0” error message when you select a vendor in Vendor Inquiry

August 9, 2007 at 12:28 pm | Posted in GP Support | Leave a comment

SYMPTOMS

“The stored procedure createSQLTmpTable returned the following results,
DBMS: 12 Great Plains: 0.” occurs when selecting a Vendor in Vendor Inquiry.

CAUSE

This error can occur if the PM Keys are missing or corrupt.

RESOLUTION

To resolve this error, re-create the PM Keys.

Microsoft SQL Users

1. Make a full backup that can be restored to should undesirable results occur.
2. Have all users exit Great Plains.
3. Click Start, Click Programs, Click Microsoft SQL Server, and then Click Query Analyzer.
4. From the database drop-down list, select your Company database.
5. Type the following script in the query pane.

delete PM00400

6. Launch back into Great Plains.
7. Click File, Click Maintenance, and then Click Check Links
8. Under Series, select Purchasing.
9. In the file list, select Payables History Logical and click Insert and then select Payables Transaction Logical and then click Insert.
10. Click OK.
11. When prompted, select Print to Screen for the Error Log Report.

The Computer Check batch in the Payables Batch IDs window does not have the correct posting status in Microsoft Dynamics GP

July 27, 2007 at 8:11 pm | Posted in Uncategorized | Leave a comment

Note Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.

SYMPTOMS

When you view the Computer Checks batch ID in the Payables Batch IDs window, the Available posting status is not in the Status column. This problem occurs in Microsoft Dynamics GP and in Microsoft Business Solutions – Great Plains.

CAUSE

This problem may occur if you try to use Microsoft Dynamics GP together with a local area network (LAN) database.

RESOLUTION

If a backup is unavailable to restore the Payables Computer Check batch, follow these steps:

1. If you cannot change the status of the Payables Computer Check batch to the Available status, complete the procedure in Microsoft Knowledge Base article 850289. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

850289 Batch held in Posting, Receiving, Busy, Marked, Locked, or Edited status
2. Click Computer Checks, click Select, and then click Delete.
3. Determine whether the invoices and the checks are updated in Payables in Microsoft Dynamics GP. Also, determine whether there is any amount remaining on open invoices that were paid by the check batch. To do this, use one of the following methods:

• Use the Payables Transaction inquiry. To do this, point to Purchasing on the Inquiry menu, and then click Transaction by Document.
• Use the Payables Aged Trial Balance report. To do this, point to Purchasing on the Reports menu, and then click Trial Balance.
• Use the Transaction History report. To do this, point to Purchasing on the Reports menu, and then click History.
4. If the invoices and the checks are still in the open file, and the amount that is remaining equals the original amount, reselect the vouchers for payment. To do this, use Select Checks. Before you post to General Ledger in Microsoft Dynamics GP, confirm that the original batch did not update General Ledger. If General Ledger was updated, post the reselected checks to Payables and not to General Ledger.
5. If there are invoices and checks in the open file, and the amount remaining is zero, verify that the links to the “PM Transaction Logical” file exist.

To do this, use the appropriate step:

• In Microsoft Dynamics GP 10.0, click Maintenance on the Microsoft Dynamics GP menu, and then click Check Links.
• In Microsoft Dynamics GP 9.0, click Maintenance on the File menu, and then click Check Links.

If Microsoft Dynamics GP can find the appropriate information to apply to the voucher, the file maintenance will move the voucher to history. If Microsoft Dynamics GP cannot find the information, Microsoft Dynamics GP will change the amount remaining to the original amount of the document. Use Select Checks to reselect the vouchers for payment. Print a Detail Trial Balance report to verify that General Ledger is correct. To do this, click Financial on the Reports menu, and then click Trial Balance. Also, verify that the figures in the Vendor Credit Summary window are correct. To do this, click Purchasing on the Cards menu, and then click Summary.
6. If both the voucher and the payment are in history, Payables Management will be correct after the check batch is deleted in step 2. Check General Ledger to make sure that everything is posted correctly.

Batches Hanging

July 27, 2007 at 7:50 pm | Posted in GP Support, Undocumented Features | Leave a comment

1. Make sure that you have a current backup of the company database. To create the backup in Microsoft Dynamics GP, follow these steps after all users log off from Microsoft Dynamics GP:

a. On the File menu, click Backup.
b. In the Company Name list, click the company that you want to back up.
c. In the Select the backup file box, click the yellow folder to open the location in which you want to put the backup file.

 2. View the contents of the following tables to verify that all users are logged off:

DYNAMICS..ACTIVITY,
DYNAMICS..SY00800,
DYNAMICS..SY00801,
TEMPDB..DEX_LOCK,
and TEMPDB..DEX_SESSION.

To do this, run the following script.

SELECT * FROM DYNAMICS..ACTIVITY
SELECT * FROM DYNAMICS..SY00800
SELECT * FROM DYNAMICS..SY00801
SELECT * FROM TEMPDB..DEX_LOCK
SELECT * FROM TEMPDB..DEX_SESSION

Note When all users are logged off from Microsoft Dynamics GP, these tables will not have any records in them.

3. If no results are returned, go to step

5. Otherwise, clear the stuck records by using any of the following appropriate scripts.

DELETE DYNAMICS..ACTIVITY
DELETE DYNAMICS..SY00800
DELETE DYNAMICS..SY00801
DELETE TEMPDB..DEX_LOCK
DELETE TEMPDB..DEX_SESSION

4. Run the following script against the company database. Replace XXX with the batch number or the name of the batch that you are trying to post or select in Microsoft Dynamics GP.

UPDATE SY00500 SET MKDTOPST=0, BCHSTTUS=0 where BACHNUMB=’XXX’

Note The value of BACHNUMB is the same as the value of the Batch ID window in Microsoft Dynamics GP.

5. Verify the accuracy of the transactions.

6. Verify that you can edit and post the batches.

Deleting Batches When Not Using the General Ledger Module

June 26, 2007 at 6:45 pm | Posted in GP Support | Leave a comment

Question:

I do not use the General Ledger module and would like to delete all of the General Ledger batches that are currently showing in my series post window.

Answer:

Make sure you have a valid backup before working forward with the following steps and renaming or running delete statement on the following files.

Instructions for Microsoft SQL:

Run this delete statement against the company datbase that you are working in:

Delete SY00500

For Pervasive SQL or Ctree:

Rename the SY00500 file in the company that you are working in. This file is located in the Company folder on the server.

**If batches exist for other modules, Check Links must be run on the work tables for the individual series so they can be accessed. Refer to the TechKnowledge titled, “Batch Held in Posting, Receiving, Busy, Marked, Locked, or Edited Status..” for the file names Check Links should be run on (depending on series).

Additional Steps: If for some reason Reconcile (Utilities-financial-reconcile-Batches) is used these batches will be brought back. To avoid this, delete statements (or renaming) must be done on the Financial work tables as well.

Delete GL10000

Delete GL10001

This article was TechKnowledge Document ID: 21118

Truncate the Log Size of All Databases on a SQL Server

June 24, 2007 at 5:29 pm | Posted in SQL Tips | Leave a comment

This piece of code will truncate the log file size for ll the databases in a SQL Server. It has been tested against SQL 2000 and 2005

–delcare variables
declare @vchrTable varchar(200),
@vchrMsg varchar(200)

–declare the cursor
DECLARE curTables CURSOR FOR
SELECT name from master..sysdatabases  order by 1

–open the cursor
OPEN curTables

–loop through the cursor lines
FETCH NEXT FROM curTables INTO @vchrTable
WHILE @@FETCH_STATUS = 0
BEGIN
set @vchrMsg = ‘DBCC SHRINKDATABASE ( [‘ + @vchrTable + ‘] ,10);’
print @vchrMsg
exec (@vchrMsg)

set @vchrMsg = ‘backup log [‘ + @vchrTable + ‘] with truncate_only;’
print @vchrMsg
exec (@vchrMsg)
–backup log tstoday with truncate_only

FETCH NEXT FROM curTables INTO @vchrTable
END

–clean up
CLOSE curTables
DEALLOCATE curTables

How to disable Advanced Security

June 23, 2007 at 4:49 pm | Posted in GP Support | 2 Comments

use DYNAMICS
–delete WDC41101
select * from WDC41101

How to create a Dexsql.log file for Microsoft Dynamics GP and Great Plains

June 23, 2007 at 4:38 pm | Posted in GP Support | Leave a comment

 

TechKnowledge Content

The information in this document applies to:

– Great Plains on Microsoft SQL Server
– Great Plains Standard on MSDE

SUMMARY

This article describes how to create a Dexsql.log file for Microsoft Dynamics GP or for Microsoft Business Solutions – Great Plains.

MORE INFORMATION

When you receive an error in Great Plains, a Dexsql.log file is a helpful tool that can frequently provide more information to troubleshoot issues. If you can re-create the error, the Dexsql.log file can capture this information.

1. Open the Dex.ini file that exists in your Great Plains application folder in Windows Explorer. Find the following statements in the Dex.ini file:

SQLLogSQLStmt=FALSE
SQLLogODBCMessages=FALSE
SQLLogAllODBCMessages=FALSE

Note If you use Great Plains 6.0, you must add the SQLLogAllODBCMessages=FALSE line to the Dex.ini file.

2. If the statements are currently set to FALSE, set them to TRUE:

SQLLogSQLStmt=TRUE
SQLLogODBCMessages=TRUE
SQLLogAllODBCMessages=TRUE

3. Start Great Plains. If you are already in Great Plains, exit, and then restart.

4. Take the steps that are required to re-create the scenario where you received the error message. Stop the steps to re-create just before you receive the error message.

5. Go back into your Great Plains application folder in Windows Explorer. You should now have a file named Dexsql.log. Delete or rename this file. If you do not see the Dexsql.log file, click View, and then click Refresh so you can see the new file.

6. Go back to Great Plains and finish the final steps to re-create the error message.

7. You will now have a new Dexsql.log file in your Great Plains folder.

8. Open the Dex.ini file and set the statements back to FALSE, or change back to the original settings.

SQLLogSQLStmt=FALSE
SQLLogODBCMessages=FALSE
SQLLogAllODBCMessages=FALSE

 

smDropUser Returned the Following Error Code, DBMS: 0 DYNAMICS: 20422

June 23, 2007 at 4:36 pm | Posted in GP Support | Leave a comment

Issue

As I attempt to delete a user within eEnterprise for SQL the following error occurs, “smDropUser returned the following error code, DBMS: 0 DYNAMICS: 20422.” What does this error mean and how can I correct it? The more info. button states that you have specified to control users through the Dynamics application but the Userid passed into this stored procedure did not exist in either the sysusers or sysalternates of the database.

Resolution

Follow these steps to correct the error:

1. Go to Enterprise Manager.
2. Click the + sign next to the Microsoft SQL Server name.
3. Click the + sign next to the Security Folder.
4. Click on Logins.
5. Find the login that you are attempting to drop within Great Plains. Right-click and choose Delete.
6. Within Query Analyzer execute this command on the DYNAMICS database:

delete SY01400 where USERID = ‘XXX’
delete SY60100 where USERID = ‘XXX’
(where XXX = the userid that you want to remove)

< OR >

1. If no access to Enterprise Manager or the Query Analyzer, the same results can be accomplished within the Great Plains application.

2. Under Setup | System | SQL Options unmark both Server Logins and Database Users.

3. Proceed to delete the User within Great Plains under, Setup | System | User.

4. Once deleted go back to the SQL Options window from Step 2 and mark both options if desired.

SYMPTOMS

When you create a new user in Microsoft Dynamics GP 9.0, you may receive the following error message:

The creation of a login failed for unknown error. Contact your administrator for assistance.

Additionally, you may receive the following error message in the Dexsql.log file:

[Microsoft][SQL Native Client][SQL Server]User, group, or role ‘XXXX’ already exists in the current database.*/

CAUSE

This problem may occur if the user already exists in the database.

RESOLUTION

To resolve this problem, follow these steps:

1. Run the following script in Microsoft SQL Query Analyzer, in Microsoft SQL Server Management Studio, or in the Support Administrator console. Run the following script against the DYNAMICS database and against all the company databases.

sp_dropuser 'XXXX'

Note Replace XXXX with the user ID that you want to create.
2. Run the following script in SQL Query Analyzer, in SQL Server Management Studio, or in the Support Administrator console. Run the following script against the master database.

sp_droplogin 'XXXX'

Note Replace XXXX with the user ID that you want to create.
Next Page »

Blog at WordPress.com.
Entries and comments feeds.