Windows 2013 VHD Creation for Sharepoint 2013 VHD

1. Download Windows 2012 Server VHD file from below link 180 days trial Version

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

2. Download VM ware or Oracle VM virtual Box

https://www.virtualbox.org/
or
https://my.vmware.com/web/vmware/free

3. download SQL Server 2012  from
http://www.microsoft.com/en-in/download/details.aspx?id=29066

4. Microsoft SharePoint 2013 Download

http://technet.microsoft.com/en-in/evalcenter/hh973397.aspx

5. VS Studio 2012 Download


 6. Active Directory Enabling if you are installing SharePoint 2013 Server

Install Active Directory Domain Services (ADDS) Role

Launch Server Manager
Click Add Roles and Features


Click Next

Select Role-based or feature-based installation and press Next

Since I am installing my forest locally I will select “Select a server from the server pool”
Select the server, in my case its DC2012
Press Next

Check off Active Directory Domain Services


When you check off Active Directory Domain Services a dialog window will pop up
Press Add Features

Press Next

Press Next on the features section, you do not need to check anything off here.
Some things will be checked off because you selected Active Directory Domain Services in the last section.

Read the important things to note about this installation.
Press Next

Press Install

Installation will complete. Press Close

Configure ADDS Forest and Domain

Next time your launch Server Manager. You will see a notification icon. Press the exclamation point. Select Promote this server to a domain controller


Warning: Do not create new Active Directory forests with the same name as an external DNS name. For example, if your Internet DNS URL is http://contoso.com, you must choose a different name for your internal forest to avoid future compatibility issues. That name should be unique and unlikely for web traffic. For example: corp.contoso.com.
Select Add a new forest
Specify the root domain name, in my case its pintolab.net
Press Next

In my new lab all servers will be Windows Server 2012. So I will be setting the functional level to Windows Server 2012. To understand more about Functional Levels please visit: http://technet.microsoft.com/en-us/library/cc771294.aspx
I will select Domain Name System (DNS), because ADDS needs DNS. This machine will function as the DNS server for the whole forest.
Enter a password for Directory Services Restore Mode (DSRM)
Press Next

Press Next. If there was an existing DNS server you would be able to change this option.

I like to set my domain name to 8 characters or less so that the NetBIOS name is the same as the domain name. If my domain name was longer, it would have truncated it. You can however make this anything you want. More information on NetBIOS Domain Names: http://technet.microsoft.com/en-us/library/cc961556.aspx
Press Next

Press Next

Press Next

Press Install
In my case I got a couple of warnings. The most important warning was the one saying I am using DHCP instead of an assigned IP Address. It’s important to make sure your Domain Controller and DNS Servers use static IP’s.

After Installation, Press Close

Setup will reboot the server, you will notice that the domain name is now in front of the username


One thing to take note of is that when DNS gets installed it will point the preferred DNS Server to itself and within DNS it will use the DNS Server that was in there before as a forwarder.

Managing Active Directory

Open Server Manager
Select AD DS
Right Click the Server Name, I find this is the quickest way to get to the Management Tools, since they removed the good ole start menu.

When you right click the server name you should see the familiar AD tools.

Active Directory Users and Computers thankfully has the same look and feel as previous versions.

 

Installing SharePoint 2013 on Windows Server 2012 R2 Preview

  

EDIT: This has changed a little for Windows Server 2012 R2 RTM, so make sure that you also read VHD creation post  Link before spending time on the below.
I was all excited when I found out that you could try out Windows Server 2012 R2 Preview for a free 30 day trial on Windows Azure.  So I proceeded to instantiate a virtual machine and also to attempt to install SharePoint 2013 Foundation.  It didn’t go so well.
The main problem is that you can’t install the SharePoint 2013 Prerequisites using the standard install wizard, since it doesn’t recognize Windows Server 2012 R2 Preview as a valid supported OS.  It displays an error: “There was an error during installation.  This tool does not support the current operating system”:


I did finally get it to work, but it was not trivial at all.  First of all, you have to install the prerequisites semi-manually.  Then, you need to make sure that permissions are set properly on certain files and directories so that the SharePoint Configuration Wizard can successfully complete.  But dear friends, I am here to walk you through the process step by step.

I. INSTALL PREREQUISITES MANUALLY

A. INSTALL WINDOWS ROLES/FEATURES

First, make sure that you have installed the following roles and features in Windows 2012 Server R2 Preview:
ROLES:

• Application Server
• Web Server (IIS)
• EDIT: IIS 6 Management Console (h/t Darma)

FEATURES:

• ASP.NET 4.5
• Windows Identity Foundation 3.5

If you can do this on your own, go ahead and do it, and then skip to the next section.  Otherwise, here’s the step by step:
1) If it’s not already up (it starts by default in 2012), bring up the server manager, Dashboard view.  It should look like this:

2) Click on “Add roles and features”.
3) On the “Before you begin” page, click “Next”.
4) Select “Role based or feature based installation” and click “Next”.
5) If it isn’t already selected, select “Select a server from the server pool”, and the server that you are currently working on.  Then click “Next”.
6) On the “Select server roles” page, check “Application Server”,  “Web Server (IIS)”, and (EDIT:) “IIS 6 Management Console” (under Web Server (IIS)->Management Tools->IIS 6 Management Compatibility->IIS 6 Management Console.  h/t Darma).  NOTE: When you check “Web Server (IIS)” or “IIS 6 Management Console”, another dialog will pop up, asking “Add features that are required for ?”  Click on the “Add Features” button here.  This will return you to the “Select Server Roles” page.  After adding all 3 roles, click “Next”.
7) On the “Select features” page, expand “.NET Framework 4.5 Features” group by clicking on it.  In here, check “ASP.NET 4.5″.
8) On the same page, check “Windows Identity Foundation 3.5″.  Click “Next”.
9) On the “Application Server” page, click “Next”.
10) On the “Select role services” page, check “Web Server (IIS) Support” and click “Next”.
11) When the “Add features that are required for Web Server (IIS) Support?” dialog pops up, click on “Add Features”.
12) Click “Next” again to go to the next page.
13) From the “Web Server Role” page, click “Next”.
14) From “Select role services”, click “Next”.
15) On the “Confirm installation selections” page, I suggest that you check “Restart the destination server automatically if required”.  (You’re going to need to reboot eventually anyway.)
16) Click “Install”.
17) Wait for feature installation to complete.  When it does, you can click “close”.  If the server doesn’t restart automatically, reboot it.

B. DOWNLOAD AND INSTALL SHAREPOINT PREREQUISITES 

OK, here’s the fun part.  You can easily download and install the prerequisites that SharePoint needs, but you have to do a little more than just download and run the install wizards.
The good news is that Craig Lussier has written three scripts to both download and install the SharePoint 2013 prerequisites.  The bad news is that only the download script works on Windows Server 2012 R2 (they probably work fine on vanilla 2012), because the install scripts use Prerequisiteinstaller.exe, which throws the same “This tool does not support the current operating system” error that we saw above.  So we’ll only take advantage of the download script, and do the rest manually.
So the steps:
1) Download the scripts from http://gallery.technet.microsoft.com/DownloadInstall-SharePoint-e6df9eb8.
2) Unzip to a directory of your choosing.
3) If you want to save the files to a new directory of your choosing, create that directory now.
4) Run PowerShell as an administrator.
5) In PowerShell, type “Set-ExecutionPolicy Unrestricted” and hit enter.  This is required because the scripts are unsigned and won’t run otherwise.
6) cd to the directory where you unzipped the scripts.  They should be .ps1 files.
7) Type ” .\Download-SP2013PreReqFiles.ps1” and press enter.
8) At the Security Warning, type “r” for “Run once”.
9) When prompted to “Please enter the directory path to where you wish to save the Sharepoint 2013 Prerequisite Files:”, type the directory of your choosing (which you may have created in step #3), and press enter.
10) The files will take some time to download, but there will be a text indicator that shows the download progress.
11) When you are done, the install files will be in the directory you chose.  Leave the PowerShell window open, since you’ll need it in a few steps.
12) Click on each file to run the installers, EXCEPT:

• Windows6.1-KB974405-x64.msu
• WindowsServerAppFabricSetup_x64.exe
• AppFabric1.1-RTM-KB2671763-x64-ENU.exe

(In fact, you can even delete Windows6.1-KB974405-x64.msu.  I couldn’t get it to run successfully on Windows 2012 R2 no matter what Google told me.  Besides, it’s for installing WIF, which you installed above in step A.8, so we won’t be using it at all.)
13) Go back to PowerShell.  cd to the same directory as the install files.
14) To install AppFabric, type the following at the command line:

.\WindowsServerAppFabricSetup_x64.exe /i CacheClient","CachingService","CacheAdmin /gac

NOTE that there are quotes surrounding the commas.  These are important in PowerShell.  (h/t to Doug Hemminger for this tip).
15) Wait a minute or two for AppFabric to install.  Unfortunately, except for the cursor hourglass, there isn’t much of a cue that it’s done.
16) Now you can click on AppFabric1.1-RTM-KB2671763-x64-ENU.exe to install the AppFabric patch.
17) Reboot the server.
All the prerequisites should now be installed, and you should be able to install SharePoint 2013.

II. INSTALL AND CONFIGURE SHAREPOINT 2013 

A. INSTALL SHAREPOINT 2013

If you already know how, just install SharePoint the standard way that you know how (not the prerequisites, of course, which you’ve just installed), and skip to the next section.
If you’re new to SharePoint, here are the directions to install SharePoint 2013 Foundation (I have no idea how SharePoint Server differs):
1) Click on the SharePoint installer (sharepoint.exe).
2) On the “SharePoint Foundation 2013″ screen, click on “Install SharePoint Foundation”.
3) Follow the prompts through the wizard.  It should be relatively intuitive. (Note: For this example, I installed SharePoint as a stand-alone server type, since I was on Windows Azure and didn’t want to install a complete SQL Server database.  You can configure the Server Type however you like, though.  If you don’t know what to do, I’d recommend stand-alone for now.)
4) Do run the SharePoint Products Configuration wizard at the end of the installation.  Just note that it will fail during configuration task 8 of 10 (Creating sampledata), with the error: “The SDDL string contains an invalid sid or a sid that cannot be translated.”:

This is fine for now, as we will run the configuration tweaks below to fix that.  But we need the wizard to set up the initial databases and users for us to tweak first, so make sure that you do run it to failure, and click “Close” to exit the Wizard.
B. CONFIGURATION TWEAKS
If you go to Administrative Tools->Services, you’ll see that the AppFabric Caching Service is not running.  This is because it does not have proper permissions to a number of resources  We’ll need to set these and then start the service (as well as configure it to start automatically).

