Batch with SQL Twist - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Batch with SQL Twist

Author

Topic

sconder
Starting Member

5 Posts

Posted - 2012-11-29 : 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

15732 Posts

Posted - 2012-11-29 : 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

5 Posts

Posted - 2012-11-29 : 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
Aged Yak Warrior

545 Posts

Posted - 2012-11-29 : 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

5 Posts

Posted - 2012-11-29 : 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

5 Posts

Posted - 2012-11-29 : 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

5 Posts

Posted - 2012-11-29 : 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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources