March 23, 2009 at 10:17 am
I have been trying to create the following stored procedure but when I try to create it, I get an EXCEPTION_ACCESS_VIOLATION error ("Process 55 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.").
I am using SQL Server 2008 Enterprise Edition on my local machine, with Windows authentication.
From doing some debugging, it looks like it doesn´t like the ROW_NUMBER() function combined with the CTE. Is this a bug in SQL Server 2008?
Here´s a cut down version of the sp:
[font="Courier New"]Create PROCEDURE [EOS].[usp_JOBS_JobSearches]
@sort nvarchar(50),
@OrderDirection nvarchar(5),
@SearchString nvarchar(1000)='""',
@CategoryId int,
@JobType int,
@ProvinceId int,
@MunicipalityID int
AS
BEGIN
SET NOCOUNT ON;
WITH PagingTable AS
(
SELECT (ROW_NUMBER() OVER (ORDER BY rank desc,)) AS RowNumber, f.rank as rank, datecreated, JobId, Subject, CategoryId, Reference, Keywords, Descr, SalaryMin, LocationId, OnTargetEarnings, SalaryMax,
Currency, CategoryName, MunicipalityName, ProvinceName
FROM uvw_JobDetails_Province_Location_for_Full_Text_Indexing,
FREETEXTTABLE(uvw_JobDetails_Province_Location_for_Full_Text_Indexing,*,@SearchString ) as f
WHERE uvw_JobDetails_Province_Location_for_Full_Text_Indexing.jobid=f.
AND Online=1
AND (CategoryId = @CategoryId OR @CategoryId IS NULL)
AND (JobType = @JobType OR @JobType IS NULL)
AND (ProvinceId = @ProvinceId OR @ProvinceId IS NULL)
AND (LocationID = @MunicipalityID OR @MunicipalityID IS NULL)
)
SELECT *
FROM PagingTable
WHERE RowNumber BETWEEN @RowStart AND @RowEnd
ORDER BY rank desc
END
GO
[/font]
Thanks in advance
David
March 27, 2009 at 4:32 am
We´ve been doing some further work on this and have managed to create the stored procedure on a SQL Server 2005 machine, so I think this is a bug in SQL Server 2008.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply