Stored Procedure & Compatibility level

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

Transact-SQL (2000)

Stored Procedure & Compatibility level

Author

Topic

tonykhela
Starting Member

2 Posts

Posted - 2002-08-02 : 04:23:33

Hi All,
I have a database on SQL 2000 that was originally on a SQL 6.5 server. The database compatibility level was left at 65 (the person doing the upgrade didn't know any better!). I have since created a stored procedure that has been working fine, but it stops working when I change the compatibility level to 80. I need to use this level as I want to use a user defined function elsewhere that requires SQL 2000 syntax. The behaviour of the SP is entirely predictable, when the comp. level is 65 it returns data, when the comp. level is 80 it returns nothing - no data, no messages, no errors. The SP is very similar to others I have that are working fine on other 2000 databases. I have tried creating a new database and using DTS to copy all objects and data from the existing database to the new one, but it exhibits exactly the same behaviour. The SP is below. Can anyone see where I'm going wrong?

Thanks in advance,

TonyK

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_docs_prec_rept2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_docs_prec_rept2]
GO

SET QUOTED_IDENTIFIER OFF
GO

----------------------------------
CREATE PROCEDURE sp_docs_prec_rept2
----------------------------------

@sdate char(10),
@edate char(10),
@searchstr varchar(200),
@restrbg varchar(200),
@restrsubj varchar(200)

AS

DECLARE @mainsql varchar(4000)
DECLARE @selectsql varchar(1000)
DECLARE @restrictsql varchar(2000)

BEGIN

IF @restrbg = ''
SELECT @restrbg = NULL

IF @restrsubj = ''
SELECT @restrsubj = NULL

IF @searchstr = ''
SELECT @searchstr = NULL

SELECT @selectsql="SELECT adesc.act_desc, a.START_DATE, typ.FULL_NAME AS typ_name, au.FULL_NAME AS au_name, pr.DOCNUMBER, pr.DOCNAME, sub.SUBJ_DESC, subcat.SUBCAT_NAME FROM docsadm.activitylog a INNER JOIN docsadm.profile pr ON a.docnumber = pr.docnumber LEFT OUTER JOIN DOCSADM.PEOPLE au ON a.AUTHOR = au.SYSTEM_ID INNER JOIN docsadm.people typ ON a.typist = typ.system_id INNER JOIN docsadm.prec_subject sub ON pr.prec_subject = sub.system_id INNER JOIN dbo.rt_activitydesc adesc ON a.activity_type = adesc.act_code INNER JOIN docsadm.sub_category subcat ON pr.sub_category = subcat.system_id WHERE a.start_date >= CONVERT(datetime, '" + @sdate + "', 103) AND a.start_date <= CONVERT(datetime, '" + @edate + "', 103) "

IF @restrbg IS NOT NULL
SELECT @restrictsql=@restrictsql + " AND subcat.SUBCAT_NAME LIKE '" + @restrbg + "%' "

IF @restrsubj IS NOT NULL
SELECT @restrictsql=@restrictsql + " AND sub.SUBJ_DESC LIKE '" + @restrsubj + "%' "

IF @searchstr IS NOT NULL
BEGIN
SELECT @restrictsql=@restrictsql + " AND (UPPER(typ.FULL_NAME) LIKE UPPER('%" + @searchstr + "%')"
SELECT @restrictsql=@restrictsql + " OR UPPER(au.FULL_NAME) LIKE UPPER('%" + @searchstr + "%')"
SELECT @restrictsql=@restrictsql + " OR UPPER(pr.DOCNUMBER) LIKE UPPER('%" + @searchstr + "%')"
SELECT @restrictsql=@restrictsql + " OR UPPER(adesc.act_desc) LIKE UPPER('%" + @searchstr + "%')"
SELECT @restrictsql=@restrictsql + " OR UPPER(pr.DOCNAME) LIKE UPPER('%" + @searchstr + "%'))"
END

SELECT @mainsql=@selectsql + @restrictsql + " ORDER BY subcat.subcat_name ASC, sub.subj_desc ASC, pr.docnumber ASC, a.start_date ASC"

EXEC (@mainsql)

PRINT @mainsql

END

GO

GRANT EXECUTE ON sp_docs_prec_rept2 TO timM

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-02 : 07:21:40

Have you the latest service pack - it sounds very similar to a v7 bug fixed in sp2 where a query could just not return data.
They changed the locking in sp2 to fix a lot of multi-user problems and also slow things down.

Other than that areas to look at are the left outer join, dates and null processing - of these null is probably the most likely.

Why use double quotes?

SELECT @selectsql='SELECT ...
date >= CONVERT(datetime, ''' + @sdate + ''', 103)...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources