[Rob Klaproth]

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]

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]

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]

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]

Did you unmark all options for the ODBC configuration?

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

[Rob Klaproth]

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]

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]

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]

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]

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)

——————————
——————————————-