June 12, 2007 at 3:32 am
Hello All,
I have a stored procedure working fine in SQL SERVER 2000 and the same stored procedure is not working in SQL SERVER 2005. Can someone please help me out if i am missing some systems settings while installing SQL SERVER 2005. I am getting Syntax Error below
Any help is greatly appreciated.
Regds
Dharmendra Mudaliar
SQL DBA
/**********************************************************/
Below is the Stored Procedure taking Dynamic SQL Statements.
------------------------------------------------------------------------
exec PersonEntity_SearchInternetVersion3_Enhanced @sDynamicQuery=N' INNER JOIN ( SELECT x.PersonEntityId FROM tblT_People_Entity_Names
x(NOLOCK) WHERE CONTAINS(x.FullNameDetailInfo,'' "osama*"'') UNION ALL SELECT y.PersonEntityId FROM tblT_People_Entity_Names_Test_Rajesh
y(NOLOCK) WHERE CONTAINS(y.FullNameDetailInfo, '' "osama*"'' ) )b ON PED.PersonEntityId = b.PersonEntityId WHERE 1=1 AND (
PED.PersonEntityId not in ( SELECT personentityid FROM tblT_People_Roles_Occupations pr(NOLOCK) INNER JOIN tblT_Search_Exclusion_OccCat
oc(NOLOCK) on pr.occcategoryid = oc.occcatid where oc.AccountID = N''9GTV000100'') OR PED.personentityid in (SELECT he.personentityid FROM
tblt_people_entity_categORies he (NOLOCK) inner join tblt_people_entity_categORies she(NOLOCK) on he.personentityid = she.personentityid
where he.recORdtypecategORy1id = 1 AND she.recORdtypecategORy1id = 3))',@sAccountID=N'9GTV000100
/**********************************************************/
Below is the code of the Stored Procedure from which i am getting Error
--------------------------------------------------------------------------
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WHERE'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'OR'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'A'.
Drop procedure [dbo].[PersonEntity_SearchInternetVersion3_Enhanced]
go
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create
PROCEDURE [dbo].[PersonEntity_SearchInternetVersion3_Enhanced]
@sDynamicQuery NVARCHAR(4000)='',
@sAccountID nvarchar(4000) ='1'
AS
Declare
@abc NVARCHAR(4000)
Declare @StaticQuery NVARCHAR(4000)
declare @sQuery NVARCHAR(4000)
SET
LOCK_TIMEOUT 3600
-- ************************GET PERSONENTITYID'S WITH RESPECT TO INPUT CRITERAI********************
SET
NOCOUNT ON
set @StaticQuery = ' tblT_People_Entity_Details PED(NOLOCK)
INNER JOIN tblT_country_search country (NOLOCK)
on country.personentityid = ped.personentityid
INNER JOIN (select countryid,countryname from tblm_countries c1 (NOLOCK) where not exists
(SELECT CountryId FROM tblT_Search_Exclusion_Country c2 (NOLOCK)
where c1.CountryId = c2.CountryId and AccountID = N''' + @sAccountID + ''')) c ON country.countryid = c.countryid '
set
@abc = @StaticQuery + @sDynamicQuery
set @sQuery = 'SELECT DISTINCT TOP 501 PED.PersonEntityID
,(Case When EXISTS( SELECT TEC.PersonEntityID FROM tblt_people_entity_categories TEC(NOLOCK) WHERE TEC.PersonEntityID = PED.PersonEntityID AND TEC.recordtypecategory2id BETWEEN 6 AND 16 AND NOT EXISTS ( SELECT PersonEntityID FROM tblT_People_Entity_References TER(NOLOCK) WHERE TER.PersonEntityID = TEC.PersonEntityID )) Then ''0'' Else PED.RecORdTypeCategORy1 END) [IconRef] ,ISNULL(A.FullName,'''')[FullName],C.CountryName,ISNULL(PED.OccupationTitle,'''')[OccupationTitle] ,PED.RecORdTypeID[RecordType]
FROM
('
+
+
' )
INNER JOIN ( SELECT b.FullName,b.PersonEntityId FROM tblT_People_Entity_Names b(NOLOCK) WHERE b.NameTypeID IN(1,7)
UNION ALL SELECT c.FullName,c.PersonEntityId FROM tblT_People_Entity_Names_Test_Rajesh c(NOLOCK) WHERE c.NameTypeID IN(1,7) ) A on PED.PersonEntityID = A.PersonEntityId ORDER BY FullName, OccupationTitle ASC'
exec sp_executesql @sQuery
Declare
@sQuery1 NVarchar(4000)
set @sQuery1 = 'SELECT count(distinct PED.PersonEntityID) FROM tblT_People_Entity_Details PED(NOLOCK) INNER JOIN tblT_Person_Entity_Countries country (NOLOCK) on country.personentityid = ped.personentityid INNER JOIN (select countryid from tblm_countries c1 (NOLOCK) where not exists (SELECT CountryId FROM tblT_Search_Exclusion_Country c2 (NOLOCK) where c1.CountryId = c2.CountryId and AccountID = N''' + @sAccountID + ''')) c ON country.countryid = c.countryid ' + @sDynamicQuery
@sQuery
exec
sp_executesql @sQuery1
Thanks & Regards
Dharmendra S Mudaliar
( OEM India RSA )
Hello - +91 40 66934555 x 34555
Mobile - 9885408049 | IM - v-dharmu@microsoft.com
June 12, 2007 at 5:02 am
The problem might occur if you use non-ansi joins anywhere in your procedure.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 12, 2007 at 6:32 am
Dharmendra
Please will you add a PRINT @sQuery1 statement to your stored procedure, then run it and post @sQuery and @sQuery1 so that we can have a go at debugging it. If the procedure is failing on the first query, you may need to comment out the first sp_executesql statement so that you are only printing the queries and not executing them.
Thanks
John
June 13, 2007 at 1:15 am
Have you tried the Upgrade Advisor for SQL 2005? Saves me lot of time with the excellent help for errors found.
June 13, 2007 at 7:47 am
Hi All,
Thanks to Sugesh / John for the info. The problem occured in SQL SERVER 2005 was that the Database Compatability level was 80 which shoule be 90. After changing the compatability level to 90 my script executed successfully.
But now the issue is the Procedure is taking 19 Secs to give the output from the backend and 22 secs to give the output from the .Net Application.
Can someonve guide me how to increase the Performance better for the above Stored Procedure.
/******************************************************/
Below is the Stored Procedure being called from the .Net Application
/******************************************************/
exec PersonEntity_SearchInternetVersion3_Enhanced @sDynamicQuery=N' INNER JOIN ( SELECT x.PersonEntityId FROM tblT_People_Entity_Names
x(NOLOCK) WHERE CONTAINS(x.FullNameDetailInfo,'' "osama*"'') UNION ALL SELECT y.PersonEntityId FROM tblT_People_Entity_Names_Test_Rajesh
y(NOLOCK) WHERE CONTAINS(y.FullNameDetailInfo, '' "osama*"'' ) )b ON PED.PersonEntityId = b.PersonEntityId WHERE 1=1 AND (
PED.PersonEntityId not in ( SELECT personentityid FROM tblT_People_Roles_Occupations pr(NOLOCK) INNER JOIN tblT_Search_Exclusion_OccCat
oc(NOLOCK) on pr.occcategoryid = oc.occcatid where oc.AccountID = N''9GTV000100'') OR PED.personentityid in (SELECT he.personentityid FROM
tblt_people_entity_categORies he (NOLOCK) inner join tblt_people_entity_categORies she(NOLOCK) on he.personentityid = she.personentityid
where he.recORdtypecategORy1id = 1 AND she.recORdtypecategORy1id = 3))',@sAccountID=N'9GTV000100'
/*****************************************************/
Query Output from the Print Command
/*****************************************************/
@sQuery SELECT DISTINCT TOP 501 TempID.PersonEntityID
,(Case When EXISTS( SELECT TEC.PersonEntityID FROM tblt_people_entity_categories TEC(NOLOCK) WHERE TEC.PersonEntityID = TempID.PersonEntityID AND TEC.recordtypecategory2id BETWEEN 6 AND 16
AND NOT EXISTS ( SELECT PersonEntityID FROM tblT_People_Entity_References TER(NOLOCK) WHERE TER.PersonEntityID = TEC.PersonEntityID ))
Then '0' Else TempID.RecORdTypeCategORy1 END) [IconRef]
,ISNULL(A.FullName,'')[FullName]
,TempID.CountryName AS [Country]
,ISNULL(TempID.OccupationTitle,'')[OccupationTitle]
,TempID.RecORdTypeID[RecordType]
FROM
( SELECT PED.PersonEntityID,PED.RecORdTypeID,PED.RecORdTypeCategORy1, PED.OccupationTitle,c.countryname FROM tblT_People_Entity_Details PED(NOLOCK)
INNER JOIN tblt_Country_Search country (NOLOCK) on country.personentityid = ped.personentityid INNER JOIN (select countryid,countryname from tblm_countries c1 (NOLOCK) where not exists (SELECT CountryId FROM tblT_Search_Exclusion_Country c2 (NOLOCK) where c1.CountryId = c2.CountryId and AccountID = N'9GTV000100')) c
ON country.countryid = c.countryid INNER JOIN ( SELECT x.PersonEntityId FROM tblT_People_Entity_Names x(NOLOCK) WHERE CONTAINS(x.FullNameDetailInfo,' "osama*"') UNION ALL SELECT y.PersonEntityId FROM tblT_People_Entity_Names_Test_Rajesh y(NOLOCK) WHERE CONTAINS(y.FullNameDetailInfo, ' "osama*"' ) )b ON PED.PersonEntityId = b.PersonEntityId WHERE 1=1 AND ( PED.PersonEntityId not in ( SELECT personentityid FROM tblT_People_Roles_Occupations pr(NOLOCK) INNER JOIN tblT_Search_Exclusion_OccCat oc(NOLOCK) on pr.occcategoryid = oc.occcatid where oc.AccountID = N'9GTV000100') OR PED.personentityid in (SELECT he.personentityid FROM tblt_people_entity_categORies he (NOLOCK) inner join tblt_people_entity_categORies she(NOLOCK) on he.personentityid = she.personentityid where he.recORdtypecategORy1id = 1 AND she.recORdtypecategORy1id = 3)) ) As TempID INNER JOIN ( SELECT b.FullName,b.PersonEntityId FROM tblT_People_Entity_Names b(NOLOCK) WHERE b.NameTypeID IN(1,7)
UNION ALL
SELECT c.FullName,c.PersonEntityId FROM tblT_People_Entity_Names_Test_Rajesh c(NOLOCK) WHERE c.NameTypeID IN(1,7) )A on TempID.PersonEntityID = A.PersonEntityId ORDER BY FullName, OccupationTitle ASC
Regds
Dharmendra Mudaliar
SQL DBA
Thanks & Regards
Dharmendra S Mudaliar
( OEM India RSA )
Hello - +91 40 66934555 x 34555
Mobile - 9885408049 | IM - v-dharmu@microsoft.com
June 13, 2007 at 8:28 am
Dharmendra
Have the stored procedure print the query, then copy and paste it into the query window, and post the query and the estimated execution plan here (please preview your post first to check it doesn't contain lots of smilies!). It looks to me as if you don't really need to use dynamic SQL at all - is there any particular reason why you're doing this?
John
June 20, 2007 at 6:21 am
The problem might occur if you use non-ansi joins anywhere in your procedure.
May be compatibility level
June 22, 2007 at 12:19 pm
Just by eyeballing the code:
Regards,
Rubes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply