Using osql with nant scripts

Question

I am currently using osql with nant by calling a batch file with arguments. Here are the properties that are defined in my nant script (no, not real username/password values):

<property name="project.config" value="debug" />
<property name="server" value="(local)" />
<property name="database" value="Test" />
<property name="username" value="sa" />
<property name="password" value="password" />

I then create the osql connection based on the username/password:

<if test="${username==''}">
  <property name="osql.connection" value="-E" />
</if>
<if test="${username!=''}">
  <property name="osql.connection" value="-U ${username} -P ${password}" />
</if>

I then pass these values onto my batch file:

<exec program="setup.bat">
   <arg value="${server}"/>
   <arg value="${database}" />
   <arg value="${osql.connection}" />
</exec>

The setup.bat file uses osql to drop the database:

osql -S %1 -d master %3 -Q "IF EXISTS (SELECT * FROM sysdatabases WHERE name = N'%2') DROP DATABASE [%2]"

This works fine if I do not pass a username/password to the nant script and use integrated security instead ("-E" to osql). If I do specify a username/password, then the nant script just pauses (like it is awaiting some input). I do know that I am specifying the correct username/password as I can log into SQL Connection Manager and delete the database.

Please let me know if there are any suggestions on what to try or alternate ways to do this.

Answer 1

I think you might want to experiment with the ORDER of your parameters... I seem to remember being bitten in the past by OSQL sensitivities to parameter order.

That's all I have for now... sorry.

Answer 2

I guess that %3 is expanded to "-U" only.

Probably this can be solved by

SET Server=%1
SET Database=%2
SHIFT 
SHIFT
osql -S %Server% ... %* -Q "...%Database%..."

OR set the property value to include double quotes.

Update after comment:

See this article on how to remove quotes from shell variables, and apply to the 3rd parameter.

Answer 3

Here are a few suggestions

1. To help diagnose the problem with the nant/batch script it would be helpful to echo out the full osql command (from the batch script) that is being executed. This is of course to make sure the osql.connection is being expanded properly when a username/pass is provided.
2. There maybe other reasons you are using a batch script that we are not privy to. However, to help diagnose the problem you can use the exec task directly passing in osql.exe as the program. Again, this is just to take out the batch script to see if it works by using osql.exe directly. Also, with that you'll be able to echo out the entire command string prior to executing, using the echo task.
3. For a completely different approach, you can try the sql task which is part of the NAntContrib distribution. Many times, it makes for a very nice alternative to osql.exe.

Maybe by trying some of these you'll be able narrow down what exactly is going on. Post your findings in the comments and I'll be happy to try and help.