which country user step here?

Tag Cloud

MOSS (47) SharePoint 2007 (37) SharePoint 2013 (23) SharePoint 2010 (22) MOSS admin (17) admin (17) PowerShell (16) developer (16) List (15) WSS (14) MOSS SP2 (13) sql query (13) end user (11) scripting (11) wss V3 (11) Moss issue (8) search (8) permission (7) sql (7) Service Pack (6) database (6) reportadmin (6) workflow (6) Excel (5) client object model (5) CU (4) Client Code (4) Command (4) Cumulative Updates (4) Patch (4) SharePoint designer (4) stsadm (4) ASP.NET (3) Content Database (3) Groove (3) Host Named Site Collections (HNSC) (3) IIS (3) RBS (3) Tutorial (3) alert (3) batch file (3) codeplex (3) error (3) incomming email (3) restore (3) upload (3) Caching (2) Folder (2) Index (2) Internet (2) News (2) People Picker (2) Share Document (2) View (2) Web Development with ASP.NET (2) authentication (2) coding (2) column (2) deploy solution (2) domain (2) download (2) enumsites (2) exam (2) export (2) issue (2) june CU (2) mySites (2) network (2) office 365 (2) orphan site (2) performance (2) profile (2) project server (2) query (2) server admin (2) theme (2) timer job (2) training (2) user porfile (2) web master (2) web.config (2) wsp (2) 70-346 (1) 70-630 (1) AAM (1) Anonymous (1) Approval (1) Cerificate (1) Consultants (1) Content Deployment (1) Content Type (1) DOS (1) Document Library (1) Drive Sapce (1) Excel Services (1) Export to Excel (1) Feature (1) GAC (1) Get-SPContentDatabase (1) Get-WmiObject (1) HTML calculated column (1) ISA2006 (1) IT Knowledge (1) ITIL (1) Install (1) Link (1) MCTS (1) Macro (1) Migration (1) My Site Cleanup Job (1) My Sites (1) NLBS (1) Nintex (1) Office (1) Open with Explorer (1) ROIScan.vbs (1) Reporting Services (1) SPDisposeCheck.exe (1) SQL Instance name (1) SSRS (1) SharePoint farm (1) Shared Services Administration (1) Site Collection Owner (1) Site template (1) Steelhead (1) URLSCAN (1) VLOOKUP (1) WSS SP2 (1) XCOPY (1) add user (1) admi (1) app (1) application pool (1) aspx (1) audit (1) availabilty (1) backup (1) binding (1) blob (1) branding sharepoint (1) cache (1) calendar (1) connection (1) copy file (1) counter (1) crawl (1) custom list (1) event (1) excel 2013 (1) facebook (1) filter (1) fun (1) group (1) iis log (1) import (1) import list (1) improment (1) interview (1) keberos (1) load balance (1) log in (1) metada (1) migrate (1) mossrap (1) onedrive for business (1) operation (1) process (1) publishing feature (1) resource (1) security (1) send email (1) size (1) sps2003 (1) sql201 (1) sub sites (1) system (1) table (1) task list (1) today date (1) vbs (1) video (1) web part (1) widget (1) windows 2008 (1) windows 2012 R2 (1) windows Azura (1) windows account (1) windows2012 (1) wmi (1)

Tuesday, December 14, 2010

querying membership data

Found one very usefull query from internet :

http://johnlivingstontech.blogspot.com/2009/11/sharepoint-2007-five-very-helpful-yet.html

thanks for the creator to share this!!  Below is the SQL query

1. Select Groups By URL

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectGroupsByUrl]

@url NVARCHAR(255)

AS

BEGIN

SELECT Groups.ID AS "GroupID", Groups.Title AS "GroupTitle", Webs.Title AS WebTitle, Webs.FullURL AS WebURL, Roles.Title AS "RoleTitle"

FROM RoleAssignment WITH (NOLOCK)

INNER JOIN Roles WITH (NOLOCK) ON Roles.SiteId = RoleAssignment.SiteId

AND Roles.RoleId = RoleAssignment.RoleId

INNER JOIN Groups WITH (NOLOCK) ON Groups.SiteId = RoleAssignment.SiteId

AND Groups.ID = RoleAssignment.PrincipalId

INNER JOIN Sites WITH (NOLOCK) ON Sites.Id = RoleAssignment.SiteId

INNER JOIN Perms WITH (NOLOCK) ON Perms.SiteId = RoleAssignment.SiteId

AND Perms.ScopeId = RoleAssignment.ScopeId

INNER JOIN Webs WITH (NOLOCK) ON Webs.Id = Perms.WebId

WHERE ((Webs.FullUrl = @url) OR (@url IS NULL))

ORDER BY

Groups.Title, Webs.Title, Perms.ScopeUrl

END

Sample Query

EXEC usp_SelectGroupsByUrl 'Sites/HR/Pay'

Results

GroupID

GroupTitle

WebTitle

WebURL

RoleTitle

71

Manager

Accounting

Sites/HR/PAY

View Only

74

Acct Sr Manager

Accounting

Sites/HR/PAY

Full Control

75

Member

Accounting

Sites/HR/PAY

View Only

76

Viewer

Accounting

Sites/HR/PAY

View Only

 

 

 

2. Select Groups By URL and User

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectGroupsByUrlUser]

@login NVARCHAR(255),

@url NVARCHAR(255)

AS

BEGIN

SELECT G.ID AS "GroupID", G.Title AS "GroupTitle", W.Title AS WebTitle, W.FullURL AS WebURL, R.Title AS "RoleTitle"

FROM RoleAssignment AS RA WITH (NOLOCK)

INNER JOIN Roles AS R WITH (NOLOCK) ON R.SiteId = RA.SiteId

AND R.RoleId = RA.RoleId

INNER JOIN Groups AS G WITH (NOLOCK) ON G.SiteId = RA.SiteId

AND G.ID = RA.PrincipalId

INNER JOIN Sites AS S WITH (NOLOCK) ON S.Id = RA.SiteId

INNER JOIN Perms AS P WITH (NOLOCK) ON P.SiteId = RA.SiteId

AND P.ScopeId = RA.ScopeId

INNER JOIN Webs AS W WITH (NOLOCK) ON W.Id = P.WebId

WHERE ((W.FullUrl = @url) OR (@url IS NULL))

AND G.ID IN

(

SELECT Groups.ID FROM GroupMemberShip WITH (NOLOCK)

INNER JOIN Groups ON GroupMembership.GroupID = Groups.ID

INNER JOIN UserInfo ON GroupMembership.MemberID = UserInfo.tp_ID

WHERE ((tp_Login = @login) OR (@login IS NULL))

)

ORDER BY

G.Title, W.Title, P.ScopeUrl

END

Sample Query

