SPCQOptimizer Wiki & Documentation Rss Feed

Updated Wiki: Home

bvanburen — Fri, 27 Jun 2008 16:52:40 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors or bug in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved. User can either use procedure to fix content database or review traces and determine another course of action.

Content Query Background - How it works and SQL Database Tables information
Forum and Blogs KBs that discuss the Content Query issue and limitations.

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query against just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists your query for a specific content type then your query could:

Not display data from various lists or limit the number of lists that will be queried
Display the wrong data from lists,
Cause a security breach,
Waste your time trying to figure out what is wrong.
Or at minimum run longer, possibly time-out and use more resources than necessary on the SQL Server.

This bug is confirmed by MS in KB 946484.

The error appeared for me as a filtering error. Although everything looked identical in the lists configurations, the same filter would work everywhere except for one or two lists. If the filer was applied then no items appeared from the list, turn it off and the list data appeared.
Turns out the fields I was filtering for the content type were mapped to different SQL Table columns in the "Bad" list than the lists that were "working". Running this procedure corrected the columns and the information appeared correctly. Again the filter worked if I pointed to the single list but broke when I queried the Site Collection.

Although the SharePoint guides stress planning , that is not much help when working with migrated sites and/or lists that were pure a year ago when you first deployed but now have been updated by users and you are adding the new functionality in the way of ContentTypes and the Content Query.

Of course the worst part is that there is nothing in SharePoint to show you what is wrong.

Project Solution
This project consists of a stored procedure that you will need to install on the SQL Server where your content database is installed. It is designed to be complied in a separate work database on the server so it is not installed in the SharePoint content database. When run it will create some work tables and scan the database for a particular content type and display any errors that if finds.

At minimum you will need

read write access to the Work database
read access to the content database
write access to the content database if need to correct the database tables.

There is an option to print the SQL update commands and/or run the update directly which will correct the database for content types that are not constant. The procedure will rank the content type across lists to determine which list represents the best candidate to use as a master template list for which all other lists will be updated. You can supply a master list template that you wish to use instead. Note that I mean a real list and not a "SharePoint List Template"

Note that any fields which are not part of the master list that appear in the other lists to be updated will simply be moved to other columns in the AllUsersData.

The update procedure performs two tasks

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
For each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
After the update, you should reset IIS and the Timer service on the all servers in the farm

Updated Wiki: Home

bvanburen — Fri, 27 Jun 2008 16:52:07 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors or bug in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved. User can either use procedure to fix content database or review traces and determine another course of action.

Content Query Background - How it works and SQL Database Tables information
Forum and Blogs that discuss the Content Query issue and limitations.

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query against just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists your query for a specific content type then your query could:

Not display data from various lists or limit the number of lists that will be queried
Display the wrong data from lists,
Cause a security breach,
Waste your time trying to figure out what is wrong.
Or at minimum run longer, possibly time-out and use more resources than necessary on the SQL Server.

At minimum you will need

read write access to the Work database
read access to the content database
write access to the content database if need to correct the database tables.

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
For each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
After the update, you should reset IIS and the Timer service on the all servers in the farm

Updated Wiki: Forum and Blogs

bvanburen — Fri, 27 Jun 2008 16:50:51 GMT

This project is the result of research into a content query problem described here
http://znetsys.spaces.live.com/blog/cns!ED65D09C41E55E06!147.entry and here
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2607842&SiteID=1
http://www.sharepointblogs.com/smc750/archive/2007/07/24/spsitedataquery-limited-to-10-document-libraries-or-lists.aspx
MS Kb 946484 that describes the problem
http://support.microsoft.com/kb/946484

UPDATED WIKI: Home

bvanburen — Sun, 13 Jan 2008 12:25:17 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors or bug in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved. User can either use procedure to fix content database or review traces and determine another course of action.

Content Query Background - How it works and SQL Database Tables information
Forum and Blogs that discuss the Content Query issue and limitations.

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query against just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists your query for a specific content type then your query could:

Not display data from various lists or limit the number of lists that will be queried
Display the wrong data from lists,
Cause a security breach,
Waste your time trying to figure out what is wrong.
Or at minimum run longer, possibly time-out and use more resources than necessary on the SQL Server.

The error appeared for me as a filtering error. Although everything looked identical in the lists configurations, the same filter would work everywhere except for one or two lists. If the filer was applied then no items appeared from the list, turn it off and the list data appeared.
Turns out the fields I was filtering for the content type were mapped to different SQL Table columns in the "Bad" list than the lists that were "working". Running this procedure corrected the columns and the information appeared correctly. Again the filter worked if I pointed to the single list but broke when I queried the Site Collection.

