Nightly query using isql or bcp? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Nightly query using isql or bcp?

Author

Topic

scream775
Starting Member

5 Posts

Posted - 2009-09-08 : 13:22:36

We need to export a report from our SAP Business One system every morning so our e-commerce software can read the data file and update inventory levels on our website.

We need a way to run the report (which contains ItemCode and Inventory data) once a day and save it to a file (which should be named by date, if possible).

It was suggested that I create a nightly job using SQL Server Agent (SQL Server 2005) and then run a command using isql or bcp.

I tried using isql to accomplish this but it results in a syntax error near "S"

isql -S SERVER -U ***** -P ***** -Q "SELECT T0.ItemCode, T0.ItemName, T0.OnHand, T0.U_SES_Web, T1.PriceList, T1.Price FROM OITM T0 INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode WHERE (T1.PriceList = 1) AND (T0.U_SES_Web = 'Yes')" -o C:\output.txt

Then I tried using bcp and it results in an error near "queryout"

bcp "SELECT T0.ItemCode, T0.ItemName, T0.OnHand, T0.U_SES_Web, T1.PriceList, T1.Price FROM OITM T0 INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode WHERE (T1.PriceList = 1) AND (T0.U_SES_Web = 'Yes')" queryout "c:\sample.txt" -T -c

Anyone have any idea what is wrong with these command lines?

Another question, what would be the easiest way to make sure the file that is output is saved with the date in the filename?

Thanks,
Steve

scream775
Starting Member

5 Posts

Posted - 2009-09-09 : 12:26:46

Bump for anyone who can give me any direction!

Thanks

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-09 : 16:40:38

The syntax for BCP is missing the server and database name:
bcp "SELECT T0.ItemCode, T0.ItemName, T0.OnHand, T0.U_SES_Web, T1.PriceList, T1.Price FROM OITM T0 INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode WHERE (T1.PriceList = 1) AND (T0.U_SES_Web = 'Yes')" queryout "c:\sample.txt" -T -c -S ServerName -d DatabaseName

Instead of isql use sqlcmd:
sqlcmd -S SERVER -E -Q "SELECT T0.ItemCode, T0.ItemName, T0.OnHand, T0.U_SES_Web, T1.PriceList, T1.Price FROM OITM T0 INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode WHERE (T1.PriceList = 1) AND (T0.U_SES_Web = 'Yes')" -o C:\output.txt

To get the date in the filename, you can use the dos functions to format the date as you like.

You can also use SSIS to extract the data

scream775
Starting Member

5 Posts

Posted - 2009-09-09 : 18:16:26

Thanks YellowBug!

I was able to get this working by doing the following:

1. Save the SQL query as a .sql file

2. Create a batch file that properly formats a date variable followed by the command:

sqlcmd -S ***** -U ***** -P ***** -i "D:\InvUpdate\InvUpdate1.sql" -s "," -W -m 1 -o D:\InvUpdate\%date%_InvUpdate1.csv

3. Used Task Scheduler to run the batch file every morning.

The system works fine, and sqlcmd does almost everything I need it to do.

Now here is the issue I am running into:

The file that is output contains column headers at the top, a row of dashes, and then at the end of the file, a line displaying how many rows were affected.

I need to retain the column headers but get rid of the row of dashes and the "rows affected" text at the end.

I know how to suppress the column headers along with the dashes, but how do I supress the dashes and "rows affected" text, while retaining the column headers.

Any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-09 : 22:14:37

why don't you use BCP queryout ?

KH
[spoiler]Time is always against us[/spoiler]

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-10 : 07:41:59

To suppress the "rows affected" message, add this line to the top of the input sql file
SET NOCOUNT ON;

To remove the dashes, add this to after the sqlcmd call:
findstr /B /V /C:"------" filename1.out > filename2.out

HTH

scream775
Starting Member

5 Posts

Posted - 2009-09-17 : 12:49:10

Thanks YellowBug!

nmeyer
Starting Member

1 Post

Posted - 2011-07-22 : 14:56:10

Thanks! Seems like there ought to be a better way, but it works! I wound up with a batch file like this for the job agent to run (because I needed tab-delimited output from a stored procedure, and I needed the headers).

sqlcmd -S (local) -E -s" " -Q "sp_MyProc" -o "C:\Documents and Settings\me\Desktop\Procoutput.txt" -W
findstr /B /V /C:"------" "C:\Documents and Settings\me\Desktop\Procoutput.txt" > "C:\Documents and Settings\me\Desktop\Myoutput.txt"
del "C:\Documents and Settings\me\Desktop\Procoutput.txt"

quote:
Originally posted by YellowBug

To suppress the "rows affected" message, add this line to the top of the input sql file
SET NOCOUNT ON;

To remove the dashes, add this to after the sqlcmd call:
findstr /B /V /C:"------" filename1.out > filename2.out

HTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources