ERP, CRM evaluation

Trying to evaluate some ERP systems, namely Dynamics AX, Dynamics CRM as well as some open source ERP systems, I found a few open source ERP systems that looks promising, such as Compiere (JBOSS+Oracle), Openbravo (Tomcat+Postgres).
will see how this goes.

outlook com addin won't release .msg file handle

I wrote a outlook com addin which will popup a dialogue box when user click on 'replyall' button to confirm whether they do want to reply to all.
However, after using it for a while, we noticed one bug:
If you open a saved .msg file and close it then you won't be able to open it for a second time.
According to Ken Slovak's answer, I will have to call ReleaseComObject method to release the handle..however, I can't seem to be able to implement this initially.
After spending hours working on it, it finally turn out that I will have to release both the inspector and mailitem objects using releasecomobject method.
And now the com addin is back to work again....

Partition Alignment in SAN

http://support.microsoft.com/default.aspx?scid=kb;en-us;929491&sd=rss&spid=3198

vmware commands

[root@localhost root]#vmware-cmd /vmfs/volumes/xxx/ESXVC/ESXVC.vmx start
[root@localhost root]#vmware-cmd /vmfs/volumes/xxx/ESXVC/ESXVC.vmx getstate
[root@localhost root]#vmware-cmd /vmfs/volumes/xxx/ESXVC/ESXVC.vmx stop

to be continued...

Conditional URL Redirection using ISA & Apache

During the process of migrating wss2 to moss, we decided to put part of the sites on production whilelist leave some other stuff on old platform, hence there is a need to setup URL redirection.
the scenario is as following:

wss2.mydomain.com/currentSites ==> moss.mydomain.com/sites/currentSites
wss2.mydomain.com/oldsites ==> not diverting
wss2.mydomain.com/* (any other sites except the above) ==> moss.mydomain.com/*

this gave us a challendge to get this done, after playing with iis url rewritter, I realized that it is far less better than Apache URL_Rewritter so I decided to use apache.

for the conditional diversion based on path, I believe that can be done by using url_rewritter's proxy feature but it is bit cumbersome to set it up.

Noticing that ISA can publish sites based on paths, I created two web publishing rules to do the job: first one to publish oldSites and point it to the old server, second on to publish any other requests for wss2 domain and point them to the linux box.

within the linux box, apache is setup to divert all traffic based on the url pattern.
all currentSites URLs will have 'sites' added to them while other request will only get its host name replace.

we also believe that ISA might be able to take care of all this work, however since I am bit lazy to find it out and the current solution works fine. I guess we can live with it.

SQL Agent Job notification via Database Mail

A few things to do before get it to work:
>enable database mail and setup default profile
>test database mail
>setup sql agent to use database mail as alert system
>restart sql agent
>create a test job.

remotely enable remote desktop

http://www.petri.co.il/remotely_enable_remote_desktop_on_windows_server_2003.htm

then you can use shutdown /m \\machinename /r /t XX to remotely restart the computer.

ASP.NET MVC

I learnt a bit about j2ee a while ago and did some small prjects as well...
However, as I am working in the fields of Microsoft products, I haven't touched java ever since that time.
I noticed asp.net MVC a while ago and tried to catch up bit with the new thing, it is quite interesting that asp.net MVC is almost a clone of java MVC stuff....anyway, it is probably a good thing coz it will be easier to switch between asp.net and java now.

Iterating through webparts on sharepoint page

we are in the middle of upgrading wss2 to moss2007, however, some webparts didn't work quite well after the upgrade. I guess part of the reason is because the upgrade was done using third-party software. To fix up the issue, I turned up with a solution of iterating through sites and then webparts on each site, update their properties manually via the program...and it seems to be working fine..

start/stop service remotely

sc \\MachineName STOP/START "Service name"
or netsvc ...?

Sharepoint Ajax Server Time Web Part

Following mike's article at: http://sharepoint.microsoft.com/blogs/mike/Lists/Posts/Post.aspx?ID=3
A web part displaying server time is really easy.
Thanks for the great article...

Find Objects in SQL Server

*********************************************************
** Find The Names And Types Of The T-SQL Objects
** Used In A Stored Procedure
**
** By: Dave Vroman
*********************************************************/
DECLARE @ProcName VARCHAR(50)
SET @ProcName = 'MyTestProc'

SELECT [Name],
CASE WHEN xType = 'C' THEN 'CHECK Constraint'
WHEN xType = 'D' THEN 'Default Or DEFAULT Constraint'
WHEN xType = 'F' THEN 'FOREIGN KEY Constraint'
WHEN xType = 'L' THEN 'Log'
WHEN xType = 'FN' THEN 'Scalar Function'
WHEN xType = 'IF' THEN 'Inlined Table-Function'
WHEN xType = 'P' THEN 'Stored Procedure'
WHEN xType = 'PK' THEN 'PRIMARY KEY Constraint (Type Is K)'
WHEN xType = 'RF' THEN 'Replication Filter Stored Procedure'
WHEN xType = 'S' THEN 'System Table'
WHEN xType = 'TF' THEN 'Table Function'
WHEN xType = 'TR' THEN 'Trigger'
WHEN xType = 'U' THEN 'User Table'
WHEN xType = 'UQ' THEN 'UNIQUE Constraint (Type Is K)'
WHEN xType = 'V' THEN 'View'
WHEN xType = 'X' THEN 'Extended Stored Procedure'
ELSE xType END AS xType
FROM sysobjects
WHERE id IN
(SELECT sd.depid FROM sysobjects so, sysdepends sd
WHERE so.name = @ProcName AND sd.id = so.id )

--------------------------------------------------
SELECT Distinct SO.Name, SO.Type
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
--AND SO.Type = 'P' --U,P,V
AND SC.Text LIKE '%ObjectsName%' --need to be replicated
ORDER BY SO.Name
------------------------------------------------------

SQL Server Management Studio & SQL Server 2000 SP4

when trying to connect to a named instance of SQL Server 2000 sp4 using Management Studio, the following error message appears:
===================================

Cannot connect to SERVER\INSTANCE.

===================================

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)


However, I am able to connect by putting in the server name as 'sqlserver, portno',
I can also connect to another named instance without problem, the only difference is the one having issue is a cluster server....weird...

How to send e-mail without using SQL Mail in SQL Server

used it a while ago...just keep a record here...

http://support.microsoft.com/kb/312839

Get Citrix Presentation Server Client working under linux and firefox

Once I was working as a webmaster for a small IT firm and was taking care of linux servers, recently I am more focusing on Windows platforms.
I think it is neccessary to keep up with my linux skills these days so I got fedora 9 installed on my desktop.
I noticed that Citrix Presentation Server has a linux client and decided to give it a go.
However, even after I imported both the site certificate and root certificate, firefox still complained about rootCA not being trusted. I did a bit search and found out that you will need to place the rootCA.crt file under ./ICAClient/linuxx86/keystore/cacerts to make it work.
I now can connect to my work Desktop via Citrix now! and it is running under windows of course.

In addition, I am now using rdesktop to connect to my VMWARE ESX VC Server and had VC client installed on the VC server, this way I can do a lot from my linux box now!

DNS-323 Print server issue

I got a D-Link DNS-323 which serves as a network storage, it also comes with a network print server, for some reason the network print server stopped working a while ago.
I did a bit search and firstly check the hidden .lpd directory and it wasn't there.
I created it and it didn't help....well after looked into it further, it seems the new firmware doesn't use that directory. instead it use .systemfiles folder. the folder was there, thus I changed it permisson to 777 and it is working now...

Deploying outlook 2007 VBA code using group policy with digital signature

1, write the code;
2, generate code signing certificate using enterprise CA;
3, Sign the macro code with certificate;
4, copy VBAProject.otm and the certificate to netlogin folder;
5, create GPO, link a logon script to copy vbaproject.otm to user profile and create a certificate rule to publish the certificate

Customizing MySite in MOSS and adding report viewer web part

The following two articles are definitely quite valuable information if you want to customize my site under moss/sharepoint 3.0:
https://blogs.msdn.com/sridhara/archive/2007/05/19/customizing-mysite-in-moss-2007.aspx
http://blogs.msdn.com/sharepoint/archive/2007/03/22/customizing-moss-2007-my-sites-within-the-enterprise.aspx

Qhile following these instructions and added sql server 2000 report viewer web part, extra problems arose: while we can assign reportmanagerurl and reportpath to the report view web part, assigning string values to toolbarsize property and height/width property won't work. After examining rswebpart.dll file by using 'Reflector', it is clear that the web part won't accept string as input for toolbarsize property, instead it will only accept a typed variable.
Here is the solution(use the second link above as example):
under 'mysitecreatepart' project, add a reference to rswebpart.dll,
and add the following line on the top of partcheck.cs file:
using Microsoft.ReportingServices.SharePoint.UI.WebParts;
inside partcheck.cs's SetWebPartProperties method, add the following code into appropriate position:

// NOTE: Hack to get around the 'ambiguous match' error
// for the Height property.
if (String.Compare("Height",
wpa.Properties.Property[p].Key, true) == 0)
{
xWp.Height = wpa.Properties.Property[p].Value;
continue;
} //end height hack

//hack to setup toolbarsize property
if (String.Compare("ViewerToolbarSize",
wpa.Properties.Property[p].Key, true) == 0)
{
//I only need to get rid of toolbar thus use a static value here... xWp.GetType().GetProperty(wpa.Properties.Property[p].Key).SetValue(xWp,
SPViewer.ToolbarSize.None, null);
continue;
} //end toolbarsize hack

ASP.NET MVC, ORM etc

I learned JAVA EE programming a while ago and did some minor projects, unfortunately there is no real world chance to practise it and those little knowledge I learned at that time has been nearly thrown alway totally!
Anyway, at these days I occassionally write some code using asp.net/c#, although it is quite easy to write a 'dirty' program using asp.net, It is definitely not a good practise. I have been trying to figure out the way to write a true multi-tier program using asp.net and came to a few discovery. Interesting, Hibernate got an asp.net version which is called NHibernate...However, I guess I might go for LINQ for ORM, however for MVC structure I only found a 'MVC framework CTP' stuff which is apparently not quite mature yet...

Also the following article has some discussion and comparison which could be useful:
ASP.NET MVC: Choosing Your Data Access Method

E4SE date shown in US format in regardless of locale setting

This is one of the strange things encountered while maintaining the E4SE application, sometimes when you change iis settings, it might overwrite the .js mapping created by ICE tools, after recreating the .js to aspnet mapping, it is back to normal.

Fixing up Intra-site replication error

I wasn't able to connect to one of the server and after looking into it, it turned out to be DNS issue which is in turn caused by AD replication failure...
coz our whole environment was built upon VMWARE, I noticed that VCB backup can freeze DC and make it fail.
the way to fix this up is to manually restart KDC service. re-enable inbound and outbound replication on the failed DC.
the following command might be helpful:

dcdiag
netdiag
replmon
repadmin

repadmin /options DC1 -DISA.....
repadmin /synall
repadmin /showreps
...

MCSE 2003 done!

Finally finished my MCSE 2003 certification.
will take MCDBA exams in the following months.

Upload a File to a SharePoint Document Library

I developed a document management system a while ago, however it can only handle the records of documents and those records are not directly linked to electronic documents which makes it far from optimistic.
By combining the system with sharepoint document library, the system can be used more regularly and reduce work load. i.e. create the document records and save a copy of documents under sharepoint document library, it can also be used to send transmittal electronically.
Thanks for the author of the following article:
http://geek.hubkey.com/2007/10/upload-file-to-sharepoint-document.html
I have been testing the solution and it seems to be working pretty well...