1. Set DATABASE permissions for NETWORK SERVICE

(I don’t remember the source for the below, but something similar was posted here.)
1) You will need SQL Server Management Studio so that you can access the database directly and set the permissions properly.  If you haven’t already installed SQL Server itself, then download and install SQL Server Management Studio Express for SQL Server Express 2008 R2 from http://www.microsoft.com/en-us/download/details.aspx?id=22985 (note that the version of SQL Express that SharePoint 2013 installs is 2008 R2, not 2012.  I know.).
NOTE: When installing SQL Server Management Studio Express, you will likely get warnings that say “This program has compatibility issues”.  Just click on “Run the program without getting help” and continue the install.
2) Run SQL Server Management Studio, and connect to the SharePoint database instance.  If you installed a stand-alone configuration, this server name you need to connect to is localhost\SHAREPOINT:


3) Expand the database, and expand the “Security” folder.  You should see the “NT AUTHORITY\NETWORK SERVICE” user

4) Right click on this NETWORK SERVICE user and select “Properties.”
5) Select “Server Roles” on the left, and check the “sysadmin” role on the right:

5) Click “OK”.  You can exit SQL Server Management Studio now.

2. Set config file permissions for the AppFabric Caching Service

(h/t: David Pokluda)
1) In the Windows File Explorer, navigate to C:\Program Files\AppFabric 1.1 for Windows Server\.
2) Right click on DistributedCacheService.exe.config and select “Properties”.
3) On the “Sharing” tab, click “Edit…”
4) Click “Add…”
5) In the white textbox on the bottom, type in “NETWORK SERVICE” and then “Check Names…”.  It should resolve to an underlined “NETWORK SERVICE”.
6) Click “OK”.
7) With “NETWORK SERVICE” selected, check the “Full Control” checkbox in the “Allow” column.
8) Click “OK”.
9) Click “OK” to exit the dialog box.

3. Set permissions on Analytics_ directory.

(h/t: Kancharia Srikanth)
1) If you are running SharePoint Foundation, navigate in the Windows File Explorer to C:\Program Files\Windows SharePoint Services\15.0\Data.  If you are running SharePoint Server, navigate to C:\Program Files\Microsoft Office Servers\15.0\Data\Office Server.
2) You’ll see a folder in this directory called “Analytics_”, for example, “Analytics_939ab742-745f-456c-8623-fddca9b02334″ (your GUID will be different, of course).
3) Right click on the folder and select “Properties”.
4) Click on the “Sharing” tab, and then click on “Advanced Sharing…”.
5) Check the “Share this folder” checkbox.  Leave the default share name.
6) Click on the “Permissions” button near the bottom.
7) Click the “Add..” button.
8) In the white textbox on the bottom, type   “NETWORK SERVICE; WSS_ADMIN_WPG”.  Then click the “Check Names” button to make sure that the users resolve.
9) Click OK.
10) Select each user that you just added (NETWORK SERVICE and WSS_ADMIN_WPG), and check the “Full Control” checkbox in the “Allow” column.
11) Click “OK” to save the permissions.
12) Click “OK” to save the share settings.
13) Click “Close” to exit the dialog box.
4. Re-run the SharePoint Product Configuration Wizard.
1) From the Windows “Start” screen, type “SharePoint 2013 Products Wizard”
2) Click on the result that comes up.  The wizard will start running.
3) Click “Next” to run the wizard.
4) When the warning “The following services may have to be started or reset during the configuration:” pops up, just click on the “Yes” button.
5) You’ll notice that the wizard begins at task 4, but will successfully pass through task 8 to the last task (task 10), and complete successfully.

C. DEFAULT WEB APPLICATION PROVISIONING

At this point, SharePoint is successfully installed!  BUT: the permissions will be all messed up on the default web application, to the point that it will be unusable (or at least, that’s what I found).  You will have to delete and re-create the default SharePoint web application for it to be accessible.  Steps:
1. Delete and re-create the default web application.
1) From the Start screen, type “SharePoint 2013 Central Administration.”.  Click on the result that comes up.
2) IE will come up with Central Administration’s home page.
3) Under “Application Management”, click on “Manage web applications.”
4) You will see two web applications: the default (which is probably called “SharePoint – 80″) and “SharePoint Central Administration v4″.  Select the default SharePoint web application.  CAUTION: Do NOT select the Central Administration web application!
5) On the ribbon, click on the “Delete” button.
6) When the “Delete Web Application” dialog comes up, select “Yes” for *both* “Delete Content Databases” and “Delete IIS Web Sites”.
7) Click the “Delete” button.
8) When the “Are you sure you want to delete this Web application…?” warning comes up, click “OK”.
9) You’ll have to wait a moment for the web application to be fully deleted, and for the Central Admin page to refresh, now showing only the Central Admin web application.
NOTE:  Clicking the “Delete” button does *not* refresh the page for me.  I don’t know why, and I can only assume it’s a SharePoint bug.  If it looks like nothing is happening, just close the warning dialog by clicking on the X on the upper right, and hit Ctrl+r to refresh the Web Applications page in Central Administration.  At *that* point, you should see that the default web application is gone, but Central Administration is still there.
10) Create a new web application by clicking the “New” button on the ribbon.
11) If you don’t know what to do at this point, just keep the defaults.  One thing you *will* have to do is go to “Select a security account for this application pool” and select “predefined”, which will probably display “Network Service”.  If you are an experienced SharePoint user, and you know what kind of web application settings you want, set them as you please.
12) Click OK.  You’ll see the “This shouldn’t take long” message, even though, of course, it will take quite some time for the web application to be created.
NOTE: Here, once again, you may have to X out of the dialog and refresh the Central Admin page to see the changes.
2. Create a default site collection in the new web application.
1) Under “Application Management” (on the left), find and click on “Create Site collections” (on the right).
2) Fill in as desired.  I would advise that one of the site collection administrators be the admin account that you used to install SharePoint.
3) Click “OK” to be returned to the Central Admin page, or click on the URL of the site to be redirected there (the site may take a while to come up on its first call).

