Automailer Batch Script using Blat

Quite sometime back we needed a simple way to inform our customers the status of their order, the system was able to generate a text file with the code of the customer but we still needed to figure out how to get it to them.


Instead of coding a complicated solution we decided to use a small batch script that did this job. We basically get all these files into a single folder and get the script to run every fifteen minutes to send across a mail to these customers with the file attached.

To enable this we use a small freeware program called blat. Its a command line mailer, a program that allows you to send a mail from the dos prompt.

Its available here

www.blat.net

The script works with a list that has the customer code in one line and the email addresses that the file needs to be sent to on the next line comma separated without any spaces. If a new customer is added, the code and email address would need to be added to the file

For this example the file is called customer.txt and the contents are in the following format

1234
abc@abc.com,def@def.com
5678
abc@abc.com,def@def.com,xyz@xyz.com

The script loops through all the files in the folder and picks up the email addresses it needs to send the email too and send it across. It then moves the file into the respective folder of the customer code.

To understand how it works, its important to understand how blat works. Blat needs to first be configured with the smtp server before the script works.

The usage is blat -savesettings

The normal usage is shown below and can be brought up by blat/?


The script is below


:start
echo off

rem This allows for the variable to work within a for loop
SETLOCAL ENABLEDELAYEDEXPANSION

rem This resets the variables used
set a=1
set rs=none

rem This is the loop that goes through for each file in the current folder and sets the
rem every line of the customer.txt to variable i
rem The variables a and b are used to alternate between the customer code and addresses
rem The rs variable is used to store the customer code on every alternative line of the txt file

for /f "tokens=*" %%i in (customer.txt) do (

if "!a!"=="1" set rs=%%i
if "!a!"=="1" set b=2
if "!a!"=="2" if exist *!rs!*.txt blat -t %%i -body "Order details" -s "Order report - !rs!" -attach *!rs!*.txt
if "!a!"=="2" if exist *!rs!*.txt move *!rs!*.txt !rs!
if "!a!"=="2" set b=1
if "!a!"=="2" set a=1
if "!b!"=="2" set a=2)
echo on

Hope you can get this script to work for you the way you want to. Do drop me a comment would love to hear from you.

Excel 2007 Macro Security Problem

I had written certain Macro on an Excel 2003 Workbook to be distributed hence the Sheet was protected as well the the Macro. It used to work perfectly, it even used to work in Excel 2007 after opening up in Compatibility Mode.

Recently after finding the need to work with larger sets of data I decided to save the file as a Macro Enabled Worksheet. Unfortunately my woes just began. On opening the newly created page I received the Security Warning that macro have been disabled as shown below.


On selecting options there was no longer an option to enable this content. Adding the location to the trusted location etc etc did not have any affect. After reading up a bit I came to know that there exist a registry hack to disable Excel 2007 from mandating that the Macro needs to be scanned by the Antivirus.

You can either save the below code into a .reg file and execute it or go into your registry to the location mentioned. Add the DWORD and set it to 1

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security]
"ExcelBypassEncryptedMacroScan"=dword:00000001


Post the registry correction you have the option to enable this content as shown in the image below.



The helpful resource that helped me solve this problem was

http://www.rondebruin.nl/password2007.htm


Another useful site that I have come across to help users move into Excel 2007 is

http://office.microsoft.com/assistance/asstvid.aspx?assetid=XT101493291033&vwidth=1044&vheight=788&type=flash&CTT=11&Origin=HA101491511033


It allows you to quickly find out where each of the option of Excel 2003 are found in Excel 2007. Hope you found this useful, do drop me a comment if possible.

Port Forwarding Airtel Beetel Modem / Netgear WRG614

Would recommend reading of my previous post as it talks about how the setup exists between the modem and wifi router.

http://piglings.blogspot.com/2008/09/airtel-beetel-modem-netgear-wgr614-wifi.html

The Wifi router is setup as an access point so the Beetel Modem acts as the DHCP and the port forwarding needs to be done on the Beetel itself.


Okay, port forwarding needs to done to enable certain application / games to interact across the internet. It provides the capability to forward traffic from the ip that currently terminates at the Modem to the computer that needs to receive it.

Step 1

Access your Beetel Modem on the following address