Although the SharePoint guides stress planning , that is not much help when working with migrated sites and/or lists that were pure a year ago when you first deployed but now have been updated by users and you are adding the new functionality in the way of ContentTypes and the Content Query.

Of course the worst part is that there is nothing in SharePoint to show you what is wrong.

Project Solution
This project consists of a stored procedure that you will need to install on the SQL Server where your content database is installed. It is designed to be complied in a separate work database on the server so it is not installed in the SharePoint content database. When run it will create some work tables and scan the database for a particular content type and display any errors that if finds.

At minimum you will need

read write access to the Work database
read access to the content database
write access to the content database if need to correct the database tables.

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
For each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
After the update, you should reset IIS and the Timer service on the all servers in the farm

UPDATED WIKI: Home

bvanburen — Thu, 10 Jan 2008 16:52:48 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved.

Content Query Background - How it works and SQL Database Tables information
Forum and Blogs that discuss the Content Query issue and limitations.

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query against just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists your query for a specific content type then your query could:

Not display data from various lists or limit the number of lists that will be queried
Display the wrong data from lists,
Cause a security breach,
Waste your time trying to figure out what is wrong.
Or at minimum run longer, possibly time-out and use more resources than necessary on the SQL Server.

The error appeared for me as a filtering error. Although everything looked identical in the lists configurations, the same filter would work everywhere except for one or two lists. If the filer was applied then no items appeared from the list, turn it off and the list data appeared.
Turns out the fields I was filtering for the content type were mapped to different SQL Table columns in the "Bad" list than the lists that were "working". Running this procedure corrected the columns and the information appeared. Again the filter worked if I pointed to the single list but broke when I queried the Site Collection.

Although the SharePoint guides stress planning , that is not much help when working with migrated sites and/or lists that were pure a year ago when you first deployed but now have been updated by users and you are adding the new functionality in the way of ContentTypes and the Content Query.

Of course the worst part is that there is nothing in SharePoint to show you what is wrong.

Project Solution
This project consists of a stored procedure that you will need to install on the SQL Server where your content database is installed. It is designed to be complied in a separate work database on the server so it is not installed in the SharePoint content database. When run it will create some work tables and scan the database for a particular content type and display any errors that if finds.

At minimum you will need

read write access to the Work database
read access to the content database
write access to the content database if need to correct the database tables.

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
For each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
After the update, you should reset IIS and the Timer service on the all servers in the farm

UPDATED WIKI: Home

bvanburen — Thu, 10 Jan 2008 16:45:32 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved.

Content Query Background - How it works and SQL Database Tables information
Forum and Blogs that discuss the Content Query issue and limitations.

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query aginst just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists your query for a specific content type then your query could:

Not display data from various lists or limit the number of lists that will be queried
Display the wrong data from lists,
Cause a security breach ,
Waste your time trying to figure out what is wrong.
or at minimum run longer, possiablily time and use more resources than necessary on the SQL Server.

At minimum you will need

read write access to the Work database
read access to the content database
write access to the content database if need to correct the database tables.

There is an option to print the SQL update commands and/or run the update directly which will correct the database for content types that are not constant. The procedure will rank the content type across lists to determine which list represents the best candidate to use as a master template list for which all other lists will be updated. You can supply a master list template that you wish to use instead. Note that I mean a real list and not a "Sharepoint List Template"

Note that any fields which are not part of the master list that appear in the other lists to be updated will simply be moved to other columns in the AllUsersData.

The update procedure performs two tasks

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
For each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
After the updae, you should te reset IIS and the Timer service on the all servers in the farm

UPDATED WIKI: Home

bvanburen — Thu, 10 Jan 2008 16:44:51 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved.

Content Query Background - How it works and SQL Database Tables information
Forum and Blogs that discuss the Content Query issue and limitations.

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query aginst just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists your query for a specific content type then your query could:

Not display data from various lists or limit the number of lists that will be queried
Display the wrong data from lists,
Cause a security breach ,
Waste your time trying to figure out what is wrong.
or at minimum run longer, possiablily time and use more resources than necessary on the SQL Server.

At minimum you will need

read write access to the Work database
read access to the content database
write access to the content database if need to correct the database tables.

There is an option to print the SQL update commands and/or run the update directly which will correct the database for content types that are not constant. The procedure will rank the content type across lists to determine which list represents the best candidate to use as a master template list for which all other lists will be updated. You can supply a master list template that you wish to use instead. Note that I mean a real list and not a "Sharepoint List Template"

Note that any fields which are not part of the master list that appear in the other lists to be updated will simply be moved to other columns in the AllUsersData.

The update procedure performs two tasks

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
for each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
You should after the update reset IIS and the Timer service on the all servers in the farm.

UPDATED WIKI: Forum and Blogs

bvanburen — Thu, 10 Jan 2008 16:44:06 GMT

UPDATED WIKI: Forum and Blogs

bvanburen — Thu, 10 Jan 2008 16:43:36 GMT

UPDATED WIKI: Home

bvanburen — Thu, 10 Jan 2008 16:43:21 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved.

Content Query Background - How it works and SQL Database Tables information
Forum and Blogs that discuss the Content Query issue and limitations.

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query aginst just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists your query for a specific content type then your query could:

Not display data from various lists or limit the number of lists that will be queried
Display the wrong data from lists,
Cause a security breach ,
Waste your time trying to figure out what is wrong.
or at minimum run longer, possiablily time and use more resources than necessary on the SQL Server.

At minimum you will need

read write access to the Work database
read access to the content database
write access to the content database if need to correct the database tables.

There is an option to print the SQL update commands and/or run the update directly which will correct the database for content types that are not constant. The procedure will rank the content type across lists to determine which list represents the best candidate to use as a master template list for which all other lists will be updated. You can supply a master list template that you wish to use instead. Note that I mean a real list and not a "Sharepoint List Template"

Note that any fields which are not part of the master list that appear in the other lists to be updated will simply be moved to other columns in the AllUsersData.

The update procedure performs two tasks

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
for each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
You should after the update reset IIS and the Timer service on the all servers in the farm.

UPDATED WIKI: Home

bvanburen — Thu, 10 Jan 2008 16:42:31 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved.

Content Query Background - How it works and SQL Database Tables information
Forum and Bloggs that discuss the Content Query issue.

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query aginst just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists your query for a specific content type then your query could:

Not display data from various lists or limit the number of lists that will be queried
Display the wrong data from lists,
Cause a security breach ,
Waste your time trying to figure out what is wrong.
or at minimum run longer, possiablily time and use more resources than necessary on the SQL Server.

At minimum you will need

read write access to the Work database
read access to the content database
write access to the content database if need to correct the database tables.

There is an option to print the SQL update commands and/or run the update directly which will correct the database for content types that are not constant. The procedure will rank the content type across lists to determine which list represents the best candidate to use as a master template list for which all other lists will be updated. You can supply a master list template that you wish to use instead. Note that I mean a real list and not a "Sharepoint List Template"

Note that any fields which are not part of the master list that appear in the other lists to be updated will simply be moved to other columns in the AllUsersData.

The update procedure performs two tasks

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
for each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
You should after the update reset IIS and the Timer service on the all servers in the farm.

UPDATED WIKI: Home

bvanburen — Thu, 10 Jan 2008 16:40:36 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved.

Content Query Background - How it works and SQL Database Tables information

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query aginst just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists your query for a specific content type then your query could:

Not display data from various lists or limit the number of lists that will be queried
Display the wrong data from lists,
Cause a security breach ,
Waste your time trying to figure out what is wrong.
or at minimum run longer, possiablily time and use more resources than necessary on the SQL Server.

At minimum you will need

read write access to the Work database
read access to the content database
write access to the content database if need to correct the database tables.

There is an option to print the SQL update commands and/or run the update directly which will correct the database for content types that are not constant. The procedure will rank the content type across lists to determine which list represents the best candidate to use as a master template list for which all other lists will be updated. You can supply a master list template that you wish to use instead. Note that I mean a real list and not a "Sharepoint List Template"

Note that any fields which are not part of the master list that appear in the other lists to be updated will simply be moved to other columns in the AllUsersData.

The update procedure performs two tasks

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
for each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
You should after the update reset IIS and the Timer service on the all servers in the farm.

UPDATED WIKI: Home

bvanburen — Thu, 10 Jan 2008 16:38:18 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved.

Content Query Background - How it works and SQL Database Tables information

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query aginst just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists your query for a specific content type then your query could:

Not display data from various lists or limit the number of lists that will be queried
Display the wrong data from lists,
Cause a security breach ,
Waste your time trying to figure out what is wrong.
or at minimum run longer, possiablily time and use more resources than necessary on the SQL Server.

The 10 list limit Blogged in the link below is a manifistation of disordered columns.

The error appeared for me as a filtering error. Although everything looked identical in the lists configurations, the same filter would work everywhere except for one or two lists. If the filer was applied. no items appeared from the list, turn it off and the list data appeared.
Turns out the fields I was filtering for the content type were mapped to different SQL Table columns in the "Bad" list than the lists that were "working". Running this procedure corrected the columns and the information appeared. Again the filter worked if I pointed to the single list but broke when I queried the Site Collection.

Although the SharePoint guides stress planning , that is not much help when working with migrated sites and/or lists that were pure a year ago when you first deployed but now have been updated by users and you are adding the new functionality in the way of ContentTypes and the Content Query.

Of course the worst part is that there is nothing in SharePoint to show you what is wrong.

Project Solution
This project consists of a stored procedure that you will need to install on the SQL Server where your content database is installed. It is designed to be complied in a separate work database on the server so it is not installed in the SharePoint content database. When run it will create some work tables and scan the database for a particular content type and display any errors that if finds. At minimum you will need read write access to the Work database and read access to the content database and write if ou need to correct the database.

There is an option to print the SQL update commands and/or run the update directly which will correct the database for content types that are not constant. The procedure will rank the content type across lists to determine which list represents the best candidate to use as a master template list for which all other lists will be updated. You can supply a master list template that you wish to use instead. Note that I mean a real list and not a "Sharepoint List Template"

Note that any fields which are not part of the master list that appear in the other lists to be updated will simply be moved to other columns in the AllUsersData.

The update procedure performs two tasks

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
for each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
You should after the update reset IIS and the Timer service on the all servers in the farm.

UPDATED WIKI: Home

bvanburen — Thu, 10 Jan 2008 16:35:25 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved.

Content Query Background - How it works and SQL Database Tables information

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query aginst just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists your query for a specific content type then your query could:

Not display data from various lists or limit the number of lists that will be queried
Display the wrong data from lists,
Cause a security breach ,
Waste your time trying to figure out what is wrong.
or at minimum run longer, possiablily time and use more resources than necessary on the SQL Server.

The 10 list limit Blogged in the link below is a manifistation of disordered columns.

The error appeared for me as a filtering error. Although everything looked identical in the lists configurations, the same filter would work everywhere except for one or two lists. If the filer was applied. no items appeared from the list, turn it off and the list data appeared.
Turns out the fields I was filtering in the content type were in different columns than the othe rlists that were "working". Running this procedure corrected the columns and the information appeared.

Although the SharePoint guides stress planning , that is not much help when working with migrated sites and/or lists that were pure a year ago when you first deployed but now have been updated by users and you are adding the new functionality in the way of ContentTypes and the Content Query.

Of course the worst part is that there is nothing in SharePoint to show you what is wrong.

Project Solution
This project consists of a stored procedure that you will need to install on the SQL Server where your content database is installed. It is designed to be complied in a separate work database on the server so it is not installed in the SharePoint content database. When run it will create some work tables and scan the database for a particular content type and display any errors that if finds. At minimum you will need read write access to the Work database and read access to the content database and write if ou need to correct the database.

There is an option to print the SQL update commands and/or run the update directly which will correct the database for content types that are not constant. The procedure will rank the content type across lists to determine which list represents the best candidate to use as a master template list for which all other lists will be updated. You can supply a master list template that you wish to use instead. Note that I mean a real list and not a "Sharepoint List Template"

Note that any fields which are not part of the master list that appear in the other lists to be updated will simply be moved to other columns in the AllUsersData.

The update procedure performs two tasks

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
for each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
You should after the update reset IIS and the Timer service on the all servers in the farm.

UPDATED WIKI: Home

bvanburen — Thu, 10 Jan 2008 16:34:12 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved.

Content Query Background - How it works and SQL Database Tables information

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query aginst just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists your query for a specific content type then your query could:

Not display data from various lists or limit the number of lists that will be queried
Display the wrong data from lists,
Cause a security breach ,
Waste your time trying to figure out what is wrong.
or at minimum run longer, possiablily time and use more resources than necessary on the SQL Server.

The 10 list limit Blogged in the link below is a manifistation of disordered columns.

The error appeared for me as a filtering error. Although everything looked identical in the lists configurations, the same filter worked everywhere except for one or two lists. If the filer was applied. no items appeared from the list. Turns out the fields I was filtering in the content type were in different columns than the othe rlists that were "working". Running this procedure corrected the columns and the information appeared.

Although the SharePoint guides stress planning , that is not much help when working with migrated sites and/or lists that were pure a year ago when you first deployed but now have been updated by users and you are adding the new functionality in the way of ContentTypes and the Content Query.

Of course the worst part is that there is nothing in SharePoint to show you what is wrong.

Project Solution
This project consists of a stored procedure that you will need to install on the SQL Server where your content database is installed. It is designed to be complied in a separate work database on the server so it is not installed in the SharePoint content database. When run it will create some work tables and scan the database for a particular content type and display any errors that if finds. At minimum you will need read write access to the Work database and read access to the content database and write if ou need to correct the database.

There is an option to print the SQL update commands and/or run the update directly which will correct the database for content types that are not constant. The procedure will rank the content type across lists to determine which list represents the best candidate to use as a master template list for which all other lists will be updated. You can supply a master list template that you wish to use instead. Note that I mean a real list and not a "Sharepoint List Template"

Note that any fields which are not part of the master list that appear in the other lists to be updated will simply be moved to other columns in the AllUsersData.

The update procedure performs two tasks

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
for each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
You should after the update reset IIS and the Timer service on the all servers in the farm.

UPDATED WIKI: Home

bvanburen — Thu, 10 Jan 2008 16:33:46 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved.

Content Query Background - How it works and SQL Database Tables information

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query aginst just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists your query for a specific content type then your query could:

Not display data from various lists or limit the number of lists that will be queried
Display the wrong data from lists,
Cause a security breach ,
Waste your time trying to figure out what is wrong.
or at minimum run longer, possiablily time and use more resources that necessary on th SQL Server.

The 10 list limit Blogged in the link below is a manifistation of disordered columns.

The error appeared for me as a filtering error. Although everything looked identical in the lists configurations, the same filter worked everywhere except for one or two lists. If the filer was applied. no items appeared from the list. Turns out the fields I was filtering in the content type were in different columns than the othe rlists that were "working". Running this procedure corrected the columns and the information appeared.

Although the SharePoint guides stress planning , that is not much help when working with migrated sites and/or lists that were pure a year ago when you first deployed but now have been updated by users and you are adding the new functionality in the way of ContentTypes and the Content Query.

Of course the worst part is that there is nothing in SharePoint to show you what is wrong.

Project Solution
This project consists of a stored procedure that you will need to install on the SQL Server where your content database is installed. It is designed to be complied in a separate work database on the server so it is not installed in the SharePoint content database. When run it will create some work tables and scan the database for a particular content type and display any errors that if finds. At minimum you will need read write access to the Work database and read access to the content database and write if ou need to correct the database.

There is an option to print the SQL update commands and/or run the update directly which will correct the database for content types that are not constant. The procedure will rank the content type across lists to determine which list represents the best candidate to use as a master template list for which all other lists will be updated. You can supply a master list template that you wish to use instead. Note that I mean a real list and not a "Sharepoint List Template"

Note that any fields which are not part of the master list that appear in the other lists to be updated will simply be moved to other columns in the AllUsersData.

The update procedure performs two tasks

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
for each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
You should after the update reset IIS and the Timer service on the all servers in the farm.

UPDATED WIKI: Home

bvanburen — Thu, 10 Jan 2008 16:32:45 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved.

Content Query Background - How it works and SQL Database Tables information

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query aginst just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists you query for a specific content type then your query could:

Not display data from various lists.
Display the wrong data from lists,
Cause a security breach ,
Waste your time trying to figure out what is wrong.
or at minimum run longer, possiablily time and use more resources that necessary on th SQL Server.

The 10 list limit Blogged in the link below is a manifistation of disordered columns.

The error appeared for me as a filtering error. Although everything looked identical in the lists configurations, the same filter worked everywhere except for one or two lists. If the filer was applied. no items appeared from the list. Turns out the fields I was filtering in the content type were in different columns than the othe rlists that were "working". Running this procedure corrected the columns and the information appeared.

Although the SharePoint guides stress planning , that is not much help when working with migrated sites and/or lists that were pure a year ago when you first deployed but now have been updated by users and you are adding the new functionality in the way of ContentTypes and the Content Query.

Of course the worst part is that there is nothing in SharePoint to show you what is wrong.