Ta-Da!  That wasn’t too long, was it?  But now you have the latest and greatest SharePoint 2013 to play with on Windows Server 2012 R2.
Hope that this helps.  Please feel free to comment with questions or corrections.
 

 

Share point queries

It is a very common requirement in SharePoint Portal Solution to analyze a SharePoint solution in terms of storage capacity and statistics of sites, document libraries, area etc., to monitor the growth of the SharePoint solution and to take prompt action, if any thing goes wrong. Like, SharePoint should know what document library is most used in terms of versions, or the number of documents uploaded.

Microsoft SharePoint Portal Solution database is Microsoft�s proprietary database, and it cannot be modified because of any problems with future service pack releases, but sometimes you can write SELECT queries that can give full information that may be useful for the SharePoint Administrator. I was looking for some queries that would help me to analyze a SharePoint solution, but unfortunately, didn�t find any article that gives a list of useful queries, so I started to write my own SQL queries.

Contents
An Overview of the SharePoint Database Schema
A List of SQL Queries
An Overview of the SharePoint Database Schema
In order to write queries against the SharePoint database schema, you should how Microsoft organizes information in SQL tables. Some common tables are:

Docs
Docversions
Sites
Webs
Information pertaining to document libraries, list, area, and sites can easily be fetched by joining these tables in the Portal_Site database. You can write a query against the PORTAL_Profile database to get useful information of user profiles etc.

A List of SQL Queries
Top 100 documents in terms of size (latest version(s) only): Collapse | Copy CodeSELECT TOP 100 Webs.FullUrl As SiteUrl,
Webs.Title 'Document/List Library Title',
DirName + '/' + LeafName AS 'Document Name',
CAST((CAST(CAST(Size as decimal(10,2))/1024 As
decimal(10,2))/1024) AS Decimal(10,2)) AS 'Size in MB'
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName NOT LIKE '%.stp')
AND (LeafName NOT LIKE '%.aspx')
AND (LeafName NOT LIKE '%.xfp')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.inf')
AND (LeafName NOT LIKE '%.css')
ORDER BY 'Size in MB' DESCTop 100 most versioned documents: Collapse | Copy CodeSELECT TOP 100
Webs.FullUrl As SiteUrl,
Webs.Title 'Document/List Library Title',
DirName + '/' + LeafName AS 'Document Name',
COUNT(Docversions.version)AS 'Total Version',
SUM(CAST((CAST(CAST(Docversions.Size as decimal(10,2))/1024 As
decimal(10,2))/1024) AS Decimal(10,2)) ) AS 'Total Document Size (MB)',
CAST((CAST(CAST(AVG(Docversions.Size) as decimal(10,2))/1024 As
decimal(10,2))/1024) AS Decimal(10,2)) AS 'Avg Document Size (MB)'
FROM Docs INNER JOIN DocVersions ON Docs.Id = DocVersions.Id
INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1
AND (LeafName NOT LIKE '%.stp')
AND (LeafName NOT LIKE '%.aspx')
AND (LeafName NOT LIKE '%.xfp')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.inf')
AND (LeafName NOT LIKE '%.css')
GROUP BY Webs.FullUrl, Webs.Title, DirName + '/' + LeafName
ORDER BY 'Total Version' desc, 'Total Document Size (MB)' descList of unhosted pages in the SharePoint solution: Collapse | Copy Codeselect Webs.FullUrl As SiteUrl,
case when [dirname] = ''
then '/'+[leafname]
else '/'+[dirname]+'/'+[leafname]
end as [Page Url],
CAST((CAST(CAST(Size as decimal(10,2))/1024 As
decimal(10,2))/1024) AS Decimal(10,2)) AS 'File Size in MB'
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
where [type]=0
and [leafname] like ('%.aspx')
and [dirname] not like ('%_catalogs/%')
and [dirname] not like ('%/Forms')
and [content] is not null
and [dirname] not like ('%Lists/%')
and [setuppath] is not null
order by [Page Url];List of top level WSS sites and their total size, including child sites in the portal: Collapse | Copy Codeselect FullUrl As SiteUrl,
CAST((CAST(CAST(DiskUsed as decimal(10,2))/1024 As
decimal(10,2))/1024) AS Decimal(10,2)) AS 'Total Size in MB'
from sites
Where FullUrl LIKE '%sites%' AND
fullUrl <> 'MySite' AND fullUrl <> 'personal'
List of portal area and total number of users: Collapse | Copy Codeselect webs.FullUrl, Webs.Title,
COUNT(WebMembers.UserId) As 'Total User'
from Webs INNER JOIN WebMembers
ON Webs.Id = WebMembers.WebId
Where fullurl NOT like '%sites%' AND
fullUrl <> 'MySite' AND fullUrl <> 'personal'
Group BY webs.FullUrl, Webs.Title
Order By 'Total User' descList of top level and sub sites in the portal and the number of users: Collapse | Copy Codeselect webs.FullUrl ,Webs.Title, COUNT(WebMembers.UserId) As 'Total User'
from Webs INNER JOIN WebMembers
ON Webs.Id = WebMembers.WebId
where fullurl like '%sites%' AND fullUrl <> 'MySite' AND fullUrl <> 'personal'
Group BY webs.FullUrl, Webs.Title
Order By 'Total User' descList of all portal area: Collapse | Copy Codeselect Webs.FullUrl As [Site Url],
Title AS [Area Title]
from Webs
Where fullurl NOT like '%sites%' AND fullUrl <>
'MySite' AND fullUrl <> 'personal'List of the total portal area: Collapse | Copy Codeselect COUNT(*)from Webs
Where fullurl NOT like '%sites%' AND
fullUrl <> 'MySite' AND fullUrl <> 'personal'List of all top level and sub sites in the portal: Collapse | Copy Codeselect Webs.FullUrl As [Site Url],
Title AS [WSS Site Title]
from webs
where fullurl like '%sites%' AND fullUrl <>
'MySite' AND fullUrl <> 'personal'List of the total top level and sub sites in the portal: Collapse | Copy Codeselect COUNT(*) from webs
where fullurl like '%sites%' AND fullUrl <>
'MySite' AND fullUrl <> 'personal'List of all list/document libraries and total items: Collapse | Copy Codeselect
case when webs.fullurl = ''
then 'Portal Site'
else webs.fullurl
end as [Site Relative Url],
webs.Title As [Site Title],
case tp_servertemplate
when 104 then 'Announcement'
when 105 then 'Contacts'
When 108 then 'Discussion Boards'
when 101 then 'Docuemnt Library'
when 106 then 'Events'
when 100 then 'Generic List'
when 1100 then 'Issue List'
when 103 then 'Links List'
when 109 then 'Image Library'
when 115 then 'InfoPath Form Library'
when 102 then 'Survey'
when 107 then 'Task List'
else 'Other' end as Type,
tp_title 'Title',
tp_description As Description,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate IN (104,105,108,101,
106,100,1100,103,109,115,102,107,120)
order by tp_itemcount descNote: the tp_servertemplate field can have the following values:

