SQL Script To Bulk Alter Users With Logins

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPBack in July 2013 I did a post where I looked at a problem copying live to test. The basic issue was that the Microsoft Dynamics GP user is also a login (at the SQL Server level) and a user (at the SQL Server database level) and when a database is copied from the live server to the test server (or from the current live top the new live) you can run a script to transfer across the logins, but the users come across with the database and will have different SIDs (Security IDs).

You can use the ALTER USER command in SQL to re-link the login with the user, but this is one statement per user per database. The old post showed how to do this, but this quickly becomes a pain when there are more than a handful of users.

As Perfect Image has grown we have clients with more and more users and/or company databases. Our largest client has over 250 users in their Dynamics GP installation while another has fewer users, but well over 100 companies. Both of these can make copying live to test problematic, especially when only a company database might be copied over rather than the whole system.

I needed to automate the process of altering the login to match the user; the below script is the result of this need. Continue reading → SQL Script To Bulk Alter Users With Logins

● Categories: Dynamics, GP, Microsoft, SQL Server ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

Find All Custom SQL Objects In Database

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft SQL ServerQuite a long time ago I started using a particular naming convention when creating SQL objects such as tables, triggers, or views. The plan was so that they were easy to find in the database.

With some clients who have objects with this naming convention looking to do upgrades I’ve taken the next step and created some SQL queries to select all of these objects (which was always the next step).

The naming convention I adopted is in the following format:

  • type
  • organisation who created
  • client
  • name (which will be omitted if the object is a generic one which might be given to multiple clients)

So, a custom table, created by azurecurve for Fabrikam, Inc. to store a Sales Order/Assembly cross reference would be called ut_AZRCRV_FAB_SalesOrderAssemblyXref.

The type prefix varies by object type, but always starts with a u for user. The types I use are:

  • ut for tables
  • uv for views
  • uf for functions
  • usp for stored procedures
  • utr for triggers

The following view (following my naming convention above lacks a client as it is generic) selects all custom objects in the database created by AZRCRV:

CREATE VIEW uv_AZRCRV_GetCustomObjects AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT o.name,'' AS 'table name', o.type_desc, o.modify_date FROM sys.objects AS o WHERE o.name LIKE 'u__AZRCRV_%'
UNION ALL
SELECT i.name, o.name, o.type_desc, o.modify_date FROM sys.indexes AS i INNER JOIN sys.objects AS o ON o.object_id = i.object_id WHERE I.name LIKE 'u%_AZRCRV_%'
UNION ALL
SELECT t.name, o.name, t.type_desc, o.modify_date FROM sys.triggers AS t INNER JOIN sys.objects AS o ON o.object_id = t.object_id WHERE o.name LIKE 'u%_AZRCRV_%'

The view can either by run manually in SQL Server Management Studio or plugged into either SmartList Designer or SmartList Builder. Once all custom items have been located, they can be extracted and preserved as scripts to be redeployed after the upgrade if necessary.

● Categories: Microsoft, SQL Server ● Tags: , , ,  ● Permalink ● Shortlink ●

MS Connect Suggestion: Add Workflow To Receivings Transaction Entry and Enter/Match Invoice

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPThis has come up from a couple of different clients in the last few weeks. It would be good if approval workflows could be added to Receivings Transaction Entry and also the Enter/Match Invoices windows.

While there is workflow on the PO, a PO might not be fully received/invoiced so it would be good to have approval of the receivings document or the invoice.

The MS Connect suggestion can be found here.

Please take a look and cast your vote.

● Categories: Dynamics, GP, Microsoft, Microsoft Connect, Workflow ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

MS Connect Suggestion: Add Bar Code Field to Item Maintenance

● Ian Grieve ●  ● 3 Comments  ● 

Microsoft Dynamics GPMVP Belinda Allen has posted a suggestion to MS Connect asking Microsoft to add a bar code field to Item Maintenance, Item Transaction Inquiry and Stock Count Entry.

I think this would be a good additin, so please take a look and cast your vote.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

Copy Fiscal Calendar To All Companies

● Ian Grieve ●  ● 4 Comments  ● 

Microsoft Dynamics GPIn the last post I mentioned that I create scripts when implementing Microsoft Dynamics GP and then make them available to clients when they would be of benefit. The last script I posted, allowed calendars to ve copied from one database to another. Afetr writing that scirpt I then adapted it by wrapping a cursor around it which allowed a source database to be defined and the calendar copied to all other databases.

The original script is still useful as it allows a targeted copying of calendars from a source to a destination database, but the new allows calendars to be quickly replicated across all companies if they share the same financial year.

