I am Happy

Why?
I have been contributing my technical knowledge with the sharepoint community through the medium of Microsoft technet forum on sharepoint. I am glad to share that I have corssed another Mile today by earning my THIRD MEDAL today with 2002 score.
My total posts as of now are 389 with 107 Answers.
Long way to go to earn the next one at 7500, but will get it for sure.
Just wanted to share the happiness with you and thanks for support.

users that have the surveys in the incomplete or saved status

While working on Surveys on Sharepoint, the strange question that we generally face is that, the number of surveys is different than the total surveys shown in the list.
Mostly, the reason is “incomplete surveys”. Also, we are asked many times about the list of users that have the surveys in the incomplete or saved status but unfortunately there is no direct method in the SharePoint UI which allow you to see this.

Today I came across the same question where list had 4 survey count but in the list there was not any survey response and the mystery again was incomplete surveys but it raised the same question, which users are these that have not completed the survey?

While moving through different forums, got the following SQL statement which can be run against the content database of the site collection and get the user name.

Select userdata.tp_author,
(Select tp_Title from UserInfo where UserInfo.tp_ID=userdata.tp_author and tp_siteID='SiteID') As UserName
from USERDATA
where tp_ListId like 'LISTID'
and tp_level = 255

Now, SiteID and LISTID are the fields that needs to be inserted.

1. For Site ID, Open Central administration -> application management->site administrators-> Select the site collection and Site ID is shown in the URL.
2. For List ID, Open the List with Admin account, click on settings->target Audience settings. LIST ID is the in the URL like {LISTID}.

Hope this will Help

My meeting with BLOB in Sharepoint

I have been working with Microsoft Sharepoint technologies since long and the question I always had somewhere in my mind was... how does the physical file gets stored into the Sharepoint database?Database itself runs on .MDf and .ldf files so saving a file into a database is pouring a file into file.

Last night I tried to scratch the question and was amazed to see the sky above me. BLUE..Deep Blue sky.

My first blog read was of Kyle:
http://www.kyletillman.net/blog/post/SharePoint_External_Binary_Store-PartI.aspx

She explained much and a line of it is "Out of the box WSS stores all binary content in the application’s Content database in the AllDocStreams.Content column (which is an image type". SO there is something that turns the file into a Binary and pushes it into the SQL database as Binary content.

Link from the same carried my mind packed to MSDN article:

http://msdn.microsoft.com/en-us/library/bb802976.aspx

This one is awseome and tells clearly "BLOB storage routed the binary data stream associated with a SharePoint file to the Microsoft SQL Server content database, which it shared with the site's structured data. Under that scenario, when you invoked a Save command on the SharePoint file, a parser in the Save path recognized the Save command and promoted a parcel of metadata out of the file stream. Then the metadata, along with the BLOB associated with the file, was stored in the SQL Server content database".

Diagram:


Image above is from Microsoft MSDN site: http://i.msdn.microsoft.com/Bb862195.be761d13-4bb4-44d2-aece-b8d99d9d7536(en-us,office.12).gif
This is great working model of Sharepoint storage and further I checked that developers may develop their own applications and use SQL BlOb storage capabilities.

Now, the question came in when I further dig into the rabbit hole and found that storing Large Blobs being unstructured are not best suited for SQL Server and may reduce the performace and cause fragmentation. There are many discussion articles and forums talking about the same and Microsoft's work on doing even better on BLOB Storage in SQL Server 2005 and much better in SQL Server 2008.

Finally the research article from MS put more focus into what the architecture asks for.
http://research.microsoft.com/pubs/64525/tr-2006-45.pdf

Most of such articles advocates storing data larger than 256KB - 1 MB on filesystem than SQL server. Default MAx upload size for a single file in MOSS is 50 MB which is Much Much larger than the 1 MB, so further, I found that it is the
chunk size whichdetermines the amount of data that the client retrieves in one go when opening a document. For example, if a client tries to open a document of 50MB and the chunk size is 10MB, the document is divided and retrieved in 5 chunks. Each chunk will be loaded into the memory of both the WFE handling the request and SQL Server. The default chunk size is 5MB and you can adjust the chunk size by issuing the following stsadm command:
Stsadm.exe -o setproperty -pn large-file-chunk-size -pv
(courtsey: http://www.lcbridge.nl/vision/2008/largefiles.htm)
but it seems that the WEBDAV doesnt use the chunk size and hence try to load the complete 50 MB file into the memory and hence causes failure with Large files sometimes.
WIth WSS SP1, Microsoft extended the Stroage and exposed the API for external Blob storage where the Large files may get stored on the filesystem and the matadata along with Binary ID would get into the Database.
THis would be just very Opaque to the Application as this would happen under the Storage Access Stack and THe sharepoint object model.
This being Grey area was not much worked uponin this Version of Sharepoint but with SHarepoint 2010, Microsoft has done great work with BLOB storage and the functionality is into the product.

I am going to try BLOB in Sharepoint 2010 and will share in my next post...

Thanks for reading