Weirdness when creating SP on differing versions

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)

Weirdness when creating SP on differing versions

Author

Topic

CtrlAltDel
Starting Member

17 Posts

Posted - 2006-02-26 : 20:25:19

Our development server is running SQL Server 2005, while our staging and production servers are running SQL Server 2000. A stored proc I've been developing builds up a query string to be executed using sp_executesql, and goes something like this:

SELECT 
	@topCount = CAST((@pageNum + @pageLimit) * @pageSize AS VARCHAR),
	@sort = '',
	@sortOrder='',
	@Filter	= '',
	@currentDateTime = GETDATE(),
	@Fields = '
	s.SectionCode,
	s.PurchaseID,	
	s.AdvertID,
	s.PurchaseRef,
	s.PublishRef,
	s.WebRef,
	s.CasualAdvertiserID,
	s.BookingStatusCode,
	s.PubCode,
	s.Deadline,
	s.RunDate,
	s.BookingDate,
	s.ModifiedDate,
	s.UserName,
	s.PostalState,
	s.PostalCountryCode,
	s.FirstName,
	s.LastName,
	s.PrimaryNumber,
	s.CompanyName,
	s.SortWord,
	s.Stage,
	s.AssignedToID,
	s.AssignedTo,
	s.FirstName + '' '' + s.Lastname AS AssignedToFullName,
	NULL, NULL', -- These fields required to match fields returned by up_SearchAdvertsForAlerts2
	@searchQuery = '
FROM 
	dbo.uv_AdvertSearchFields AS s INNER JOIN
	(
		SELECT 
			sf.PurchaseId,
			sf.AdvertTypeCode,
			sf.LifeCycleStatusCode,
			sf.Version,
			MIN(CASE WHEN sf.RunDate > GETDATE() THEN sf.Rundate ELSE NULL END) AS NextFutureDate,
			MAX(sf.Rundate) AS MaxLastRunDate
		FROM
			dbo.uv_AdvertSearchFields AS sf',
	@group = '
		GROUP BY
			sf.PurchaseId,
			sf.AdvertTypeCode,
			sf.LifeCycleStatusCode,
			sf.Version
		HAVING
			(sf.LifeCycleStatusCode = 2 OR (sf.LifeCycleStatusCode = 1 AND sf.Version = 1)) -- Currently active version
	) AS s2 ON (
			s.PurchaseId = s2.PurchaseId AND 
			s.AdvertTypeCode = s2.AdvertTypeCode AND 
			s.RunDate = ISNULL(s2.NextFutureDate, s2.MaxLastRunDate) -- Run Date matches the NextFutureDate or the LastRunDate
			)
'

When I run this code on SQL 2005, no problems. However, when I try to deploy to staging or production, Query Analyser barfs at the carriage returns next to the assignments to

@searchQuery

and

@group

with the following error:

Incorrect syntax near the keyword 'INNER'.

Is there an environment setting I'm missing or something? I don't remember SQL 2000 being this sensitive about carriage returns in a SELECT statement

--
"Life is like a sewer - what you get out of it depends on what you put into it"; Tom Lehrer

CtrlAltDel
Starting Member

17 Posts

Posted - 2006-02-26 : 20:28:00

A workaround is to change the query to:

SELECT 
	@topCount = CAST((@pageNum + @pageLimit) * @pageSize AS VARCHAR),
	@sort = '',
	@sortOrder='',
	@Filter	= '',
	@currentDateTime = GETDATE(),
	@Fields = '
	s.SectionCode,
	s.PurchaseID,	
	s.AdvertID,
	s.PurchaseRef,
	s.PublishRef,
	s.WebRef,
	s.CasualAdvertiserID,
	s.BookingStatusCode,
	s.PubCode,
	s.Deadline,
	s.RunDate,
	s.BookingDate,
	s.ModifiedDate,
	s.UserName,
	s.PostalState,
	s.PostalCountryCode,
	s.FirstName,
	s.LastName,
	s.PrimaryNumber,
	s.CompanyName,
	s.SortWord,
	s.Stage,
	s.AssignedToID,
	s.AssignedTo,
	s.FirstName + '' '' + s.Lastname AS AssignedToFullName,
	NULL, NULL' -- These fields required to match fields returned by up_SearchAdvertsForAlerts2

SET	@searchQuery = ' FROM dbo.uv_AdvertSearchFields AS s INNER JOIN
	(
		SELECT 
			sf.PurchaseId,
			sf.AdvertTypeCode,
			sf.LifeCycleStatusCode,
			sf.Version,
			MIN(CASE WHEN sf.RunDate > GETDATE() THEN sf.Rundate ELSE NULL END) AS NextFutureDate,
			MAX(sf.Rundate) AS MaxLastRunDate FROM dbo.uv_AdvertSearchFields AS sf'
SET	@group = ' GROUP BY sf.PurchaseId, sf.AdvertTypeCode, 			sf.LifeCycleStatusCode,
			sf.Version
		HAVING
			(sf.LifeCycleStatusCode = 2 OR (sf.LifeCycleStatusCode = 1 AND sf.Version = 1)) -- Currently active version
	) AS s2 ON (
			s.PurchaseId = s2.PurchaseId AND 
			s.AdvertTypeCode = s2.AdvertTypeCode AND 
			s.RunDate = ISNULL(s2.NextFutureDate, s2.MaxLastRunDate) -- Run Date matches the NextFutureDate or the LastRunDate
			)
'

Most strange.

--
"Life is like a sewer - what you get out of it depends on what you put into it"; Tom Lehrer

CtrlAltDel
Starting Member

17 Posts

Posted - 2006-02-26 : 20:29:42

Hmm, I've traced it down to the comment

-- These fields required to match fields returned by up_SearchAdvertsForAlerts2

. Removing the comment makes everything work nicely. Now that's just bizarre.

--
"Life is like a sewer - what you get out of it depends on what you put into it"; Tom Lehrer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources