Unable to upgrade ProGet 4.0.14 to 4.1.3

[Derek Evermore]

I attempted to upgrade our ProGet instance from 4.0.14 to 4.1.3 last week and it resulted in the database being corrupted. We had to restore the database from backup. (we are using an MS SQL 2012 database on a different server than ProGet)

The error received was "Unable to find stored procedure __AddStoredProcInfo. When I looked at the database I saw two instance of this stored procedure present... one under a schema [MYDOMAIN\MyUserId] - and the other under a schema [MYDOMAIN\MyCompany'sDevGroup]. For whatever reason it was not using the default "dbo" schema.

This may be related to how the application was initially installed. After the restore of the previous night's database backup I noticed that all of the stored procedures are already duplicated. There is one set of them other under a schema [MYDOMAIN\MyCompany'sDevGroup] ... and the other is under the "dbo" schema as it should be.

Product: ProGet
Version: 4.1.3

[Alana]

Everything should be under the [dbo] schema...

You can use the ALTER SCHEMA command to move objects as needed to [dbo], or rerun the install under a user that has access to the [dbo] schema.

[Derek Evermore]

I’ve tried the suggestions in the support forum. They did not work.
I also did a complete uninstall \ reinstall of the software and database.

In both cases I experienced the same issue as when I tried to upgrade last time.
Error: Could not find stored procedure 'dbo.__AddStoredProcInfo'.

When I look in the database all of the of the database objects get created under a schema [dbo].[NUVEEN\deidad], which is my network username. I also noticed that the installer is creating an account on the database for me even though I already have access through a security group. I used the ALTER SCHEMA command to move all of the tables, procs, etc. to the dbo schema (as suggested by your support team) but when running the installer again I’m right back at square one… all of the database objects are back to being in the [NUVEEN\deidad] schema.

This looks very much like a bug in your installer’s SQL code. When you run a SQL statement to create an object you must specify the schema as part of the CREATE statement otherwise it will generate a schema under that username and use it.
For example…

CREATE PROCEDURE prc_this_is_a_proc
Will result in the procedure being created as [NUVEEN\deidad]. prc_this_is_a_proc

Whereas…
CREATE PROCEDURE [dbo].[prc_this_is_a_proc]
Will result in the procedure being created as [dbo].[prc_this_is_a_proc]

-- snip for security resons --

[Alana]

I removed the log for security reasons.

When you create a user in a SQL Server database, you have the option to pick which schema to use. Make sure to select [dbo], or you will have this issue.

CREATE USER [MYDOMAIN\MyUserId] FOR LOGIN [MYDOMAIN\MyUserId] WITH DEFAULT_SCHEMA=[dbo]

[Derek Evermore]

I updated the default schema on the security group I was using to dbo and I was able to run a successful install. Thank you!