This is some behaviour I observed from renaming databases. What is the cause of this? The example is simplified for the purposes of this question.
CREATE DATABASE [Database_A]
GO
CREATE DATABASE [Database_B]
GO
CREATE DATABASE [Database_B_Copy]
GO
-- create a table for testing purposes
USE [Database_B]
GO
CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Column1] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [Database_B].[dbo].[Table_1] (Column1) values ('[Database_B]')
GO
-- create an copy of the table with the same schema
USE [Database_B_Copy]
GO
CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Column1] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [Database_B_Copy].[dbo].[Table_1] (Column1) values ('[Database_B_Copy]')
GO
-- create a stored procedure to demonstrate the problem
USE [Database_A]
GO
CREATE PROCEDURE [dbo].[TestSP]
AS
BEGIN
SELECT * FROM Database_B.dbo.Table_1
END
GO
[1]. when I run the stored procedure TestSP the result as expected is the contents of DatabaseB Table_1.
ID Column1
1 [Database_B]
[2]. Renaming Database_B to Database_B_Original will cause this error as would be expected:
Msg 208, Level 16, State 1, Procedure TestSP, Line 4
Invalid object name 'Database_B.dbo.Table_1'.
[3]. Taking Database_B_Original offline, the error from the stored procedure changes to:
Msg 942, Level 14, State 4, Procedure TestSP, Line 4
Database 'Database_B_Original' cannot be opened because it is offline.
[4]. I then renamed Database_B_Copy to Database_B. I would have expected the stored procedure to work since the a database named Database_B was available. However the error remains the same.
Finally if I restart Sql Server this magically fixes the problem and the output is, as expected:
ID Column1
1 [Database_B_Copy]
Is SQL Server caching the tables and database name information incorrectly? Is there a way to force Sql Server to refresh the information?
I would like to gain a better understanding of what the underlying cause is for this issue and whether it's by-design or an error with sql server.
EXEC sp_refreshsqlmodule
and you may also consider using synonyms - however, is this a very realistic use case? How often are you dropping a database, and renaming another one to take its place? – Aaron Bertrand Aug 16 '13 at 13:32