Ha Doan's blog @hamanhdoan-blog - Tumblr Blog

Where does SharePoint store list items?

In one of my previous posts, I've described where does SharePoint store all documents (like pdf, doc or xls files) from its document libraries. But, items from other lists are not stored at the same place. They are stored in the same SQL database, but in a different table and in a very odd fashion. NOTE: This post refers only to SharePoint 2010. NOTE 2: It needs to be noted that Microsoft does not recommend that you execute any SQL queries directly on SharePoint databases, or to be specific, it is not allowed. But, sometimes, it is the easiest and quickest way of getting data you need. List of all SharePoint lists is located in dbo.AllLists table in WSS_Content database. Next image shows query in SQL Management Studio: Name of your list can be found in tp_Title column. If you want to see all the data of that list, then you need to copy its ID from tp_ID column and go to dbo.AllUserData table. ID of that list can also be found in the url when you open list settings in SharePoint: Server_Name/Site_Name/_layouts/ListEdit.aspx?List={4B656BB9-0193-49D9-B5E4-0D76B6601198} Now, if you go to dbo.AllUserData table with following query, you can see data of you SharePoint list: Now, the columns which you get with this query have name like: nvarchar1, nvarchar2…int1, int2…datetime1…float1. If you want to know in which column is your data located, there are many ways, but I will explain three of them. First, and very stupid way is comparing data from SharePoint list with data in AllUserData table looking for the same name. For example, name of SharePoint list is usually stored in nvarchar1 column, so, if you need to find your names of all your SharePoint lists, you can look for it with following SQL query: SELECT nvarchar1 AS TitleFROM [WSS_Content].[dbo].[UserData]WHERE tp_ListId = '4B656BB9-0193-49D9-B5E4-0D76B6601198' In AllUserData table, number of columns of certain type is fixed (for example: there are 8 columns of datatimetype), but you can have more then 8 datatime columns in SharePoint list. If there are more then 8 columns ofdatatime type in SharePoint list (all 8 datatime columns ar filled), in SQL table AllUserData, this ninth data will go in the datatime1 column, but in the next row. So, now this list item will occupy two rows in SQL table, and this will be marked in tp_RowOrdinal column. Every new row will have the same tp_ID like others, but tp_RowOrdinal will be incremented by one with each new row, and each new row get 8 new datatime columns. Same procedure works with other types of data, but there does not have to be 8 columns of certain type, for example, there are 64 of nvarchar type columns. In my next post, I will show you how can you see in which column is you data stored using Powershell and C#.

----

http://sharepoint1on1.blogspot.sg/2013/03/where-does-sharepoint-store-list-items.html

#sharepoint list #sharepoint

Where does SharePoint store list items?

Have you ever wondered where does Sharepoint store all your documents you upload to document libraries? No? Well, I'll tell you anyway. Sharepoint stores all documents to a single SQL Server database, usually it is called WSS_Content, or, WSS_Content_someGuid if you have many Web Applications in your SP Farm. Yes, they are all stored to database, which is bad news for your machines performace. Why is that bad? Well, because documents (pdf, doc, xls, ...) are what we call BLOBs (Binary Large Object) and databases are not very good in handling BLOBs. Large amount of documents in Sharepoint is going to decrease performance of your server. What can you do about that? You can send all your BLOBs to a file system using EBS or RBS, but we'll talk about that some other time. Today, let's just see where are our documents located. Open SQL Management Studio and connect to Microsoft##SSEE database. Expand Database in Object explorer on left side of the screen. Expand WSS_Content database. This is what you get: Now, you can see two tables where all your document data is stored; dbo.AllDocs and dbo.AllDocStreams. Let's say that you have document in one of your libraries named "Report.xls". You can find it in dbo.AllDoc table: In that table you can find all sort of data about your document, but document data is not there, it is in another table. Here you just need to copy Id of document so you can search document data in another table, dbo.AllDocStreams, like this: And here, in column "Content" is your documents data.

---