Kerberos authentication & delegation for SQL Server & IIS

To use Kerberos authentication

All computers must be in the same Active Directory forest or in domains in separate forests with trust relationships.
You must configure the Web server or application server machine accounts in Active Directory for delegation. Alternatively, if your ASP.NET application runs under a specific custom domain account, you can configure the domain account for delegation.
IIS must be configured for Windows authentication, or for certificate authentication with certificate mapping.
You must enable impersonation in your applications Web.config (see "How to impersonate the original caller").

http://www.securityguidanceshare.com/wiki/ASP.NET_2.0_Security_Questions_and_Answers_-_Impersonation_/_Delegation

Check SPN
setspn -L Machine_Name
Setspn -A http/www.microsoft.com (DNS) webserver1 (NetBIOS Name) [this is the one I made Reporting service works]
Check IIS Authentication method:
cscript c:\InetPub\Adminscripts\adsutil.vbs get w3svc/NTAuthenticationProviders

Grant delegation permission to the SQL Server service account domain user account (if not using localSystem account)

How to use Kerberos authentication in SQL Server
http://support.microsoft.com/kb/319723/en-us

HOME ESX LAB

looks like I might need to expend the storage...I got 12 VMs loaded at the moment and will definitely add more!

Excel Service & External data connection

After struggling to find out what is wrong, I eventually got Excel Service working now.
The important thing here is to deploy SSO for excel service (there are other options too, but I use SSO in my case). Here is a good reference from Technet:
http://technet.microsoft.com/en-us/library/cc262899.aspx (Plan external data connections for Excel Services)

Programatically create site under sharepoint server 2007 using web service

I was working on the integration between E4SE and sharepoint service 3.0(we actually use sharepoint server 2007). the integration involves providing web service for site creation and team members update. Although we got an older version that works with WSS 2.0, the source code is missing! I re-established the source code and make it working with WSS 3.0. Due to the limitation of sharepoint service, a customized sharepoint web service is also used. Will release the code if anyone interested.

Build an ESX Server 3.5 Server with ML110 G4

well, this esx server was built using HP ML110 G4 (Pentium D 2.8G, 6G RAM, 1XSATA HDD as system install disk and 2XSATA HDD on Adpatec 2610SA RAID Card for VMFS storage).

I loaded it with 4 virtual machines at the same time and the speed is really impressive.

No need to worry about no place to play with new systems/apps now.

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.
///

[STAThread]
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)
{
Console.WriteLine(site.Url);
Console.WriteLine(site.LastContentModifiedDate);
}
}
*/
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++)
{
try
{
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())
{
try
{
//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());
}
finally
{
web.Close(); //Close objects
}
}
}
}
catch(Exception ex)
{
Console.WriteLine("ERROR VIRTUAL SERVER MESSAGE: " + ex.ToString());
}
finally
{
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();
// }

}
//*************************************

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

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.
...to be continued...

Identifying db locks

performance monitor -> locks objects
profiler
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)

sp_lock
sp_who/sp_who2

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

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.

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)

bingo!

MOSS 2007 basic configuration

1, 3 web applications: intranet, mysite and SSPadmin;
2, Create and configure Shared Service Provider and associate 'SSP Administration Site' with 'SSPAdmin', 'My site Location' with 'mysite' web app;
3, Create top level site collection and associate it with 'intranet' web app;
4, Configure indexing through SSP Administration site.
5, For Excel Service, might need to configure SSO (Single Sign ON).

SQL - Get current user

I was thinking of a way to delivery report based on user identity...it turned out that a simple sql statement can help
T-SQL: SELECT SYSTEM_USER

BTW, I was busy working on Epicor's E4SE & EBO stuff in the past few weeks. Eventually I was able to build the whole system and it is running well now. Some other stuff I am working on include WSS 2.0 (to be upgraded to MOSS 2007), SQL 2005 upgrade, BI/Reporting etc.