SQL service account

The service account for SQL is generally domain\sqlservice or similar. You should not be using domain\Administrator as that is bad practice.

In order for this, (notionally) non-standard admin account to function, it should be also made a member of the local computer Administrators Group.

Event Log Errors in pairs like:

Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: ip address local host]

with

SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: ip address local host]

indicate that the service account is not authorised to access its own SQL server. Adding the domain\sqlservice account to the local administrators group should fix this error.

Sugar CRM and Merge Records

SugarCRM provides an ideal platform for centralising name and address details for full CRM processes but also to remove duplication by presenting details to other applications. I work on a project that supports 3 applications that use name/address details and linking them all works really well.

An issue was that when merging records like Accounts in Sugar that there was no complete record kept of what accounts were merged only what account was saved.

Sugar CE (Community Edition aka Open Source) includes a Trackers module that does track some transactions within Sugar for users / sessions but not all.

One of the main shortcomings is that it does not track deletions of accounts. Noting that Sugar does not actually delete but disables via a deleted flag.

So the first task that I addressed was the tracking of what accounts were deleted.

The changes for this are all in the file /data/sugarbean.php starting at line 4264 or there-abouts:

4264 $query = "UPDATE $this->table_name set deleted=1 , date_modified = '$date_modified' where id='$id'";
4265 $this->db->query($query, true,"Error marking record deleted: ");
4266 $this->mark_relationships_deleted($id);
4267
4268 // Take the item off the recently viewed lists

Changing the code to include both a log entry for deletions and the tracker entry.

4264 $query = "UPDATE $this->table_name set deleted=1 , date_modified = '$date_modified' where id='$id'";
4265 # thowden 20090518: Added for logging of deletions
4266 $GLOBALS['log']->info('Deletion ' . $id . ' query : ' . $query);
4267 # end thowden
4268 $this->db->query($query, true,"Error marking record deleted: ");
4269 $this->mark_relationships_deleted($id);
4270 # thowden 20090518 added to tracker for deletions
4271 $this->track_view($current_user->id, $this->module_dir, 'deleted');
4272 # end thowden
4273 // Take the item off the recently viewed lists

The log entry could be changed to only debug, error, or warning level, but I figure that 'delete' will be one of those things we will constantly be chasing for our beloved end-users.

Hand-in-hand with deletion is the concept of restoration and for good measure this can also be tracked.

Further down the file: noting that line numbers shown here vary as they are already changed compared to the original file due to the inclusion of the code above.

4289 $query = "UPDATE $this->table_name set deleted=0 , date_modified = '$date_modified' where id='$id'";
4290 $this->db->query($query, true,"Error marking record undeleted: ");
4291
4292 // call the custom business logic

Changes to:

4295 # thowden 20090518: Added for logging of restorations
4296 $GLOBALS['log']->info('Restoring ' . $name . ' ' . $value . ' query : ' . $query);
4297 # end thowden
4298 $this->db->query($query, true,"Error marking record undeleted: ");
4299 # thowden 20090518 added to tracker for restores
4300 $this->track_view($current_user->id, $this->module_dir, 'restored');
4301 # end thowden

Now you can review in both the sugarcrm.log or whatever you call your log file and in the Tracker table in your SugarCRM database.

For the merge process details to be really useful we wanted to add a trigger in the database to run some scripts. In order for the trigger to function it needs to have a unique record that it 'knows' means that the merge transactions have completed.

So I added a new entry to the Tracker database. File to modify is
modules\MergeRecords\SaveMerge.php
Find line 118 in the file:

118 $GLOBALS['log']->debug("Merged record with id of ".$return_id);

... and add the following

118 $GLOBALS['log']->debug("Merged record with id of ".$return_id);
119 # thowden 20090518 added to tracker for mergecomplete
120 $mergesource->track_view($current_user->id, 'MergeRecords', 'mergecomplete');
121 # end thowden

This completes a merged accounts transaction set for any number of merged accounts with a mergecomplete transaction. The database trigger is linked to that entry and recognises all the transactions by the monitor_id column value which is unique for a transaction set.

To query the Tracker table try using something like the following and modify the date value to something more appropriate.

select * from tracker where convert(char(10),date_modified,112)>='20090520'

This supports merging of Accounts and Contacts in SugarCRM CE 5.2.0d and possibly other 5.x.x variants.

sharepoint BDEADEE2-C265-11D0-BCED-00A0C90AB50F failed due to the following error: 800703fa

A Sharepoint error this morning which when searching the net revealed some obscure reasons for its occurrence.

The full message was more like this:

Retrieving the COM class factory for component with CLSID {BDEADEE2-C265-11D0-BCED-00A0C90AB50F} failed due to the following error: 800703fa.

The issue was a change of service account password in Active Directory following an enforced password change. We had gone through and modified the password for the service account on the MOSS server and then restarted it all and oops!.

Changed the password back and restarted and its all ok again.

Lesson: Always management SharePoint passwords from SharePoint.

Debian and FreeTDS

Installing SugarCRM on our Debian server for some testing of interaction between Sugar on Linux and its data on MS-SQL on Windows.

So freeTDS is a supporting tool to make the database connections from Linux based PHP to Sybase and MS-SQL database servers.

Problem was that trying to locate the correct freeTDS stuff to install was a bit obscure.

