Code suddenly stopped working and Im stumped!

Question

Code suddenly stopped working and Im stumped!

DominantDBA

SSC Eights!

Points: 862
More actions
September 28, 2012 at 4:38 am

#267398

Hi All
So Here is some code that was happily running until today:
SELECT 1
FROM EWH_Staging.dbo.STAGE_REMP_VCC_Distinct vcc
It has now failed with
Invalid object name 'EWH_Staging.dbo.STAGE_REMP_VCC_Distinct'.
First thing I think is EWH_Staging has been dropped. No EWH_Staging ever existed on the local server
There is an EWH_Staging database on a remote server so I thought "There must have been a synonym that existed but now doesnt?"
There are no server aliases either?
Looking at DDL cahnges for the last week in a ssms report there were no aliases dropped in the last week. My question is DOES SQL SERVER CACHE synonym definitions?
Maybe it was dropped over a week ago but the definition remained in the cache and then it was flushed and then it broke?
Ive run out of ideas and this is going to bug me going into the weekend

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

wedgemail (9/28/2012)
First thing I think is EWH_Staging has been dropped. No EWH_Staging ever existed on the local server

Check whether table/view called STAGE_REMP_VCC_Distinct exists in the database EWH_Staging.

DominantDBA SSC Eights! Points: 862 More actions · Answer 2

DominantDBA

SSC Eights!

Points: 862

September 28, 2012 at 6:10 am

#1543373

It does but its on a remote server not local?

Lowell SSC Guru Points: 323518 More actions · Answer 3

wedgemail (9/28/2012)
It does but its on a remote server not local?

in that case, being on a remote server, you need a linked server so you can get to it.

and the command would change to

SELECT 1

FROM MyLiunkedServer.EWH_Staging.dbo.STAGE_REMP_VCC_Distinct vcc

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

If it is on remote server, I guess query should be as follows:

SELECT 1

FROM RemoteServerName.EWH_Staging.dbo.STAGE_REMP_VCC_Distinct vcc

DominantDBA SSC Eights! Points: 862 More actions · Answer 5

I dont think Im being clear

I know how to get it to run(add a linked server) but my question is how can

this code run successfully:

SELECT 1

FROM EWH_Staging.dbo.STAGE_REMP_VCC_Distinct vcc

It should have failed but it didnt until today

I know it hasnt changed either?

Is it a plan cache thing?

Lowell SSC Guru Points: 323518 More actions · Answer 6

wedgemail (9/28/2012)
I dont think Im being clear
I know how to get it to run(add a linked server) but my question is how can
this code run successfully:
SELECT 1
FROM EWH_Staging.dbo.STAGE_REMP_VCC_Distinct vcc
It should have failed but it didnt until today
I know it hasnt changed either?
Is it a plan cache thing?

if it ran yesterday on the exact same machine, but not today, something changed;

it might be the user you are running the query doesn't have a matching user in the EWH_Staging database(was it restored?)

it might be the database or table was dropped or renamed.

can you check the basics first...

when you said it exists on a remote machine, that made us think you are ont eh wrong machine, or missing a linked server.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!