Project Solution
This project consists of a stored procedure that you will need to install on the SQL Server where your content database is installed. It is designed to be complied in a separate work database on the server so it is not installed in the SharePoint content database. When run it will create some work tables and scan the database for a particular content type and display any errors that if finds. At minimum you will need read write access to the Work database and read access to the content database and write if ou need to correct the database.

There is an option to print the SQL update commands and/or run the update directly which will correct the database for content types that are not constant. The procedure will rank the content type across lists to determine which list represents the best candidate to use as a master template list for which all other lists will be updated. You can supply a master list template that you wish to use instead. Note that I mean a real list and not a "Sharepoint List Template"

Note that any fields which are not part of the master list that appear in the other lists to be updated will simply be moved to other columns in the AllUsersData.

The update procedure performs two tasks

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
for each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
You should after the update reset IIS and the Timer service on the all servers in the farm.

UPDATED WIKI: Home

bvanburen — Thu, 10 Jan 2008 16:32:28 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved.

Content Query Background - How it works and SQL Database Tables information

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query aginst just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists you query for a specific content type then your query could:

Not display data from various lists.
Display the wrong data from lists,
Cause a security breach ,
Waste your time trying to figure out what is wrong.
or at minimum run longer, possiablily time and use more resources that necessary on th SQL Server.

The 10 list limit Blogged in the link above is a manifistation of disordered columns.

The error appeared for me as a filtering error. Although everything looked identical in the lists configurations, the same filter worked everywhere except for one or two lists. If the filer was applied. no items appeared from the list. Turns out the fields I was filtering in the content type were in different columns than the othe rlists that were "working". Running this procedure corrected the columns and the information appeared.

Although the SharePoint guides stress planning , that is not much help when working with migrated sites and/or lists that were pure a year ago when you first deployed but now have been updated by users and you are adding the new functionality in the way of ContentTypes and the Content Query.

Of course the worst part is that there is nothing in SharePoint to show you what is wrong.

Project Solution
This project consists of a stored procedure that you will need to install on the SQL Server where your content database is installed. It is designed to be complied in a separate work database on the server so it is not installed in the SharePoint content database. When run it will create some work tables and scan the database for a particular content type and display any errors that if finds. At minimum you will need read write access to the Work database and read access to the content database and write if ou need to correct the database.

There is an option to print the SQL update commands and/or run the update directly which will correct the database for content types that are not constant. The procedure will rank the content type across lists to determine which list represents the best candidate to use as a master template list for which all other lists will be updated. You can supply a master list template that you wish to use instead. Note that I mean a real list and not a "Sharepoint List Template"

Note that any fields which are not part of the master list that appear in the other lists to be updated will simply be moved to other columns in the AllUsersData.

The update procedure performs two tasks

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
for each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
You should after the update reset IIS and the Timer service on the all servers in the farm.

UPDATED WIKI: Home

bvanburen — Thu, 10 Jan 2008 16:30:50 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved.

Content Query Background - How it works and SQL Database Tables information

The Problem
This is all fine for a single list and how the data is arrange does not matter, but for the cross site content query, order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists you want to query for a specific type then your query could:

Not display data from various lists.
Display the wrong data from lists,
Cause a security breach ,
Waste your time trying to figure out what is wrong.
or at minimum run longer, possiablily time and use more resources that necessary on th SQL Server.

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
for each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
You should after the update reset IIS and the Timer service on the all servers in the farm.

UPDATED WIKI: Home

bvanburen — Thu, 10 Jan 2008 16:30:21 GMT

Project Description

To discover, analyze and correct optimization issues and / or query errors in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved.

Content Query Background - How it works and SQL Database Tables information

This project is the result of research into a content query problem described here
http://znetsys.spaces.live.com/blog/cns!ED65D09C41E55E06!147.entry and here
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2607842&SiteID=1
http://www.sharepointblogs.com/smc750/archive/2007/07/24/spsitedataquery-limited-to-10-document-libraries-or-lists.aspx

The Problem
This is all fine for a single list and how the data is arrange does not matter, but for the cross site content query, order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists you want to query for a specific type then your query could:

Not display data from various lists.
Display the wrong data from lists,
Cause a security breach ,
Waste your time trying to figure out what is wrong.
or at minimum run longer, possiablily time and use more resources that necessary on th SQL Server.

Determines which lists are out of sync with the master list.
Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
for each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
You should after the update reset IIS and the Timer service on the all servers in the farm.

Good luck