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

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

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.

Note:

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.

   Note:

   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.

   Important:

   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.

   Note:

   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:

• The 2007 Microsoft Office Servers Service Pack 2 (SP2) (http://go.microsoft.com/fwlink/?LinkID=148352&clcid=0x409).

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.

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