FreeTDS is not listed as such for apt-get on Debian.

It is freetds-dev or tdsodbc.

apt-get install freetds-dev

or

apt-get install tdsodbc

Remote Server Admin Tools: The update does not apply to your system.

Trying to install the server tools for Windows servers to my Vista notebook.

Why is it so damn difficult ?

"The update does not apply to your system."

So, how about an explanation of why! 32bit vs 64bit, Vista is missing SP1, you're not holding your tongue the right way, anything...... but give me something to work with. Useless messages are no better than no message.

If you are reading this it's because you are where I was with that message and no clue.

It's telling us that it's already installed!

Go to your Control Panel -> Programs ->Programs and Features -> Turn Windows features on or off -> Remote Server Administration Tools

Turn on your options, as they are apparently disabled by default - the wonders of Microsoft.

Then Access from the Alls Programs selection: Administrative Tools menu.

Update: I have since discovered that my install has disappeared from the menu. In the control panel it is all still enabled just not appearing on the menu. Will need further investigation.

Date and Time in Batch for Log File Name

Setting up a batch file and configuring a command line utility and identifying, for Windows servers, a process to execute the application and have unique log files recorded.

REM take the system date and convert it to YYYYMMDD format
set MyDate=%date:~10,4%%date:~7,2%%date:~4,2%

REM take the system time and convert to an HHMM format
set MyTime=%time:~0,2%%time:~3,2%
REM if earlier than 10 oclock then convert leading space to a 0:
REM note it is %MyTime:<space>=0%
set MyTime=%MyTime: =0%
REM if its after 10 then this has no effect so theres no need to test for the time

REM Next set the %logfile% configuration for daily retention

set logfile=%MyDate%%MyTime%.log

REM use the %logfile% variable in the command line e.g.
c:\bin\spwakeup.exe -log:c:\logs\spwakeup\%logfile%

Of course, the date and time editing could be to any sequence you prefer.

The idea is to break the system date into little strings and manipulate them to your needs.

set MyDate=%date:~10,4%%date:~7,2%%date:~4,2%

is really saying to the system

get the system date "%date"

do something with it ":"
edit it down "~"
from character position "10"
for a length of ",4" (the year including century YYYY format)
end this variable component "%"

repeat for the characters at position 7 for a length of 2 (the month in MM format)
and again for the day at position 4 for a length of 2 (the day in DD format)

and because all three instructions are strung together then the result is pushed into the variable named MyDate (without the %'s when setting, but with %MyDate% when you want to use the value) the output will be the date in YYYYMMDD format.

Likewise with the Time value and then setting the logfile name.

SharePoint Slow to Start

SharePoint is a current topic for me and I'd noted that the first call to any of my SharePoint sites was always slow first thing in the morning. A little research identified that the server defaults to restarting and doing some clean-up early every morning.

Also as a part of that research there are numerous pointers to Wake Up scripts but some are out of date. So if you are looking for a solution there is an excellent script available on CodePlex called SPWakeUp.

I set up a batch file for it and scheduled it twice - once for 0300 daily and again on server restart. This way my users will not have the 30 second wait the first time they hit on one of the sites.

I also configured the batch file to provide for spwakeup log files that have a date/time stamp in the file name so that the log is not overwritten. The batch file process for handling date and time in a file name is relatively simple for any Windows server.

Education Real World Skills

I was reading the Australian Financial Review Monday 2nd Feb here in downtown Adelaide (yes I am travelling again) and the Education section rang a bell with me. (in a past life I was a education sector IT boffin) and I noted a number of things in the article by Joanna Mather on Real World Skills worth funding.

It was not so much the sentiment or the goal, as the time frame being suggested and is it worth funding on that basis.

The outcome of 8 months of research in 2008 culminates in the winning of a grant... "The work-integrated learning study was funded by the Australian Learning and Teaching Council in partnership with the Australian Collaborative Education Network..." etc.

Based on a Canadian example (like we do not have enough ideas in this country?) is due to 'go live' in late 2010... ! What tha!

Its January 2009 (ok its really Feb 2 but..) given the current economic situation and the known issue with IT projects scheduled against Moore's Law timing, I would expect that this will be another dismal failure. 18 months in the unknown future against unknown external factors we will deliver a system to assist youth in the future with skills.

So what about today ? and todays youth who within 2 years will be implicitly 2/3rds of their way through todays general 3 year courses to attain their qualification. A late 2010 delivery does nothing for today and is potentially a wasted effort as a consequence.

Given that we can deliver prototype websites in 24 hours, have domain names created within minutes, and be pushing traffic to sites a week after they are first born, why on earth do we persist with the concept of delivery in 18 months for any project, much less one that is meant to be fixing issues now.

What will the project do for the next 18 months ? Discover that they have planned the last failing website? Confirm that any IT project scheduled over 12 months is doomed to failure? (a particular favourite opinion of mine!) or that if we wait long enough and spend $400k that we can appear to have done lots without really achieving much.

If we really want to address the issues for todays students we should have started in October last year, developed on an incremental basis, delivered at least a starting point and not be reporting about what we intend to do 2 years hence.

If they are really serious about doing this now, then the time line could be to deliver something in 2009 for real benefits in 2009, stealing a complete 2 year march on other influences.



End of my education rant for this week!