104 = Announcement
105 = Contacts List
108 = Discussion Boards
101 = Document Library
106 = Events
100 = Generic List
1100 = Issue List
103 = Links List
109 = Image Library
115 = InfoPath Form Library
102 = Survey List
107 = Task List
List of document libraries and total items: Collapse | Copy Codeselect
case when webs.fullurl = ''
then 'Portal Site'
else webs.fullurl
end as [Site Relative Url],
webs.Title As [Site Title],
lists.tp_title As Title,
tp_description As Description,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 101
order by tp_itemcount descList of image libraries and total items: Collapse | Copy Codeselect case when webs.fullurl = ''
then 'Portal Site'
else webs.fullurl
end as [Site Relative Url],
webs.Title As [Site Title],
lists.tp_title As Title,
tp_description As Description,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 109 -- Image Library
order by tp_itemcount descList of announcement list and total items: Collapse | Copy Codeselect case when webs.fullurl = ''
then 'Portal Site'
else webs.fullurl
end as [Site Relative Url],
webs.Title As [Site Title],
lists.tp_title As Title,
tp_description As Description,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 104 -- Announcement List
order by tp_itemcount descList of contact list and total items: Collapse | Copy Codeselect case when webs.fullurl = ''
then 'Portal Site'
else webs.fullurl
end as [Site Relative Url],
webs.Title As [Site Title],
lists.tp_title As Title,
tp_description As Description,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 105 -- Contact List
order by tp_itemcount descList of event list and total items: Collapse | Copy Codeselect case when webs.fullurl = ''
then 'Portal Site'
else webs.fullurl
end as [Site Relative Url],
webs.Title As [Site Title],
lists.tp_title As Title,
tp_description As Description,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 106 -- Event List
order by tp_itemcount descList of all tasks and total items: Collapse | Copy Codeselect
case when webs.fullurl = ''
then 'Portal Site'
else webs.fullurl
end as [Site Relative Url],
webs.Title As [Site Title],
lists.tp_title As Title,
tp_description As Description,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 107 -- Task List
order by tp_itemcount descList of all InfoPath form library and total items: Collapse | Copy Codeselect
case when webs.fullurl = ''
then 'Portal Site'
else webs.fullurl
end as [Site Relative Url],
webs.Title As [Site Title],
lists.tp_title As Title,
tp_description As Description,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 115 -- Infopath Library
order by tp_itemcount descList of generic list and total items: Collapse | Copy Codeselect
case when webs.fullurl = ''
then 'Portal Site'
else webs.fullurl
end as [Site Relative Url],
webs.Title As [Site Title],
lists.tp_title As Title,
tp_description As Description,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 100 -- Generic List
order by tp_itemcount descTotal number of documents: Collapse | Copy CodeSELECT COUNT(*)
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName NOT LIKE '%.stp')
AND (LeafName NOT LIKE '%.aspx')
AND (LeafName NOT LIKE '%.xfp')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.inf')
AND (LeafName NOT LIKE '%.css')Total MS Word documents: Collapse | Copy CodeSELECT count(*)
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.doc')
AND (LeafName NOT LIKE '%template%')Total MS Excel documents: Collapse | Copy CodeSELECT count(*)
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.xls')
AND (LeafName NOT LIKE '%template%')Total MS PowerPoint documents: Collapse | Copy CodeSELECT count(*)
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.ppt')
AND (LeafName NOT LIKE '%template%')Total TXT documents: Collapse | Copy CodeSELECT count(*)
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.txt')
AND (LeafName NOT LIKE '%template%')Total Zip files: Collapse | Copy CodeSELECT count(*)
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.zip')
AND (LeafName NOT LIKE '%template%')Total PDF files: Collapse | Copy CodeSELECT count(*)
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.pdf')
AND (LeafName NOT LIKE '%template%')Total JPG files: Collapse | Copy CodeSELECT count(*)
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.jpg')
AND (LeafName NOT LIKE '%template%')Total GIF files: Collapse | Copy CodeSELECT count(*)
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName LIKE '%.gif')
AND (LeafName NOT LIKE '%template%')Total files other than DOC, PDF, XLS, PPT, TXT, Zip, ASPX, DEWP, STP, CSS, JPG, GIF: Collapse | Copy CodeSELECT count(*)
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName NOT LIKE '%.pdf')
AND (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.doc')
AND (LeafName NOT LIKE '%.xls')
AND (LeafName NOT LIKE '%.ppt')
AND (LeafName NOT LIKE '%.txt')
AND (LeafName NOT LIKE '%.zip')
AND (LeafName NOT LIKE '%.aspx')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%.stp')
AND (LeafName NOT LIKE '%.css')
AND (LeafName NOT LIKE '%.jpg')
AND (LeafName NOT LIKE '%.gif')
AND (LeafName <>'_webpartpage.htm')Total size of all documents: Collapse | Copy CodeSELECT SUM(CAST((CAST(CAST(Size as decimal(10,2))/1024
As decimal(10,2))/1024) AS Decimal(10,2)))
AS 'Total Size in MB'
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName NOT LIKE '%.stp')
AND (LeafName NOT LIKE '%.aspx')
AND (LeafName NOT LIKE '%.xfp')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.inf')
AND (LeafName NOT LIKE '%.css')
AND (LeafName <>'_webpartpage.htm')Conclusion