http://192.168.1.1/main.html

Username: admin
Password: admin

Step 2

Select Advanced Setup ---> NAT ---> Virtual Servers




Step 3

Click Add, check if the application you need to port forward exists, if so please select and in the Server IP address put in the address of the computer you need to forward to 192.168.1.10.

Make sure that the IP of this machine is made static, refer my above blog entry to figure out how to do that else, this will not work if your computer keeps getting different IP addresses.



Click the save apply.

Step 4

This step is to be used if the application you need to do port forwarding for does not exist in the list that has been provided in the DSL Modem.

Select Custom Service, put a name for example I have put VNC.

Put the start port number and the end port number, the internal port numbers will automatically fill up.



If the Service requires multiple ranges, you can enter in each of the ranges on the next lines.

If your not sure of whether the service requires a TCP or a UDP would suggest you select the option of TCP / UDP in the Protocol drop down.

Step 5

After saving you need to reboot the system for it to take place. Best way to is to power off and power on the setup.

The best way to check if your setup is working is to go to a site that lets you check for example

http://www.canyouseeme.org/

Keep in mind the IP of your computer you need to forward to is static. If this works do drop me a line, would love to hear. If your feeling generous click an ad :)

Asus Mobile Phone Service Center Bangalore

I recently had a problem with my Asus P320 mobile phone, looked up on the net and couldn't find details of the service center for Bangalore, apparently it recently shifted last month. Thought I post the details here to help out others. The service center services all ASUS products, not just mobile phones.

The ASUS Service Center or Asus Royal Club as it is called is located at

Number 1105, 3rd Floor, Kambi complex,
Near Corporation circle,
Bangalore 560002.

In terms of Landmark it is behind the LIC building adjacent to Adigas restaurant.

The phone number is (080)22112942. Prior to this I came across an All India toll free number but I couldn't get anyone to pick it up, the number was 1800 2090 365.

The timing are 10 am to 8 pm on weekend days and 10 am to 5 pm on Saturday. You will need your bill as proof for warranty.

I have put together a blog for this phone over here

http://p320.blogspot.com/

If you have found this useful, do drop me a comment, if your feeling generous click an ad :)

There is a lot more information available on the Asus P320 at the following site

http://p320.blogspot.com

Airtel Beetel Modem + Netgear WGR614 Wifi Router

There are a couple of articles available on the internet explaining how to set this up but I after playing around a lot I realized that there are much easier ways of setting up a connection such as Airtel / BSNL / Tata with your own Wifi router such as Netgear / Buffalo / Linksys.

The most widely published explanations involves converting your ADSL Modem / Router (Beetel) into Bridge mode thereby only making it work as a modem and making the Wifi Router (Netgear) do all the work.

While this works fine it involves changes to be made to the configuration of the ADSL Modem in terms of setup as well the requirement of the username and password to entered into the Wifi Router setup. Also once you do this setup you will lose access to your Modem, requiring you to unplug the wifi router to access it.

Will be descibing a method below on an easier approach to getting your wifi online without needing to know your password or change any other settings on the ADSL modem.

Step 1

Plug in the network cable coming out of the ADSL modem /router ( Beetel) into the network port of your computer / laptop.

The computer should automatically get assigned a ip address of the following series

192.168.1.2


This means that the ADSL modem address would be 192.168.1.1. Type in the following address to access the page of the ADSL modem.

http://192.168.1.1/main.html

It is will pop up a username and password

username : admin
password : password

Other standard is admin / admin

Step 2

Click on Advanced Setup --> LAN, you should see the following page , or something very similar. This is fine , no need to change anything, though you may want to change your password for security reasons as this page can be access from the internet as well.


From the device Info ---> Summary Screen write down the Primary DNS Server address and the Secondary DNS Server address.

Step 3

Disconnect from the ADSL modem, switch on your wireless router, if you have a system with wireless, switch on your laptop wireless, it should connect to the wireless on the router. Otherwise plug in the network cable into one of the LAN ports of the router with the other end into your computer.

Let the router assign you an IP. It typically would be of the series 192.168.0.2 ..

Type the following address to access the main page

http://192.168.0.1

It is will pop up a username and password

username : admin
password : password

Other standard is admin / admin


Step 4

