This one stumped even Salesforce.com, but I didn’t give up!

Data loader is a free tool provided by Salesforce.com and has a command line interface and can be called from within a batch file. The problem is, within the batch file it forces a change directory so it is next to impossible to call the batch file, within a SQL Job as a CmdExec, from a remote server.

There are plenty of blog questions asking for a solution. I’d even called our highest Technical Support person at Salesforce.com, with a very intruiging “Wow, I don’t think you can do this or if you can I don’t know how you would” response. So reassuring.

I stumbled on a blog post that hinted a solution might be calling a windows scheduled job on the remote server. After pouring over Google solutions for hours (ok, probably closer to a whole day) I managed to incorporate this solution and will outline the steps here, for those who are just that bored or are looking for this insight.

Server A
I needed to put the Sales Force data loader, batch and configuration files, as well as the data files on a stand-alone server (Windows 2008 Server).
Server B
Separately, I had a SQL node (2005) on a Windows 2003 server.

I had to pull in data from sales force through the data loader (which downloads .csv files), pull the data into SQL tables, compare and transform, then push data back through the data loader to Salesforce.com.

The SQL job steps are similar to this:
1 – Set all bit files to zero (ActiveX script)
2 – Pull Data from SFDC (scheduled task)
3 – Check for download completion (stored proc)
4 – Pull into staging database (SSIS Package)
Etc…

I created windows scheduled jobs (using the command line because I needed to initiate backwards compatibility for the scheduled jobs, enabling them to be remotely called). So there are scheduled jobs on Server A calling the batch file which pulls and/or pushes through the data loader command line tool (syntax: schtasks /tn “ScheduledTaskName” /tr “c:\path to batch file” /st 01:00 /v1 (you can change the task once created, remove the scheduled time, the v1 switch is to make it backwards compatible).

Note – Step 1 above: The problem with calling scheduled jobs through SQL Server is you issue the command and it returns, immediately, a success – unless it is unable to start the task. Which means the command has been issued but not necessarily that the batch file which was called completed. This can take minutes, depending upon the size of the .csv you are attempting to pull down or push up. To solve this problem, I begin my series of SQL Steps with an ActiveX script which writes a series of text files with a zero (0) in them, then I have a stored procedure which I pass in the name of the .txt file as the parameter and it loops and waits for 60 seconds, until it finds the 1 in the file.


Step 2 - I run this through a SQL Job – operating system (CmdExec) step using this syntax: schtasks /Run /S ServerName /TN "ScheduledTaskName"

Step 3 – I created a stored procedure which checks the contents of the file, the call would be similar to: EXEC [dbo].[pr_ReadFinishFileBit] '\\server\c$\folder\filetocheck.txt' and the contents of the proc are similar to:

CREATe PROCEDURE [dbo].[pr_ReadFinishFileBit]
(@filetocheck varchar(255))
AS
BEGIN
SET NOCOUNT ON;

set @filetocheck = 'type ' + @filetocheck

DECLARE @output TABLE (bout bit)
INSERT INTO @output
EXEC master.dbo.xp_cmdshell @filetocheck
DECLARE @check bit
SET @check = (SELECT TOP 1 bout FROM @output WHERE bout IS NOT NULL)
DECLARE @ctr int
SET @ctr = 0

WHILE @check = 0
BEGIN
SET @ctr = @ctr + 1
IF @ctr = 60 --too long, error out
BEGIN
RAISERROR('Error - download/upload not complete within 60 minutes',16,1)
BREAK;
END
--wait for one minute, check again
WAITFOR DELAY '0:1:00'
DELETE FROM @output
INSERT INTO @output
EXEC master.dbo.xp_cmdshell @filetocheck
SET @check = (SELECT TOP 1 bout FROM @output WHERE bout IS NOT NULL)
END

END

Step 4 – This is a pretty straight forward SSIS package which pulls from the .csv files into a staging database – nothing fancy so I won’t bore you with the details.

If you find yourself in this same situation and need more tedious details, please feel free to post a comment and I’ll attempt to help where I can.

Peace out.

Upgraded JustTheWeather

Because GPS service is intermitent depending on where you are, if you are indoors, etc. I added the ability to enter a city or use GPS with menu options. Still learning, hoping to build an app for Android Developer Challenge II (http://code.google.com/android/adc/ ). Hopefully I'll find the time in the next month to whip something up.

Please post comments with any JustTheWeather application.

Already had to upgrade JustTheWeather

Like any noob developer, my program didn't work for everyone. I believe I've fixed the major problem: uses GPS location instead of last known network location. I also added an end user license agreement, to protect myself - nothing more (in this crazy world).

Please let me know if you encounter any issues by e-mailing me or posting comments to this blog.

T-SQL simple query optimization - search arguments and predicates

Once you start really trying to understand the "how's" in T-SQL, you can implement much more optimized queries. Take the following simple lesson with search predicates. 

These two queries technically will return the same results, looking for all records where the date variable is equal to the datecolumn, or the date column is null and the date variable is null.

SELECT [somecolumnname]
FROM [schema].[sometablename]
WHERE COALESCE([datecolumn],'20100101') = COALESCE(@date, '20100101')

SELECT [somecolumnname]
FROM [schema].[sometablename]
WHERE [datecolumn] = @date OR ([datecolumn] IS NULL AND @date IS NULL)

But performance wise, the first query is manipulating the table column which means the predicate isn't a search argument and SQL Server cannot efficiently use an index on the datecolumn, if one exists.

Simple rule, always attempt to find a way around using a function on your table columns in the WHERE clause. If you have to, use a CTE, temp table or table variable if you need to limit the results to search by beforehand.

Another gotcha is that even if you don't have a function on your search clause, using some evaluations aren't a search argument. Like this one:

SELECT [columnname]
FROM [schema].[tablename]
WHERE [columnname] LIKE '%8'

Not only is this not a search argument and cannot utilize indexes, but horrible performance as each row value needs to be evaluated individually to see if it ends with the number 8. 

Although WHERE [columnname] LIKE '8%' is a search argument.

Good to have a thorough grasp of T-SQL. Here's a site with good overview of this and other search argument topics. 

Happy searching!

JustTheWeather app for Android / Google Phone

I'm playing around with the new Android platform and have produced my first application. It's called JustTheWeather and takes your GPS location and queries the Google weather api for current and forecast conditions, displaying the results with text and images.

I wanted to make a simple app with just the weather so my 10 year old step daughter could look on my phone for her daily question of "What's the weather going to be like today?".

It has been fun and I've learned quite a lot, mostly that I have a lot to learn!

Probably going to publish it to the android market soon, for free of course, it's so simple that I can't imagine charging for it.

This was just a stepping stone (or diving board depending upon how you look at it) into building the application I really want on my Google phone. Shhh.... more to come.