Friday, November 29, 2013

Dynamics GP - Ar Reconcile Error

While running an AR reconcile, this error was coming up

A get/change first operation on table 'RM_Distribution_History' (45)

[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'S'.

[Microsoft][SQL Server Native Client 10.0][SQL Server]Unclosed quotation mark after the character string ') ORDER BY RMDTYPAL ASC ,DOCNUMBR ASC ,SEQNUMBR ASC '.

This was due to a customer ID set as AL'S and the ' was causing the code to error out

-- Script shows all records with an id containing the ' character
declare @ts as varchar (50)

set @ts = '%' + '''' + '%'
print @ts

select * from rm30301 where custnmbr like @ts

Dynamics GP - Report Writer - Calculated field to get a specific Vendor Address ID Info

User-Defined System Function -RW_PMADDRINFO

More info and Function list here

Dynamics GP - Users login and instead of initializing, GP hangs at a blank white screen

We experienced this issue due to a single faulty SOP transaction that could not post properly.
A single user was trying to do a transaction post on a specific invoice, and caused the entire database to lock up.
GP would get stuck attempting to post this bad invoice.
No other users could log in.

The only solution was to restore to a previous backup file, and run a check links and reconcile to fix the transaction.
The transaction itself was then deleted and re-entered manually.
This resolved the issue.

You can also try deleting any stuck transaction-level postings by running this script.

delete from sy00500 where  bchsourc like '*%'

It also corrupted our Reports dictionary,and that had to be restored from a backup.

Wednesday, November 27, 2013

SSRS 2008 - Action - Go to URL in a new window. Pass parameters. Handle Empty spaces in parameter values.

If you choose go to report, you cannot open the report in a new window.

You must select Go to URL and click the Fx button to enter an expression.
You will use SQL to write javascript that will generate the correct link when clicked on from the SSRS report

Points to note

  • Click on the SSRS report you want, and get the URL in the address bar. The only part you need is the actual report name after the first ? to the end of the report name
  • That will go in the space for "MyReportName"
  • Use Reportserver, not Reports
This code is entered into the Fx box.

="javascript:void('http://gpsql/Reportserver?MyReportName&FiltDate="+ Fields!DATERECD.Value+"&FiltRct="+replace(Fields!MOPRCTNM.Value," ","+") +"'))"

When clicked, it should open a new window and go to this link


Tuesday, November 26, 2013

Dynamics GP 2013 Analysis Cubes - analysis cubes dynamics gp sql server connection failed. please verify the dynamics gp sql server information

This error happens because your windows account does not have access to read the DYNAMICS database on the SQL server you are trying to read from.

On the GP SQL server, add your windows account to the dyngrp, and give it access to the dynamics and all company databases.

Friday, November 8, 2013