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

      State=37000

      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)

       

      DECLARE @JobName NVARCHAR(MAX)

       

       

       

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

       

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

       

      IF (@jobId IS NULL)

       

      BEGIN   

       

          BEGIN TRANSACTION

       

          DECLARE @ReturnCode INT

       

          SELECT @ReturnCode = 0

       

         

       

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

       

          BEGIN

       

          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

       

         

       

          END

       

         

       

          DECLARE @Owner NVARCHAR(MAX)

       

          DECLARE @DbName NVARCHAR(MAX)

       

          SET @Owner = N'sa'

       

          SET @DbName = DB_NAME()

       

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

       

                  @enabled=1,

       

                  @notify_level_eventlog=0,

       

                  @notify_level_email=0,

       

                  @notify_level_netsend=0,

       

                  @notify_level_page=0,

       

                  @delete_level=0,

       

                  @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',

       

                  @step_id=1,

       

                  @cmdexec_success_code=0,

       

                  @on_success_action=1,

       

                  @on_success_step_id=0,

       

                  @on_fail_action=2,

       

                  @on_fail_step_id=0,

       

                  @retry_attempts=1,

       

                  @retry_interval=5,

       

                  @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',

       

                  @database_name=@DbName,

       

                  @flags=0

       

          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',

       

                  @enabled=1,

       

                  @freq_type=4,

       

                  @freq_interval=1,

       

                  @freq_subday_type=2,

       

                  @freq_subday_interval=15,

       

                  @freq_relative_interval=0,

       

                  @freq_recurrence_factor=0,

       

                  @active_start_date=20151201,

       

                  @active_end_date=99991231,

       

                  @active_start_time=0,

       

                  @active_end_time=235959

       

          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

       

          COMMIT TRANSACTION

       

          GOTO EndSave

       

          QuitWithRollback:

       

              IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

       

          EndSave:

       

      END

       

      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.