In one of my previous posts, I've described where does SharePoint store all documents (like pdf, doc or xls files) from its document libraries. But, items from other lists are not stored at the same place. They are stored in the same SQL database, but in a different table and in a very odd fashion. NOTE: This post refers only to SharePoint 2010. NOTE 2: It needs to be noted that Microsoft does not recommend that you execute any SQL queries directly on SharePoint databases, or to be specific, it is not allowed. But, sometimes, it is the easiest and quickest way of getting data you need. List of all SharePoint lists is located in dbo.AllLists table in WSS_Content database. Next image shows query in SQL Management Studio: Name of your list can be found in tp_Title column. If you want to see all the data of that list, then you need to copy its ID from tp_ID column and go to dbo.AllUserData table. ID of that list can also be found in the url when you open list settings in SharePoint: Server_Name/Site_Name/_layouts/ListEdit.aspx?List={4B656BB9-0193-49D9-B5E4-0D76B6601198} Now, if you go to dbo.AllUserData table with following query, you can see data of you SharePoint list: Now, the columns which you get with this query have name like: nvarchar1, nvarchar2…int1, int2…datetime1…float1. If you want to know in which column is your data located, there are many ways, but I will explain three of them. First, and very stupid way is comparing data from SharePoint list with data in AllUserData table looking for the same name. For example, name of SharePoint list is usually stored in nvarchar1 column, so, if you need to find your names of all your SharePoint lists, you can look for it with following SQL query: SELECT nvarchar1 AS TitleFROM [WSS_Content].[dbo].[UserData]WHERE tp_ListId = '4B656BB9-0193-49D9-B5E4-0D76B6601198' In AllUserData table, number of columns of certain type is fixed (for example: there are 8 columns of datatimetype), but you can have more then 8 datatime columns in SharePoint list. If there are more then 8 columns ofdatatime type in SharePoint list (all 8 datatime columns ar filled), in SQL table AllUserData, this ninth data will go in the datatime1 column, but in the next row. So, now this list item will occupy two rows in SQL table, and this will be marked in tp_RowOrdinal column. Every new row will have the same tp_ID like others, but tp_RowOrdinal will be incremented by one with each new row, and each new row get 8 new datatime columns. Same procedure works with other types of data, but there does not have to be 8 columns of certain type, for example, there are 64 of nvarchar type columns.

http://sharepoint1on1.blogspot.sg/2012/10/where-are-my-documents-stored-have-you.html

http://sharepoint1on1.blogspot.sg/2013/03/where-does-sharepoint-store-list-items.html

#sharepoint

Photo View

Xamarin.Android PhotoView

PhotoView aims to help produce an easily usable implementation of a zooming Android ImageView

https://components.xamarin.com/view/photoview

#xamarin #xamarin.android

DisableLoopbackCheck

HKEY_LM\system\CCS\Services\Lanmanserver\param we will create a dword DisableStrictNameChecking

HKEY_LM\System\CCS\Control\LSA\MSV1.0

#Sharepoint

New Web Application button disabled

Run the link of Central Administration as administrator.

This helps getting you correct permissions in Central Administration

Famous SharePoint URLs & Locations

Some times you want to jump right to a specific system list, page or go to the edit mode in a SharePoint site. I wanted to list all the famous urls I used for the past years and ask you to tell me about any other ones you know to add it here in the list. Other have already contributed a bunch

(Remember for SharePoint 2013, 2016 you might want to add “15”, “16” respectively after “/_layouts/” but if you don’t SharePoint will take care of that for you) .. Also some of these are turned off on Office 365 SharePoint online.

Site collection level recycle bin:

/_layouts/15/AdminRecycleBin.aspx

Site level recycle bin (Added by Steve Stewart):

/_layouts/RecycleBin.aspx

Recreate default site sp groups (Added by Neal Bongers):

_layouts/15/permsetup.aspx

Load document tab initial (Added by Dominik Gempeler)

?InitialTabId=Ribbon.Document

Delete user from Site collection (on-premises) (Added by SamDavid):

/_layouts/15/people.aspx?MembershipGroupId=0

Display list in grid view. ‘True’ is case sensitive (Added by Antoine L.):

?ShowInGrid=True

Quick Launch settings page (Added by Ishani M.):

/_layouts/quiklnch.aspx

Navigation Settings page (Added by Abdur Raheem):

/_layouts/15/AreaNavigationSettings.aspx

Sandboxed Solution Gallery:

/_catalogs/solutions/Forms/AllItems.aspx

Workflow history hidden list:

/lists/Workflow History

Filter toolbar for Lists and libraries (Added by Asimaili):

?Filter=1

Site usage page (Added by @Dnyag):

/_layouts/usage.aspx

Site content and structure page (Added by @Dnyag):

/_layouts/sitemanager.aspx

Site settings page (Added by Aowworld):

/_layouts/settings.aspx