The script has two parameters (highlighted) at the top which need to be set:

  • Year
  • SourceDatabase
  • Continue reading → Copy Fiscal Calendar To All Companies

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

    Copy Fiscal Calendar To A New Company

    ● Ian Grieve ●  ● 5 Comments  ● 

    Microsoft Dynamics GPI have a bit of catching up to do on writing posts, but to start the year off I am back to some SQL scripts. As I have mentioned before I have a number of clients with multiple, and in some cases well over 100, companies in Microsoft Dynamics GP.

    I do try to make life easier for them when possible so have been sharing a number of scripts I have for implementing systems. One such script is one which will copy financial (fiscal) calendars from one database to another.

    There are three parameters at the top which need to be set before running the script:

    1. Year
    2. Source Company
    3. Destination Company

    These parameters are highlighted below:

    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    */
    DECLARE @Year VARCHAR(4) = '2014'
    DECLARE @SourceDatabase VARCHAR(5) = 'TWO'
    DECLARE @DestinationDatabase VARCHAR(5) = 'THREE'
    DECLARE @SQLStatement VARCHAR(1000)
    
    SET @SQLStatement =
    	'INSERT INTO ' + @DestinationDatabase + '..SY40101
    		(YEAR1,FSTFSCDY,LSTFSCDY,NUMOFPER,HISTORYR)
    	--VALUES
    		(SELECT
    			YEAR1,FSTFSCDY,LSTFSCDY,NUMOFPER,HISTORYR
    		FROM
    			' + @SourceDatabase + '..SY40101
    		WHERE
    			(SELECT
    				COUNT(YEAR1)
    			FROM
    				' + @DestinationDatabase + '..SY40101
    			WHERE
    				YEAR1 = ' + @Year + ') = 0
    		AND
    			YEAR1 = ' + @Year + ')'
    EXEC (@SQLStatement)
    
    SET @SQLStatement =
    	'INSERT INTO ' + @DestinationDatabase + '..SY40100
    		(CLOSED,SERIES,ODESCTN,FORIGIN,PERIODID,PERIODDT,PERNAME
    		,PSERIES_1,PSERIES_2,PSERIES_3,PSERIES_4,PSERIES_5,PSERIES_6,
    		YEAR1,PERDENDT)
    	--VALUES
    		(SELECT
    			CLOSED,SERIES,ODESCTN,FORIGIN,PERIODID,PERIODDT,PERNAME
    			,PSERIES_1,PSERIES_2,PSERIES_3,PSERIES_4,PSERIES_5,PSERIES_6,
    			YEAR1,PERDENDT
    		FROM
    			' + @SourceDatabase + '..SY40100
    		WHERE
    			(SELECT
    				COUNT(YEAR1)
    			FROM
    				' + @DestinationDatabase + '..SY40100
    			WHERE
    				YEAR1 = ' + @Year + ') = 0
    			AND
    				YEAR1 = ' + @Year + ')'
    EXEC (@SQLStatement)
    GO

    The calendar will only be copied if the destination database does not contain a calendar with the same name.

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

    Happy New Year

    ● Ian Grieve ●  ● 0 Comments  ● 

    Happy New Year to everyone in the Dynamics GP community.

    Image courtesy of Stuart Miles at FreeDigitalPhotos.net

    ● Categories: Site ● Tags:  ● Permalink ● Shortlink ●

    New Year eBook Sale at azurecurve Publishing

    ● Ian Grieve ●  ● 4 Comments  ● 

    Microsoft Dynamics GPFrom now until the 5th January 2016 the ebook versions of Implementing the Microsoft Dynamics GP Web Client and Microsoft Dynamics GP Workflow 2.0 can each be bought for the discounted price of $9.99 using coupon code NEWYEAR2016.

    Implementing the Microsoft Dynamics GP Web Client Microsoft Dynamics GP Workflow 2.0

    Make sure you enter the coupon code NEWYEAR2016 at the checkout to get the discount.

    ● Categories: azurecurve Publishing, Dynamics, GP, Microsoft, Publishing, Web Client, Workflow ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

    Customization Maintenance: “Unable to open customizations dictionary”

    ● Ian Grieve ●  ● 4 Comments  ● 

    Microsoft Dynamics GPI ‘ve been involved in quite a few upgrades recently and have had a few issues arise. One of them was on one site after I had installed Microsoft Dynamics GP, the web client and web services and also Management Reporter.

    I did some work to upgrade the customised forms and reports and then imported them through Customisation Maintenance (Microsoft Dynamics GP menu ¯ Tools ¯ Customisation ¯ Customisation Maintenance). Or at least I tried to as I got an error with the SOP_Entry form:

    Errors

    Continue reading → Customization Maintenance: “Unable to open customizations dictionary”

    ● Categories: Dynamics, GP, Microsoft, Modifier with VBA, Web Client ● Tags: , , , , ,  ● Permalink ● Shortlink ●

    Error Registering Table GL_Account_MSTR

    ● Ian Grieve ●  ● 3 Comments  ● 

    Microsoft Dynamics GPWhile ding an upgrade of a client to Microsoft Dynamics GP I suddenly started getting an error when navigating the system; for example, when opening the General Journal Entry window:

    Microsoft Dynamics GP: Error Registering Table GL_Account_MSTR

    Microsoft Dynamics GP

    Error Registering Table GL_Account_MSTR

    Continue reading → Error Registering Table GL_Account_MSTR

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●