While administering a Microsoft Dynamics GP system, there are times when a fix or change needs to be applied to each company in the system at the SQL Server level.  On a system with a large number of companies this can be a very time consuming task.

Wouldn't it be nice if there was some method of automating the process so that a SQL script file could be automatically executed against every company database in an instance of SQL Server without…

Christina recently had the need to send notification e-mails to Business Portal users based on certain transactions that had been entered. To keep things simple, she was looking for a solution that could be implemented in SQL Server. After discussing it briefly, we were pretty confident that we could setup a simple routine to send e-mail from SQL Server. But there was a catch--we only had access to the user's Windows /Domain username in the SQL database, and we did not have the user's e-mail address stored anywhere in the GP database. However, the e-mail addresses were stored in Active Directory. So the search was on to find a way to access Active Directory from SQL Server.

It turns out that it is surprisingly easy to setup a simple SQL Server query that…

In my previous blog I talked about how my SQL wizard (Tom Celvi) was able to help migrate masses of data when Excel and Integration Manger could not step handle it. I asked him if he would elaborate briefly as to his steps. Thanks to Tom for this contribution!

A Custom database was created

"I did this so I could keep the initial import data separate from GP and to provide a central point of access for both the GP production and test companies.

This way, I could use the same data and processes for both testing and production deployment without having to re-import the data for…

I was working on a project recently with a very experienced and respected GP consultant. He has taught me many things but I was able to show him something very simple that made his life much…

Just recently I had been helping my friend Cal at EMC to resolve an issue with the Professional Services Tools Library (PSTL) Item Combiner utility. In a previous life, his company had installed the Manufacturing series and decided not to use it. They upgraded to Dynamics GP 10.0 and decided they wanted to take advantage of PSTL's Item Combiner to do some cleanup work on their product master.

No…

Often times, when needing to perform maintenance in GP, your users will be logged out but…

Hi all In this article I am providing a script to delete the activity table records for a…

I've been asked this a few times, and so, I thought a post might be in order.

If you ever get "locked" out of the system, i.e. something crashes and then you cannot log back in, this little piece of SQL will kick everyone off ( or,more specifically, it…

Here's a SQL nugget I love to use when writing stored procs, triggers, and the like. Copy and paste this into query analyzer and replace %TableName% with the name of the Table in which you want to insert records. Execute it to return the INSERT into statement complete with default values for every field in the table. Paste the results into your object and populate the fields with your values. This can be a real time save for developers and when doing data conversions.

A slick cat we'll call Mo gave this to me. I can't take credit for it.

set nocount on
DECLARE @sTableName varchar(128)set @sTableName = '%TableName%'
select @sTableName as sTableName into #tmpTableName
DECLARE @lTableID intset @lTableID = NullSELECT @lTableID = [ID] from sysobjects where (objectproperty(id, N'IsTable') = 1) and (id = object_id(@sTableName))
if (@lTableID is Null)begin print 'Table not found! Aborting.' returnend
SELECT [name], xtype, prec, scale, colorder, isnullable into…

veeyeskay is back a SQL Script to delete stranded activity for a specific user.

Real Life Dynamics GP user has a nice look at the basics of how to use SQL Views.

I have seen this question come up multitude of times on public newsgroups and forums. In fairly large Dynamics GP installations, Navigation Bar shortcuts tend to be deployed in a standard fashion for groups of users at a time, this is, to ensure all users have the same set of options to perform their functions, or access to company documents, etc. With this in mind, it is very common to setup one user's shortcuts and wanting to replicate these across other Dynamics GP user accounts. Unfortunately, there isn't a facility in GP to allow this.

The following T-SQL script allows you to copy the Navigation bar…