AnsweredAssumed Answered

Incorrect Server Name When Creating Vault

Question asked by Matt McCormick on Dec 6, 2017
Latest reply on Dec 8, 2017 by 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

Outcomes