Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Stored Procedure - Used to work, now fails

Posted on 2010-01-11
15
Medium Priority
?
193 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
  • 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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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 a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

578 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