2 Replies Latest reply on Dec 8, 2017 3:12 PM by Matt McCormick

    Incorrect Server Name When Creating Vault

    Matt McCormick

      When attempting to create a new vault named "Sandbox3", I am getting an error where PDM appears to be attempting to access a server with a different name than the database is on. The database server name is "TESTENGVAULT", and the PDM Administration tool appears to be attempting to query data from "ENGVAULT".


      During the creation vault creation process, a message box is shown indicating that an error has occured, which is blocking the creation process from continuing. After clicking "OK", everything is rolled back. While the blocking message box is shown, I am able to open SQL Server Management Studio and verify that the "Sandbox3" database has been created: it does exist. Before attempting to create the vault, and after everything has been rolled back, the "Sandbox3" database does not exist in SQL Server Management Studio.


      It appears that there is a reference somewhere that contains "ENGVAULT". In an attempt to reset it to query that appropriate server, I modified the server installation and set the registry key as detailed in: 2017 SOLIDWORKS PDM Help - Configuring the SOLIDWORKS PDM Database Server . However, this did not do the trick.


      Where else might this reference exist, which would be causing this?


      Log output:


      Error running query in SpDbOdbc


      Msg=[Microsoft][ODBC SQL Server Driver][SQL Server]The specified @server_name ('ENGVAULT') does not exist.


      ========= LAST COMMAND ========



      * CREATE Job ReplicatioMonitor_<database_name>                                    *




      * Purpose                                                                        *


      *    Runs the monitor script periodically. This script calls the                    *


      *    fnMonitorReplication function which checks for delay                        *


      *    This is script runs on the primary and is vault specific.                    *


      *    For this job to run, SQLAGENT must be running                                *




      DECLARE @jobId binary(16)






      SET @JobName = N'ReplicationMonitor_' + DB_NAME()


      SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = @JobName)


      IF (@jobId IS NULL)






          DECLARE @ReturnCode INT


          SELECT @ReturnCode = 0




          IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)




          EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'


          IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback








          DECLARE @Owner NVARCHAR(MAX)




          SET @Owner = N'sa'


          SET @DbName = DB_NAME()


          EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name = @JobName,














                  @description=N'Monitors replication status for the vault database',


                  @category_name=N'[Uncategorized (Local)]',


                  @owner_login_name=@Owner, @job_id = @jobId OUTPUT


          IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback




          EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Monitor Step',


















                  @os_run_priority=0, @subsystem=N'TSQL',


                  @command=N'create table #tmpReplicationDetails


                          (ServerName [nvarchar](255) NOT NULL,


                          RoleDesc [nvarchar](255) NOT NULL,


                          StateDesc [nvarchar](255) NOT NULL,


                          DelayTime [numeric] NULL)


                          insert into #tmpReplicationDetails select * from fnMonitorReplication()


                          truncate table ReplicationDetails


                          insert into ReplicationDetails select * from #tmpReplicationDetails


                          drop table #tmpReplicationDetails',






          IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


          EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1


          IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


          EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Monitor Schedule',
























          IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


          EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = @@SERVERNAME


          IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback




          GOTO EndSave










      Server: TESTENGVAULT


      Database: Sandbox3

        • Re: Incorrect Server Name When Creating Vault
          Charley Saint

          Hey Matt,


          Have you been playing around with database replication? This job is part of that functionality, you can see if the test SQL Server has a reference to the prod SQL Server with this query


          select * from sys.dm_hadr_database_replica_states

            • Re: Incorrect Server Name When Creating Vault
              Matt McCormick

              Thanks for the reply Charley. I have not been playing around with database replication.


              The quick fix was to uninstall SQL Server and reinstall, which fixed the issue. However, this solution might not be applicable to most use cases: All database references for existing vaults were lost. In my case, I was planning on deleting all existing vaults and creating a new one anyway, so whether you can recover those databases and how cumbersome it would be is up in the air since I didn't attempt to recover them.