Wednesday, February 17, 2010

Sharepoint Wakeup/Sharepoint List Adapters

Currently we have a nightly process that loads some records into a staging WSS list with Sharepoint List Adapters in SSIS 2008. Loading Moss/WSS lists with list adapters is a quick and easy solution that everyone needs to check out (list adapters). But I started noticing timeout failures in the mornings. After some additional research I came to the conclusion the site is trying to wake up at the same time I am loading 10,000+ records into a wss list.

So I decided to setup a Sharepoint WakeUp process to warm the site prior to loading the records. If you have not explored this simple process you need to look into it. By setting up a simple scheduled task on the web front end of your site you can wakeup miltiple sites. The process even has the ability to exclude some sites like "mysites"

The only catch is that you must setup the exe on the actual MOSS/WSS webfront end. It references a sharepoint.dll that is needed to wake the site.

Simple command:
spwakeup.exe -site: -exclude: -log:c:\log.txt

Concatenating rows as a alternative to using a cursor

As I was reading the "question of the day" on I came across a simple but very effective alternative solution to using a cursor by concatenating rows.

declare @table table (id int, [name] varchar(10), gender char(1))
declare @MaleNames varchar(255)

insert into @table values (1,'John','M')
insert into @table values (2,'Sue','F')
insert into @table values (3,'Jane','F')
insert into @table values (4,'Mark','M')
insert into @table values (5,'Bill','M')

select @MaleNames = IsNull(@MaleNames + ', ','') + [name] from @table where gender = 'M'

Select @MaleNames as MalePatients