Monday, March 31, 2008

getting the lastmodifieddate of sharepoint sites

code snipet
using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;

namespace ConsoleApplication2
/// Summary description for Class1.

class Class1
/// The main entry point for the application.

static void Main(string[] args)
// TODO: Add code to start application here
SPGlobalAdmin globalAdmin = new SPGlobalAdmin();
SPVirtualServerCollection vServers = globalAdmin.VirtualServers;
foreach (SPVirtualServer vs in vServers)
foreach (SPSite site in vs.Sites)
string LocationPath = "http://localhost";
SPSite site = null; //Site object
Uri url = new Uri(LocationPath);
SPGlobalAdmin globalAdmin = new SPGlobalAdmin();
SPVirtualServer virtualServer = globalAdmin.OpenVirtualServer(url);
//Get all sites from SharePoint location.
for(int cnt=0; cnt < virtualServer.Sites.Count;cnt++)
site = virtualServer.Sites[cnt];
//SPWeb web = site.RootWeb;
foreach (SPWeb web in site.AllWebs){
//Console.WriteLine(" new root web \r\n");
foreach(SPWeb subWeb in web.GetSubwebsForCurrentUser())
//Get the site and web url.
Console.Write("SITE NAME : " + subWeb.Title + " \t");
Console.Write("SITE URL : " + subWeb.Url + " \t");
Console.Write("Site Modified: " + subWeb.LastItemModifiedDate + " \r\n" );
catch(Exception ex)
Console.WriteLine("ERROR WEB MESSAGE: " + ex.ToString());
web.Close(); //Close objects
catch(Exception ex)
Console.WriteLine("ERROR VIRTUAL SERVER MESSAGE: " + ex.ToString());
site.Close(); //Close site object
globalAdmin.Close(); // CloseSPGlobalAdmin object.


// public void RenameRootSite(string newSiteName, string siteURL)
// {
// SPSite wssSite = new SPSite(siteURL);
// SPWeb wssWeb = wssSite.RootWeb;
// wssWeb.Title = newSiteName;
// wssWeb.Update();
// }


Thursday, March 27, 2008

fixed orphaned SQL Server users

EXEC sp_change_users_login 'Report'
EXEC sp_change_users_login 'Auto_Fix', 'user'
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

new toys

Just bought a HP ML110 G4 server and an Adaptec 2610SA RAID card, I will use them to build a test environment for VMWARE ESX Server....can't wait for it to arrive now...

Tuesday, March 11, 2008

Move DB Server for Epicor E4SE & eBackoffice

Draft notes:
1. Modify site.config & web.config for e4se site, security manager;
2. SQL schedule task for BI.
3. Crystal Report Server - nothing here.
4. SQL Server - Linked Server
5. eBackOffice - SQL Alias
6. sharepoint site - change configuration database and set content databases
7. Project server -> use conninfo.exe utility
8. ebackoffice created objects (masterlst table etc) under master db, will need to move them to new server and assign appropriate permission.

E4SE & EBO installation notes

just a draft in case I forgot it later...
1. when install on win2k3 R2, turn off Data Execution Prevention;
2. WSS 2.0 integration...copy the customized web service dll & asmx, aspx files, adjust security settings on WSS Server.
3. Crystal Reports Server,turn off DEP, also copy customized U2LE4SE1.dll files to system32 folder.
4. BPM, re-initialize, make adjustment to some certain events settings.
5. IIS worker group etc
6. EBO -> might need service pack or replace certain dll files (regapp.bat etc).
7. might need to create ODBC connections or Linked servers...
8. DTC security settings
9. MSMQ settings
10. SMTP settings
11. cashsheet upload...iSYSUtilities.dll on citrix/terminal server...
12. eBackOffice installs objects onto master table, if you are restoring database, make sure to copy these. be continued...

Monday, March 10, 2008

Identifying db locks

performance monitor -> locks objects
might be caused by I/O bottleneck
try to use index for update and delete
SP_INDEXOPTION : page lock, row lock, table lock ...etc
use nolock for select (dirty read)


--shwo long running process--
SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)

SELECT spid, waittime, lastwaittype, waitresource
FROM master..sysprocesses
WHERE waittime > 10000 --The wait time is measured in milliseconds
AND spid > 50

Wednesday, March 5, 2008

15401 error

Error 15401: Windows NT user or group '%s' not found. Check the name again.

SELECT name FROM syslogins WHERE sid = SUSER_SID ('YourDomain\YourLogin')

sp_revokelogin 'yourlogin'

bear in mind that if user changes logon ID, you will need to remove the previous ID to add the new one.

Sunday, March 2, 2008

find rate category a resource belongs to

I was doing a task this morning which will need to find the rate category a resource belongs to, each resource category has a rate and the resource has its own rate. what I need to do is use the individual rate to match the resource category's rate and find the nearest one.

the resourcecategoryBands table has the following fields: ResourceCategoryCode, Rate

after some thinking, I came up with the following solution:

select top 1 @ResourceCategoryCode = ResourceCategoryCode from ResourceCategoryBands where CategoryType = 'FEES' order by abs(Rate-@IndividualRate)