Month: January 2007

Dynamic FTP Scripts

Matt Simner Miscellaneous, Setup and Deployment ,

I seem to be getting back to basics at the moment with some of the things I’m doing – and didn’t imagine myself having to get an automated FTP update working this week…


My objective was to ‘get’ files from a remote machine using a folder pattern consisting of the date in an ‘yymmdd’ format.  I’d forgotten that script files used with ftp (ftp -s:myscript.txt) are dumb text files, and if you want to ‘get’ from a dynamic location – or dynamic filenames you need to magic something up.


My interest was sparked here, and although I found a C# FTP library on CodeProject that would clearly do the job, and would be cool to use in itself, I was more interested in using ‘old skool’ lo-tech methods to solve the problem.


I read one article that suggested using batch files to spit out the ftp script.  Excellent idea I thought – so this is what I ended up with…. (I’ve changed a few details to protect the innocent so if it doesn’t quite work it’s only due to my own typo 🙂 )


I won’t explain each bit as that would spoil the fun – but the general gist is that FTPReportsForDate is normally run without parameters and it finds files in a specific folder and renames them on the local host.  The process also does other stuff I didn’t have time to go into….


FTPReportsForDate.cmd (which accepts a parameter or gets current date if not supplied)


@echo off


echo Creating Log Folder
IF NOT EXIST C:\logs\FTP\ md c:\logs\FTP


echo Removing previous processing date
IF EXIST processingdate.txt del processingdate.txt /F


IF “%1” == “” (
 echo Getting current processing date
 cscript //NoLogo GetCurrentDate.vbs >> processingdate.txt
 echo Transferring File
 FOR /F %%f in (processingdate.txt) do CALL FTPCommand.cmd %%f
)


IF “%1” NEQ == “” (
 echo Transferring File
 CALL FTPCommand.cmd %1
)


FTPCommand.cmd  This constructs the FTP script file and does the FTP itself…


IF EXIST FTPLatestFile.txt del FTPLatestFile.txt /F


Type FTPHeader.txt >> FTPLatestFile.txt
echo cd RP%1.IN >> FTPLatestFile.txt
echo get staticfilename.fil dyn%1.fil >> FTPLatestFile.txt
echo close >> FTPLatestFile.txt
echo bye >> FTPLatestFile.txt


ftp -s:FTPLatestFile.txt >> c:\logs\ftp\transfer_%1.log


FTPHeader.txt This contains the static information used in the FTP script


open 192.168.0.1
myftpuser
myftppwd
prompt
lcd c:\incoming



GetCurrentDate.vbs gets current date in a yymmdd format in the event we don’t pass in a date to FTPReportsForDate.cmd


‘Simple script to output todays date in yymmdd format for FTP processing


Function pd(n, totalDigits)
 if totalDigits > len(n) then
  pd = String(totalDigits-len(n),”0″) & n
 else
  pd = n
 end if
End Function


Wscript.echo pd(RIGHT(YEAR(date()),2),2) & pd(MONTH(date()),2) & pd(DAY(date()),2)
Wscript.echo
        

SQL Server Tips – Optional Parameters and Find Closest Match

Matt Simner SQL Server ,

It’s funny how you can survive for years not knowing some really simple stuff.  I learned two useful things last week.  For instance…


Optional SQL Parameters


To have an ‘optional’ parameter in a SQL ‘search’ Stored Proc you’d often declare a parameter thus…


@MyParam VARCHAR(20) = NULL –to denote that we want to ignore this param


and you’d probably



  1. have some convaluted logic to possibly build dynamic SQL around whether to factor the parameter into your query
  2. build some nasty ‘like’ clause that defaults to ‘%’
  3. derive MIN and MAX values from the input parameter and use a BETWEEN
  4. use a ‘magic value’ in place of your NULL to denote ‘ignore’
  5. something else equally ugly I’ve not considered

…or (assuming this parameter is expecting to do a direct match) you could just simply do


WHERE @MyParam = ISNULL(@MyParam, MyFieldIWantToMatch)


This will ensure if your field is nulled out it just does a simple (IF 1 = 1) type thing.


 


Find Closest Match


I had a need to find the closest match to a particular value within a 10% tolerance +-.  The following does that fairly simply (I did nick the idea from thescripts.com) but these sorts of things do avoid the need for nasty tempdb use with temp tables…


The following is an example – so don’t think I design tables with one column!


DECLARE @Amount INT


SET @Amount = 50


DECLARE @MinAmount INT
DECLARE @MaxAmount INT


SET @MinAmount = @Amount * 0.9
SET @MaxAmount = @Amount * 1.1


SELECT  AT.Amount
FROM AT.AmountTable AT
WHERE Amount BETWEEN @MinAmount AND @MaxAmount –within range
ORDER BY ABS(@Amount – AT.Amount) ASC –Nifty ‘closest’ match


You can then take the first row in your result set to be the closest match.


 

Using Batch Build in Visual Studio

Matt Simner Setup and Deployment, Visual Studio ,

This is a really simple one, but if you want to build a solution in a specific ‘Configuration’ and you have a big solution open, it can sometimes be an awfully long time to switch configurations – e.g. Debug -> Release.  You may find yourself needing to do this when you’re needing to build and rebuild setup projects whilst testing.


You could of course run a command line compile, but a simple way to stay in the IDE and Debug configuration whilst running a Release build is the Batch Build facility. 


Just go to Build –> Batch Build, then select all the Release versions of the projects you want to build (or whatever you want to build), and then click Build.  The settings will be remembered for the next time you go in too…


I said it was simple. 

Beware the unseen checkout in Visual Studio Setup Projects

Matt Simner Setup and Deployment, Visual Studio ,

I was inadvertently responsible for a broken build this morning as I checked out a Setup Project last night in Visual Studio 2003 that ended up checking out a merge module that was included in the Setup.  This merge module is built and subsequently stored in SourceSafe (I know – it shouldn’t be! – this is a temporary measure) by the build script, and the build failed as the .msm output file was checked out to me.

 

Shouldn’t Visual Studio prompt you when you check out?

The project was checked out in Visual Studio and it gave no warning that the .msm was also going to be checked out.

 


In other setup projects you’ll often find that other files will appear in the Check Out dialog (i.e. you’re about to check  out all the ‘files‘ that have been explicitly included into your setup – from their source location).  As a matter of course it’s wise to first untick everything (right at the top), and then select only the .vdproj file to actually check out.

 

Why didn’t it prompt in this case?

The problem in this case appears to be that the offender is a merge module and was added to the setup project as such rather than as an included ‘file’.  It seems that Visual Studio neglects to mention merge modules when you check out as it probably thinks they’re static and not something it should worry you about! (which is probably true – yadayadayada)

 

How to prevent this happening to you

Well, while this file is still in VSS there’s little option but to just be vigilant whenever you check out a setup project that includes it. Either check out in VSS Explorer or if in Visual Studio -go immediately to VSS and undo the offending checkout.  Another option is to add some more resilience into the build and somehow force an undo-checkout (as no one other than the build should be updating the file).  A variation of this is to disallow checkout for all but the user running the build process through SourceSafe Admin (but this is a pain to administer), and the final and best solution is to remove the need to store the merge module in Source Control by sorting out cross-dependencies in different components so that they all pick the file up from the same central location.