Changing DB name in a view

Transact-SQL
1

Hello,

SQL newb that has hit the wall on what he knows.

I have a SQL view that uses a linked server connection to another database on a different SQL server. We have moved our production database(The Linked Server DB) over to a new piece of hardware and we have renamed the database itself. The view I'm trying to modify is still talking to the old database on the old hardware. I need to update the view to talk to the newly moved and renamed database. I thought I could just replace DB1 with DB2 and create the second view but I get the following errors. I'm not the person who created the original view nor am I anywhere near as fluent with SQL as this person was. Any help or pointers in the right direction would be appreciated.

Aaron

Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 13
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.TaskInstances.TaskVersionGuid" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 14
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.TaskInstances.ResourceGuid" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 14
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Evt_Task_Instances._ResourceGuid" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 15
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.TaskInstances.TaskInstanceGuid" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 15
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Evt_Task_Instances.TaskInstanceGuid" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 17
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Evt_Task_Instances._ResourceGuid" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 17
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.RM_ResourceComputer.Guid" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 19
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Evt_Task_Instances.ParentTaskInstanceGuid" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 21
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.ItemVersions.ItemGuid" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 21
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Item.Guid" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 22
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.ItemVersions.VersionGuid" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 4
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.RM_ResourceComputer.Name" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 5
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Item.Name" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 5
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Item.Name" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 6
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Evt_Task_Instances.ExecutedBy" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 7
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Evt_Task_Instances.InstanceStatus" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 7
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Evt_Task_Instances.InstanceStatus" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 7
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Evt_Task_Instances.InstanceStatus" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 7
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Evt_Task_Instances.InstanceStatus" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 7
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Evt_Task_Instances.InstanceStatus" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 8
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Evt_Task_Instances.StartTime" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 9
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Evt_Task_Instances.EndTime" could not be bound.
Msg 4104, Level 16, State 1, Procedure vComp_ComputerTaskStatusInfo33, Line 24
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Evt_Task_Instances.StartTime" could not be bound.
Msg 4104, Level 16, State 1, Procedure vAltiris_ComputerTaskStatusInfo33, Line 24
The multi-part identifier "LINKEDSERVERNAME.DB2.dbo.Evt_Task_Instances.EndTime" could not be bound.

Inventory Database = Internal
Old Linked Server database = DB1
Newly renamed Linked Server database = DB2

I'm just trying to create a second view that talks to the new database. Below is the view that I currently have.

USE [Internal]
GO

/****** Object: View [dbo].[vComp_ComputerTaskStatusInfo33] Script Date: 10/23/2015 12:07:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create VIEW [dbo].[vComp_ComputerTaskStatusInfo33]
AS
SELECT TOP (100) PERCENT DB1.dbo.RM_ResourceComputer.Name AS CompName, 'Software' AS Source,
CASE WHEN i.ProductUninstalled = 1 THEN 'Uninstall ' ELSE '' END + CASE WHEN DB1.dbo.Item.Name = i.Name THEN DB1.dbo.Item.Name ELSE
' - ' + i.Name END AS Summary, DB1.dbo.Evt_Task_Instances.ExecutedBy,
CASE DB1.dbo.Evt_Task_Instances.InstanceStatus WHEN 3 THEN 'Failure' WHEN 0 THEN 'Not Started' WHEN 2 THEN 'Success' WHEN 4 THEN 'Cancelled'
WHEN 1 THEN 'In Progress' ELSE 'Not Reported' END AS Status, DB1.dbo.Evt_Task_Instances.StartTime,
DB1.dbo.Evt_Task_Instances.EndTime, i.Description AS Detail
FROM DB1.dbo.Item AS i INNER JOIN
DB1.dbo.ItemVersions AS iv INNER JOIN
DB1.dbo.ItemVersionData AS ivd ON iv.VersionGuid = ivd.VersionGuid ON i.Guid = iv.ItemGuid INNER JOIN
DB1.dbo.TaskInstances ON ivd.VersionGuid = DB1.dbo.TaskInstances.TaskVersionGuid INNER JOIN
DB1.dbo.Evt_Task_Instances ON DB1.dbo.TaskInstances.ResourceGuid = DB1.dbo.Evt_Task_Instances._ResourceGuid AND
DB1.dbo.TaskInstances.TaskInstanceGuid = DB1.dbo.Evt_Task_Instances.TaskInstanceGuid INNER JOIN
DB1.dbo.RM_ResourceComputer ON
DB1.dbo.Evt_Task_Instances._ResourceGuid = DB1.dbo.RM_ResourceComputer.Guid INNER JOIN
DB1.dbo.TaskInstances AS TaskInstances_1 ON
DB1.dbo.Evt_Task_Instances.ParentTaskInstanceGuid = TaskInstances_1.TaskInstanceGuid INNER JOIN
DB1.dbo.ItemVersions INNER JOIN
DB1.dbo.Item ON DB1.dbo.ItemVersions.ItemGuid = DB1.dbo.Item.Guid ON
TaskInstances_1.TaskVersionGuid = DB1.dbo.ItemVersions.VersionGuid
WHERE (i.Attributes = 0)
ORDER BY DB1.dbo.Evt_Task_Instances.StartTime DESC, DB1.dbo.Evt_Task_Instances.EndTime DESC

GO

2

Is the new server linked to the server from which you are querying? You can use sp_linkedservers to see a list of servers that are linked.

If it is not, you will need to create the linked server. See here on how to do that. If you have the old server still listed as a linked server, you can pattern your new linked server based on that.

3

Yes, the new server is linked. The old server is not. Maybe I should add the original linked server back and try that?

Added the original linked server back and it didn't make a difference. Didn't think it would but I figured it was worth a try.

I'm trying to alter the view and I get the same errors.