Can not create a company in GP Utilities 2015R2

  • Can not create a company in GP Utilities 2015R2

    Posted by DSC Communities on December 13, 2016 at 7:40 pm
    • Rob Klaproth

      Member

      December 13, 2016 at 7: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:MSSQLGPSABCDDat.mdf’, SIZE = 50, FILEGROWTH = 20% ) LOG ON (NAME = ‘GPSABCDLog.ldf’, FILENAME = ‘L:MSSQLGPSABCDLog.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
      ——————————

    • Kerry Hataley

      Member

      December 14, 2016 at 8: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
      ================================================
      ————————————————————————-

    • Beat Bucher

      Member

      December 14, 2016 at 8: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
      ————————————————————————-

    • Rob Klaproth

      Member

      December 14, 2016 at 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

      ——Original Message——

      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
      ================================================
      ——————————

    • Bill Marshall

      Member

      December 14, 2016 at 12:04 PM

      Did you unmark all options for the ODBC configuration?

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

    • Rob Klaproth

      Member

      December 14, 2016 at 2: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.

      ——Original Message——

      Did you unmark all options for the ODBC configuration?

      ——————————
      Bill Marshall
      President
      mc²
      ——————————

    • Rob Klaproth

      Member

      December 14, 2016 at 2: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

      ——Original Message——

      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.

    • Bill Marshall

      Member

      December 15, 2016 at 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²
      ————————————————————————-

    • Toulou Yong

      Member

      September 28, 2021 at 1:23 PM

      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:

      create database [GPXYZ]         ON          (NAME = ‘GPXYZDat.mdf’,             FILENAME = ‘F:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATAGPXYZDat.mdf’,             SIZE = 50,             FILEGROWTH = 20% )                              LOG ON           (NAME = ‘GPXYZLog.ldf’,             FILENAME = ‘F:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATAGPXYZLog.ldf’,             SIZE = 20,             FILEGROWTH = 25% )  

      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
      ——————————
      ——————————————-

    • Beat Bucher

      Member

      September 28, 2021 at 2:30 PM

      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
      ——————————
      ——————————————-

    DSC Communities replied 6 years, 11 months ago 1 Member · 0 Replies
  • 0 Replies

Sorry, there were no replies found.

Log in to reply.

Welcome to our new site!

Here you will find a wealth of information created for people  that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!