Batch to run SQL script

[steelstorm]

Hi all,

I am looking for help to run a SQL query from a batch file. I mainly just do helpdesk for pc issues so this is starting to get a little over my head. The main reason I want to do this is because I basically get about 40 tickets a day to run this script, and I thought if a batch file could be made for the person that has to enter these he could easily do it.

Below is the script I run:

USE ExampleDataBase
exec DocumentsCopyVendorViewable @OrderNo=119997, @SourceItemNo=1, @DestItemNo=2
Now the part that changes that I would like the end user to be able to clarify via the command prompt are:
OrderNo=
SourceItemNo=
DestItemNo=

and once they entered these three fields echo to the screen to show how many rows were affected....or if there was an error.

Any help is much appreciated, I have done some research via google but I'm just not getting anywhere. I'm more of the hardware guy in this place)

[foxidrive]

Are you showing us the script or the output from the script?

[steelstorm]

Below is the script itself I enter in. The parts in bold is what are constantly changing that the end user would need to enter themselves.

USE ExampleDataBase
exec DocumentsCopyVendorViewable @OrderNo=119997, @SourceItemNo=1, @DestItemNo=2

[foxidrive]

Your question is unclear.

I think you seem to want to enter the commands in an SQL window - I'm hazy here as I don't use SQL.

If they are SQL commands then you need to firstly look into how you can use the .exe command line tools that come with the SQL install to see how you can enter them from the command line.

After that the batch file can be written to launch the commands, with parameters for your data input.

[aGerman]

steelstorm

A batch script can write an SQL script and pass it to your SQL application. So in case you want to do that this code could be the first step:

Code: Select all

@echo off &setlocal
set /p "OrderNo=OrderNo: "
set /p "SourceItemNo=SourceItemNo: "
set /p "DestItemNo=DestItemNo: "
>"test.sql" (
  echo USE ExampleDataBase
  echo exec DocumentsCopyVendorViewable @OrderNo=%OrderNo%, @SourceItemNo=%SourceItemNo%, @DestItemNo=%DestItemNo%
)

Since we don't know the application where to pass the script (or the file extension that is associated with this application) I don't know how to write the second step.

Regards
aGerman

[steelstorm]

thanks for all the replies. The above example was exactly what I was looking to do. I use Sequel Server Management Studio to run any queries. I'm guessing this is the application you were needing info on to write the second step?

I read up a little on the sqlcmd via a batch file and I'm guessing this is something I may need to look into more?

[aGerman]

Seems that sqlcmd is the interface you have to use.
http://technet.microsoft.com/en-us/library/hh213540.aspx

The -i switch seems to be the right one to specify the SQL script.
Try to append the following line to my code.

Code: Select all

"sqlcmd.exe" -i "%~dp0test.sql"

If sqlcmd.exe isn't located in the PATH environment you have to write the entire path of sqlcmd.exe.

Regards
aGerman

Edit:
I read some more. It looks like you have to close the lines with semicolons and you have to add GO commands.

Code: Select all

@echo off &setlocal
set /p "OrderNo=OrderNo: "
set /p "SourceItemNo=SourceItemNo: "
set /p "DestItemNo=DestItemNo: "
>"test.sql" (
  echo USE ExampleDataBase;
  echo GO
  echo exec DocumentsCopyVendorViewable @OrderNo=%OrderNo%, @SourceItemNo=%SourceItemNo%, @DestItemNo=%DestItemNo%;
  echo GO
)
"sqlcmd.exe" -i "%~dp0test.sql"
del "test.sql"
pause

The PAUSE command prevents the window from closing to see the messages that are displayed. You can remove it if you don't need it anymore.

[steelstorm]

Awesome. Thanks alot for the help. I believe from the info you have provided I should be able to get this working.

This is seriously going to cut about an hour out of my day I can spend on much more productive items.

Thanks again!

Phill