?
Solved

SQL Stored Procedure - Used to work, now fails

Posted on 2010-01-11
15
Medium Priority
?
192 Views
Last Modified: 2012-05-08
Hello,

  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'.






set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetDealersByZipAndProduct]
@zipcode varchar(5), @radius int, @productIDs varchar(500) = null
AS

BEGIN
	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

END

Open in new window

0
Comment
Question by:DigitalFusionTom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
15 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26286632
please pass the output of this

SELECT * FROM information_schema.tables WHERE table_name = 'vDealerZip'
0
 

Author Comment

by:DigitalFusionTom
ID: 26286857
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.  
0
 
LVL 10

Expert Comment

by:lof
ID: 26288011
it may not be a table but a view and it may be something in the view code. please provide it as well.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:DigitalFusionTom
ID: 26288114
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
0
 
LVL 10

Expert Comment

by:lof
ID: 26288140
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
0
 

Author Comment

by:DigitalFusionTom
ID: 26288154
perhaps we are getting somewhere then?  There is nothing under "views" except system views.
0
 
LVL 10

Expert Comment

by:lof
ID: 26288258
let's try to find out what it is

select * from sys.objects where name like 'vDealerZip'
0
 

Author Comment

by:DigitalFusionTom
ID: 26288377
returned 0 rows
0
 
LVL 10

Accepted Solution

by:
lof earned 1500 total points
ID: 26288404
then the two facts seem to be:

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

0
 

Author Comment

by:DigitalFusionTom
ID: 26288579
right.   The real question becomes where the heck did it go and how?  
0
 
LVL 10

Expert Comment

by:lof
ID: 26288607
do you have a backup from before the conversion took place?
0
 

Author Comment

by:DigitalFusionTom
ID: 26288626
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.  
0
 
LVL 10

Expert Comment

by:lof
ID: 26288690
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
0
 

Author Comment

by:DigitalFusionTom
ID: 26442368
hosting company finally fessed up.  There were some issues with the SQL server.  
0
 

Author Closing Comment

by:DigitalFusionTom
ID: 31675688
comments
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question