You should see the following page

Make the following entries

Does your Internet connection require a logn ---> No

Account Name ---> Blank
Domain Name ---> Blank

Internet Connection

Check the box that says use Static IP Address
Enter the address as 192.168.1.x where x is a larger number larger than the number of system you ever plan to connect, in my case 20 so that it never gets assigned by the ADSL router.

The 192.168.1 needs to be the same that you used for accessing your ADSL router.(192.168.1.1). Basically now this router is part of the ADSL addressing system.

Check the box that says use the following DNS server
Put in the DNS address you have copied in the previous step

Step 5

In the advanced menu, click LAN IP Setup, you should see the screen below.


In the LAP IP Setup, enter the IP Address, the same as entered in the previous screen, 192.168.1.x, in my case 192.168.1.20

Enter the subnet as 255.255.255.0

Leave the other options as is.

Make sure the Use Router as DHCP option is Unchecked !



Step 6

Click on the Wireless setting tab , you should see the screen below


assign any name you want for your wireless connection. You can choose to have no protection but its unsafe. Its very important to password protect your internet connection. There are typically two types WEP and WPA. Make sure you write down the password you are setting because you will need this to connect wirelessly. The password in the above case is for WEP connection, it is the hexadecimal code.

Below is the option for WPA. You need to remember the passcode you have entered here. WPA is much more secure, WEP can be hacked, I've done it :)



Step 7

You are almost done , but pay attention. Power off both the ADSL modem / router and wifi router. Take the network cable and plug it into the available LAN socket on the ADSL Modem. The other end on the LAN cable need to be plugged in any of the Wifi Router's LAN sockets. Be very careful not to plug it into the WAN socket, this is a single socket, typically the Netgear Router will have 1 WAN and 4 LAN sockets. So I say again, the LAN socket only, if your still confused, just plug it into the middle socket. :)

You Wifi router is now working as an Access point and all the computers that connect to it will be assigned IP from the ADSL Modem / Router in the 192.168.1.2 , 192.168.1.3 series once you enter the password based on the protection you have choosen.

You are now up and running :)

Points to note

1. You can still access your ADSL router page with 192.168.1.1/main.html
2. You can access your Netgear Wifi router with the new address of 192.168.1.20

Do drop me a comment if you have found this useful. If your feeling generous you could donate a dollar via paypal :)

Will link in port forwarding separately. Ok have done the port forwarding part, its available here

http://piglings.blogspot.com/2008/10/port-forwarding-airtel-beetel-modem.html

Incase of errors in connecting to certain sites / VPN / mail etc do check out the following page

http://piglings.blogspot.com/2009/03/airtel-blocking-websites-partially.html

Dot .Net 3.5 SP1 Installation Error

The other day for one of our projects we needed to upgrade to SQL Server 2008. Part of the pre requisites was to have .Net framework 3.5 installed on the machine. The DVD had the install for the framework but it just wouldn't install.

The setup would unarchive all the required files, and fire the setup program. The progress bar would increase all the way upto 95 - 98 % and just hang. I am talking about the full package and not the boot strapper. Tried after a couple of re boots but exactly the same problem on a couple of my machines, all running XP Pro SP2.

Did some readups and a few people have had some install problems. The solution for this kind of problem is to disable all your network adapter. Go to the Network Connection menu, right click the connections and say disable. Once this is done, run the install for the full package, it should go through rather quickly with the Microsoft EULA popping up as soon the setup progress finishes.

Don't know why this happens, am assuming that the package still tries to verify if all parts are available for install and wants to connect to the net but is unable to do so through our proxy servers.

Either way, this workround works well. You would need to install the framework for SQL Server 2008, Visual Studio 2008 and going forward probably a lot more applications that get developed on the microsoft platform.

The microsoft page is here
http://www.microsoft.com/downloads/details.aspx?familyid=AB99342F-5D1A-413D-8319-81DA479AB0D7&displaylang=en

The full package install is here
http://download.microsoft.com/download/2/0/e/20e90413-712f-438c-988e-fdaa79a8ac3d/dotnetfx35.exe

Hope this helps, do drop me a comment would love to know.

TCPView

