November 10, 2009 at 12:37 pm
Hello My friend
This is my first post here and i hope to find a solution for this really nasty problem.
I have a sql scripts which works fine with SQL2005 but as soon as i try to run it under SQL2000 i get a strange error message.I tried MSDN but with no luck since 3 days ago.I have really no idea what causes this.
The piece of script i try to run in query analyzer 2000 to create a store procedure is:
create procedure [{databaseOwner}].[{objectQualifier}post_list](@TopicID int,@UpdateViewCount smallint=1, @ShowDeleted bit = 1, @StyledNicks bit = 0) as
begin
set nocount on
if @UpdateViewCount>0
update [{databaseOwner}].[{objectQualifier}Topic] set Views = Views + 1 where TopicID = @TopicID
select
d.TopicID,
TopicFlags= d.Flags,
ForumFlags= g.Flags,
a.MessageID,
a.Posted,
Subject = d.Topic,
a.Message,
a.UserID,
a.Position,
a.Indent,
a.IP,
a.Flags,
a.EditReason,
a.IsModeratorChanged,
a.IsDeleted,
a.DeleteReason,
UserName= IsNull(a.UserName,b.Name),
b.Joined,
b.Avatar,
b.Signature,
Posts= b.NumPosts,
b.Points,
d.Views,
d.ForumID,
RankName = c.Name,
c.RankImage,
(SELECT Count(*) FROM [dbo].[yaf_Thanks] i WHERE i.MessageID=a.MessageID) as MessageThanksNumber,
(SELECT Count(*) FROM [dbo].[yaf_Thanks] i WHERE i.ThanksFromUserID=a.UserID) as ThanksFromUserNumber,
(SELECT Count(*) FROM [dbo].[yaf_Thanks] i WHERE i.ThanksToUserID=a.UserID) as ThanksToUserNumber,
(SELECT Count(DISTINCT MessageID) FROM [dbo].[yaf_Thanks] i WHERE i.ThanksToUserID=a.UserID) as ThanksToUserPostsNumber,
(SELECT Null as Tag, Null as Parent, ThanksFromUserID AS UserID, ThanksDate FROM [dbo].[yaf_Thanks] i WHERE i.MessageID = a.MessageID for xml raw) AS ThanksInfo,
Style = case(@StyledNicks)
when 1 then ISNULL(( SELECT TOP 1 f.Style FROM [{databaseOwner}].[{objectQualifier}UserGroup] e
join [{databaseOwner}].[{objectQualifier}Group] f on f.GroupID=e.GroupID WHERE e.UserID=b.UserID AND LEN(f.Style) > 2 ORDER BY f.SortOrder), c.Style)
else '' end,
Edited = IsNull(a.Edited,a.Posted),
HasAttachments= (select count(1) from [{databaseOwner}].[{objectQualifier}Attachment] x where x.MessageID=a.MessageID),
HasAvatarImage = (select count(1) from [{databaseOwner}].[{objectQualifier}User] x where x.UserID=b.UserID and AvatarImage is not null)
from
[{databaseOwner}].[{objectQualifier}Message] a
join [{databaseOwner}].[{objectQualifier}User] b on b.UserID=a.UserID
join [{databaseOwner}].[{objectQualifier}Topic] d on d.TopicID=a.TopicID
join [{databaseOwner}].[{objectQualifier}Forum] g on g.ForumID=d.ForumID
join [{databaseOwner}].[{objectQualifier}Category] h on h.CategoryID=g.CategoryID
join [{databaseOwner}].[{objectQualifier}Rank] c on c.RankID=b.RankID
where
a.TopicID = @TopicID
AND a.IsApproved = 1
AND (a.IsDeleted = 0 OR (@showdeleted = 1 AND a.IsDeleted = 1))
order by
a.Posted asc
end
GO
But i get the following errors:
Server: Msg 170, Level 15, State 1, Procedure {objectQualifier}post_list, Line 38
Line 38: Incorrect syntax near 'xml'.
Server: Msg 156, Level 15, State 1, Procedure {objectQualifier}post_list, Line 41
Incorrect syntax near the keyword 'ORDER'.
Server: Msg 170, Level 15, State 1, Procedure {objectQualifier}post_list, Line 44
Line 44: Incorrect syntax near ','.
Server: Msg 156, Level 15, State 1, Procedure {objectQualifier}post_list, Line 46
Incorrect syntax near the keyword 'from'.
The bothering line is:
(SELECT Null as Tag, Null as Parent, ThanksFromUserID AS UserID, ThanksDate FROM [dbo].[yaf_Thanks] i WHERE i.MessageID = a.MessageID for xml raw) AS ThanksInfo,
Any idea please to get me out of this hell:(
Thank You
November 10, 2009 at 1:34 pm
You may have trouble with this one. It's having a hard time with with XML data type column you have (XML wasn't a valid data type in 2000), so the FOR XML is causing issues.
You might be able to get away with adding ", TYPE" to that sub-query, IF you have SQLXML3.0 installed (which seems to say it applies to 2000 as well as 2005). I don't have a 2000 instance to test this out with at this time.
The original version of SQLXML in 2000 didn't allow any form of nexting XML clauses in this fashion (you had to build out FOR XML EXPLICIT commands) to make it work. It would be interesting to see if this update does in fact change that feature, like it seems to imply.
http://msdn.microsoft.com/en-us/library/aa258005(SQL.80).aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 10, 2009 at 1:46 pm
Thank you matt
SQLXML3 is not installed unfortunately.
Any idea how to bypass or fix this?
Thanks
November 10, 2009 at 2:01 pm
I'm not sure if it helps to explicit cast the xml result as varchar(8000) or nvarchar(4000).
Another option could be to put the XML subselect into a separate (temp?) table with messageid and the xml structure as varchar column and do a direct join to it. Might help performance as well...
I'm not sure if SQL2000 would let you have a calculated varchar column based on a FOR XML statement. This would allow you to use a direct join to [dbo].[yaf_Thanks], too.
November 11, 2009 at 8:23 am
a1042629 (11/10/2009)
Thank you mattSQLXML3 is not installed unfortunately.
Any idea how to bypass or fix this?
Thanks
If you need this functionality, then it might be worth testing out what happens when you DO install SQLXML3.0, since this will give you the functionality you require (again - assuming it actually does what the above referenced article says it will in SQL 2000.
Otherwise - Lutz is right - you'd have to do the heavy lifting yourself by building the XML columns by hand (slow).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply