Topic: ‘SQL Statements’

While setting up an integration for a client, it became apparent that there is a problem with the MSGP Publisher that will only allow you to publish messages to the scribepublisherqueue for specific tables. After adding a table to the publisher and choosing options, I tried to save the publisher and it threw the error “Error when setting up GP Publisher: Sequence contains more than one matching element”. I never did figure out what that meant. After doing my diligence and checking the knowledgebase and forums on Open Mind to no avail, Scribe support was contacted for some assistance. They informed me that there is a known issue with many tables not being accounted for by the publisher and that it is a defect and there is no fix at this time. This left me with no resolution or workaround (thanks Scribe!) While Scribe gave us what they considered an acceptable answer to the problem, it didn’t solve anything for our client’s integration which left us to figure out one on our own. Here is what I came up with: Read the rest of this entry »

You have created a stored procedure, tested it, and figured out how to call it from X++. However, when you execute your code the procedure doesn’t work as expected. Your calculations are inconsistent.

Execution feedback is one reason that will cause inconsistent, wrong calculations when your stored procedures are called from X++. Feedback messages interrupt your X++ execution of your SQL stored procedure. For example, if you have a have a While Loop that updates five data rows only three might be updated because the execution was interrupted, by the feedback, before your Loop got to row four.

Read the rest of this entry »

Below is a simple SQL Statement to change the format of Contacts’ names in Microsoft CRM.  We didn’t reinvent the wheel with this, and we’re sure this is posted somewhere, but we have it and thought we would share.

Run this SQL statement against the MSCRM Organization DB.

(As always, before you make a modification to the DB, it is recommended that you make a backup of the DB or the tables that are being modified.)

 
“Last Name, First Name” format for Contacts:

UPDATE contactbase SET fullname = ISNULL(lastname, ”) + ‘, ‘ +

ISNULL(firstname, ”)

“First Name Last Name” format for Contacts:

UPDATE contactbase SET fullname = ISNULL(firstname, ”) + ‘  ‘ + 

ISNULL(lastname, ”)

 *This solution involves making changes to 1 or more databases.  It is not suggested that changes made to a database without first making a backup of the database.  If possible, alternatives such as a MS Hotfix or MS rollup should be used to try to solve the issue, before modifying a database.  If you do decide to modify the database, it is suggested that the modifications are made by an IT Director, Technical Specialist or MIS department.  Serious problems might occur if you modify the database incorrectly.  Be sure to backup the database before making changes.  Any problems might require that you rollback your databases and even result in the loss of data records.  I.B.I.S., Inc. cannot guarantee that these problems can be solved. Modify the database at your own risk.