We sometimes need to figure out whats running on our computers that have created connections for the outside world. The needs could be for check if you have any ad ware / trojans. The other reason is when we ourselves want to know if a certain program is running properly or what port it is listening too or figure out which system has connected into your machine.

There is this built in program in Dos called netstat but its very very basic and works only in the dos prompt. As always there is a lovely little stand alone program that is part of the sysinternal suite called tcpview. t doesn't need to be installed and has a zero footprint.

It shows all the application on your PC / Server that have open connection / established conections and the ports that are getting used. It also has a setting whereby you set the refresh time to update the current connections.

Sysinternals got bought over by Microsoft and now is hosted at microsoft

http://technet.microsoft.com/hi-in/sysinternals/bb897437(en-us).aspx

Hope this has helped you out. Do drop me a line !

Network Bridge Wired and Wireless Connections not working

If you have issues in getting to work after trying to the bottom

The Basics ....

The first question to be answered is why would someone want to bridge a wired with a wireless connection. Well the situation in my case was that I have a small network with a router connecting 4 machines. I also have a netgear wireless router serving a couple of laptops elsewhere close by. Now the idea in my mind is that why can't I have internet access for the this small network for basic stuff such as Email, Antivirus etc

So I get myself a USB wireless card which plugs into the USB port of one of the laptops. Now this allows this computer to be part of the LAN as well as part of the wireless network with the laptops but this does not allow the remaining computers to have access to the internet or to the other computers part of the wireless network.

Fortunately Windows XP has an solution for the problems and its called a network bridge. It allows you to create a network bridge on the computer that has access to both networks and allows for cross traffic across both. The concept is well documented in many sites and if you want to set it up would recommend a google search for a more detailed explanation with screen shots. I will briefly explain here.

Assume that the router acts a DHCP Server and is setup to allocate IPs in the range of 196.168.0.x so the systems on that network would have IPs such as 192.168.0.2 / 192.168.0.3 etc The desktop connected through the USB wireless will also have an TCP/IP automatically setup with an IP as 192.168.0.5.

Do a ipconfig in DOS or properties of the connection and note down the DNS settings of this connection.

Now the other network (wired) since its a router you have to physically set up the TCP/IPs. On each of the systems setup the IP as 192.168.0.101 / 192.168.0.102 etc with the subnet as 255.255.255.0 and the gateway as 192.168.0.1 Now the system which has the USB connection as well as the lan connection will be having two networks connected with two IPs say 192.168.0.105 and 192.168.0.5

In the network connections page select the two networks right click and select network bridge. You will be required to enter the details of the bridged network. Use the same setting of the LAN TCP/IP settings for IP , subnet and gateway.

Additionally now the DNS settings need to be setup since the connection has been manually setup. Use the DNS settings you have captured earlier and plug it in here as well as all the other computers that are part of the wired LAN.






Ideally doing all the above should work but unfortunately it didn't after lots of search I came across this setting to force a certain mode on the computer

In DOS type in the following commands

netsh bridge show adapter

-------------------------
ID AdapterFriendlyName ForceCompatibilityMode
---------------------------
1 Wireless Local Connection disabled
2 Local Area Connection disabled
---------------------------

if you get the above output then type the two statements below ...

netsh bridge set adapter 1 forcecompatmode=enable
netsh bridge set adapter 2 forcecompatmode=enable


The "netsh bridge show adapter" command nows shows the following

-------------------------
ID AdapterFriendlyName ForceCompatibilityMode
---------------------------
1 Wireless Local Connection enabled
2 Local Area Connection enabled
---------------------------


This finally got my bridged network working :)

If you found this useful, do drop me a line.

Member Hierarchy with Multiple Parents / Many to Many Dimensional Modeling

Over the course of one of my projects this unique issue posed itself that needed to be solved. I needed to build a reporting analysis tool that allowed me to analyze data across various dimensions such as Time / Geography / Product etc. One of the parameters on which analysis was need was on Type. Now Type unfortunately wasn't a one is to one mapping or a many to one mapping.

For example a single store could be Hair Focus outlet , it could also be a Skin focus outlet, a Food focus etc. Also possible that the it could just be one of the above.

Now if we go ahead and build a normal hierarchy with this kind of mapping of outlets to their respective types as defined below it runs into various problems of incorrect totaling.

