SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Batch with SQL Twist
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sconder
Starting Member

USA
5 Posts

Posted - 11/29/2012 :  17:25:57  Show Profile  Reply with Quote
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
15659 Posts

Posted - 11/29/2012 :  17:29:53  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

sconder
Starting Member

USA
5 Posts

Posted - 11/29/2012 :  17:36:52  Show Profile  Reply with Quote
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???
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

349 Posts

Posted - 11/29/2012 :  17:49:24  Show Profile  Reply with Quote
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.

Go to Top of Page

sconder
Starting Member

USA
5 Posts

Posted - 11/29/2012 :  17:53:06  Show Profile  Reply with Quote
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 "")??
Go to Top of Page

sconder
Starting Member

USA
5 Posts

Posted - 11/29/2012 :  19:00:53  Show Profile  Reply with Quote
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.....
Go to Top of Page

sconder
Starting Member

USA
5 Posts

Posted - 11/29/2012 :  19:09:42  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000