tags not working in 2.1b1

Reply

Roberto Posted: Wed, Jul 19 2006 2:50 PM

rated by 0 users

Just upgraded to 2.1b1 and browsing by tags is not working on my site. Creating tags works fine, it seems, but when I try to access a tag it throws an error and puts up the standard error page with the redirect here. You can click on http://robertmoir.com/tags/default.aspx and see the tags, but as soon as you click one it breaks down.

Checked my event viewer and exceptions reports and cannot see any errors logged for this. I'm looking for errors logged around the time I click and grepping for 'tags' and not finding anything.

I'm stumped! 

Reply

Scott Watermasysk replied on Wed, Jul 19 2006 2:53 PM

rated by 0 users

Could you turn custom errors off and report back the error?

Thanks,
Scott

Reply

Roberto replied on Wed, Jul 19 2006 3:13 PM

rated by 0 users

I'm a little embarassed not to have thought of doing that myself!

Error text below. This may well be related to the issue I report in my other recent post about the RSS feed roller, maybe.

Cannot resolve collation conflict for equal to operation.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot resolve collation conflict for equal to operation.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: Cannot resolve collation conflict for equal to operation.]
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
   System.Data.SqlClient.SqlCommand.ExecuteReader() +41
   CommunityServer.Data.SqlCommonDataProvider.GetTags(String sectionQuerySql, Object tagXml) +273
   CommunityServer.Data.SqlCommonDataProvider.GetTags(String[] tags) +38
   CommunityServer.Components.Tags.GetTags(String[] tags) +192
   CommunityServer.Controls.TagCloud.get_Tags() +363
   CommunityServer.Controls.BaseTagCloud.DataBind() +25
   CommunityServer.Controls.BaseTagCloud.OnLoad(EventArgs e) +16
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Control.LoadRecursive() +98
   System.Web.UI.Page.ProcessRequestMain() +750

Reply

Jose Lema replied on Wed, Jul 19 2006 3:26 PM

rated by 0 users

cs_Tags_Get.txt

Hey Roberto,

This looks like an issue that we found soon after the beta and have since resolved. I'm attaching the updated sql stored procedure for you to update.

Just open Query Analyzer. Open this file, cs_Tags_Get.PRC, and execute it.

Please let us know how it works out.

Thanks.

Reply

Roberto replied on Wed, Jul 19 2006 3:45 PM

rated by 0 users

Thanks José, but that file seems to be Missing or filtered?

I get a 404 error whether I click on it to download or right-click and do a save as.

Reply

Jose Lema replied on Wed, Jul 19 2006 4:03 PM

rated by 0 users