View all site content page (Site content) (Added by Aowworld):

/_layouts/viewlsts.aspx

Manage site collection features – CASE SENSITIVE – (Added by Vardhaman):

/_layouts/ManageFeatures.aspx?Scope=Site

Manage site features (Added by Vardhaman):

/_layouts/ManageFeatures.aspx

Get the version of the SharePoint server (Patch level) (Added by: John Liu):

/_vti_pvt/Service.cnf

Web Part Maintenance Page (Added by: Ricky):

?Contents=1

Show Page in Dialog View (Added by:Ricky):

?isdlg=1

Application page for registering SharePoint apps

/_layouts/15/appregnew.aspx

Save Site as a template

/_layouts/savetmpl.aspx

Sign in as a different user

/_layouts/closeConnection.aspx?loginasanotheruser=true

Enable SharePoint designer

/_layouts/SharePointDesignerSettings.aspx

Welcome Page (Default page settings)

/_layouts/AreaWelcomePage.aspx

Change Site Master Page

/_layouts/ChangeSiteMasterPage.aspx

Page Layouts and Site Templates

/_Layouts/AreaTemplateSettings.aspx

Master Pages library

/_catalogs/masterpage/Forms/AllItems.aspx

Quick Deploy List

Quick%20Deploy%20Items/AllItems.aspx

Open Page in Edit Mode

?ToolPaneView=2

Taxonomy Hidden List (MMS)

Lists/TaxonomyHiddenList/AllItems.aspx

User Information List:

_catalogs/users

_catalogs/users/simple.aspx

Force displaying the user profile in the site collection:

/_layouts/userdisp.aspx?id={UserID}&Force=True

Site hierarchy page (lists of sub sites) – (Added by community contributions)

/_layouts/vsubwebs.aspx

/_layouts/1033/vsubwebs.aspx

Some of these URLs are specific to a SharePoint version or to a site collection template. I will add these details later. Please comment to this post any other link you think its useful to have it shared with the community and I will added to the list with your name associated to it.

------

https://blogs.msdn.microsoft.com/how24/2013/05/23/famous-sharepoint-urls-locations/

#Sharepoint #sharepoint pages

Powellshell Script to backup WSP files

For these kind of situations, please use below mentioned Powershell command $farm = Get-SPFarm $file = $farm.Solutions.Item("solution.wsp").SolutionFile $file.SaveAs("c:\Solution.wsp")

Some times you want to jump right to a specific system list, page or go to the edit mode in a SharePoint site. I wanted to list all the famous urls I used for the past years and ask you to tell me about any other ones you know to add it here in the...

#sharepoint #sharepoint tips

Multiple upload - UrlScan error

Rejected disallowed+header request+headers translate: -

=> Remove translate: in [DenyHeaders] of UrlScan

SP Users

All Authenticated Users = c:0(.s|true

All Users (windows) = c:0!.s|windows

All Users (provider) = c:0!.s|forms:provider

http://social.technet.microsoft.com/wiki/contents/articles/13921.sharepoint-2013-claims-encoding-also-valuable-for-sharepoint-2010.aspx

New SP Web app by Powershell

$ap=New-SPAuthenticationProvider $admin=Get-SPManagedAccount 'domain\name' New-SPWebApplication -Name "Web name" -ApplicationPool "pool" -ApplicationPoolAccount $admin -Port 1111 -DatabaseName "Db_name" -AuthenticationProvider $ap

New Site Collection

New-SPSite -URL http://servername:port/ -OwnerAlias domain\user" -Name "web_name" -Template "STS#0"

Remember to use “Http://servername:port”

Backup / Restore Site Collection

Backup-SPSite -Identity http://localhost:1234 -Path c:\tmp\backup.bak Restore-SPSite http://server_name/sites/site_name -Path C:\Backup\site_name.bak -Force

#sharepoint #sharepoint administrators

Opensources for Xamain

https://github.com/pierceboggan/Moments

https://github.com/XAM-Consulting/SlideOverKit

Tools for troubleshooting

1. Log Parser Studio (IIS)

https://blogs.technet.microsoft.com/exchange/2013/06/17/log-parser-studio-2-0-is-now-available/

2. Query database by LINQ

http://www.linqpad.net/

3. ULS Viewer

https://ulsviewer.codeplex.com/

#troubleshooting #debugging

Trending Blogs

Recently Viewed Blogs

Ha Doan's blog