SQL Stored Procedure - Used to work, now fails


  I have a stored procedure that was wrote almost two years ago by someone else, and had been working fine for those two years.  Now, the hosting company did some upgrades, and the SP will no longer function.  They are telling me there is no issue on the hosting end, and are dumping it on me.  I have other SP's that will fire correctly, but I need someone who is more versed in SP's to tell me if there appears to be any issues with it.  

  When I attempt to execute it via MS SQL Server Managment Studio or via the ASP page, it produces the following error:

Invalid object name 'recsup.vDealerZip'.

ALTER PROCEDURE [dbo].[GetDealersByZipAndProduct]
@zipcode varchar(5), @radius int, @productIDs varchar(500) = null

	DECLARE @lat float, @long float

	SELECT @lat = Latitude, @long = Longitude
		FROM recsup.US_ZIPS
		WHERE ZIP = @zipcode
	DECLARE @distances table (zip varchar(5), lat float, long float, distance float)

	INSERT INTO @distances(zip, lat, long, distance)
	(SELECT zip = DealerZip, lat = Latitude, long = Longitude, distance = dbo.Distance(@lat, @long, v.Latitude, v.Longitude)
		FROM recsup.vDealerZip v)

		SELECT DISTINCT(DealerID), DealerAddress, DealerZip, DealerName, DealerPhone,
			DealerCity, DealerState, DealerWebsite, distance
		FROM recsup.vDealerZip v
			INNER JOIN @distances d ON v.DealerZip = d.zip
		WHERE d.distance <= @radius
			ORDER BY d.distance


Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
please pass the output of this

SELECT * FROM information_schema.tables WHERE table_name = 'vDealerZip'
DigitalFusionTomAuthor Commented:
that is what is so danged confusing to me.  There is no vDealerZip table at all.  There is a Dealers table, and within that table there is a DealerZip column.

I have written a lot of simple SQL queries in my day, and a few SP's.  I dont understand this SP at all.  I never saw the SP before the sever change, so I dont know if it was always like this or not.  
it may not be a table but a view and it may be something in the view code. please provide it as well.
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

DigitalFusionTomAuthor Commented:
wow, now I am starting to feel pretty ignorant.

When I ran the "SELECT * FROM information_schema.tables WHERE table_name = 'vDealerZip'" query, there was 0 rows returned.

How do I post the View code, where do I find it?  I looked under views in the database with MS SQL Server Management Studio Express, but it is full of System Views
in the Management Studio object explorer

Server -> Databases -> YourDatabase -> Views

it should be there and not in system views.

once you found it right click and Modify
DigitalFusionTomAuthor Commented:
perhaps we are getting somewhere then?  There is nothing under "views" except system views.
let's try to find out what it is

select * from sys.objects where name like 'vDealerZip'
DigitalFusionTomAuthor Commented:
returned 0 rows
then the two facts seem to be:

the stored procedure is referencing vDealerZip object
the object vDealerZip does not exist in the database


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DigitalFusionTomAuthor Commented:
right.   The real question becomes where the heck did it go and how?  
do you have a backup from before the conversion took place?
DigitalFusionTomAuthor Commented:
The host claims that the SQL database server was not altered, transferred, or changed in any way.  Seems a little fishy to me that this thing goes down, and vDealerZip disappears at the same time as the site was transfered to the new server?

I do not have any backups of the SQL db, but I guess I can ask the host if they do.  
unless... maybe it wasn't view at all but a UDF defined in an assembly.
If the database was moved to a new server and the assembly was not migrated you will get exactly that problem
DigitalFusionTomAuthor Commented:
hosting company finally fessed up.  There were some issues with the SQL server.  
DigitalFusionTomAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.