This one’s very good trick to Force SQL Server 2005 / 2008 to sort of use ORDER BY in Views.
Never knew this till Steve’s article. Good one for all SQL Developers (also for Part Time ones).
VAIDY
Microsoft has given us a limited period free availability of SQL Server 2008 R2 Features Introduction reference.
You can download this reference from the path: http://go.microsoft.com/fwlink/?LinkId=189147
And for more details on this offer: http://www.microsoft.com/learning/en/us/training/sql-server.aspx#tab1
VAIDY
In spite of having more critical deliverables for today, I still wanted to post this article, because of it’s importance.
This particular issue is no less than an acute pain. And I mean it.
In SQL Server Management Studio, from versions 2005 till 2008, there is an option called “Auto Recover” which triggers a process that save whatever we are writing in the Query Editor… every 5 to 10 mins. And that’s real pain for us, especially if we are working on remote or thru’ internet.
Sometimes, the system gets hung for so long that you would sit in front and wait till it “recovers” from the “recovery” itself.
BUT, there IS a solution which is unsupported by Microsoft SQL Server team, but harmless and effective. There is a registry entry called “AutoRecover Enabled“, which by default is set to 1. We have to change it to 0. This will not trigger that painful process anymore.
Do a find in Registry Editor for the above option, and you would be able to see that.
References for this solution:
1. http://connect.microsoft.com/SQLServer/feedback/details/125840/how-to-turn-off-auto-recovery-in-sql-server-management-studio-2005
2. http://blog-mstechnology.blogspot.com/2009/06/turn-off-saving-auto-recovery.html
We are thru’… Enjoy a Hang-Free SQL Session…!!!
VAIDY
I learned it only today, that too after 2 hours of thinking. This probably is a readily available answer on Internet or Forums or Manuals, but it never struck my mind to check all those sources.
Issue: I wrote a SQL query and wanted to process the records in a specific sort order. Let’s say for instance, my query is:
SELECT ITEMNMBR, DOCTYPE, DOCDATE, TRXQTY
FROM IV30300
ORDER BY ITEMNMBR, DOCTYPE, DOCDATE DESC
Please note that I have used DESC for my sorting order. Basically I wanted to sort the records based on:
1. Item Number (to be in Ascending Order)
2. Document Type (to be in Descending Order)
3. Document Date (to be in Descending Order)
The above query for my requirement, always returned records in a wrong sort order. The Sort Order was like this:
1. Item Number (Ascending Order) – which is Correct
2. Document Type (Ascending Order) – which is Wrong
3. Document Date (Descending Order) – which is Correct
The reason I learned for this, is a trivial SQL concept, which I never knew till today.
Let’s revisit my query, which is now corrected one:
SELECT ITEMNMBR, DOCTYPE, DOCDATE, TRXQTY
FROM IV30300
ORDER BY ITEMNMBR, DOCTYPE DESC, DOCDATE DESC
The above, corrected, query shows one more DESC added to column DOCTYPE. That means, if we want to sort records in Descending Order based on more than one column, we must specify DESC on each relevant column. Otherwise, it will take only the last column.
Till now, I was under the impression that specifying DESC in the last (once) is more than enough.
I thought I would share this with people, in case anyone else overlooked this simple concept, like me.
VAIDY
As promised in my previous article, here we go.
This is another frequent error message that Users get when they enter CBM Manual Payments in a CBM Batch:
CAUSE: There will be an Activity record inserted when you open a Batch for Payment Entry, in the table CB300006 (Display Name: Batch Entry Records Locked). This does not get cleared at times.
SOLUTION:
I. Follow the Resolution Steps I & II from this article: http://www.vaidy-dyngp.com/2010/04/cbm-checkbook-checkbook-id-is-already.html.
II. Run the following query once above steps are completed.
DELETE CB300006 WHERE USERID = ‘[username]’ AND BACHNUMB = ‘[batch number]’
We are good to go now.
VAIDY
I often come across this request from GP Users:
This happens when the User try to reconcile from CBM Reconcile. And once he/she selects the Checkbook which needs to be reconciled.
After some SQL Profiling & Dex Script Log, I found the following:
Cause:
The table CB100006 contains the User Checkbook Activity records, as and when a User reconciles a Checkbook. This table for some reason is not cleared properly. Sometimes:
1. If you open the form, enter the Checkbook ID and just close it without any activity, this record is not cleared.
2. If you open the form, enter the Checkbook ID and just close GP directly without any activity, this record is stuck.
The above scenarios are faced by me and have not heard of this from any other consultants, so the above need not be recreated consistently.
Resolution:
I. Consultants must open SQL Management Studio, log on to the Data Server and connect to relevant company DB.
II. Run the following queries against DYNAMICS and the respective companies:
/*
  This below queries will delete all stranded and unwanted SQL Sessions.
*/
DELETE TEMPDB..DEX_LOCK WHERE SESSION_ID NOT IN (SELECT SQLSESID FROM DYNAMICS..ACTIVITY)
DELETE TEMPDB..DEX_SESSION WHERE SESSION_ID NOT IN (SELECT SQLSESID FROM DYNAMICS..ACTIVITY)
/*
  The below queries will ensure that the respective user, against whom the error message was thrown, would be cleared from GP Application Session(s).
*/
SELECT * FROM DYNAMICS..ACTIVITY WHERE USERID = ‘[username shown in the CBM error message]’
–Ask that user to log off, if this user has logged on for the day.
III. Run the below query to clear the CBM Checkbook Lock:
DELETE CB100006 WHERE USERID = ‘[username shown in the CBM error message]’
DELETEÂ CBEU1020Â WHEREÂ CHEKBKID = ‘[stuck checkbook ID]’ AND USERID = ‘[username shown in the CBM error message]’
That’s it. We are good to go with our Checkbook Reconciliation in CBM.
NOTE: I will be posing another article on CBM Payments Batch Lock Error (which is identical to this error message).
VAIDY
I installed Dynamics GP 10, a fresh install to make sure that I properly install SSRS Add-On and configure it to GP 10.0.
Everything went on well and I started configuring SSRS Paths in Dynamics GP Reporting Tools Setup window.
I entered the path and I received a message “The Report server URL entered is not valid.”. No clue, whatsoever.
I surfed and here I found Mariano’s input to one of the users.
If you are busy enough to not going to that link and read, here is what Mariano had to say:
If you are on SQL Server 2005, the URL should be http://ServerName:PortNumber/reportserver/reportservice.asmx.
However, if your server happens to be a SQL Server 2008 server, the URL should be http://ServerName:PortNumber/reportserver/reportservice2005.asmx.
Thanks Mariano for this input and I am delighted to have succeeded in my task. Though this is for my own R&D, this has kick started a really interesting and sincere learning process.
VAIDY
Another article which stresses on using relevant and optimal Datatypes instead of regular and unnecessary ones. There is an illustration which explains realtime.
The article, Data Types Matter, is written by Steve Jones on his blog SQL Musings.
Must read for all Developers out there.
VAIDY
MichaelJ2 has pointed out to a very good article in SQL Server Central portal, which explains step-by-step on how to generate charts using SSRS.
As he mentioned in the end, it’s worth to have a look since GP is moving more towards SSRS and it would be great to see more Decision Making charts generated right from GP.
VAIDY
Dynamics GP - Learn & Discuss 