How to edit the Display page of Events in Sharepoint 2003 Version

How can i edit the Display page of Events in Sharepoint 2003 Version ?

1) go To the folder "C:\Program Files\Common Files\Microsoft Shared\web server extensions\60\TEMPLATE\1033\MPS" where events related files present
2) Check for the Default.aspx

3) I have received a requirement during my experience to disable to "Go to Events" for every event page .

4) For every event page this page will loads so at the time of body load i have called a function to disable the "Go To Events" link and its image based on the control name rendered .
5) That's it we are ready .



add the function to the body onload as below

onload="MWSOnLoad();disableEventLink();"

Deleting users from all sites in SPS 2003

Deleting users from all sites in SPS 2003
By tpham
Sure, you can use the stsadm.exe tool with the “deleteuser” command, but you have to do that with *every* site which that user is a member of. And sure, you can use the “enumusers” switch to find these dead users, but again that is limited to listing the users for one site only.

So, fire up SQL Query Analyzer, connect to your SharePoint SQL database, and switch the active database to PORTALNAME_SITE. Then paste the following SQL:

SELECT DISTINCT N’”C:\Program Files\Common Files\Microsoft Shared\web server extensions\60\BIN\stsadm.exe”‘+ N’ -o deleteuser -url “http://portaladdress/’ + dbo.Webs.FullUrl + N’” -userlogin “‘ + dbo.UserInfo.tp_Login + N’”‘ AS Command FROM dbo.Webs RIGHT OUTER JOIN dbo.UserInfo ON dbo.Webs.SiteId = dbo.UserInfo.tp_SiteID WHERE (dbo.UserInfo.tp_Login = N’DOMAINNAME\username1′ OR dbo.UserInfo.tp_Login = N’ DOMAINNAME\username2′) AND (dbo.Webs.FullUrl IS NOT NULL) AND (dbo.Webs.FullUrl LIKE N’sites%’)

Of course, change the path to stsadm.exe if it is different for your server, replace http://portaladdress with your root portal address, and replace DOMAINNAME\username1 with the accounts that you want to remove. Then run the query, and it will generate one “stsadm –o deleteuser” command for every user and for every site which that user is a member of. Copy the output to the clipboard, paste it into Notepad and save it as a .bat file. Then run the .bat file in the Command Window using “ | clip”. The output will be silent but captured to the clipboard so you can review any possible errors after the operation is completed. Some errors that may occur include deleting the last administrator of a Site, missing users (I got lots of these but don’t know why yet) and deleting users from sub-sites in a Site collection that does not have unique permissions. All of these errors are harmless, but you’ll be left with a much cleaner collection of Site memberships in SharePoint, free of those dratted ex-employees whom you want to forget about and can now safely do so since their name won’t keep popping up anymore.

How To Make SharePoint 2003 Play Nice With Office 2007 Files

