Inner join between MOSS list and SQL table

Ahmed.AHameed — Tue, 22 Apr 2008 03:09:00 GMT

IF you have a MOSS list that contains a lot of data including UserID's. These ID's are stored in the Users table in SQL because the site is working by Forms authentication provider.
we need to display a specific result from this list in a grid including the user names not the IDs.
we have a lot of solutions for doing that:
- loop to every row and send this ID to a stored which returns the user name (Don't loop to SQL -very very bad performance-)

-Concatenating these ID's together and form a static select statement to be sent to the SQL and executed (very bad performance because this statemnet is not compiled, note that the "in" statment doesn't accept parameters so it can't be stored procedure) "select UserName from Users where UserId in (12,45,465,5,56,78,89,90)".
- Use Linque to join different data sources (Good solution)
- User SQL 2008: where you can create a stored procedure with parameters of type table. (then we can create a table in .net including the IDs and then send this table as a parameter to the stored procedure.)
- Use Open XML in SQL 2005, where you can format XML string and pass this XML to a stored procedure to read it as a table and then join this virtual table to the users table.

Ahmed Abdel Hameed (MCSD .Net)

MOSS 2007 & Request time out problem

Ahmed.AHameed — Tue, 22 Apr 2008 02:42:00 GMT

One of the famous server errors that you may get is Request time out. This error is normal for developers .The causes usually are a process that takes a very long time "lengthy operation" or a connection to a DB that is disconnected or a server side code that exexutes infinite loop. But what was very strange in MOSS 2007 that you may get a time out error from a data entry in the MOSS navigation.

Once a day I had been informed that my live site that is based on MOSS 2007 is down, then I found that the site gives a time out error. I spent about two day searching for the problem and eventually I found that the client submitts in the menu about 30 static links under the same node with the same title and URL.

The problem wasn't in the menu itself but it was in the site map data source.

We have ASP.Net SiteMapDataSource which is the data source for the share point menu. It seems that this problem is a bug in the integration between the datasource and MOSS. Because normally the sitemapdatasource throws an exception if the data provided contains duplicate URLS. so It is clear that MOSS can't handle this exception and throws time out error

Ahmed Abdel Hameed (MCSD .Net)

MOSS 2007 content query web part & RSS problem

Ahmed.AHameed — Fri, 18 Apr 2008 19:58:00 GMT

You can get RSS feed from a content query web part, just be selecting the Enable feed check box.
It looks very easy to use, but the problem here is that RSS in the content query web part is not working fine.
If you have more than one RSS in the site, you will find them getting wrong data, or you may find them all get the same data. The problem is that content query web part inherits from a data form web part, and unfortunately the data form web part caches the content in a memory cache.
The conflict occured because the content query web part use the same key for all the cached instances, so you will find all the Rss feeds shows the same data. There are a lot of work around to solve this problem but unfortunately they don't work according the following link in the MSDN:

http://msdn2.microsoft.com/en-us/library/bb608445.aspx

------------------------------------------

I customized MOSS RSS by developing a page that takes the same query and generates the RSS

public partial class FeedPage : System.Web.UI.Page

{

/// <summary>

/// write the RSS content to the page output stream.

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

protected void Page_Load(object sender, EventArgs e)

{

// get the RSS data

Microsoft.SharePoint.SPSite SiteCollection = SPContext.Current.Site;

DataTable tbl = null;

SPSecurity.RunWithElevatedPrivileges(delegate()

{

string WebPath, PageID, WebPartID;

WebPath = Request.QueryString[WebPartResources.web];

PageID = Request.QueryString[WebPartResources.page];

WebPartID = Request.QueryString[WebPartResources.wp];

// intialize the site with a token

SPWeb web = SiteCollection.OpenWeb(WebPath);

SPFile page = web.GetFile(new Guid(PageID));

ContentByQueryWebPart part = (ContentByQueryWebPart)page.GetLimitedWebPartManager(System.Web.UI.WebControls.WebParts.PersonalizationScope.User).WebParts[new Guid(WebPartID)];

CbqQueryVersionInfo query = part.BuildCbqQueryVersionInfo();

SPSiteDataQuery sQuery = new SPSiteDataQuery();

sQuery.Lists = query.VersionCrossListQueryInfo.Lists;

sQuery.Query = query.VersionCrossListQueryInfo.Query;

sQuery.RowLimit = query.VersionCrossListQueryInfo.RowLimit;

sQuery.ViewFields = query.VersionCrossListQueryInfo.ViewFields;

sQuery.Webs = query.VersionCrossListQueryInfo.Webs;

web = SiteCollection.OpenWeb(part.WebUrl);

tbl = web.GetSiteData(sQuery);

});

XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, System.Text.Encoding.UTF8);

WriteRSSPrologue(writer);

bool BodyExists=false;

//DateTime ArticleDate="";

for (int counter=0; counter<tbl.Columns.Count;counter++)

{

if (tbl.Columns[counter].ColumnName.ToLower() == WebPartResources.PublishingPageContent.ToLower())

BodyExists= true;

}

for (int itemsCounter = 0; itemsCounter < tbl.Rows.Count; itemsCounter++)

{

if (!BodyExists)

AddRSSItem(writer, tbl.Rows[itemsCounter][WebPartResources.ItemTitle].ToString(),

tbl.Rows[itemsCounter][WebPartResources.ItemLink].ToString().Split('#')[1],

string.Empty, tbl.Rows[itemsCounter][WebPartResources.ArtilceDate].ToString());

else

AddRSSItem(writer, tbl.Rows[itemsCounter][WebPartResources.ItemTitle].ToString(),

tbl.Rows[itemsCounter][WebPartResources.ItemLink].ToString().Split('#')[1],

tbl.Rows[itemsCounter][WebPartResources.PublishingPageContent].ToString(),

tbl.Rows[itemsCounter][WebPartResources.ArticleStartDate].ToString());

}

WriteRSSClosing(writer);

writer.Flush();

writer.Close();

Response.ContentEncoding = System.Text.Encoding.UTF8;

Response.ContentType = WebPartResources.textxml;

Response.Cache.SetCacheability(HttpCacheability.Public);

Response.End();

}

/// <summary>

/// Write RSS

/// </summary>

/// <param name="writer"></param>

/// <returns></returns>

public XmlTextWriter WriteRSSPrologue(XmlTextWriter writer)

{

writer.WriteStartDocument();

writer.WriteStartElement(WebPartResources.rss);

writer.WriteAttributeString(WebPartResources.version,WebPartResources.two);

writer.WriteStartElement(WebPartResources.channel);

writer.WriteElementString(WebPartResources.Title.ToLower(),WebPartResources.RSSTitle);

writer.WriteElementString(WebPartResources.link, Request.Url.AbsoluteUri);

writer.WriteElementString(WebPartResources.RSSDescription.ToLower(), string.Empty);

writer.WriteElementString(WebPartResources.ttl.ToLower(), (60).ToString());

// writer.WriteElementString("generator", "RSSviaXmlTextWriter v1.0");

return writer;

}

/// <summary>

/// Add RSS item

/// </summary>

/// <param name="writer"></param>

/// <param name="sItemTitle"></param>

/// <param name="sItemLink"></param>

/// <param name="sItemDescription"></param>

/// <returns></returns>

public XmlTextWriter AddRSSItem(XmlTextWriter writer,

string sItemTitle, string sItemLink,

string sItemDescription, string dItemArticleDate)

{

writer.WriteStartElement(WebPartResources.item);

writer.WriteElementString(WebPartResources.Title.ToLower(), sItemTitle);

writer.WriteElementString(WebPartResources.link,ConfigurationManager.AppSettings[WebPartResources.AppPath] + WebPartResources.slash + sItemLink);

writer.WriteElementString(WebPartResources.RSSDescription.ToLower(), sItemDescription);

//if (Request.QueryString[WebPartResources.web].ToLower().Contains(WebPartResources.arabicURL))

// writer.WriteElementString(WebPartResources.pubDate, dItemArticleDate.ToString(WebPartResources.PrintDateFormatArabic));

//else

writer.WriteElementString(WebPartResources.pubDate, dItemArticleDate);

writer.WriteEndElement();

return writer;

}

/// <summary>

/// Write RSS closing

/// </summary>

/// <param name="writer"></param>

/// <returns></returns>

public XmlTextWriter WriteRSSClosing(XmlTextWriter writer)

{

writer.WriteEndElement();

writer.WriteEndDocument();

return writer;

}

Ahmed Abdel Hameed (MCSD .Net)

MOSS & .Net World

Inner join between MOSS list and SQL table

MOSS 2007 & Request time out problem

MOSS 2007 content query web part & RSS problem