Use Dynamic SQL To Generate a Dynamics GP Login Macro

/*
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