Stored Proc works on 2005 not 2000 ?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Proc works on 2005 not 2000 ??

Author

Topic

bogey
Posting Yak Master

166 Posts

Posted - 2006-06-01 : 15:19:27

I'm away from my office and we are helping one of my developers in trying to figure out where the error is comming from. This stored proc is OK on a 2005 box but gives all kind of errors on a 2000 box. Here is the stored proc

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [som_online_app].[spUpdateAMCASDesMed]
@vapp_year AS CHAR(4)
AS
BEGIN

DECLARE @vamcas_id AS varchar(15), @vstatus AS varchar(5), @vaction_cd AS varchar(2), @vnew_id AS INT, @vupdate_success AS INT

DECLARE c1 CURSOR LOCAL FAST_FORWARD FOR
SELECT AAMC_id, 'PR' AS AMCAS_code FROM AMCAS_Prelim_Withdrawal WHERE app_year = @vapp_year
UNION
SELECT app_amcas_id, amcas_code
FROM applicant a INNER JOIN appLookupStatus s ON a.app_status = s.status_code
WHERE amcas_code IN ('PR', 'RJ', 'WB', 'WA') AND app_year = @vapp_year

OPEN c1

FETCH NEXT FROM c1 INTO @vamcas_id, @vstatus

WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @vaction_cd = (SELECT TOP 1 rsaa.ADMISSION_ACTION_CD FROM AMCAS2.amcas2user.ADMISSIONS_ACTION aa
INNER JOIN AMCAS2.amcas2user.REF_SCHOOL_ADMISSION_ACTION rsaa ON aa.SCHOOL_ADMISSION_ACTION_ID = rsaa.SCHOOL_ADMISSION_ACTION_ID AND aa.APPL_YEAR = rsaa.APPL_YEAR
INNER JOIN AMCAS2.amcas2user.DESIGNATED_SCHOOL ds ON aa.DESIGNATED_SCHOOL_ID = ds.DESIGNATED_SCHOOL_ID AND aa.APPL_YEAR = ds.APPL_YEAR
INNER JOIN AMCAS2.amcas2user.APPLICANT_PERSON ap ON ds.APPL_PERSON_ID = ap.APPL_PERSON_ID AND ds.APPL_YEAR = ap.APPL_YEAR
WHERE (ap.AAMC_ID = @vamcas_id AND ap.appl_year = @vapp_year) ORDER BY aa.action_date DESC)

SET @vupdate_success = 1

IF @vaction_cd IS NOT NULL
BEGIN
IF @vaction_cd NOT IN ('PR','RJ','DF','MA','WB','WA')
BEGIN
SET @vnew_id = ((SELECT MAX(LOCAL_AAUU_ID) FROM AMCAS2.amcas2user.LOCAL_AAUU) + 1)

BEGIN TRY
INSERT INTO AMCAS2.amcas2user.LOCAL_AAUU (LOCAL_AAUU_ID,AAMC_ID,APPL_YEAR,ADMISSION_ACTION_CD,USER_ID,TIMESTAMP)
VALUES (@vnew_id,@vamcas_id,@vapp_year,@vstatus,'FinActCMD',CURRENT_TIMESTAMP)
END TRY

BEGIN CATCH
SET @vupdate_success = 0
END CATCH

INSERT INTO som_online_app.DesMedLog (amcas_id,app_year,amcas_status,success)
VALUES (@vamcas_id,@vapp_year,@vstatus,@vupdate_success)
END
END
ELSE
BEGIN
SET @vnew_id = ((SELECT MAX(LOCAL_AAUU_ID) FROM AMCAS2.amcas2user.LOCAL_AAUU) + 1)

BEGIN TRY
INSERT INTO AMCAS2.amcas2user.LOCAL_AAUU (LOCAL_AAUU_ID,AAMC_ID,APPL_YEAR,ADMISSION_ACTION_CD,USER_ID,TIMESTAMP)
VALUES (@vnew_id,@vamcas_id,@vapp_year,@vstatus,'FinActCMD',CURRENT_TIMESTAMP)
END TRY

BEGIN CATCH
SET @vupdate_success = 0
END CATCH

INSERT INTO som_online_app.DesMedLog (amcas_id,app_year,amcas_status,success)
VALUES (@vamcas_id,@vapp_year,@vstatus,@vupdate_success)
END

SET @vamcas_id = ''
SET @vstatus = ''
SET @vaction_cd = ''

FETCH NEXT FROM c1 INTO @vamcas_id, @vstatus
END

CLOSE c1

DEALLOCATE c1

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-01 : 15:42:28

You can't use Try/Catch in 2000. It is only available in 2005.

Tara Kizer
aka tduggan

bogey
Posting Yak Master

166 Posts

Posted - 2006-06-01 : 15:45:58

quote:
Originally posted by tkizer

You can't use Try/Catch in 2000. It is only available in 2005.

Tara Kizer
aka tduggan

Thanks Tara. I was just reading this article http://www.sswug.org/see/20064

Take it easy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-02 : 02:22:57

To handle errors in sql server 2000, read this
http://www.sommarskog.se/error-handling-I.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources