| Author |
 Topic  |
|
|
sconder
Starting Member
USA
5 Posts |
 Posted - 11/29/2012 : 17:25:57
|
Ok so I have been learning SQL on my own and have run into a scenario where I need to take text.txt get two pieces of data (PO and DATE) and run Sql query, use the return on that and append it to the original file....
So, the first section runs perfect upto the first pause, then I get an sql connection error, it works if I use sqlcmd -S <server> -E -i <input file> -o <output file>, but I don't know how to use the %variables% from the first section with that method, ideas, suggestions????
Echo on
For /f skip^=3^ tokens^=5^,11^ delims^=^" %%G in (R554312.txt) do (
SET POnum=%%~G
SET Rdate=%%~H
)
echo %POnum%
echo %Rdate%
pause
sqlcmd -S mpvssql1 -E -i SELECT [LotKey] FROM [Access].[dbo].[LotMaster] where [WorkOrderNumber]=%POnum% or [PurchaseOrderNumber]=%POnum%
and [Date]=%Rdate% -o "\\mpvs0009\groups\90 day storage\sconder\rie for labels\wip\sql.txt"
@echo off
for /f "skip=2 delims=" %%a in (\\mpvs0009\groups\90 day storage\sconder\rie for labels\wip\sql.txt) do if not defined item set item=%%a
setlocal EnableDelayedExpansion
set c=0
del "R554312.dd" 2>nul
for /f "delims=" %%a in (R554312.txt) do (
set /a c=c+1
if !c! EQU 3 (
>>"R554312.dd" echo %%a, "Lot Number"
) else (
if !c! EQU 4 (
>>"R554312.dd" echo %%a, "%item%"
) else (
>>"R554312.dd" echo %%a
)
)
)
pause
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 11/29/2012 : 17:29:53
|
Try this:
sqlcmd -S mpvssql1 -E -Q"SELECT [LotKey] FROM [Access].[dbo].[LotMaster] where [WorkOrderNumber]=%POnum% or [PurchaseOrderNumber]=%POnum%and [Date]=%Rdate%" -o "\\mpvs0009\groups\90 day storage\sconder\rie for labels\wip\sql.txt"
The -i switch indicates a SQL file, not a SQL statement. -Q does the latter. |
 |
|
|
sconder
Starting Member
USA
5 Posts |
Posted - 11/29/2012 : 17:36:52
|
| Thanks, that seemed to fix the connection issue, now I am getting "Unexpected argument" which is after the "or" statement, I believe this is referrancing the "and" statement, however that works if I use it in a SQL query in my SQL server management studio, what am I missing there??? |
|
|
|
bitsmed
Yak Posting Veteran
Denmark
98 Posts |
Posted - 11/29/2012 : 17:49:24
|
Space is missing between "%POnum%" and "and"
quote:
Originally posted by robvolk
Try this:
sqlcmd -S mpvssql1 -E -Q"SELECT [LotKey] FROM [Access].[dbo].[LotMaster] where [WorkOrderNumber]=%POnum% or [PurchaseOrderNumber]=%POnum%and [Date]=%Rdate%" -o "\\mpvs0009\groups\90 day storage\sconder\rie for labels\wip\sql.txt"
The -i switch indicates a SQL file, not a SQL statement. -Q does the latter.
|
|
|
|
sconder
Starting Member
USA
5 Posts |
Posted - 11/29/2012 : 17:53:06
|
| I tried with space and without and got the same error message, is there a way to include the whole query (i.e. I tried quotations to no avail "")?? |
|
|
|
sconder
Starting Member
USA
5 Posts |
Posted - 11/29/2012 : 19:00:53
|
Ok I put the whole sql argument into () and now it isn't breaking up the command and added '%Rdate' so that the sql part looks like the following:
(sqlcmd -S mpvssql1 -E -Q SELECT [LotKey] FROM [Access].[dbo].[LotMaster] WHERE [WorkOrderNumber]=%POnum% OR [PurchaseOrderNumber]=%POnum% AND [Date]='%Rdate%' -o "\\mpvs0009\groups\90 day storage\sconder\rie for labels\wip\sql.txt")
I still get "unexpected argument" error at the end..... |
|
|
|
sconder
Starting Member
USA
5 Posts |
Posted - 11/29/2012 : 19:09:42
|
ok got it sql part needed to be:
sqlcmd -S mpvssql1 -E -Q "SELECT [LotKey] FROM [Access].[dbo].[LotMaster] WHERE [WorkOrderNumber]=%POnum% OR [PurchaseOrderNumber]=%POnum% AND [Date]='%Rdate%'" >> "\\mpvs0009\groups\90 day storage\sconder\rie for labels\wip\sql.txt"
whew thanks for the help! |
|
|
| |
Topic |
|
Batch with SQL Twist
