[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Stored Procedures between SQL 2000 and 2005

Posted on 2008-02-07
5
Medium Priority
?
155 Views
Last Modified: 2010-03-19
Hi,

I am moving from SQL 2000 - 2005 and have copied existing SP's over, however the example SP below works fine in 2000 but yields no results in 2005 even though the table structures and data are the same. If I remove the joins then I do get a result, does 2005 deal with joins differently?
CREATE PROCEDURE dbo.wbAdmin
(
 	@USERNAME VARCHAR(20),
	@PASSWORD VARCHAR(10)
)
 
AS
SET NOCOUNT ON
 
SELECT  ISNULL(B.EmpID,'0') AS EMPMGR, ISNULL(C.EmpID,'0') AS COSTMGR, COMPNAME,A.COSTID,A.CompID,[Password],A.EmpID,Email,Service,Personal,BillType
FROM  wbEmpl A  
LEFT OUTER JOIN wbEmplManager B ON A.EmpID = B.EmpID
LEFT OUTER JOIN wbCostManager C ON A.EmpID = C.EmpID
INNER JOIN wbCompany D ON D.COMPID = A.COMPID
 
WHERE EMAIL =  @USERNAME AND [PASSWORD] = @PASSWORD
GO

Open in new window

0
Comment
Question by:trojan_uk
  • 3
  • 2
5 Comments
 
LVL 11

Accepted Solution

by:
f_o_o_k_y earned 500 total points
ID: 20839607
Hello
First of all you should use Upgrade Advisor to check if there can be some problem with your database on 2005
http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en

There is no difference in JOINs because they must be compilant with SQL-92

Did you try to run this code without WHERE

Use this qeuries to find out where problem is: (code below)

Best Regards
FooKy

SELECT  ISNULL(B.EmpID,'0') AS EMPMGR, ISNULL(C.EmpID,'0') AS COSTMGR, COMPNAME,A.COSTID,A.CompID,[Password],A.EmpID,Email,Service,Personal,BillType
FROM  wbEmpl A  
 
SELECT  ISNULL(B.EmpID,'0') AS EMPMGR, ISNULL(C.EmpID,'0') AS COSTMGR, COMPNAME,A.COSTID,A.CompID,[Password],A.EmpID,Email,Service,Personal,BillType
FROM  wbEmpl A  
LEFT OUTER JOIN wbEmplManager B ON A.EmpID = B.EmpID
 
SELECT  ISNULL(B.EmpID,'0') AS EMPMGR, ISNULL(C.EmpID,'0') AS COSTMGR, COMPNAME,A.COSTID,A.CompID,[Password],A.EmpID,Email,Service,Personal,BillType
FROM  wbEmpl A  
LEFT OUTER JOIN wbEmplManager B ON A.EmpID = B.EmpID
LEFT OUTER JOIN wbCostManager C ON A.EmpID = C.EmpID
 
SELECT  ISNULL(B.EmpID,'0') AS EMPMGR, ISNULL(C.EmpID,'0') AS COSTMGR, COMPNAME,A.COSTID,A.CompID,[Password],A.EmpID,Email,Service,Personal,BillType
FROM  wbEmpl A  
LEFT OUTER JOIN wbEmplManager B ON A.EmpID = B.EmpID
LEFT OUTER JOIN wbCostManager C ON A.EmpID = C.EmpID
INNER JOIN wbCompany D ON D.COMPID = A.COMPID

Open in new window

0
 
LVL 11

Expert Comment

by:f_o_o_k_y
ID: 20839612
Sorry my mistake I forgot to remove some fields from SELECT but you can correct them.
0
 

Author Comment

by:trojan_uk
ID: 20839825
Thanks fooKy,

That did help me get to the bottom of it, basically it looks like when I imported the data accross it has reset all the identities instead of importing the existing ones, so where the original empid in the emp table was 114 and the compid was 12, the empid is now 107 and the compid is 2. so when the last inner join checked for a company id, it was 12 in the emp table but 2 in the company table, hence why the join failed.

Is there a way to import the existing identity fields?
0
 
LVL 11

Expert Comment

by:f_o_o_k_y
ID: 20840123
Yes
You must set the identity_insert option to on and then use insert but you must specify column names in insert.
take a look.
http://msdn2.microsoft.com/en-us/library/ms188059.aspx


0
 

Author Comment

by:trojan_uk
ID: 20840176
Thanks fooKy, I did work it out in the end I did a Backup/Restore between 2000 and 2005 and that did the trick.

Thanks again
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

590 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