Monday, March 31, 2008

Searching for Data in a Large SQL Database like Great Plains

Most of the time spent designing a report, or writing some code against great plains is spent in finding the right table. I have written about this before.

Using Mark's Excel Sheets and Resource Descriptors


I also refer to -

SQL script that lets you search for a keyword in all tables of a database.


Today I found this on Mariano Gomez's blog -

SQL Script to find all tables which contain a particular Column -


The following example query attempts to solve the issue by exposing all tables where the account index (ACTINDX) column is found within the Fabrikam database (TWO)

select distinct rtrim(objs.name)
from syscolumns cols
inner join sysobjects objs on (cols.id = objs.id)
inner join sysindexes indx on (cols.id = indx.id)
where (cols.name = 'ACTINDX') and (objs.xtype = 'U') and (indx.rowcnt <> 0)

The results are as shown below:


GP Installation/Update Error : An installation package for the product Microsoft Dynamics GP 10.0 cannot be found. Try the installation again using a valid copy of the installation package 'GreatPlains.msi'

You run Great Plains setup to install/update a feature and get the error message - "An installation package for the product Microsoft Dynamics GP 10.0 cannot be found. Try the installation again using a valid copy of the installation package 'GreatPlains.msi'"

I have seen this a few times so here is the fix -

You generally get this error, if you installed Great Plains from a location say - C:\GreatPlains\CD1\ and now you are updating it from a location like - D:\greatplains\Cd1\.

Option 1 :

This has worked for me most of the times -

1. Backup your registry(If you dont know how to do this, you should skip to Option 2)

2. Search For GreatPlains.msi

3. There may be several entries so you need to find the one which lists a LastUsedSource entry. This is the entry you will need to edit. The entry will look something like this.

m;1;C:\Great Plains Installers\Dynamics GP 10\CD1\Bin

You need to change the path name so it is the location of the GreatPlains.msi file on the install CD.

4. After you edit the registry you can close regedit.

Try running the installation again.

Option 2 :

You can un-install and then re-install Dynamics GP. This will correct the registry setting that stores the path to GreatPlains.msi. Your accounting Data should not be erased by uninstall as that's stored in SQL.

Friday, March 28, 2008

Microsoft Aims to lure QuickBooks users over to Dynamics GP

A Washington Post article about Microsoft getting aggressive about targeting Quickbooks Users.


Microsoft is also launching a new Web site, www.asmartmove.com, in April. The site will contain "example pricing" for complete product packages, which include software licenses and partner implementation costs.

Found Via Mariano Gomez's GP Blog : http://dynamicsgpblogster.blogspot.com/

Wednesday, March 19, 2008

GP 10 does not have an inbuilt role/task for Safe Pay

Today while giving a user access to Safe Pay (Tools > Routines > Financial > Safe Pay), I realized that there wasn't an inbuilt task/role for Safe Pay. I was a little surprised, as this was the first major missing task in the otherwise excellent default Security Role /Task Setup.

However, it wasn't too difficult to set it up, here's what you have to do -

1. Create a new task called SAFE_PAY (or whatever, you want to name it according to your security setup)

2. Select Safe Pay as Third Party Product and give access to the Safe Pay Windows and Reports (Let me know if you need details on this)

3. Assign this tasks to a Role

4. Give the user who needs safe pay access, access to this role.

You should be able to see Safe Pay Menu option after this.

Tuesday, March 18, 2008

GP Resources from the Great Plains Guy

David has put up a nice list of resources for Great Plains issues. Can come in handy for "that" issue you cant find anything about.

Great Plains Developer Resource List

Saturday, March 15, 2008

My Top Five Strength's According to Gallup

I did this Gallup Foundation StrengthFinder Test a few years back. It told me my top strength was "achiever" and I needed to accomplish something everyday in order to feel good about myself. It was a bit funny, but very true. After all, I was the guy who carried a Steve McConnell book to a snow camp.

It also told me I liked learning about new things - whether or not they are useful. Knowing my strengths was pretty helpful, I could plan my work, my life around it. I picked roles at my job which gave me chances to learn. I knew if I had a tough day, all I needed to do to get back on track was to accomplish something.

Recently, some of my friends at work gave the test too, and this has become a part of our conversations at work. Somebody whom you always knew to be a "Whatever you say" guy - had a strength "Harmony". Somebody whom you knew to be great planner - had the strength "Strategic" and "Futuristic". 

My sister, after hearing our conversations gave this test and she found that she had the top strength -"Woo". She has already convinced 10 odd people into giving the test themselves!

Here are my top 5 strengths according to the test -

Your Achiever theme helps explain your drive. Achiever describes a constant need for achievement. You
feel as if every day starts at zero. By the end of the day you must achieve something tangible in order to
feel good about yourself. And by "every day" you mean every single day-workdays, weekends, vacations.
No matter how much you may feel you deserve a day of rest, if the day passes without some form of
achievement, no matter how small, you will feel dissatisfied. You have an internal fire burning inside you. It
pushes you to do more, to achieve more. After each accomplishment is reached, the fire dwindles for a
moment, but very soon it rekindles itself, forcing you toward the next accomplishment. Your relentless need
for achievement might not be logical. It might not even be focused. But it will always be with you. As an
Achiever you must learn to live with this whisper of discontent. It does have its benefits. It brings you the
energy you need to work long hours without burning out. It is the jolt you can always count on to get you
started on new tasks, new challenges. It is the power supply that causes you to set the pace and define the
levels of productivity for your work group. It is the theme that keeps you moving.

