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.