Outlet Type
1 Skin Focus
1 Food Focus
2 Skin Focus
2 Hair Focus
3 Hair Focus
..
..
..


The problem being if we try and report say sale based on Type it will double count outlets 1 and outlet 2 and the resulting total would be incorrect.

Another typical example of this based on my reading is when in a bank individual have single accounts as well as joint accounts and the same needs to modeled ensuring that we don't end up double / triple counting the same record while reporting balance or any other parameter.

I came across this fantastic white paper, that describes this problem and more importantly the solution to the same.

http://www.sqlbi.eu/Projects/Manytomanydimensionalmodeling/tabid/80/language/en-US/Default.aspx

The issue is treated as a many to many dimension modeling and has a very straight forward solution using SQL Server 2005 and something known as a factless dimension. More importantly it gives insight into how one may be able to model the same and the fundamental principle could be attempted to be used in other schemas as well.

The capability solved my problem of allowing me to create a dimension on the type which allows for navigational / selection and ensuring that no double counting takes place at the total levels.

If you have found this page useful, do drop me a comment.

Multiple Remote Desktop Connections (RDC) XP not working

If you have tweaked around with Windows XP you probably know that its possible to connect to another system across a network, intranet / internet as a remote user with the same interface as sitting on the system.

The capability is bundled with XP pro and I'm sure that you will find enough and more blogs / tutorial about how it works with screenshots and all. The issue is that its not available in XP Home and in XP Pro it allows only one user to work on the computer.

Based on my searches I believe its also possible to hack XP Home to support RDC, though I haven't tried it myself the following link does have all the details that you could possibly need to try this out.

http://www.geekport.com/2007/08/15/enabling-remote-desktop-in-xp-home/

http://www.mydigitallife.info/2008/06/14/install-and-enable-remote-desktop-in-windows-xp-home-edition/

Now to the problem in XP pro , as mentioned earlier , the licencing agreement of MS allows only one user to access the computer so if you RDC to a computer the existing user gets logged out (not desirable). There is a way of overcoming this issue with some registry changes and a changed Terminal Service Dynamic Link Library (termserv.dll).

There are detailed article on the procedure available below

http://sig9.com/articles/concurrent-remote-desktop

http://www.mydigitallife.info/2008/06/13/enable-multiple-concurrent-remote-desktop-connections-or-sessions-in-windows-xp/

Additional some good soul has also written a program that neatly makes all these changes and also patches the termserv.dll the link for that is

http://www.kood.org/terminal-server-patch/

For most of the people in the world doing all this worked just fine and if you have tried and are successful good for you .... but then again there are those who are not so fortunate in life such as me who after trying out each and everything on all the links that I have posted including the application got nothing to work. The main user still got logged out each time I tried to RDC to the system.

After more googling I came across this obscure little package called
TermSrvNoRestrPatch-1-3

The location of the file is as following

http://ice-club.com.ua/antiwpa/Other/TermSrvNoRestrPatch-1-3/

which was also a real pain to find a valid link for it. It worked like a charm, it step by step patched all the file which the other application also did but this one got it to work ! It also patched the capability to RDC to a local host, yes there are a few reason why someone would want to do something like this :) such as running multiple instances of a single program.

A word of caution, since the code edits few of the windows' files, some antivirus programs through it up as a virus. You may need to run this in safemode if your antivirus prevents its from executing and more importantly if your convinced that it isn't a virus.

Hope this has helped all the hapless people out there trying to get this to work. As always do drop me a comment if you found this useful, if your feeling generous you could donate a dollar via paypal :)

Ipmsg IP Messenger

If you have a small network and needed to communicate without needed to use the internet or having to set up a communication server the following application is really great. It does not need an installation and can be run whenever needed. It works on port 2425 and automatically picks up other users on the network that are running the application that are part of the same subnet.

The good is that it has a log, quotes the last message in the reply, lets each member be part of a group and puts those people together. Additionally the really cool part is that it supports file transfer including folder transfer as well ....

Its freeware and maintains a small footprint about 5 MB of memory, peer to peer, scalable and almost never interferes with other applications.











The link for the site is below

http://www.ipmsg.org/index.html.en

If you find the post useful drop me a line would love to know.

Cheers,
Vikram.