My Analysis : I would come back at 2 AM after a complete day out at the movies or partying - and want to do something worthwhile - Read a tech book, Catch up on some blogs or do some work. Then I could sleep happy. 

You are inquisitive. You collect things. You might collect information-words, facts, books, and quotations-or
you might collect tangible objects such as butterflies, baseball cards, porcelain dolls, or sepia photographs.
Whatever you collect, you collect it because it interests you. And yours is the kind of mind that finds so
many things interesting. The world is exciting precisely because of its infinite variety and complexity. If you
read a great deal, it is not necessarily to refine your theories but, rather, to add more information to your
archives. If you like to travel, it is because each new location offers novel artifacts and facts. These can be
acquired and then stored away. Why are they worth storing? At the time of storing it is often hard to say
exactly when or why you might need them, but who knows when they might become useful? With all those
possible uses in mind, you really don't feel comfortable throwing anything away. So you keep acquiring and
compiling and filing stuff away. It's interesting. It keeps your mind fresh. And perhaps one day some of it will
prove valuable.

My Analysis : I hate throwing things away! And I just keep collecting books, movies, more than I can read, more than I can watch... I would subscribe to 260 blogs knowing I would only read 30.  But you never know when it might be useful.

"Where am I headed?" you ask yourself. You ask this question every day. Guided by this theme of Focus,
you need a clear destination. Lacking one, your life and your work can quickly become frustrating. And so
each year, each month, and even each week you set goals. These goals then serve as your compass,
helping you determine priorities and make the necessary corrections to get back on course. Your Focus is
powerful because it forces you to filter; you instinctively evaluate whether or not a particular action will help
you move toward your goal. Those that don't are ignored. In the end, then, your Focus forces you to be
efficient. Naturally, the flip side of this is that it causes you to become impatient with delays, obstacles, and
even tangents, no matter how intriguing they appear to be. This makes you an extremely valuable team
member. When others start to wander down other avenues, you bring them back to the main road. Your
Focus reminds everyone that if something is not helping you move toward your destination, then it is not
important. And if it is not important, then it is not worth your time. You keep everyone on point.

My Analysis : Sometimes this makes me the irritating guy - who just wants to get work done.

You love to learn. The subject matter that interests you most will be determined by your other themes and
experiences, but whatever the subject, you will always be drawn to the process of learning. The process,
more than the content or the result, is especially exciting for you. You are energized by the steady and
deliberate journey from ignorance to competence. The thrill of the first few facts, the early efforts to recite or
practice what you have learned, the growing confidence of a skill mastered-this is the process that entices
you. Your excitement leads you to engage in adult learning experiences-yoga or piano lessons or graduate
classes. It enables you to thrive in dynamic work environments where you are asked to take on short
project assignments and are expected to learn a lot about the new subject matter in a short period of time
and then move on to the next one. This Learner theme does not necessarily mean that you seek to become
the subject matter expert, or that you are striving for the respect that accompanies a professional or
academic credential. The outcome of the learning is less significant than the "getting there."

My Analysis :  Ah ... the thrill of learning new things! Teach me a new thing and I will add your name to the list of people I have to do something good for.


Your Analytical theme challenges other people: "Prove it. Show me why what you are claiming is true." In the face of this kind of questioning some will find that their brilliant theories wither and die. For you, this is precisely the point. You do not necessarily want to destroy other people's ideas, but you do insist that their theories be sound. You see yourself as objective and dispassionate. You like data because they are value free. They have no agenda. Armed with these data, you search for patterns and connections. You want to understand how certain patterns affect one another. How do they combine? What is their outcome? Does this outcome fit with the theory being offered or the situation being confronted? These are your questions. You peel the layers back until, gradually, the root cause or causes are revealed. Others see you as logical and rigorous. Over time they will come to you in order to expose someone's "wishful thinking" or "clumsy thinking" to your refining mind. It is hoped that your analysis is never delivered too harshly. Otherwise, others may avoid you when that "wishful thinking" is their own.

My Analysis : My conversations with one of my best friends go like this ...

He says pretty confidently - "Hillary would be the president. I am sure.". He is very specific and very confident when he states his theory.

I go - "How can you say that ? The polls prove otherwise? "

He -"I just know it"

I - "What do you mean you know it. Give me your facts or reasons. I mean she is trailing. Besides you are totally ruling out the republicans. "

He - "I just know it and you will see"

I keep trying ... to find a logical reason ... but I just end up getting frustrated. Now he wouldn't give me his reason, and I have to wait till an year to prove it to him that he was wrong. By that time I will forget. Total "lose-lose" situation. Damn it!

Tuesday, March 11, 2008

SQL Script to Backup All Databases

Richard pointed out this out to me yesterday. This script would backup all non-system databases on a SQL Server. You can modify it to read the company names from SY01500 and backup just the dynamics and company databases if you like.

From : Simple script to backup all SQL Server databases

You will need to change the @path to the appropriate backup directory and each backup file will take on the name --  "DBnameYYYDDMM.BAK"

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\' 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
BACKUP DATABASE @name TO DISK = @fileName 
FETCH NEXT FROM db_cursor INTO @name  
CLOSE db_cursor  
DEALLOCATE db_cursor