Office 2007 and SharePoint 2003, How To Fix Icons and File Open Issues
After digging around more than a dozen websites to get my own SharePoint site accepting Office 2007 files (docx, pptx, etc) as Word and PowerPoint files and not zip files I thought I’d try to bring all the info into one place for easy reference.
As you may of have not noticed if you’re using SharePoint 2003 and Office 2007 and save your documents to SharePoint you never see the correct icon and if you try to open the file from SharePoint it now thinks the file is a zip file. You can work around this by ignoring the icon and doing the little "Drop Down" and say "Edit in Word" but just because Microsoft has released SharePoint 2007 that doesn’t mean you have to do work arounds just to get your own site to work with Office 2007.
Since we are going to be working with IIS let’s go ahead and stop it, ‘net stop iisadmin’.
So first off let’s get the icons we need. A big shout out to Alexander for creating those icons for us. Just open the zip, grab the icons from the "gif-light" directory, copy them into ‘%program directory%\Common Files\Microsoft Shared\web server extensions\60\TEMPLATE\IMAGES’. Now just edit the XML file which loads those icons by going to, ‘%program directory\Common Files\Microsoft Shared\web server extensions\60\TEMPLATE\XML’, open "docicon.xml" with Notepad. ALERT: For me any time I start messing with windows files I expect Murphy to show up so do yourself a favor and before you edit this and other files do a right-click, send to, compressed file just in case. Locate the section section and add the following keeping your formatting inline for easy reference and nice XML:

















Now that we have the icons and SharePoint knows how to assign them let’s move on to getting IIS to understand that a .docx file should be opened by Word instead of Zip. This also is pretty straight forward once you either know IIS and knew immediately the problem was a mime type association or you keep reading and just let me tell you how to fix this. Basically the problem is IIS doesn’t know how to serve up these file extensions so we need to tell it how by adding the mime type associations. Since we stopped IIS earlier it’s just a matter of editing the XML file containing the extensions which is ‘MetaBase.XML’ located at, %Windows%\system32\inetsrv. What we need to do is add the following to the IIsMimeMap section. Again keep the formatting pretty and do the ‘send to compressed’ just in case. Also note there is a leading and trailing " in that section so be careful how you paste and edit :
.docm,application/vnd.ms-word.document.macroEnabled.12
.docx,application/vnd.openxmlformats-officedocument.wordprocessingml.document
.dotm,application/vnd.ms-word.template.macroEnabled.12
.dotx,application/vnd.openxmlformats-officedocument.wordprocessingml.template
.potm,application/vnd.ms-powerpoint.template.macroEnabled.12
.potx,application/vnd.openxmlformats-officedocument.presentationml.template
.ppam,application/vnd.ms-powerpoint.addin.macroEnabled.12
.ppsm,application/vnd.ms-powerpoint.slideshow.macroEnabled.12
.ppsx,application/vnd.openxmlformats-officedocument.presentationml.slideshow
.pptm,application/vnd.ms-powerpoint.presentation.macroEnabled.12
.pptx,application/vnd.openxmlformats-officedocument.presentationml.presentation
.xlam,application/vnd.ms-excel.addin.macroEnabled.12
.xlsb,application/vnd.ms-excel.sheet.binary.macroEnabled.12
.xlsm,application/vnd.ms-excel.sheet.macroEnabled.12
.xlsx,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
.xltm,application/vnd.ms-excel.template.macroEnabled.12
.xltx,application/vnd.openxmlformats-officedocument.spreadsheetml.template

Now just start IIS back up, simple way for that is to do an ‘IISReset’ at the command prompt and go test SharePoint. If you have followed the above correctly and not made any editing mistakes to the XML files you should be good to go.

deploying The Reports in Sharepoint 2003 using Report Viewer WebPart

Reporting Services provides several Web Parts that work with specific versions of a report server and in particular deployment modes. If you want to access report server content on a SharePoint site from a native mode report server, use the SharePoint 2.0 Web Parts that are included with Reporting Services. Instructions for installing and using the 2.0 Web Parts are provided in this topic.

If you want to use a SharePoint 3.0 Web Part to access a report server that runs in SharePoint integrated mode, use the Reporting Services Add-in. For more information about the add-in, see Overview of Reporting Services and SharePoint Technology Integration.

About Report Explorer and Report Viewer
--------------------------------------------------------------------------------

Report Explorer and Report Viewer are SharePoint 2.0 Web Parts that were introduced in SQL Server 2000 Reporting Services Service Pack 2 (SP2) and continue to be available in current releases.

The Web Parts provide a way to view reports and explore the report server folder hierarchy from a SharePoint site:

Report Explorer connects to Report Manager on the report server computer. You can browse available reports on a report server and subscribe to individual reports. If Report Builder is enabled and you have sufficient permissions, you can start Report Builder from the Report Explorer Web Part.

Report Explorer displays the contents of a folder using a page in Report Manager. Access to individual items and folders throughout the report server folder hierarchy are controlled through role assignments on the report server. When you select a report, it opens in a new browser window. The HTML viewer on the report server displays the report and provides the report toolbar, not the Report Viewer Web Part. If you want to customize the toolbar settings, be sure to specify the URL access parameters on the report server. For instructions, see Using URL Access Parameters.

Report Viewer displays a report and provides a toolbar that you can use to navigate pages, search for content, or export the report. You can add the Report Viewer Web Part to a Web Part page to always show a specific report on that page or you can connect it to Report Explorer to display reports that are opened through that Web Part.

Note
The Report Viewer Web Part has the same name as a newer Report Viewer Web Part that is part of the Reporting Services Add-in, a SharePoint integration feature for Reporting Services report servers. Although it has the same name, it is a different Web Part that has a different schema and implementation. The Report Viewer Web Parts are not interchangeable despite having the same name. Visually, you can distinguish the two Web Parts through the following characteristic: the Report Viewer 3.0 Web Part that is installed through the add-in has an Actions menu on the toolbar.


Requirements
--------------------------------------------------------------------------------

Requirements for using the Report Viewer and Report Explorer Web Parts include the following:

Supported versions of SharePoint products and technologies include Windows SharePoint Services 2.0, 3.0, and SharePoint Foundation 2010; Microsoft SharePoint Portal Server; and Microsoft Office SharePoint Server 2007 and SharePoint Server 2010.

The report server version must be SQL Server 2005 Reporting Services or later.

The report server must run in native mode. You cannot use the Report Explorer and Report Viewer Web Parts to connect to or view reports on a report server that runs in SharePoint integrated mode. For more information about modes, see Planning a Deployment Mode.

Report Manager must be installed.

The Web Parts are unchanged from previous releases. Furthermore, they do not support the SharePoint integration feature set that is part of the Reporting Services Add-in. Specifically, they do not support the features that are described in the topic Overview of Reporting Services and SharePoint Technology Integration.

Report Explorer and Report Viewer Web Parts are distributed through a cabinet (.cab) file that is included with Reporting Services. Instructions for installing, configuring, and using the Web Parts are provided in the following sections of this topic.

Installing Web Parts
--------------------------------------------------------------------------------

Web parts are delivered to a SharePoint server as a cabinet (.cab) file. Run the Stsadm.exe tool on the .cab file from the command line to install the Web Parts. The Stsadm.exe tool is included in a SharePoint installation. To learn more about the tool and Web part deployment see your SharePoint documentation.

Note
The following instructions assume that you are using SQL Server 2005 Reporting Services or later and Windows SharePoint Services 3.0 or Office SharePoint Server 2007.


Copy the RSWebParts.cab to a folder on the SharePoint server. The .cab is installed with Reporting Services. By default, it is located in the C:\Program Files\Microsoft SQL Server\100\Tools\Reporting Services\SharePoint folder. You can copy it to any folder on the SharePoint server, and then delete it later after you install the Web Parts.

On the computer that has the installation of the SharePoint product or technology, open a Command Prompt window and navigate to the folder that has the Stsadm.exe tool. The path will vary depending on which version of Windows SharePoint Services you are running or if you running SharePoint Foundation 2010. For example, if you are using Windows SharePoint Services 3.0, the path is C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN.

Run Stsadm.exe on the .cab, using the following syntax:

CopySTSADM.EXE -o addwppack -filename "C:\ Program Files\Microsoft SQL Server\100\Tools\Reporting Services\SharePoint\RSWebParts.cab" -globalinstall
Specifying -globalinstall adds the Web Parts to the global assembly cache (GAC). This step is necessary if you want to connect the Web Parts.

Adding and Configuring Web Parts
--------------------------------------------------------------------------------

After you install the Web Parts, you can add them to a Web Part Page on a SharePoint site. You must have permission to create Web sites and add content.

Note that customizing the Web Parts is not supported. The Web parts are intended to be used as is, and should not be extended or modified.

To add the Web Parts to a team site
In a SharePoint Web application, open a Web Part page.

In Site Actions, click Edit Page.

In a zone on the page, click Add a Web Part.

In the Add Web Parts dialog box, scroll down to Miscellaneous. If you are using Office SharePoint Server , you might need to expand All Web Parts first.

Select Report Explorer. You can also select Report Viewer if you want to add both Web Parts at the same time to the same zone.

Do not select SQL Server Reporting Services Report Viewer if you see it in the same list. That Web Part is registered when you install the Reporting Services Add-in used for running a report server in SharePoint integrated mode. It cannot be used to view reports on a native mode report server.

Click Add.

While the page is in edit mode, click Edit in the Report Explorer Web Part.

In Report Manager URL, type a URL to a Report Manager instance that is associated with the native mode report server you want to access. By default, a Report Manager URL has the following syntax: http:///reports.

Optionally, set the Start Path. The start path is a folder in the report server folder hierarchy. You can specify a start path if you want the default page to be a folder further down the folder hierarchy. The path must begin with a forward slash. You must specify a complete path that starts with the root node of the report server folder hierarchy, but does not include the server name or Report Manager virtual directory. For example, to open a folder named Adventure Works just below the root node, specify /Adventure Works in the Start Path.

If you want to connect Report Explorer and Report Viewer, set the connection while the page is in Edit mode:

Click Edit on the Report Explorer Web Part menu.

Point to Connections.

Point to Show report in.

Click Report Viewer.

If you added Report Viewer to show a report within the page, enter a URL to the report.

Click Edit on the Report Viewer Web Part menu.

Click Modify Shared Web Part.

In Report Manager URL, type the full URL to a Report Manager instance that is associated with the native mode report server you want to access.

In Report Path, specify a forward slash, followed by the folder path, and the report name. Do not include the server name or Report Manager virtual directory. For example, to open the Company Sales report in the Adventure Works folder, specify /Adventure Works/Company Sales.