Search all GP Blogs, Groups and Forums New!
GPWindow is Everything Dynamics GP. Try the Custom GP Search engine which searches high quality GP Blogs, and the Microsoft Dynamics GP Community Forums.
Running a SQL Script against all GP Company Databases
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…
Accessing Active Directory From SQL Server
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…
SQL Magic...post script
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…
SQL view to show security roles and tasks in Dynamics GP 10
Dynamics GP 10.0 has brought about a drastic change in GP security. Not only is the security pessimistic now, so by default no permissions are granted, but setting up and administering security is quite different from what many of us are used to after working with previous versions of GP for many years. And, of course, reporting on security requires a completely new set of tables. Below is a view to show the security roles and tasks assigned to each user in each GP company. And for a lot more information about GP security, take a look at David Musgrave’s Microsoft Dynamics GP Application…
Simple Table Backups with T-SQL
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…
How to search for a text within a SQL Server trigger, stored procedure, or UDF
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.
SQL Scripts – Clear Users and Activity Locks
Often times, when needing to perform maintenance in GP, your users will be logged out but…
Kick Everyone Out SQL Script
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…
SQL Nugget - Shortcut to create INSERT into
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…
SQL - Copying Microsoft Dynamics GP Navigation Bar shortcuts from one user to another
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…