Security settings

Useful article on security settings in Dynamics GP 10 from the legend David Musgrave, only just discovered by me :)


http://blogs.msdn.com/b/developingfordynamicsgp/archive/2008/11/10/how-to-transfer-security-tasks-and-roles-between-systems.aspx

Event ID ( 27745 ) in Source ( Windows SharePoint Services 3 ) cannot be found

The description for Event ID ( 27745 ) in Source ( Windows SharePoint Services 3 ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: #50071: Unable to connect to the database SharePoint_Config on server. Check the database connection information and make sure that the database server is running.

Resolution
The database SharePoint_Config had a 2GB log file, I truncated this and then ran a Windows Sharepoint Timer Service restart for it to kick into action

SQL SERVER - 2005 - Find Index Fragmentation Details - Slow Index Performance - script

The following document was from the great Pinal Dave on from the following website: http://www.sqlauthority.com/. This article was extremely useful when I was troubleshooting performance issues in Dynamics GP:


Just a day ago, while using one index I was not able to get the desired performance from the table where it was applied. I just looked for its fragmentation and found it was heavily fragmented. After I reorganized index it worked perfectly fine. Here is the quick script I wrote to find fragmentation of the database for all the indexes.

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
GO

You can REBUILD or REORGANIZE Index and improve performance. Here is article SQL SERVER - Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script for how to do it.

Reference : Pinal Dave (http://www.sqlauthority.com/)

SQL SERVER - 2005 - Find Index Fragmentation Details - Slow Index Performance

The following document was found in www.sqlauthority.com and was extremely useful.

Index Rebuild : This process drops the existing Index and Recreates the index.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO

Index Reorganize : This process physically reorganizes the leaf nodes of the index.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO

Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

Reference : Pinal Dave (http://www.SQLAuthority.com)

Parser Error Message: Cannot use 'partitionResolver' unless the mode is 'StateServer' or 'SQLServer'

Installed SSRS 2005 on a Sharepoint MOSS 2007 server and what a pain as I realise SSRS did not work due to confliction with MOSS. Error was shown when trying to access it:

"Parser Error Message: Cannot use 'partitionResolver' unless the mode is 'StateServer' or 'SQLServer'"

1. Create a new virtual directory off \inetpub\wwwroot\
2. Give it a specific port number I used SSRS / 87
3. Configure (in SSRS) a new "Report Server Virtual Directory"
4. Configure (in SSRS) a new "Report Manager Virtual Directory"
5. Go to Web Service Identity and click the New button next to Report Server
6. Create a new App Pool for RS to run within, I used SSRSAppPool
7. Set the authenticated user for the new app pool to the SQL Server Service account you used to set up MOSS such as OSS_USER
8. This should have all the SQL permissions you need to run SSRS (I hope...)
9. Set Report Server and Report Manager to the new app pool and click Apply
10. After making these changes run IISRESET go to the following URL's:
http://Myserver:87/ReportServer/
http://Myserver:87/Reports/

SQL versions

Identify version by running query:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Look up on list below:

RTM 90.1399
SQL Server 2005 Service Pack 1 90.2047
SQL Server 2005 Service Pack 2 90.3042

SQL 2000 has similar query with the following versions:

RTM 80.194.0
SQL Server 2000 SP1 80.384.0
SQL Server 2000 SP2 80.534.0
SQL Server 2000 SP3 80.760.0
SQL Server 2000 SP3a 80.760.0
SQL Server 2000 SP4 8.00.2039

Microsoft SQL Server 2008 R2 November CTP Report Builder 3.0

Microsoft SQL Server 2008 R2 Report Builder 3.0 provides an intuitive report authoring environment for business and power users. It supports the full capabilities of SQL Server 2008 R2 Reporting Services. The download provides a stand-alone installer for Report Builder 3.0.

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=f78b6b1e-8ccb-407a-bc3e-7955d60e1a6c

UNHANDLED SCRIPT EXCEPTION: Wrong number of parameters sent to script ‘convertEXT40100forGulfStream’

Updating the DYNAMICS Database from Great Plains 8.0 SP5 to Dynamics GP 10 and we receive the following error.

UNHANDLED SCRIPT EXCEPTION: Wrong number of parameters sent to script ‘convertEXT40100forGulfStream’

Issue related to the extender product being installed and The latest Hotfix for Service Pack 4. The GP install was GP 10 SP4+hotfix. To correct the issue:

1. Uninstall the GP 10 software

2. Intall Dynamics GP 10 and Service Pack 4. DO NOT APPLY FURTHER HOTFIXES

3. Run the Dynamics GP upgrade

4. Apply Hotfix afterwards if required.

Management Reporter and future of FRX Reporting 6.7

Management Reporter is the new reporting tool released with Dynamics GP 2010 and will phase out FRx Reporter 6.7. Management Reporter is now in beta Microsoft have functionality lists, plans for future releases, migration guides etc on Customersource and Partnersource

Timeline

MR V2, which will begin releasing with Dynamics GP 2010 and will contain most of the FRx Reporter functions.

MR V3 (Calendar 2012) will contain Budgeting, Planning, and Forecasting.

MR V4 (Calendar 2014) will contain Eliminations and Allocations and absorb additional functions from the Enterprise Reporting product



Please note that clients with Microsoft Forecaster 7.0 will not be able to link reports with Management Report until Feature Pack 1 is released. This however will simply introduce an importer to copy data back into Dynamics GP 2010 budgets for reporting. 2014 it will be completely replaced by Management Reporter

"The stored procedure createSQLTmpTable returned the following results: DBMS: 12" exceptions error in Dynamics GP

• "The stored procedure createSQLTmpTable returned the following results: DBMS: 12" exceptions error in Dynamics GP
  
  https://community.dynamics.com/blogs/developingforgp/comments/16090.aspx
  
  Developing for Dynamics GP
  Developing for Dynamics GP by David Musgrave (Australia) and the Microsoft Dynamics GP Developer Support Team (USA
  
  Syndicated From: http://blogs.msdn.com/DevelopingForDynamicsGP/
  
  "The stored procedure createSQLTmpTable returned the following results: DBMS: 12" exceptions
  Many of inquiry windows in Microsoft Dynamics GP have been optimized to leverage SQL Server to increase performance. This is especially common with transaction inquiry windows which can combine data from more than one table (ie. Work, Open and History).
  
  The method that is usually used is based on a temporary table. This temporary table (created in the TempDB) is where the data displayed in the scrolling window of the inquiry window comes from. When the search criteria are entered and the window is redisplayed, the Dexterity code grabs the physical name for the temporary table and uses it to generate the SQL commands to insert the data from the appropriate tables. It then uses pass through SQL to run the commands and populate the temporary table. Once the temporary table is populated the data is displayed in the scrolling window.
  
  If an error occurs while the pass through SQL script is being executed it will look similar to the error messages below (using Dexterity message ID 18060):
  
  The stored procedure createSQLTmpTable returned the following results: DBMS: 2627, Microsoft Dynamics GP: 0.
  
  The stored procedure createSQLTmpTable returned the following results: DBMS: 12, Microsoft Dynamics GP: 0.
  
  The stored procedure createTmpTable returned the following results: DBMS: 12, Microsoft Dynamics GP: 0.
  
  These error messages refer to a createSQLTmpTable or createTmpTable stored procedure. However, the stored procedures referenced do not actually exist. The message is in fact referring to pass through the SQL script called from the Dexterity code and the name is just an arbitrary string assigned by the developer.
  
  You can usually look up the DBMS (Database Management System) error codes or SQL Server error codes on the Internet and find out the exact meanings. Two common DBMS error codes seen from the pass through SQL are 2627 and 12.
  
  DBMS: 2627 refers to a duplicate key error and is covered in more detail in the article: Identifying Duplicate Transactions.
  
  DBMS: 12 is a little bit more complex as there is little or no documentation on what error code 12 really means. Error code 12 is a syntax error and is caused by the SQL script attempting to reference a resource that does not exist. The easiest way to explain the error is by describing a scenario:
  
  1. User logs into Microsoft Dynamics GP. SQL Server assigns a Server Process ID or SPID to the connection.
  
  
  2. User opens inquiry window and Dexterity creates private temporary table in TempDB associated with the current SPID.
  
  
  3. User leaves inquiry window open.
  
  
  4. Connection to SQL Server is lost. Reasons I have heard of for losing the connection include:
  
  
  o Workstations set to Stand By, Sleep or Hibernate when not used for a period of time.
  
  o Timeout settings on SQL Server, disconnecting idle sessions.
  
  o Faulty network infrastructure causing the connection to the server to be dropped.
  
  o Not installing critical updates, causing a faulty network driver to drop the connection.
  
  5. User comes back to inquiry window and starts using the window again.
  
  6. Dexterity realizes the connection is dropped and automatically re-establishes a new connection.... with a new SPID.
  
  7. The new SPID cannot "see" the temporary table created when the window was opened and so when the pass through SQL script executes and uses the name of the temporary table it expects to be there, an error 12 is generated.
  
  Most people find that when they close the window or the application and try again that it works fine. Which is exactly what we would expect:
  
  · Restarting the application will create a new SPID and the temporary table will be created associated with that SPID and all is fine.
  
  · Closing and re-opening the window will create a new temporary table associated with the current SPID and all is fine.
  
  This explains why the error is so hard to reproduce and why no obvious causes can be found.
  
  The Knowledge Base (KB) Article below discusses a similar error and how stopping and restarting the SQL Server service will fix the error and clean-up the TempDB:
  
  Error message when you double-click the "Journal Entry" field in the Detail Inquiry window in General Ledger in Microsoft Dynamics GP: "Stored Procedure Creating SQL TMP Table DBMS: 12 eEnterprise: 0" (KB 852594)
  
  Please let me know if you find this information useful.
  
  
  ALSO FOUND ON THIS ISSUE:
  
  http://groups.google.com/group/microsoft.public.greatplains/msg/fc7ce61f9d965c8e
  
  
  The two most memorable cases when it took some time to find the cause for
  this:
  1. User's network cable was cramped between the wall and a filing cabinet
  almost cutting it in half by the time we found it. Amazingly, while she was
  getting other intermittent errors, the biggest issue was GP. However, this
  was also someone who is in GP all day every day entering a lot of
  transactions.
  
  
  2. Customer had ordered new computers for the entire accounting department
  and they all came with the network card set to automatically go to sleep
  after 15 minutes of inactivity.

Add company message warnings for TEST and HISTORICAL companies for GP 10

Message to help warn the user they are in a test company is possible.

Adding the following labels to the end of your company name will have the following effect:

"This company is set up for testing only. Do not use this company when processing live data."

"This company is used for storing historical information only. Do not use this company when processing current-year data."
Information above was summarised from the forwarded blogs of Vaidhyanathan Mohan of Dubai, United Arab and the great David Musgrave

http://www.vaidy-dyngp.com/2008/09/test-company-message-while-logging-on.html

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/02/setting-up-test-and-historical-companies.aspx

Dynamics GP Product Numbers

A big thank you to Christina Phillips for putting out a blog that helps with product information version numbers for GP 10.
http://dynamicsgpland.blogspot.com/2009/01/gp-10-version-list-heaven.html

lists are available below
GP 10
https://mbs.microsoft.com/downloads/customer/MDGP10_MSPVersionList.xls
(PartnerSource login required)

GP 9
https://mbs.microsoft.com/partnersource/products/greatplains/downloads/servicepack/microsoftdynamicsgp90_mspreleases.htm?printpage=false

Summary below:

Product ID Product
0 Dynamics
1 Dexterity
258 Project Accounting
309 Fixed Assets
346 Manufacturing
414 Human Resources
949 Field Service
1042 Interfund Management
1045 Revenue Expense Deferrals
1058 Cashbook (Bank Management)
1150 Scheduled Instalment
1157 Collections Management
1235 Safe Pay
1428 Electronic Reconcile
1472 EFT for Receivables Management
1493 SmartList
1632 Cash Flow Management
1878 Excel-Based Budgeting
2150 Payment Document Management
2199 Business Activity Statement
2277 Purchase Order Enhancements
2365 RMS Integration
2416 Control Account Management
2547 Enhanced Commitment Management
2788 Enhanced Intrastat
2992 CopierSeries
3096 VAT Daybook
3104 Advanced Security
3107 Extender
3180 Analytical Accounting
3258 Encumbrance Management
3278 Report Scheduler
3830 SmartList Builder
4067 ML Checks
4100 Perceptiones
4103 Analisis Crediticio
4350 Document Legales
4421 Grant Management
4612 Advanced Go Tos
4965 Electronic Signatures
4966 Audit Trails
5000 Ajustes Por Inflacion AF
5040 Shipping Documents
5064 COA
5373 Taxes and Returns
5597 HITB Report

Forecaster 70 does not appear listed as a product in FRX Designer

Forecaster 70 does not appear listed in the Accounting System product in FRX Designer. This prevents you from setting up a company linked to Forecaster 70 in FRX Report Designer.

RESOLUTION:
Problem is due to the install of the Forecaster 70 Direct Link not being able to update the actsys32.mdb file in the SYSDATA folder. By coying over the actsys32.mdb file from a system that contains it this will then work. Having the sysdata folder pointing locally often helps

Conditional Logos in Dynamics GP Reports

Following article on using conditional logos in Dynamics GP Reports from the great David Musgrave!

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/07/15/rw-conditional-logos-example.aspx

The following Report Writer example demonstrates how it is possible to show different logo images on a report based on a defined condition.

The Report Writer was used to create a custom report which has two logos on it. Then each logo covered with a string conditional calculated field, which has the background and foreground color set to white and the field mode of Hide When Empty. Then the condition is added to either set the field to a character (I used period/full stop) or empty. This causes the logo to be covered or exposed. The second calculated field uses the reversed condition

ERROR: System.Data.SqlClient.SqlError: Procedure 'taAnalyticsDistribution' expects parameter '@I_vDOCNMBR'

When you use Integration Manager for Microsoft Dynamics GP to run a General Journal integration with Analytical Accounting data mapped, you receive the following error message: ERROR: System.Data.SqlClient.SqlError: Procedure 'taAnalyticsDistribution' expects parameter '@I_vDOCNMBR'

Resolution: https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?WTNTZSMNWUKNTMMYYMYQKSYMXOSOOUMLVPZRNUSTZQRSNZUXRVTRPLZRSWRPMUYR

Integration Manager 10 and Analytical Accounting

Integration Manager 10 using the Dynamics GP eConnect adaptor GL Journal, is able to import Dynamics GP Journals with Analytical Accounting Dimension Codes. You must specify the journal number however in your source file to prevent issues. Other adaptors such as Sales Journals for SOP also have this ability. I know this was a problem before and really relieved that that this is available

Integration Manager Error on 64bit Windows 2008 server

Running integration manager for payable transactions import we get the following error:

"DOC 1 ERROR: Unable to cast COM object of type
'MSScriptControl.ScriptControlClass' to interface type
'MSScriptControl.IScriptControl'. This operation failed because the
QueryInterface call on the COM component for the interface with IID
'{0E59F1D3-1FBE-11D0-8FF2-00A0D10038BC}' failed due to the following error:
Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A
(TYPE_E_CANTLOADLIBRARY)).
DOC 2 ERROR: Unable to cast COM object of type
'MSScriptControl.ScriptControlClass' to interface type
'MSScriptControl.IScriptControl'. This operation failed because the
QueryInterface call on the COM component for the interface with IID
'{0E59F1D3-1FBE-11D0-8FF2-00A0D10038BC}' failed due to the following error:
Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A
(TYPE_E_CANTLOADLIBRARY)).
Integration Failed"

To solve run the following command

RUN CMD AS ADMINISTRATOR
regsvr32 C:\Windows\SysWOW64\MSScript.ocx

Mail Merge and Dynamics GP Macro

I know Dynamics GP is coming on with leaps and bounds with all the new technology tied in with Office 2007 but some modules such as Analytical Accounting do not have integration manager import access. So what do we do. We use the good old faithful Macro and word mail merge.

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?WTNTZSMNWUKNTMMYPKZPWNYYQNZZXYLWRONSRMONWZOWWVSVVMSQKOONNYYUZWUY#ECH

:) Macros will be around for a while but please comment if you know this is excluded in version 11

Reducing databases with an ever expanding transaction log

Reducing a database due to its transaction log expanding to a large size run the following steps:

All done in SQL Server Management Studio

1. Run the following t-sql command: DUMP TRANSACTION [db_name] WITH NO_LOG
2.Run a shrink on the database

SSRS and forcing rectangle to not overlap on to second page

Using SSRS 2005 we had an issue with a report that contained a table and a rectangle at the bottom of the report. The rectangle acted as a printed form at the bottom of the report, however if the table contained too many lines then part of the rectangle would print on the next page. The solution seems to be to put the a new list box underneath the rectangle and this then forced the rectangle to always print as a whole

what a pain. I believe this is easier in SSRS 2008

The value provided for the report parameter 'XXXX' is not valid for its type

I received an error in Visual studion 2005 when trying to pass a parameter to my datasource. My parameter was a datetime field which the user uses a calendar to select their require date. In the datasource was a column called TRXDATE which was a datetime column. Everytime i tried to rest the report within Visual Basic I received the following error ('XXXX' being my parameter)

The value provided for the report parameter 'XXXX' is not valid for its type

After a few wasted hours which i will never get back, I realised that the problem was down to Visual Basic 2005 and nothing to do with my report. When publishing the report to Report Server it worked fine.

My advice when receiving this issue is to try the following:

1. Test the datasource in SQL. Check that it works with a date being passed through with the format of YYYY-MM-DD. If no then the problem is the data source
2. Publish the report to reportserver and test. If problem then check parameter and datasource setup in Visual Studio 2005
3. Look forward to the upgrade to SQL Server 2008

'/' is an unexpected token. The expected token is '='. Line 9, position 33.

When running a large SQL Server Reporting Services 2005 report I was receiving the following error:

'/' is an unexpected token. The expected token is '='. Line 9, position 33.

Resolution was to change the Web Config file, located at the following location - C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer. I adjusted the executionTimeout element from 90 to 900 in this file to correct the issue.