Aaron Berquist Rotating Header Image

Use Dynamic SQL To Generate a Dynamics GP Login Macro

If you run Dynamics GP and have more than a handful of companies, I am sure you have felt the pain of installing or upgrading a 3rd party product, and then having to log in to each┬ácompany as “sa” to complete the install process. I had to do this recently for a new 3rd party we are using (SmartView from eOne Solutions). Once the install is complete, a log in to each database as “sa” is required to create the necessary tables to support the tool.

Wanting to avoid the aggravation (and tedium) of repeating this process for the 150+ company databases we have, I wrote the SQL script below to generate a Dynamics GP macro which can be played which will log in to each database in sequence without needing any intervention.

It took me a while to figure out how the get the right “Item” number into the macro. I first thought it was the CMPANYID value from the SY01500 table, but that wasn’t right. Next I tried the DEX_ROW_ID value, but that wasn’t it either. Some trial an error finally yielded the right formula. The Item number is the index position in the dropdown list, ordered by CMPANYID! Despite the fact that the dropdown list displays items ordered by CMPNYNAM (in other words, alphabetically), it appears to first populate the list with the items ordered by CMPANYID and assigns them the item number based on that sequencing, then reorders the list to display by CMPANYNM.

/*
AUTHOR: AARON BERQUIST
DATE: 07/07/2011
PURPOSE: THE SCRIPT BELOW WILL GENERATE A DYNAMICS GP MACRO WHICH WILL LOG IN TO EACH COMPANY IN SEQUENCE.
USEFUL WHEN YOU HAVE INSTALLED A NEW 3RD PARTY PRODUCT, OR A SERVICE PACK,
AND EACH DATABASE NEEDS TO BE INITIALIZED BY LOGGING IN AS "SA".
 
TESTED WITH DYNAMICS GP 10, BUILD 10.00.1257
 
OVERVIEW:
 
THE SCRIPT BELOW BUILDS A MACRO WHICH CAN BE RUN IN DYNAMICS GP.
DEPENDING ON THE NUMBER OF COMPANIES YOU HAVE, THE OUTPUT OF THE DYNAMIC SQL BELOW WILL BE TOO LARGE TO BE
FULLY DISPLAYED IN THE RESULTS PANE OF MANAGEMENT STUDIO.
 
THEREFORE, THE DYNAMIC SQL CODE IS INSERTED INTO THE TABLE __MACRO.
 
USE MANAGEMENT STUDIO TO EXPORT THE CONTENTS OF THE TABLE, WITHOUT THE HEADER ROW, INTO A FLAT FILE WITH AN EXTENTION OF .MAC
THE FILE CAN THEN BE PLAYED INSIDE DYNAMICS GP.
 
USAGE:
 
1. EXECUTE THE SCRIPT BELOW - IT USES DYNAMIC SQL TO BUILD A LOGIN MACRO FOR ALL DYNAMICS GP DATABASES.
2. THE CONTENTS OF THE SCRIPT ARE INSERTED INTO THE TABLE __MACRO.
3. EXPORT THE CONTENTS OF THE TABLE TO A FLAT FILE, WITH A .MAC EXTENSION.
4. LOG IN TO ANY DATABASE AS "SA" AND RUN THE MACRO. THE MACRO WILL LOG IN TO EACH DATABASE IN ALPHABETICAL SEQUENCE.
 
POTENTIAL MODIFICATIONS:
 
WHEN INSTALLING SOME 3RD PARTY PRODUCTS, YOU MAY NEED TO CLICK "OK" ON A WINDOW AT LOGIN TO INSTALL, OR PERFORM SOME REPETITIVE TASKS.
THE MACRO CODE BELOW CAN BE MODIFIED TO SUIT YOUR NEEDS. THE "SPECIAL SAUCE" IS THE ROW_NUMBER() OVER CMPANYID.
 
*/
USE DYNAMICS
GO
DECLARE @ssQL VARCHAR(MAX) = ''
 
SELECT @sSQL = @sSQL+'  CommandExec dictionary ''default''  form ''Command_System'' command ''Switch Company''
NewActiveWin dictionary ''default''  form ''Switch Company'' window ''Switch Company''
  ClickHit field ''(L) Company Names'' item '+CONVERT(VARCHAR(3),ROW_NUMBER() OVER (order BY CMPANYID))+'  # '+LTRIM(RTRIM(CMPNYNAM))+'
  MoveTo field ''OK Button''
  ClickHit field ''OK Button''
NewActiveWin dictionary ''default''  form sheLL window sheLL
NewActiveWin dictionary ''default''  form sheLL window sheLL
ActivateWindow dictionary ''default''  form sheLL window sheLL '+CHAR(13)
FROM DYNAMICS..SY01500
ORDER BY CMPNYNAM
 
PRINT (@sSQL)
 
/****** Object:  Table [dbo].[__Macro]    Script Date: 07/07/2011 21:41:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[__Macro]') AND type in (N'U'))
DROP TABLE [dbo].[__Macro]
GO
 
/****** Object:  Table [dbo].[__Macro]    Script Date: 07/07/2011 21:41:48 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[__Macro](
	[ssql] [varchar](max) NULL
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
INSERT __MAcro SELECT @ssQL
 
--NOW EXPORT THE CONTENTS OF THE TABLE TO A FLAT FILE AND RUN IN GP.
--YOU CAN THEN SAFELY DROP THE TABLE __MACRO

You can either copy and paste the code above, or download a script file here.

Post to Twitter

4 Comments

  1. [...] site: Use Dynamic SQL To Generate a Dynamics GP Login Macro VN:F [1.9.10_1130]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F [...]

  2. [...] Comments 0 Aaron Berquist has written a post showing how to Use Dynamic SQL To Generate a Dynamics GP Login Macro. [...]

  3. [...] Use Dynamic SQL To Generate a Dynamics GP Login Macro (Aaron Berquist) came up with this trick when he had to login to 150+ GP companies! [...]

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA Image

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>