Looks like we've got some issue on cs.org with these attachments that we're looking into. :(

For now, the only change we've made to the file is the following. You may want to change it locally until we resolve the attachment issue.

What's happening is the tempdb of the server is using a different collation so the join to the temp table (#Tags) causes this error.

Find the line:

[Name] nvarchar(255)

and change it to

[Name] nvarchar(255) collate database_default

This should cause the temp table (#Tags created in the tempdb database) to be created with the same collation settings as your CS database.

Thanks

Reply

Roberto replied on Wed, Jul 19 2006 4:14 PM

rated by 0 users

Wonderful. That seems to have fixed it. Thank you very much :-)

I know I'm pushing my luck but: Any ideas on the roller problem?

Reply

Jose Lema replied on Wed, Jul 19 2006 4:24 PM

rated by 0 users

Glad to hear it worked!

Since I haven't seen this issue, you could attempt the same and report back

That is, in the table declaration (DECLARE @FeedPosts TABLE...) you could add "collate database_default" to each nvarchar/ntext field.

No guarantees...yet

Reply

Roberto replied on Wed, Jul 19 2006 4:50 PM

rated by 0 users

Yet again, many thanks José. That fixed it. I'm pasting the updated stored procedure that worked for me below (and for the little it counts with my readership) I'm going to link here from my blog comments so anyone else having problems with this can maybe find a fix in google a little easier.

  Many Many thanks. You and the other devs rock, and it looks like CS 2.1 will rock too.

---------- begin stored procedure -----------------------------

CREATE PROC [dbo].cs_FeedPost_UpdatePosts
    @FeedId INT,
    @FeedItemList NTEXT
AS

SET NOCOUNT ON

DECLARE @idoc INT
DECLARE @FeedPosts TABLE
(
    FeedId INT,
    Author NVARCHAR(255) collate database_default,
    Title NVARCHAR(255) collate database_default,
    Description NTEXT collate database_default,
    Source NVARCHAR(255) collate database_default,
    GuidName NVARCHAR(255) collate database_default,
    GuidIsPermaLink BIT,
    Link NVARCHAR(255) collate database_default,
    PubDate DATETIME,
    CommentsUrl NVARCHAR(255) collate database_default,
    EnclosureUrl VARCHAR(255) collate database_default,
    EnclosureLength BIGINT,
    EnclosureType NVARCHAR(100) collate database_default,
    Creator NVARCHAR(255) NULL,
    CommentApiUrl NVARCHAR(255) NULL,
    CommentRssUrl NVARCHAR(255) NULL,
    CommentCount INT NULL
)

EXEC sp_xml_preparedocument @idoc OUTPUT, @FeedItemList

-- First off, let's move all the XML into the table variable.
INSERT INTO @FeedPosts
  SELECT C.FeedId,
    C.Author,
    C.Title,
    C.Description,
    C.Source,
    C.GuidName,
    C.GuidIsPermaLink,
    C.Link,
    C.PubDate,
    C.CommentsUrl,
    C.EnclosureUrl,
    C.EnclosureLength,
    C.EnclosureType,
    C.Creator,
    C.CommentApiUrl,
    C.CommentRssUrl,
    C.CommentCount
FROM OPENXML(@idoc, '/feeds/feed', 3) 
WITH (    FeedId INT,
    Author NVARCHAR(255) collate database_default,
    Title NVARCHAR(255) collate database_default,
    Description NTEXT collate database_default,
    Source NVARCHAR(255) collate database_default,
    GuidName NVARCHAR(255) collate database_default,
    GuidIsPermaLink BIT,
    Link NVARCHAR(255) collate database_default,
    PubDate DATETIME,
    CommentsUrl NVARCHAR(255) collate database_default,
    EnclosureUrl VARCHAR(255) collate database_default,
    EnclosureLength BIGINT,
    EnclosureType NVARCHAR(100) collate database_default,
    Creator NVARCHAR(255) collate database_default,
    CommentApiUrl NVARCHAR(255) collate database_default,
    CommentRssUrl NVARCHAR(255) collate database_default,
    CommentCount INT) AS C

-- Insert missing posts
INSERT INTO cs_FeedPost
(
    FeedId,
    Author,
    Title,
    Description,
    Source,
    GuidName,
    GuidIsPermaLink,
    Link,
    PubDate,
    CommentsUrl,
    EnclosureUrl,
    EnclosureLength,
    EnclosureType,
    Creator,
    CommentApiUrl,
    CommentRssUrl,
    CommentCount
   
)
SELECT     C.FeedId,
    C.Author,
    C.Title,
    C.Description,
    C.Source,
    C.GuidName,
    C.GuidIsPermaLink,
    C.Link,
    C.PubDate,
    C.CommentsUrl,
    C.EnclosureUrl,
    C.EnclosureLength,
    C.EnclosureType,
    C.Creator,
    C.CommentApiUrl,
    C.CommentRssUrl,
    C.CommentCount
FROM @FeedPosts AS C
WHERE C.GuidName NOT IN     (
  SELECT GuidName FROM cs_FeedPost
  WHERE FeedId = @FeedId
            ) 

-- Update existing posts.
UPDATE cs_FeedPost
SET    Author = C.Author,
    Title = C.Title,
    Description = C.Description,
    Source = C.Source,
    GuidName = C.GuidName,
    GuidIsPermaLink = C.GuidIsPermaLink,
    Link = C.Link,
    PubDate = C.PubDate,
    CommentsUrl = C.CommentsUrl,
    EnclosureUrl = C.EnclosureUrl,
    EnclosureLength = C.EnclosureLength,
    EnclosureType = C.EnclosureType,
    Creator = C.Creator,
    CommentApiUrl = C.CommentApiUrl,
    CommentRssUrl = C.CommentRssUrl,
    CommentCount = C.CommentCount
FROM @FeedPosts AS C
WHERE cs_FeedPost.GuidName = C.GuidName



EXEC sp_xml_removedocument @idoc
GO
 

Reply

Jose Lema replied on Wed, Jul 19 2006 4:59 PM

rated by 0 users

Thank you for the kind words.

One is glad to be of service.