Open Forum

Expand all | Collapse all

Can not create a company in GP Utilities 2015R2

  • 1.  Can not create a company in GP Utilities 2015R2

    TOP CONTRIBUTOR
    Posted Dec 13, 2016 07:40 PM

    I have never in my 10 years as a GP consultant seen this issue... A new one for sure.   Logged in as the "sa" user to a SQL 2012 server, they already have like 5 companies, so this would be their 6th.   But I can't create it.  I get an error on the screen that mirrors the same error I get in DEX SQL log file shown below.  I can take all of the statements into SQL Management studio and they execute without error.  I have verified that all the ODBC boxes are un-checked just as Microsoft recommends for the data source (Ansi nulls, paddings, etc).  I have verified plenty of RAM, free disk space, path names are correct, etc.    SO GP Utilities is being a pill.. What next?  I've tried different company ID's too just to see if it didn't like that one, but no dice.  

    /* Date: 12/13/2016 Time: 16:15:20
    stmt(28436856):*/
    create database [ABCD] ON (NAME = 'GPSABCDDat.mdf', FILENAME = 'E:\MSSQL\GPSABCDDat.mdf', SIZE = 50, FILEGROWTH = 20% ) LOG ON (NAME = 'GPSABCDLog.ldf', FILENAME = 'L:\MSSQL\GPSABCDLog.ldf', SIZE = 20, FILEGROWTH = 25% )

    /* Date: 12/13/2016 Time: 16:15:21
    stmt(28436856):*/
    use master
    /*
    /* Date: 12/13/2016 Time: 16:15:21
    SQLSTATE:(01000) Native Err:(5701) stmt(28436856):*/
    [Microsoft][SQL Server Native Client 11.0][SQL Server]Changed database context to 'master'.*/
    /*
    /* Date: 12/13/2016 Time: 16:15:21
    SQLSTATE:(00000) Native Err:(5701) stmt(28436856):*/
    */
    /*
    /* Date: 12/13/2016 Time: 16:15:21
    SQLSTATE:(00000) Native Err:(5701) status(0):*/
    SWSTATUS DUMP*/

    /* Date: 12/13/2016 Time: 16:15:21
    stmt(28436856):*/
    ALTER DATABASE [ABCD] SET RECOVERY SIMPLE
    /*
    /* Date: 12/13/2016 Time: 16:15:21
    SQLSTATE:(37000) Native Err:(1934) stmt(28436856):*/
    [Microsoft][SQL Server Native Client 11.0][SQL Server]SELECT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or i*/
    /*
    /* Date: 12/13/2016 Time: 16:15:21
    SQLSTATE:(00000) Native Err:(1934) stmt(28436856):*/
    */
    /*
    /* Date: 12/13/2016 Time: 16:15:21
    SQLSTATE:(00000) Native Err:(1934) status(58):*/
    SWSTATUS DUMP*/

    ------------------------------
    Rob Klaproth
    Sr. Dynamics GP Consultant
    Armanino
    San Diego CA
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Can not create a company in GP Utilities 2015R2

    Posted Dec 14, 2016 08:06 AM

    Good Morning Rob,

    So lets start with the CONCAT_NULL_YIELDS_NULL value.

    With SQL 2012 the default is now 'ON'. We need to make sure this is set to on. Run the following and it should be set to 'ON'

    DECLARE @CONCAT_NULL_YIELDS_NULL VARCHAR(3) = 'OFF';

    IF ( (4096 & @@OPTIONS) = 4096 ) SET @CONCAT_NULL_YIELDS_NULL = 'ON';

    SELECT @CONCAT_NULL_YIELDS_NULL AS CONCAT_NULL_YIELDS_NULL;

    Check ANSI_WARNINGS - should be 'ON'

    DECLARE @ANSI_WARN VARCHAR(3) = 'OFF';

    IF ( (8 & @@OPTIONS) = 8 ) SET @ANSI_WARN = 'ON';

    SELECT @ANSI_WARN AS ANSI_WARNINGS;

    Check ANSI_PADDING - should be 'ON'

    DECLARE @ANSI_PADDING VARCHAR(3) = 'OFF';

    IF ( (16 & @@OPTIONS) = 16 ) SET @ANSI_PADDING = 'ON';

    SELECT @ANSI_PADDING AS ANSI_PADDING;

    ------------------------------
    ================================================
    Kerry Hataley
    Nanook Software
    Microsoft Dynamics GP Consultant
    ================================================

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Can not create a company in GP Utilities 2015R2

    Posted Dec 14, 2016 12:04 PM

    Did you unmark all options for the ODBC configuration?

    ------------------------------
    Bill Marshall
    President
    mc²

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Can not create a company in GP Utilities 2015R2

    GPUG ALL STAR
    Posted Dec 14, 2016 08:44 AM

    Hi Rob,

    Agree with Kerry,

    Some of your SQL server defauilt settings might have been changed.. check this forum thread :

    http://stackoverflow.com/questions/9235527/incorrect-set-options-error-when-building-database-project

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP MVP
    Ultra-Electronics Forensic Technology Inc.
    Montreal QC/Canada
    +1-514-489-4267
    @GP_Beat http://dyngpbeat.wordpress.com/
    Montreal QC GPUG Chapter Leader
    GP2013R2 / MR2012 CU14

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Can not create a company in GP Utilities 2015R2

    TOP CONTRIBUTOR
    Posted Dec 14, 2016 10:31 AM
    Kerry,

    Does this set it at the server level permanently?  Are these properties not shown in the server properties box in SQL management studio? I compared 3 GP server installs side by side and they were all set exactly the same on the server properties dialog box  so unless those settings are hidden settings that don't show up then it doesn't make sense why all of them are set the same and 2 of the 3 work and the other one doesn't.  I'll double check those settings via the query window.  

    Sent from my iPhone



    Academy - Online Interactive Learning from Experts


  • 6.  RE: Can not create a company in GP Utilities 2015R2

    TOP CONTRIBUTOR
    Posted Dec 14, 2016 02:28 PM
    Yes, bill, all options are unmarked.  In fact the ODBC was created as part of the GP install, but just to be sure I re-created another one with all the options unmarked with the same results.



    Academy - Online Interactive Learning from Experts


  • 7.  RE: Can not create a company in GP Utilities 2015R2

    TOP CONTRIBUTOR
    Posted Dec 14, 2016 02:46 PM
    Problem has been solved.  I submitted a support ticket to Microsoft.  They suggested that I review the "server triggers", which is different then a database trigger.   Under "Server Objects" there is also a Triggers.  In here, I found a trigger called Netwrix_Server_Trg and this was interrupting the create company process.  This is for a product called Netwrix SQL Auditor which tracks all changes made to the server and databases.  It apparently does something that interrupts the create company.  Once I turned it off, the create company worked fine.  

    One more lesson learned, always check for custom SQL server auditing products, this is the first time I have seen this issue, but now I know what to do!  This also is a good lesson for the client too, IT should communicate with their partner when they are making any changes to the GP server so we are aware of any custom products installed in that environment.  :)  

    Rob



    Academy - Online Interactive Learning from Experts


  • 8.  RE: Can not create a company in GP Utilities 2015R2

    Posted Dec 15, 2016 10:12 AM

    Figured the ODBC was right, but sometimes we overlook the obvious.  Very odd issue, for sure.  Glad you found a resolution.  Didn't even know that there were server triggers, just database ones. 

    ------------------------------
    Bill Marshall
    President
    mc²

    Academy - Online Interactive Learning from Experts


  • 9.  RE: Can not create a company in GP Utilities 2015R2

    Posted 22 days ago
    It's been close to six years since that issue was bought up but I wanted to chime on it:
    I had to create a new company over the weekend and of course, we always go through a Dev environment setup first.
    The DEV portion went flawlessly, so we decided to move to Prod over last weekend.

    Sadly, I came across the same error:

    The following SQL statement produced an error:<o:p></o:p>

    create database [GPXYZ]         ON          (NAME = 'GPXYZDat.mdf',             FILENAME = 'F:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\GPXYZDat.mdf',             SIZE = 50,             FILEGROWTH = 20% )                              LOG ON           (NAME = 'GPXYZLog.ldf',             FILENAME = 'F:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\GPXYZLog.ldf',             SIZE = 20,             FILEGROWTH = 25% )   <o:p></o:p>


    however, the additional message came with it :

    Could not obtain exclusive lock on database 'model'. Retry the operation later.
    CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)

    So, while the error looked similar, the resolution focused on a lock on Model database from a specific program mentioned through SQL.
    The model database is used as the template for all databases created on an instance of SQL Server.

    After clearing the lock on the model database, we were able to create our "GPXYZ" Database through the Utilities.

     



    ------------------------------
    Toulou Yong
    Senior software engineer
    Arch Insurance Group Inc
    Raleigh NC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: Can not create a company in GP Utilities 2015R2

    GPUG ALL STAR
    Posted 22 days ago
    Thank you Toulou for sharing your experience..
    Can always be useful for other users running into issues.. The "model" db is indeed used as template for all new DB creation and some settings could have an adverse effect on a new GP company if they were changed after the initial setup of SQL.

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role