Using osql with nant scripts
I am currently using osql with nant calling a batch file with arguments. Here are the properties that are defined in my nant script (no, not the 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" />
Then I create osql connection based on 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>
Then I pass these values to 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 great if I don't pass the username / password to the nant script and instead use integrated security ("-E" for osql). If I provide a username / password, then the nant script just pauses (for example, it waits for input). I know I am entering the correct username / password as I can log into the SQL Connection Manager and drop the database.
Please let me know if there are any suggestions for what to try or alternative ways to do this.
source to share
Here are some suggestions
- To help diagnose the problem with a nant / batch script, it would be helpful to respond to the complete osql command (from the batch script) that is being executed. This is, of course, to make sure the osql.connection expands correctly when the username / password is provided.
- There may be other reasons why you are using a script package that we are not tied to. However, to help diagnose the problem, you can use the exec task passed directly to osql.exe as a program. Again, this is just to take the script package to see if it works directly with osql.exe. Also, with this, you will be able to trace the entire command line before execution using the echo task .
- For a completely different approach, you can try the sql task which is part of NAntContrib . Many times this makes a very nice alternative to osql.exe.
Perhaps by trying some of them, you can narrow down what exactly is happening. Post your findings in the comments and I'll be happy to try and help.
source to share
I am assuming% 3 is only expanded to "-U".
Perhaps it can be solved with
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 third parameter.
source to share