EXEC usp_SelectGroupsByUrlUser 'acmedomain\jdoe', 'Sites/HR/Pay'

Results

GroupID

GroupTitle

WebTitle

WebURL

RoleTitle

74

Sr Manager

Accounting

Sites/HR/PAY

Full Control

 

 

 

3. Select Groups By User Login

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectGroupsByUserLogin]

@login NVARCHAR(255)

AS

BEGIN

SELECT DISTINCT Groups.ID AS "GroupID", Groups.Title AS "GroupTitle"

FROM GroupMemberShip WITH (NOLOCK)

INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID

INNER JOIN UserInfo WITH (NOLOCK) ON GroupMembership.MemberID = UserInfo.tp_ID

WHERE ((tp_Login = @login) OR (@login IS NULL))

ORDER BY Groups.Title

END

Sample Query

EXEC usp_SelectGroupsByUserLogin 'acmedomain\jdoe'

Results

GroupID

GroupTitle

74

Sr Manager

52

Corporate Viewer

14

HR Team Member

4. Select Users By Group

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectUsersByGroup]

@groupTitle NVARCHAR(255)

AS

BEGIN

SELECT dbo.UserInfo.tp_ID AS UserID, dbo.UserInfo.tp_Title AS UserTitle, dbo.UserInfo.tp_Login AS UserLogin, dbo.UserInfo.tp_Email AS UserEmail, dbo.Groups.ID AS GroupsID, dbo.Groups.Title AS GroupsTitle

FROM UserInfo WITH (NOLOCK)

INNER JOIN GroupMembership WITH (NOLOCK) ON UserInfo.tp_ID = GroupMembership.MemberID

INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID

WHERE ((dbo.Groups.Title = @groupTitle) OR (@groupTitle IS NULL))

ORDER by dbo.UserInfo.tp_Login

END

Sample Query

EXEC usp_SelectUsersByGroup 'HR Team Member'

Results

UserID

UserTitle

UserLogin

UserEmail

GroupsID

GroupsTitle

23

Doe, John

acmedomain\jdoe

jdoe@acmedomain.com

14

HR Team Member

20

Doe, Jane

acmedomain\jadoe

jadoe@acmedomain.com

14

HR Team Member

2

Smith, Bob

acmedomain\bsmith

bsmith@acmedomain.com

14

HR Team Member

24

Yamamoto, Kazue

acmedomain\kyamamoto

kyamamoto@acmedomain.com

14

HR Team Member

46

Nakamura, Ichiro

acmedomain\inakamura

inakamura@acmedomain.com

14

HR Team Member

5 Select Users By Groups

USE [WSS_Content]

CREATE PROCEDURE [dbo].[usp_SelectUsersByGroups]

@groupTitles VARCHAR(1000)

AS

BEGIN

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @groupTitles

SELECT dbo.UserInfo.tp_ID AS UserID, dbo.UserInfo.tp_Title AS UserTitle, dbo.UserInfo.tp_Login AS UserLogin, dbo.UserInfo.tp_Email AS UserEmail, Groups.Title AS GroupTitle

FROM UserInfo WITH (NOLOCK)

INNER JOIN GroupMembership WITH (NOLOCK) ON UserInfo.tp_ID = GroupMembership.MemberID

INNER JOIN Groups WITH (NOLOCK) ON GroupMembership.GroupID = Groups.ID

JOIN OPENXML(@idoc, '/Root/Group', 0) WITH (Name VARCHAR(100)) AS g

ON dbo.Groups.Title = g.Name

ORDER by dbo.Groups.Title, dbo.UserInfo.tp_Title

END

Sample Query

EXEC usp_SelectUsersByGroups '<Root><Group Name="HR Team Member"></Group><Group Name="Acct Sr Managers"></Group></Root>'

Results

UserID

UserTitle

UserLogin

UserEmail

GroupsID

GroupsTitle

23

Doe, John

acmedomain\jdoe

jdoe@acmedomain.com

14

HR Team Member

20

Doe, Jane

acmedomain\jadoe

jadoe@acmedomain.com

14

HR Team Member

2

Smith, Bob

acmedomain\bsmith

bsmith@acmedomain.com

14

HR Team Member

24

Yamamoto, Kazue

acmedomain\kyamamoto

kyamamoto@acmedomain.com

14

HR Team Member

46

Nakamura, Ichiro

acmedomain\inakamura

inakamura@acmedomain.com

14

HR Team Member

7

Horowitz, Rick

acmedomain\rhorowitz

rhorowitz@acmedomain.com

15

Acct Sr Managers

3

Davis, Jiro

acmedomain\jdavis

jdavis@acmedomain.com

15

Acct Sr Managers

user's list of web sites in SharePoint : SharePoint

SELECT DISTINCT w.Title, r.WebId, r.Title AS accessLevel, ui.tp_Login,
ui.tp_DomainGroup, ui.tp_Title, w.FullUrl, w.AlternateCSSUrl,
p.ScopeUrl
FROM RoleAssignment AS ra INNER JOIN
Roles AS r ON r.SiteId = ra.SiteId AND r.RoleId
= ra.RoleId INNER JOIN
UserInfo AS ui ON ui.tp_SiteID = ra.SiteId AND
ui.tp_ID = ra.PrincipalId INNER JOIN
Sites AS s ON s.Id = ra.SiteId INNER JOIN
Perms AS p ON p.SiteId = ra.SiteId AND p.ScopeId
= ra.ScopeId INNER JOIN
Webs AS w ON w.Id = p.WebId

 

copy from Forum

Discovering all workflows in SharePoint farm

I got a question from user to identify all workflow in my Sharepoint environment. I tried to identify some out of box option but could not find one. After that I have to put my DB cap on and write query on backend content database of MOSS farm to get list all sites and sub containers which are having active workflow running in them. Here is the query :


select distinct(w.fullurl) as SiteName, L.tp_Title as ListName from webs w,workflow wf , Alllists L where w.id=wf.webid and w.siteId=wf.siteId and wf.listId=L.tp_Id

 

Copy from Here

Wednesday, December 8, 2010

Clean Up Event ID 7888 and 5553.

see the error at your event viewer ?

 

 

failure trying to synch site 51d6d8b3-9ee0-4af3-af2f-fa5f78cc9651 for ContentDB 840eac13-c23e-4123-9fa2-9d2975d0ff13 WebApp 53ea7eb2-ec0f-4250-9d87-1550224223b8.  Exception message was Cannot insert duplicate key row in object 'dbo.UserMemberships' with unique index 'CX_UserMemberships_RecordId_MemberGroupId_SID'.
The statement has been terminated..

 

 

Solution here:

http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/11af8a85-0fca-466d-beac-d29d03297edf

 

solution summary :

 

  • Run 'stsadm- o preparetomove -Site <http://whatevertheguiltysiteis/> -OldContentDb <GUID of the contentdb listed in event log error>'
  • Run 'stsadm -o sync -deleteolddatabases 0'

FQA:

  • How do i know which site is it by GUID ?
    • -- QUERY#1 Run this against your config database, this will give you the content database name

      SELECT *
      FROM [SharePoint_Config].[dbo].[Objects]
      WHERE ID='<Content DB Id from error msg>'

      --QUERY#2
      -- Run followign queries against the database name returned by the QUERY#1

      SELECT [FullUrl]
      ,[Id]
      ,[RootWebId]
      FROM [WSS_Content_##_03].[dbo].[Sites]
      WHERE ID='<Site ID from event log message>'








      or you can run the query at sitemap table at the config database , then you will see all the site under this content db , then manually go to find out on the Path column of the site URL.



      SELECT [Id]

            ,[ApplicationId]


            ,[DatabaseId]


            ,[Path]


            ,[Pairing]


            ,[Status]


            ,[Version]


            ,[RedirectUrl]


            ,[HostHeaderIsSiteName]


        FROM [MOSS_CONFIG_DB].[dbo].[SiteMap]


      where [DatabaseId]='<Content DB Id from error msg>'

      Thursday, December 2, 2010

      Create an installation source that includes software updates (Office SharePoint Server 2007)

       

      is time to build another environment, to save some step to install the service pack so we can extract the sp2.exe to update folder at the installer folder then will help direct install during installed. **aslipstreamed

       

      http://technet.microsoft.com/en-us/library/cc261890(office.12).aspx

       

      In server farm deployments, all your Web servers must have the same software update version applied. This means that, before you add a new Web server to an existing server farm, this new Web server must have the same software updates as the rest of the Web servers in your server farm. To accomplish this, we recommend that you follow the procedures in this topic to create an installation source that contains a copy of the released version of the software, along with software updates that match those installed on your server farm (also known as aslipstreamed installation source). When you run Setup from this updated installation source, the new Web server will have the same software update version as the rest of the Web servers in your server farm.

      NoteNote:

      In this article, we use the term software update as a general term for all update types, including any service pack, update, update rollup, feature pack, critical update, security update, or hotfix used to improve or fix this software product.

      Use the updates folder

      To create an installation source, you must add software updates to the updates folder of the released version of the software.

      To use the updates folder
      1. Copy the files from the released version source media for the product to a folder that you can use as an installation point for the servers in your server farm.

      2. Download the appropriate software update package.

      3. Extract the software update files, by using this command:

        <package> /extract: <path>

        The /extract switch prompts you to provide a folder name for the files, for example, for x86 systems:

        wssv3sp2-kb953338-x86-fullfile-en-us.exe /extract: <C:\WSS> \Updates

        <C:\WSS> is the location to which you copied the files that you extracted from the Windows SharePoint Services 3.0 released version.

        NoteNote:

        You must use the default location for the updates folder. If you use the SupdateLocation="path-list" property to specify a different location, Setup stops responding.

      4. Copy the files that you extracted from the Windows SharePoint Services 3.0 software update package to the updates folder you created in the previous step.

      5. Extract the Microsoft Office SharePoint Server 2007 software update files, by using this command:

        officeserver2007sp2-kb953334-x86-fullfile-en-us.exe /extract: <C:\rtm_product_path> \Updates

        <C:\rtm_product_path> is the location to which you copied the files that you extracted from the Office SharePoint Server 2007 released version.

      6. Copy the files that you extracted from the Office SharePoint Server 2007 software update package to the updates folder containing the source for the released version. You must verify that the Svrsetup.dll file has been copied from the Office SharePoint Server 2007 software update package and you should delete the Wsssetup.dll file.

        ImportantImportant:

        Delete Wsssetup.dll because it may conflict with Svrsetup.dll. Having both Wsssetup.dll and Svrsetup.dll in the updates folder for a slipstreamed installation source is not supported.

      7. You can now use this location as an installation point, or you can create an image of this source that you can burn to a CD-ROM.

        NoteNote:

        If you extracted the software update files to a location to which you had previously copied the source for a released version, the source is updated and is ready to use.

      For more information about using enterprise deployment tools to deploy updates, see the article Distribute product updates for the 2007 Office system.

      You can download the 32-bit or 64-bit edition of Service Pack 2 (SP2) for Office SharePoint Server 2007 at the following location:

      Language template packs

      Use the following procedure to create an installation location that you can use to install the language template packs with software updates already applied.

      To use the updates folder with language template packs
      1. Download the language template pack package for the released product.

      2. Extract the files from the language template pack package.

      3. Copy the extracted files to a folder that you can use as an installation point for the servers in your server farm.

      4. Download the updated language template pack package for the released product.

      5. Extract the files from the updated language template pack package.

      6. Copy these extracted files to the updates folder, in the subfolder in which you stored the files for the released product in step 3.

        You can now use this location as an installation point, or you can create an image of this source that you can burn to a CD-ROM.

      7. To install the language template pack with the software update already applied, run Setup from this location, and then run the SharePoint Products and Technologies Configuration Wizard to complete the configuration.

      Wednesday, December 1, 2010

      Delete old folder script

      nice script from internet again :D http://www.eggheadcafe.com/software/aspnet/31061977/an-easy-script-to-delete-folders-on-a-network-share-that-are-older-than-14-days.aspx

      change at cint( XX ) > XX for how many days old
      Directory > location of the folder you want delete. Network drive also can :)


      Dim Directory
      Dim Noofdays
      Dim FSO
      Dim FSO2
      Dim LogFile
      Dim Folderlist
      Dim folders
      Directory ="C:\Program Files\Microsoft Office Servers\12.0\Logs\UAP\"
      Noofdays=cint(30)
      LogFile="C:\Program Files\Microsoft Office Servers\12.0\Logs\UAP\deleted.txt"
      Set FSO = CreateObject("Scripting.FileSystemObject")
      Set FSO2 = CreateObject("Scripting.FileSystemObject")
      Set oFSO = CreateObject("Scripting.FilesyStemObject")
      '
      If oFSO.FileExists(Logfile) Then
      Const ForAppending = 8
      Set ofile = oFSO.OpenTextFile(LogFile, ForAppending, True)
      Else
      Set oFile = oFSO.CreateTextFile(logfile, true)
      End If
      ofile.writeline "Delete Folders older than 30 days Started   --> " & now()
      Set Folderlist = FSO.GetFolder(Directory)
      Set folders = Folderlist.SubFolders
      For Each d In Folders
      '          msgbox d.name
      '          msgbox d.size
      '          msgbox d.dateCreated
      '          msgbox d.dateLastModified
      '          msgbox d.dateLastAccessed
      tempdirectory = Directory & d.name
      If  datediff("d",d.dateCreated,now()) > Noofdays Then
      FSO2.DeleteFolder(tempdirectory )
      ofile.writeline "Deleting Folder...." & tempdirectory
      if err.number <>0 then
      ofile.writeline cstr(now()) & "    " & Err.description
      err.clear
      end if
      End If
      Next
      ofile.writeline "Delete Folders older than 30 days Completed --> " &now()
      ofile.writeline "--------------------------------------------"
      ofile.close

      Wednesday, November 10, 2010

      Batch file to keep track enumsites with date :D

      SET D1=%date:~-4,10%
      SET D2=%date:~4,-8%
      SET D3=%date:~-7,-5%

      set tarik=%D1%%D2%%D3%

      echo %tarik%

      "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\stsadm" -o enumsites -url http://abc.com > %tarik%.csv

      This report will run at scheduled tasks and the purpose is keep track on the sites in which Database , when user need to restore the sites then we only know which DB need to restore back for the sites.

      Monday, November 8, 2010

      Site Collection Administrator VS Site Owner

      hi hi..one of the question ask by interviewer :D . as i understand is about the recycle bin and quota reach limit contact!!  After that i found the answer here :

       

      http://blogs.sharepointhosting.com/Lists/Posts/Post.aspx?ID=61

       

      About twice a week someone calls or writes into support trying to figure out why their Site Owners cannot reset passwords, manage the recycle bin, or "see everything" in site settings. The answer is pretty easy – you have to be a Site Collection Administrator to be able to perform those actions. The next question we get from Site Owners is "how do I become a Site Collection Administrator?" The answer is easy enough, find out who your current Site Collection Administrator is and ask that individual to promote you to that role. Here's a short tutorial on how to create new Site Collection Administrator.

      Take a look at the graphic below (click for full version). It displays the different views if you will see if you log into the Site Settings portion of your WSS 3.0 site as a member of the site owner's group and then as a Site Collection Administrator. Hint – the Site Collection Administrator's view is the top image. You can easily replicate this for yourself if you want to test it out – just create a test user that is a member of the Site Owners (or similar) SharePoint security group and go to the site settings page.

       

      Extra this are at site setting :D [all the time our permission is Farm admin , then will never notice about this!! ha ha] Now you know? :D

      image

      Tuesday, November 2, 2010

      How to confirm your update item is going to do incremental crawl or not ?

       

      Search problem for the sub sites… unable to get any error from iis, uls log.

       

      so we need go to search database to see can get any information.

       

      in this case is we want to see the specify sites after i add the item in , this item going to involved in incremental crawl or not.

       

      normally we can see the item at MSSChangeLogCookies table before the incremental crawl start.

       

      1. Run this query: select * from dbo.MSSChangeLogCookies order by UpdateTime desc > you will see all the item change going to crawl

      This table keeps track of the last change that the crawler processed for each content database.  You'll want to look at the ChangeLogCookie_new column and you'll see several rows but the output of each will look something like this:

       

      1;0;1b82082a-66eb-47bb-a053-26dde844f5ac;634242420353000000;7403

       

      The GUID 1b82082a-66eb-47bb-a053-26dde844f5ac is the content database id

       

      i already have the Database ID on the sites (go to your content DB , check the database information table will know) , so i will search the results and see my database id is available on the MSSChangeLogCookies.

       

      if you want see how many crawl from the content DB you check, you can use below query on the targert content db:

      select * from eventcache with (NOLOCK) where ID > '’7403'

       

      so the total row is the item goign to crawl

       

      yeah  !! found the item…so go to run the incremental crawl now…but after run also not working!!! ha ha ha…continue checking….DAY 5

       

      here you go for more detail on the .MSSChangeLogCookies :

      http://blogs.msdn.com/b/russmax/archive/2008/11/17/how-to-determine-the-number-of-changes-an-incremental-crawl-will-process-prior-to-initiating-the-crawl.aspx

      http://www.cnblogs.com/awpatp/archive/2010/11/02/1866938.html > Chinese version

      Monday, November 1, 2010

      Crawling history from SSP search DB ( SQL Query)

      Copy from MS:

       

      select * from MSSCrawlhistory

      select h.crawlid,
      c.catalogid,
      c.contentsourceid,
      c.startaddressid,
      typeofcrawl=
      case h.crawltype
      when 1 then 'full crawl'
      when 2 then 'incremental crawl'
      end,
      h.status as status_id,
      status=
      case h.status
      when 4 then 'start'
      when 9 then 'pause'
      when 10 then 'resume'
      when 13 then 'stop'
      when 11 then 'done'
      else 'other'
      end,
      h.requesttime, h.starttime, h.endtime, datediff(n, h.starttime, h.endtime) as 'duration'
      from msscrawlhistory h left join msscrawlcontent c
      on h.crawlid=c.crawlid where h.projectid=1 order by h.requesttime desc

      Tuesday, October 26, 2010

      change sub-site to a new sub-site URL and wanted the old link auto redirect to the new link.

      1. Go to old URL link and open in SPD
      2. Create an .aspx page
      3. Paste the coding in .aspx page(target site URL)
        •  <meta http-equiv="refresh" content="0;url=http://xxxxx/sites/xxxxxxxxxx/yyyyyy">
      4. Right click .aspx page > click Set as Home Page
      5. Rename the .aspx page to default.apsx

      Search log where to see ?

       

      problem with the search no results under some subsites , when can check the crawl log or error ?

       

       

      1. crawl log under SSP
      2. enable ULS for wss query with veberos, then can see the log at ULS log
      3. go to search database to run the query on terfert sites , see have crawl or not :
        • select * from MSSCrawlURL (nolock)
          where DisplayURL like '%/sites/XXXXX%'

      but the problem not yet resolved…to be continue… ha ha

      Monday, October 25, 2010

      Who Deleted my SharePoint Top Level Sites!!!!

      have customer want to know who deleted his top level sites ( aka site collection ).

       

      the easy answer is the one site collection admin or the user have full control permission at your site collection!!

       

      But let say have 100 site collection admin!!! ha ha..so which one is the one??

       

      oh yeah easy to trace is go to the IIS log file then search it at the folder level:

       

      A word or phrase in the file with

      /sites/<sites collection name>/_layouts/deleteweb.aspx ”.

       

      yeah!!! shoot the one delete it!!! ha ha

      Tuesday, October 19, 2010

      -- Query to get all the SharePoint groups in a site collection

       

      Another cool query from sharepointkings

       

      Customer request the report on all the user in the group on the specify sites collection.

       

      -- Query to get all the SharePoint groups in a site collection
      SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.Groups.ID AS Expr1,
      dbo.Groups.Title AS Expr2, dbo.Groups.Description
      FROM dbo.Groups INNER JOIN
      dbo.Webs ON dbo.Groups.SiteId = dbo.Webs.SiteId

       

      -- Query to get all the members of the SharePoint Groups
      SELECT dbo.Groups.ID, dbo.Groups.Title, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login
      FROM dbo.GroupMembership INNER JOIN
      dbo.Groups ON dbo.GroupMembership.SiteId = dbo.Groups.SiteId INNER JOIN
      dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID

       

       

      after query the all the memeber of the group, use excel to remove the duplicate data then you will know how many user in the group :)

      Unable to reorder content type

      Have user copy the list template from another site and want to reorder the content type, when click on the column order at the content type you will see the error :

       

      Object reference not set to an instance of an object.   at Microsoft.SharePoint.ApplicationPages.ChangeFieldOrderPage.OnLoad(EventArgs e)
         at System.Web.UI.Control.LoadRecursive()
         at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

       

      Work around:

      you will not be able to manage the order of your columns on the list settings page again until you switch “Allow management of content types” back to No. When the management of content types if enabled, you can then reorder the columns for each specific content type. You may or may not want this to be your standard setup, so my best practice here would be to turn this feature back off when you are finished managing the content type.

       

      Detail step:

       

      1. change the list setting at Advanced settings and change the Allow management of content types to no. > click ok

      2. under the column, you will see Column ordering. Click on the column ordering and change the order.> click ok when you done

      3. change the list setting at Advanced settings and change the Allow management of content types to yes > click ok

       

      guessing of the root cause:

       

      i think because the content type is attach to the template and no content type created for the sites. once you click on ordering, then will go to search the content type gallery at site level. once cannot found it and error message will show at above. i guess is consider bug :)

      Thursday, October 14, 2010

      Default Recovery Models for MOSS2007 Databases

      Today attend SQL2008 training is talking about what is the different Simple recovery model VS Full recovery Model.Then i am thinking what is the moss2007 setting? Below is the Default Recovery Model.

       

      Simple model :

      • Automatic reclaims the transaction log
      • Allow only Full backups

      Full Model:

      • Require transaction log backup (which mean not automatic to shrink your transaction log file)
      • able to enable recovery the data very near to down time

      * oh ya…please remember all the Database here, because one of my interview is asking about this :) . ha ha..

      image

      Monday, October 11, 2010

      System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘dbName’ database

       

      familiar with this message?  one of my problem always face when restore the shaerpoint content DB from other farm with backup file. today i found out the solution at this blog : http://www.linglom.com/2009/07/08/solved-system-data-sqlclient-sqlerror-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing-dbname-database/

      *if you restore the path to different path of mdf and ldf, you need to change it

      is great to keep it , so i will copy paste here for future reference.

      before start restore the bak file, you need to create the new Database then restore the bak.

      Problem

      You have backup a database on SQL Server 2005. Then, you try to restore the backup file on an existing database and receive the error message below:

      Restore failed for Server ‘SQL Server name‘. (Microsoft.SqlServer.Smo)
      Additional information:
      System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘dbName‘ database. (Microsoft.SqlServer.Smo)
      The backup set holds a backup of a database other than the existing

      This error message indicates that you are going to restore a database on another database which are not the same database. For example, you have backup Northwind database and try to restore the backup to AdventureWorks database, the error will occur.

      Solution

      To solve the problem, you can use the overwrite the existing database option while you’re restoring.

      1. On Restore Database, select Options tab on the left menu. Then, check Overwrite the existing database on Restore options.
        Note: This option will overwrite any existing data on the targeted database.
        Restore Database Option
      2. Try to restore the database, the problem should be gone now.
        Restore Database Successful

      Note: you can also delete the targeted database before perform restore a database. This way also gives the result as same as the solution above.

      Wednesday, October 6, 2010

      Filter the calendar of today event

      The start time at calendar Filter is not available then we cannot use [Today] to filter the item. In this case you just created one column and select Calculated (calculation based on other columns)  then Formula =[Start Time].

       

      after that you can edit the view with filter on column you created is equal to [Today]:

       

      In this example , i created column name as DOB.

      image

      Friday, October 1, 2010

      How to Move Large SharePoint Document Libraries

      Faced some problem to move large SharePoint Document during migration , so found out the solution here.

       

      Scenario : one document library is over 25GB so unable to use stsadm export and import.

       

      Solution: have 2 post is same, so i not sure who is the owner. anyway just like to keep track on the coding and sharing here :)  [This can move different farm :) ]

       

      http://www.law.com/jsp/lawtechnologynews/PubArticleLTN.jsp?id=1202443237483

      http://pravyns.blogspot.com/2010/08/how-to-move-large-sharepoint-document.html

       

       

       

      EXPORT AND IMPORT WITH THE SHAREPOINT OBJECT MODEL

      To accomplish the goal of copying an arbitrarily large document library, and only that document library, from one site to another, we'll need to use a bit of .NET code and the SharePoint object model. The code will perform two broad tasks:

      1. export the source document library to a set of one or more files on disk; and
      2. import the file-set created in step 1 to the target location.

      You could write this code in a number of ways, but for simplicity, I'll create two .NET console applications: ExportDocLib and ImportDocLib. Both these applications must be run on the SharePoint web front-end server of the farm containing the source and target document libraries respectively.

      ExportDocLib exports the source document library to operating system files. To create the application, open Visual Studio 2005 or later, and create a new C# console application. Next, add a reference to the Windows SharePoint Services .NET assembly, and then add "using" statements at the top of the program to reference the Microsoft.SharePoint and Microsoft.SharePoint.Deployment namespaces.

      The rest of the code is fairly straightforward; you will need to provide references to the site collection and the web site within it that contains your document library (I simply referenced the RootWeb property of my site collection because my document library was contained in the top-level web site), a reference to the list to be copied, and information about where to create the export files and export log. I've also instructed SharePoint to retain the security via the IncludeSecurity switch, and to display progress to a command window as the program runs using the CommandLineVerbose switch. There are many other settings you can use, but those shown below are all you'll need for a basic list export:

       

      using System;

      using System.Collections.Generic;

      using System.Linq;

      using System.Text;

      using Microsoft.SharePoint;

      using Microsoft.SharePoint.Deployment;

      namespace ExportDocLib

      {

      class Program

          {

      static void Main(string[] args)

              {

      // Get handle to web and doclib to export

      SPSite site = new SPSite("http://localhost/sites/SITE1");

      SPWeb web = site.RootWeb;

      SPList list = web.Lists["Shared Documents"];

      // Define export settings

      SPExportSettings settings = new SPExportSettings();

                  settings.SiteUrl = "http://localhost/sites/SITE1";

                  settings.FileLocation = @"C:\Export Files\";

                  settings.BaseFileName = "SITE1.cmp";

                  settings.ExportMethod = SPExportMethodType.ExportAll;

                  settings.LogFilePath =

                        settings.FileLocation + "SITE1_export_log.txt";

                  settings.CommandLineVerbose = true;

                  settings.IncludeSecurity = SPIncludeSecurity.All;

      // Add reference to document library to export

      SPExportObject exportObject =

      new SPExportObject(

                              list.ID,

      SPDeploymentObjectType.List,

                              web.ID, false);

                  settings.ExportObjects.Add(exportObject);

      // Export it

      SPExport export = new SPExport(settings);

                  export.Run();

              }

          }

      }

      ImportDocLib is almost a mirror image of ExportDocLib. ImportDocLib will import the document library from the operating system files created by the ExportDocLib program. As before, start by creating a new C# console application in Visual Studio, adding a reference to the Windows SharePoint Services .NET assembly, and by adding "using" statements to reference the two Microsoft.SharePoint namespaces.

      Next you'll create a SPImportSettings object and set its properties to define the location of the import files, the location of the site collection and web site where you want your new copy of the document library, and a location for the import log.

      Although it’s beyond the scope of this article, the RetainObjectIdentity setting is noteworthy because its value will determine whether you can apply subsequent incremental imports to this same document library. For example, you could copy the full library once, and then periodically import only changed or new items from the source into the target library. To enable these subsequent imports, however, the RetainObjectIdentity setting must be set to "true." However, you may not set it to "true" if you will be importing a document library into the same content database as the source library, because all objects in a SharePoint database must have unique object IDs. The most likely scenario for which you would use the RetainObjectIdentity switch is to create a copy of a document library on a different SharePoint farm, and subsequently to refresh that second library with updates from the original.

       

      using System;

      using System.Collections.Generic;

      using System.Linq;

      using System.Text;

      using Microsoft.SharePoint;

      using Microsoft.SharePoint.Deployment;

      namespace ImportDocLib

      {

      class Program

          {

      static void Main(string[] args)

              {

      // Settings for import

      SPImportSettings settings = new SPImportSettings();

      // File & path

                  settings.FileLocation = @"C:\Export Files\";

                  settings.BaseFileName = "SITE1.cmp";

      // Site and web to import to

                  settings.SiteUrl = "http://localhost/sites/SITE2";

                  settings.WebUrl = "http://localhost/sites/SITE2";

      // Set log file location

                  settings.LogFilePath =

                        settings.FileLocation + "SITE2_import_log.txt";

      // Display messages while running

                  settings.CommandLineVerbose = true;

      // Don't retain object GUIDs, only necessary

      // if want to do incremental imports to same list

      // at a later time

                  settings.RetainObjectIdentity = false;

      // Keep security, versions, and date/time stamps

                  settings.UpdateVersions = SPUpdateVersions.Append;

                  settings.UserInfoDateTime =

      SPImportUserInfoDateTimeOption.ImportAll;

      // Import it

      SPImport import = new SPImport(settings);

                  import.Run();

              }

          }

      }

       

      You might correctly observe that both the ExportDocLib and ImportDocLib routines can be combined into a single program. This would simplify the process in that you wouldn't need to execute two separate programs to complete the copy process. But when copying a document library from one SharePoint farm to another, you will need to run ExportDocLib on a WFE server in the source farm, and the ImportDocLib on a WFE in the target farm. Keeping them separate gives you the flexibility you need in such instances.

       

      CONCLUSION

      With just a bit of .NET coding you can copy document libraries of arbitrary size from one site to another, within or between farms, without the need to purchase a third-party product. With a bit of additional coding you can add the capability of applying incremental updates from a master document library to a copy, thus keeping the secondary library in synch.

      At Fenwick & West we have found this technique invaluable for migrating large document libraries between SharePoint farms, filling the gap between the built-in capability to save small libraries using a list template, and backing up full site collections using the backup and export functions of STSADM.

      Wednesday, September 29, 2010

      About TEMP folder concept

      Error : STSADM.EXE Export “There is not enough space on the disk”

      I was recently doing an export for a pretty big MOSS site and I ran across the following error while using the following stsadm command:

      stsadm -o export -url http://servername/site -filename D:\backup\sharepoint.cab -includeusersecurity -overwrite

      We verified that the C drive had 1 GB of space and the D drive had 50 GB of space. Simple enought right. Wrong!

       

      After some head scratching, we figured out that the export uses the C drive to store the entirety of the backup files before writing it to the D drive. More specifically, the location pointed to by the TEMP environment variable was being used for the backup.

       

      The solution we found was to change the TEMP and TMP environment variable to point to a temporary folder on the D drive and then do the backup. (I had to also logout and login in order for the variables to take effect).

       

      detail step to change the temp is here

       

       

      Information from : http://aboutdev.wordpress.com/2008/03/21/stsadmexe-export-there-is-not-enough-space-on-the-disk/

      Thursday, September 23, 2010

      Tech insight 2010 Penang

      22-23 Sept 2010 at USM penang, so far for  the SharePoint related session only have 3 but this also help to increase some sharepoint 2010 knowledge.Today have one session is sharing on the SharePoint list Improvement , is look great a lot limitation user cannot get now already available.


      Example,

      • unique column ( something like primary key for the list , cannot duplicate)
      • validate the columns ( using excel formula)
      • Lookup Field Relationship Behavior –  behavior that can be enforced includes; Project multiple fields into a child list; Joins between lists, Relational integrity between child and parent lists – this brings up new possible security issues & considerations
      • List Relationships & Data Integrity – one-to-many allows you to trigger cascade delete and restrict delete
      • more detail you can get from here

      ok let me finish the tomorrow session then will share more :)

      Monday, September 13, 2010

      Set searchadcustomquery back to OOB setting

      how do you reset the searchadcustomquery to oob setting? which is <Property Exist = “No”> , normally used stsadm the value will showed as <Property Exit=”Yes”, Value=””>”.

       

      to set the value back to <Property Exist = “No”>  , we can use simple coding as below to reset it.

       

      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
      using Microsoft.SharePoint;
      using Microsoft.SharePoint.Administration;

       

      namespace reset_customQuery
      {
          class Program
          {
              static void Main(string[] args)
              {
                  using (SPSite site = new SPSite(@"http://abc.com"))
                  {
                      SPWebApplication apps = site.WebApplication;
                      //reset to null
                      apps.PeoplePickerSettings.ActiveDirectoryCustomQuery = null;
                       apps.Update();

                      Console.WriteLine("Done");
                  }
              }
          }
      }

       

      to checking your value you can use the stsadm below:

       

      stsadm -o getproperty -url "http://abc.com" -pn peoplepicker-activedirectorysearchtimeout
      stsadm -o getproperty -url "http://abc.com" -pn peoplepicker-distributionlistsearchdomains
      stsadm -o getproperty -url "http://abc.com" -pn peoplepicker-nowindowsaccountsfornonwindowsauthenticationmode
      stsadm -o getproperty -url "http://abc.dhl.com" -pn peoplepicker-onlysearchwithinsitecollection
      stsadm -o getproperty -url "http://abc.dhl.com" -pn peoplepicker-searchadcustomquery

      Wednesday, September 1, 2010

      Workflow History Hidden list

      Workflow error ? where to check ?  as what i know we have 3 place to check the log :

      Friday, August 27, 2010

      Basic sharing on Code Access Security (CAS)

      Controlling access to system resources ( Dir structures and file) 

      WSS have 2 security approaches :
      • user identity-based
      • code identity-based [digital certificate or URL or site]
      WSS is use .NET Framework , which is using code identity-based approach so call CAS


      Levels of trust
      • Full > unmanaged code
      • High > cannot call unmanaged code and enterprise service but can send email using SMTP servers , also can access sockets and file system
      • Medium >  SMTP and SQL but cannot access sockets and registry
      • Low > read only application virtual dir and no network connectivity
      • Minimal > have execute permission only
      WSS have 2 policy ( Virtual server extedned to wss this policy will applied to)
      • wss_minimaltrust.config
      • wss_mediumtrust.config
      WSS security setting for CAS:
      • machine.config > c:\windows\micorsoft.net\framwork\V2.0.50727\config
      • web.config

      Web.config located

      • top level conetn root > C:\Inetpub\wwwroot > configuration for web server
      • web part resoucces of the global assemy cache
      • CONFIG ….\web server extensions\60\config\ > define for other extending vs
      • ISAPI ….for the /_vti_bin
      • LAYOUTS > for /_layouts
      • ADMIN > SharePoint CA

      Thursday, August 26, 2010

      How to change the Sharepoint theme template option

      C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\1033

       

      have one file name SPTHEMES.XML , this is the file control the theme for SharePoint. if you want to add some custom theme make sure here is added

       

      1033 is for English version , 1031 is German…if you have more so different language keep at different folder .

      Monday, August 23, 2010

      Utility to quickly reapply themes to sites collection

      After MOSS SP2 Deployment have faced some problem on the theme, so we need to edit the CSS format then to make it take effect on all sites.

      now the problem come in …we have so many sites and subsites how going to get it done? as you know we need to reapply the theme then the CSS only take effect.

      Luckily have some good utility tool at coplex , is so call retheme.exe.

      this help a lot, you can download it from CodePlex.

      Below is the coding from the Codeplex reTheme :)


      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
      using Microsoft.SharePoint;

      namespace reTheme
      {
          class Program
          {
              static void Main(string[] args)
              {
                  string siteUrl;
                  if (args.Length == 0)
                  {
                      Console.Write("Site Collection URL:");
                      siteUrl = Console.ReadLine();
                  }
                  else
                  {
                      siteUrl = args[0];
                  }

                  try
                  {
                      using (SPSite site = new SPSite(siteUrl))
                      {
                          foreach (SPWeb web in site.AllWebs)
                          {
                              Console.Write(web.ServerRelativeUrl + ": ");
                              try
                              {

                                  string themeName = web.Theme;
                                  web.ApplyTheme("");
                                  web.ApplyTheme(themeName);
                                  Console.WriteLine("OK");
                              }
                              catch (Exception e)
                              {
                                  Console.WriteLine(e.Message);
                              }
                          }
                      }
                  }
                  catch (Exception e)
                  {
                      Console.WriteLine(e.Message);
                  }
              }
          }
      }

      Tuesday, August 10, 2010

      Why There is a Shortage of SharePoint Experts

      Copied from here : SharePoint expert are we on the right path ? :D

       

      This post started out as a short reply to a discussion on LinkedIn about this post: Why there is a shortage of SharePoint Experts. Once I hit the fourth paragraph I decided it made a better blog post then a comment. :)

      There is a shortage of SharePoint experts because the learning curve for the base technologies is large and the learning curve for the breadth of SharePoint functionality is even larger.

      Consider SharePoint 2010 Server Enterprise edition. You can deploy the platform as a public facing site with heavy customization and branding, as a team collaboration portal, as a business intelligence and information delivery, as a stand-alone or connected enterprise content management system, as a search appliance, as a business process automation (workflow) platform, and as a basis for custom applications. There are even more uses, but hopefully you get the basic idea – you can use it for lots of unrelated things.

      To call yourself an expert, you should understand most or all of these core uses and the related tools used by end users, developers, and administrators. The SharePoint learning curve is honestly measured in years - assuming that you have a few years' experience building either Windows applications or building and administering complex server systems.

      Fortunately, most organizations do not need a full-time SharePoint expert to be successful. Most deployments involve a small subset of the functionality. Most people who work in a corporate setting will use a subset of the tools and fulfill a role in a larger team. If you are looking to implement SharePoint, my advice is to spend the money on a good consultant(s) who is an actual SharePoint expert to create a good foundation and focus on hiring or training full time staff to participate in the build-out and maintain the completed system. Once you are up and running, say goodbye for now to the experts and use them on an ongoing basis only as needed.

      The multi-year learning curve plus the sharp upward demand curve means that the supply of experts will be well short of demand for several more years. The supply of people who have serious SharePoint experience will obviously increase along with adoption, but there is no reason you should expect someone who worked in a large heavily controlled collaboration environment where the focus was to maximize stability and minimize risk through aggressive governance to know much about building high-quality public facing sites with significant branding and customization (or vice-versa). That's not to say the person could not make the switch, but it is to say that person is not an expert.

      For those organizations looking to find a "SharePoint expert" on the cheap - don't hold your breath. If you are offering a rate that is comparable to that earned by a qualified Web developer in your area, ask yourself why someone who is already a qualified to do work that pays the Web developer rate as a Web developer is willing to provide the additional value of their additional 3+ years of learning in the SharePoint domain at no additional cost to work on SharePoint. Instead try to hire to the narrower skill set you actually need.

      The pool of people who have solid experience in one of WCM, ECM, BPA, BI, information architecture, system administration, etc. who can be very productive in a SharePoint environment with the right training and mentoring is large. Build your team from this pool. Trying to build a team of SharePoint experts is much harder and more expensive. More importantly, it's probably an unnecessary and poor strategy.

      Posted at 11:30 PM by Doug WareNo presence information | Category: Random Whatnot | Permalink | Email this Post | Comments (3)

      Comments
      great post

      I started working with SharePoint back in 2004, was a member of the hosted SharePoint team at Microsoft (now called BPOS-Dedicated), and now work for a SharePoint ISV (echoTechnology, recently acquired by Axceler), and actively write and speak on SharePoitn solutions -- and yet I still feel like a newbie. I know several MVPs and MCMs who are "the" experts on the SharePoint platform who find themselves constantly learning as the platform growns and develops, and especially as partners and customers use it to create new solutions.
      But here's my point for responding: If you're looking for SharePoint expertise, you really need to focus on the business problem you're trying to solve rather than find a generalist. Someone with deep vertical experience and some basic SharePoint training is probably a better bet than someone with years of SharePoint but new to your business.

      @buckleyplanet at 8/6/2010 1:59 AM

      Nice response

      The idea that the competency takes years to achieve is an interesting angle I did not consider - I saw it as a skill set developers are not interested in.

      Nadir Kamdar at 8/6/2010 11:39 AM

      Lack of Investment

      As a certified Master, I guess I am one of the *experts*, and I can attest to the enormous learning curve on SharePoint including not only the actual technical skills and APIs, but also the problem domains related to the solutions that SharePoint provides a platform for: Business Intelligence, Knowledge Management, Social Computing, Business Process Automation, etc. These all include significant learning burdens beyond just the technology.
      My observations on the lack of SharePoint developers more firmly points a finger a broad problem within the IT industry: Organizations are no longer investing in employees who work in IT favoring to advocate hiring specialists that already have the skills to solve the problem at hand.
      - the learning curve is steep and takes a lot of time
      - organizations don't yet recognize the learning curve is steep
      - organizations don't want to provide the required training
      - organizations don't want to pay more for those skills
      - organizations don't provide an environment where learning on the job is really tolerated anymore (how can you accurately estimate how long something will take or cost if you have never done it before)
      There just isn't a lot of incentive for developers to absorb the learning costs for very little return. IT in general is already suffering from long hours, understaffed projects, and a new technology release cycle that makes it challenging just to keep your skills current, let alone learn significant chunks of new skills. I have seen very few IT organizations in recent years where the IT staff were not *just barely hanging on*.
      To take advantage of the SharePoint platform and the considerable features it can provide for a Business Productivity platform, they are simply going to have to change their IT resource management strategies to make appropriate investments in skills development. Until they do, I see the shortage of qualified SharePoint Developers being in short supply for many years to come.

      Chris Beckett / SharePoint Bits at 8/6/2010 8:30 PM

      The faster way get the enumsites report in excel

      Just notice have the easy way for you to get the nice report with the stsadm enumsites command. you can see the top level sites , owner, content db report between one minute!!! cool !!

      stsadm -o enumsites -url http://abc.com > sitelist.xml
      Then just opened up the xml file with excel2007. (really beginning to like the features in this version)

      It warned that there was not an associated schema, would I like to create on from the file, ‘yes Please’!

      And now we can sort, filter, sum, etc.

      Wednesday, August 4, 2010

      Great SharePoint Tutorials site to share

      here have some freebies to share from internet, if you like to have some training tutorials for your SharePoint user here is have good one.

       

      Video Tutorials > easy to learn :D enjoy ya

       

      http://www.fpweb.net/sharepoint-tutorials/

    • Templates & Navigation
    • Create a SharePoint Group
    • Delete a Group
    • Create a Site Template
    • Create a List Template
    • Change Title Description & Logo
    • Change Quicklist Order
    • Add Quicklist Link
    • Top Link Bar - Change Order
    • Top Link Bar - Delete Item
    • Top Link Bar - Add Item
    • Enable Tree View
    • Explorer View
    • Log out
    • Site & User Administration
    • Add a new User
    • Reset User Password
    • Create a Site Collection
    • Delete a Site Collection
    • SharePoint Designer - Backup Site
    • Check Site Usage
    • Regional Settings
    • Set up Anonymous Access
    • Permissions & Alerts
    • Set Document Permissions
    • Library Permissions
    • List Permissions
    • Create Email Alerts
    • Delete an Email Alert
    • Documents & Tasks
    • Assign Tasks
    • Deassign Tasks
    • Restore Deleted Item
    • Web Parts
    • Add Web Parts
    • Create a Web Part Page
    • Content Editor Web Part

      • SQL query to find "DayLastAccessed" in portal's Site DB

        is time to do clean up for the SharePoint environment , have 2 option i know we can do that. one is manual one is auto :P ha ha ha

         

        Auto one you can setup at CA, Site use confirmation and deletion  /_admin/applications.aspx . not sure how correct is it!! ha ha

         

        but i prefer manual one.so use below query to check day last accessed , so we can guess is still used or not.

         

        SELECT     FullUrl AS 'Site URL', TimeCreated, DATEADD(d,
        DayLastAccessed + 65536, CONVERT(datetime, '1/1/1899', 101)) AS
        lastAccessDate
        FROM Webs
        WHERE (DayLastAccessed <> 0) AND (FullUrl LIKE N'sites/%')
        ORDER BY lastAccessDate

        Useful SQL Queries to Analyze and Monitor SharePoint Portal Solutions Usage

         

        keep track on what i can get from internet about SQL query for SharePoint :

         

        i am looking for last access to sites query, but unable to find..but found some other interesting one.. at below from this link

         

         

        A List of SQL Queries

        • Top 100 documents in terms of size (latest version(s) only):

          Collapse

          SELECT 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' DESC



        • Top 100 most versioned documents:

          Collapse



          SELECT 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)' desc



        • List of unhosted pages in the SharePoint solution:

          Collapse



          select 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



          select 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



          select 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' desc



        • List of top level and sub sites in the portal and the number of users:

          Collapse



          select  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' desc



        • List of all portal area:

          Collapse



          select 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



          select 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



          select 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



          select COUNT(*) from webs
          where fullurl like '%sites%' AND fullUrl <>
          'MySite' AND fullUrl <> 'personal'



        • List of all list/document libraries and total items:

          Collapse



          select  
          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 desc


          Note: 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



          select  
          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 desc



        • List of image libraries and total items:

          Collapse



          select 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 desc



        • List of announcement list and total items:

          Collapse



          select 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 desc



        • List of contact list and total items:

          Collapse



          select 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 desc



        • List of event list and total items:

          Collapse



          select 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 desc



        • List of all tasks and total items:

          Collapse



          select  
          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 desc



        • List of all InfoPath form library and total items:

          Collapse



          select  
          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 desc



        • List of generic list and total items:

          Collapse



          select  
          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 desc



        • Total number of documents:

          Collapse



          SELECT 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



          SELECT 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



          SELECT 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



          SELECT 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



          SELECT 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



          SELECT 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



          SELECT 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



          SELECT 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



          SELECT 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



          SELECT 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



          SELECT 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')