I am waiting for long time to own a new domain , and now it’s happened. Just visit my new blog here, http://ktskumar.com/blog . And I want to share with you all the things I learned, mainly Sharepoint, Silverlight, JQuery, Bing , so always tune me on my new Url  to keep updated with my blog.

I noticed in MSDN forums, many of them are struggling with creation of sub-sites using webservice in sharepoint.

I take a look on that, Because they are all try to use CreatSite method in Admin webservice. But that will help only on creating SiteCollections.

Then how we can create a susite? It’s very easy if you are look in to Meetings webservice. This will have a CreateWorkspace method.

URL for Meetings webservice: ~site/_vti_bin/meetings.asmx

Syntax :
CreateWorkspace([Site Title],[Site Template],[LCID],[TimeZone Information]);

Sample Code:
MeetingWebService.Meetings WMService = new MeetingWebService.Meetings(); MeetingWebService.TimeZoneInf tz = new MeetingWebService.TimeZoneInf(); XmlNode xnode= WMService.CreateWorkspace(“sitename”, “STS#0″, 1033, tz);

Read More….

Cheers guys

Where are the Documents or files we uploaded or attached stored in sharepoint? Many of them know, those files are stored under SharePoint Content Database. But on which database table, on which format?

For that, i did a quick research on that, and here i give those results,

• The AllDocStreams table in SharePoint Content Database stores the Document Contents.
• The Contents of the document are stored in Content Column in the AllDocStream table.
• The Content column is the Image Datatype, (stores in the format of Binary).

I provide a simple SQL Query to retrieve the Document List Name,File Name, URL, and the Content (Binary Format)

SELECT AllLists.tp_Title AS ‘List Name’,
AllDocs.LeafName AS ‘File Name’,
AllDocs.DirName AS ‘URL’,
AllDocStreams.Content AS ‘Document Contnt (Binary)’
FROM AllDocs
JOIN AllDocStreams
ON
AllDocs.Id=AllDocStreams.Id
JOIN AllLists
ON
AllLists.tp_id = AllDocs.ListId

By Sooner, I’ll come up with other interesting Sql Query Tips.

Read more…

Now, we are going to see how we can download all documents(incluing sub folders) from SharePoint Library.

First of all,we have get all documents from SharePoint Library. The following code help us to get all the documents including Subfolders.
//Get the Document Library and its view
SPList list = web.Lists["Test Document"];
SPView view = list.Views["All Documents"];
//Query all documents in Library including files under subfolders
SPQuery squery = new SPQuery(view);
squery.ViewAttributes = “Scope=\”Recursive\”";
SPListItemCollection items = list.GetItems(squery);

Now we have to write a code for downloaing documents,
//Read all documents and write those files to Local System
foreach (SPListItem item in items)
{
byte[] binfile = item.File.OpenBinary();
FileStream fstream = new FileStream(“F:\\” + item.File.Name, FileMode.Create, FileAccess.ReadWrite);
fstream.Write(binfile, 0, binfile.Length);
fstream.Close();
}

By using the above code snippets, we can download all document from SharePoint Library.
Read more….

In my WSS – User Information List post, I’ll list out the fields available under hidden “User Information List”. Now i’ll like to share the details of URL for that “User Information List”.

By using follwoing code, we can get the Users list url,

SPList userList = SPContext.Current.Web.SiteUserInfoList;
string url = userList.Forms[PAGETYPE.PAGE_DISPLAYFORM].Url;

the above code will return like the following url http://mysite/_layouts/userdisp.aspx. But, this url is not the exact url.

The actual url for “User Information List” available under _Catalogs folder.
FYI, I list out the url’s here,

• http://mysite/_catalogs/users/detail.aspx
              Shows the detail view of only Users in the list.
• http://mysite/_catalogs/users/simple.aspx
              Shows the simple view of only Users in the List
• http://mysite/_catalogs/users/allgroups.aspx
              Shows all the groups without users available in Site.

In my next post, i’ll explain about the default folders available in this “User Information List”.

Read more….

Now, we are going to retrive the file details from AllDocs Database. Which has the informations about the files stored in SharePoint List or Library.

– Returns all document from all lists availabe in WebApplication
SELECT AllDocs.Leafname AS FileName’, 
                 AllDOcs.Dirname AS ‘Folder Path’, 
                 AllLists.tp_Title AS ‘List Title’,
                 Webs.Title AS ‘Web Title’
FROM AllDocs
JOIN AllLists
ON
AllLists.tp_Id=AllDocs.ListId
JOIN Webs
ON
Webs.Id=AllLists.tp_WebId
ORDER BY webs.title

If you need the file informations about particular document type. Use the Extension column to check the document type.

For Ex., The following Query returns only master pages on all WebSites,

– Returns master pages in WebApplication for all WebSites
SELECT AllDocs.Leafname AS FileName’, 
                 AllDocs.Dirname AS ‘Folder Path’, 
                 AllLists.tp_Title AS ‘List Title’,
                 Webs.Title AS ‘Web Title’
FROM AllDocs
JOIN AllLists
ON
AllLists.tp_Id=AllDocs.ListId
JOIN Webs
ON
Webs.Id=AllLists.tp_WebId
WHERE Extension=’master’
ORDER BY Webs.Title

Like the above query, we can get all type of documents.

Read more….

Today, we are going to see the query for returning Webs and Site Collections available in the Database (WebApplication).

–Returns Total Number of Site Collections in WebApplication
select  count(*) as ‘Total Site Collection’ from sites

–Returns Root Site Title for each Site Collection available in WebApplication
 select Title as ‘Root Web Title’, Sites.RootWebId, Sites.Id as ‘Site Collection ID’ from webs inner join Sites on Webs.Id = Sites.RootWebId

–Returns Total Web Sites in WebApplication
select count(*) from Webs

–Returns WebSite Title and Site Id
select Title as ‘Site title’,FullUrl, SiteId as ‘Site Collection Id’ from Webs order by SiteId

–Returns Total number of Web Sites under each SiteCollection
select SiteId, count(*) as ‘Total Sub Sites’ from Webs inner join Sites on Sites.Id = Webs.SiteId group by SiteId

Webs Database stores the informations on Web Sites.
Sites Database stores the informations on Site Collections.

Read more….

Microsoft released the Silverlight 3 Beta and Microsoft Expression Blend 3 Preview at Mix09.

Just browse the follwoing links to know more information about the releases.
What’s New in Expression Blend 3 Preview
Silverlight 3 Beta
* Silverlight 3 is now available to developers for testing purposes

Download Links
Silverlight 3 Beta – Windows Developer Runtime
Silverlight 3 Beta – Mac Developer Runtime

Expression Blend 3 Preview installer

Today onwards on daily basis, I decided to post the Sql Query to access the SharePoint Databases. To know more about the databases in Sharepoint , check my SharePoint Databases Post.
To retrieve all lists from the database, use the following query,

select AllLists.tp_Title as ‘List Title’,webs.Title as ‘Web Title’
from dbo.AllLists as AllLists
inner join dbo.Webs as webs
on webs.Id=AllLists.tp_WebId
order by webs.Title

dbo.AllLists Table contains all the lists available in WebApplication Level *

dbo.Webs Table Contains all the webs or sites available in WebApplication Level  *

Sql Query Result - Returns All Lists with Site

*If the Database configured for SiteCollection, then the table only contains the datas for SiteCollection Level.

Read more…

I’m just researching on Excel Services.I found some posts, are usefull to me, so i like to share with you all.
MSDN Blog – Excel Services
Cum Grano Salis Blog
MSDN Library
Microsoft Office Online – Excel Services
Click on the above links to navigate to those links.

Read more…