Wednesday, August 29, 2007

The OSQL utility

Well this is more of a tip a smart tip is guess we came across this scenario where we were working a interface between Navision database for the financials and 17 other POS databases from where the daily sales was imported into navision.

osql is a sql client with a command line interface it is very similar to what query analyzer does in functionality i.e it takes a SQL batch and passes it on to the relevant server just that it accepts command line parameters which makes it that useful.

The interface was based on SQL procedures and everytime there was any change required someone was required to physically connect to these 17 servers manually and update the scripts one by one it was really painful, it was then that we thought of using a shortcut we just made one batch file with connection information to all the 17 stores and then provided a file name as a parameter to osql this file contained the batch we wanted to execute on all the servers the syntax was like this

osql -S192.168.10.3 -Usa -Psk74fa5z -dAlainMall -iBatch.sql -n

The parameters help can be got on the osql command line "batch.sql" was the file we would place our sql code into and it would execute on all the servers in the right databases.

No comments: