SQL Server database for OWF - a couple questions

[bwel...@gmail.com]

I am looking at the OWF Config guide dated 9/1/2015. I am creating the database on Microsoft SQL 2014.

Step 1)    Create a new SQL Server database for use with OWF.

I manually created this DB and named it OWFDB

Step 2)    Create a SQL Server user with full access to the OWF database created above.

I created a owf_admin SQL account and gave that account db_owner on the database.

skipping to step 5

Step 5)   Create the schema by running the SQLServerPrefsInitialCreate.sql script, prior to starting OWF.

Where is this done? Do you right click the OWF database and choose "New Query" and then paste the contents of the script? Or, do you just double click the sql file? Is the schema run against the Default Instance, or just the OWF database? I cannot find any additional info. 

Note that I have many other small databases on my default instance of SQL. Is it OK to have the OWF database on an existing default instance with other databases, or should OWF have its own instance?

skipping to step 7

Step 7)      Navigate to \dbscripts\SQLServerPrefsUpdate_v7.0.0.sql in the bundle.

Where is this done? Do you right click the OWF database and choose "New Query" and then paste the contents of the script? Or, do you just double click the sql file? 

Thanks in advance to anyone who can help me out with these questions.

Bob

W

W

[kdrumm...@gmail.com]

Hi Bob,

You need to load those two scripts into your database.  To do that, two common methods with MS SQL Server as as follows:

• If you prefer to load scripts from the command line, you can use the “sqlcmd” command.  See https://docs.microsoft.com/en-us/sql/relational-databases/scripting/sqlcmd-run-transact-sql-script-files for an example command line.
• You will need to alter the scripts to in clude the USE command seen at the top of the example.
  
• If you are using the MS SQL Server Management Studio,  then you can user the “New Query” interface and paste in the contents of the script.  If you have your database selected to run against, then you can execute the Query

For step 7, That script appears to be missing from the last bundle that was generated.  We will open a ticket for that oversight. This script is used only for developer or sample setups as it contains only examples widgets and dashboard.  If you are setting up a production environment, you can skip this step.

Thanks,
Kyle Drumm
Next Century Corporation

[Bob Welshon]

Thanks for the quick reply. 

Yes, those are the steps I used in SQL Mgmnt Studio.

The SQLServerPrefsInitialCreate.sql script ran OK with no errors.

The second script, SQLServerPrefsUpdate_v7.0.0.sql gave me the following errors. If I do not need this script, and the first script alone will get me where I need to be for a test/production environment, then that would be great. Thanks.

Msg 2705, Level 16, State 4, Line 12

Column names in each table must be unique. Column name 'last_notification' in table 'dbo.person' is specified more than once.

Msg 2627, Level 14, State 1, Line 15

Violation of PRIMARY KEY constraint 'PK_DATABASECHANGELOG'. Cannot insert duplicate key in object 'dbo.DATABASECHANGELOG'. The duplicate key value is (7.10.0-1, owf, changelog_7.10.0.groovy).

The statement has been terminated.

 

(1 row(s) affected)

 

(1 row(s) affected)

 

(1 row(s) affected)

 

(1 row(s) affected)

 

(1 row(s) affected)

 

(1 row(s) affected)

 

(1 row(s) affected)

 

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 43

Violation of PRIMARY KEY constraint 'PK_DATABASECHANGELOG'. Cannot insert duplicate key in object 'dbo.DATABASECHANGELOG'. The duplicate key value is (7.10.0-2, owf, changelog_7.10.0.groovy).

The statement has been terminated.

 

(1 row(s) affected)

 

(1 row(s) affected)

 

(1 row(s) affected)

 

(1 row(s) affected)

 

(1 row(s) affected)

 

(1 row(s) affected)

 

(1 row(s) affected)

 

(1 row(s) affected)

 

(1 row(s) affected)

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 418 [Batch Start Line 83]

No item by the name of '[dbo].[widget_definition].[image_url_large]' could be found in the current database 'OWFDB', given that @itemtype was input as '(null)'.

 

(1 row(s) affected)

 

(0 row(s) affected)

 

(0 row(s) affected)

Msg 2627, Level 14, State 1, Line 99

Violation of PRIMARY KEY constraint 'PK_DATABASECHANGELOG'. Cannot insert duplicate key in object 'dbo.DATABASECHANGELOG'. The duplicate key value is (7.16.0-1, owf, changelog_7.16.0.groovy).

The statement has been terminated.

Msg 2705, Level 16, State 4, Line 103

Column names in each table must be unique. Column name 'requires_sync' in table 'dbo.person' is specified more than once.

 

(0 row(s) affected)

Msg 2627, Level 14, State 1, Line 109

Violation of PRIMARY KEY constraint 'PK_DATABASECHANGELOG'. Cannot insert duplicate key in object 'dbo.DATABASECHANGELOG'. The duplicate key value is (7.16.0-2, owf, changelog_7.16.0.groovy).

The statement has been terminated.

Msg 2705, Level 16, State 4, Line 113

Column names in each table must be unique. Column name 'default_group_id' in table 'dbo.stack' is specified more than once.

Msg 2714, Level 16, State 5, Line 116

There is already an object named 'FK68AC28835014F5F' in the database.

Msg 1750, Level 16, State 1, Line 116

Could not create constraint or index. See previous errors.

Msg 2627, Level 14, State 1, Line 119

Violation of PRIMARY KEY constraint 'PK_DATABASECHANGELOG'. Cannot insert duplicate key in object 'dbo.DATABASECHANGELOG'. The duplicate key value is (7.16.0-3, owf, changelog_7.16.0.groovy).

The statement has been terminated.

Msg 1913, Level 16, State 1, Line 123

The operation failed because an index or statistics with name 'domain_mapping_all' already exists on table 'dbo.domain_mapping'.

Msg 2627, Level 14, State 1, Line 126

Violation of PRIMARY KEY constraint 'PK_DATABASECHANGELOG'. Cannot insert duplicate key in object 'dbo.DATABASECHANGELOG'. The duplicate key value is (7.16.0-5, owf, changelog_7.16.0.groovy).

The statement has been terminated.

Msg 2705, Level 16, State 4, Line 130

Column names in each table must be unique. Column name 'mobile_ready' in table 'dbo.widget_definition' is specified more than once.

Msg 2627, Level 14, State 1, Line 133

Violation of PRIMARY KEY constraint 'PK_DATABASECHANGELOG'. Cannot insert duplicate key in object 'dbo.DATABASECHANGELOG'. The duplicate key value is (7.16.0-6, owf, changelog_7.16.0.groovy).

The statement has been terminated.

--
You received this message because you are subscribed to a topic in the Google Groups "ozoneplatform-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ozoneplatform-users/jx1A5xWac-8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ozoneplatform-users+unsub...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[kdrumm...@gmail.com]

Yeah, you should only need the SQLServerPrefsInitialCreate.sql script.  You may want to clear the database and rerun that script in case the Upgrade script you ran causes some issues.

The 'upgrade' script you ran is actually for upgrading from a previous version.  We are missing the 'update' script for MS SQL Server that would be useful for you for demo/sample data.  I submitted a ticket to github to get that fixed.  The naming on some of the scripts can be hard to notice the subtle differences with 'update' vs 'upgrade'.

Thanks,
Kyle Drumm
Next Century Corporation

[Bob Welshon]

Thanks Kyle,

I have no problem deleting and recreating the DB since I'm just getting started.

Bob