Skip to Content

For this you can use SQLCMD from the command line as shown below:

## running a proc to update a view eg ##
SQLCMD -S ServerSQL -d SBODemoUS -U sa -P sap123 -Q “EXEC Proc_Name”

## exporting a file as example ##

SQLCMD -S ServerSQL -d SBODemoUS -U sa -P sap123 -Q “SELECT * FROM OITM” -o C:\MyOutput.txt

## Generically you can use the following syntax ##

SQLCMD -S ServerSQL -d SBODemoUS -U sa -P sap123 -Q “Your Query” -s “,” -o C:\MyOutput.csv

Now you can go to your file location and open the file and you will see that new csv file created there. When you open the csv file you will notice the results of the query.

You can use SQLCMD combined with the scheduled task

More information:

http://msdn.microsoft.com/en-us/library/ms180944.aspx

http://msdn.microsoft.com/en-us/library/ms162773.aspx

Create a scheduled task in Windows Server 2008

http://www.hosting.com/support/windows-server-2008/create-a-scheduled-task-in-windows-server-2008

How To Schedule Tasks in Windows XP

http://support.microsoft.com/kb/308569

Regards,

Marcelo Silva Santos

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Richard Thurlow

    Hi Marcelo,

    I got your email address off SAP Community Network – we are a SAP B1 customer.

    I am trying to locate some assistance using the SQLCMD function in querying a database to export to a file.

    I have seen your good example page – http://scn.sap.com/community/business-one/blog/2013/06/28/how-to-schedule-a-command-to-sql-server but this helps me to a point.

    I have been able to have the SQLCMD run from a batch file periodically to my server.

    However – I need to amend the query that I am using.

    At the moment this is what I have in the batch file:

    SQLCMD -S <servername> -d <databasename> -U <username> -P <password> -Q “SELECT ItemCode, FrgnName, Qrygroup2, U_Dimensions, U_AgeRange, U_Category, OnHand-IsCommited FROM OITM” -s “,” -o “\\serveraddress\TestBat.csv” -h-1 -s”~” -W -w 999

    This outputs a CSV with the fields:

    • Product Code
    • Item Name
    • Property 2
    • Dimensions (UDF)
    • Age Range (UDF)
    • Category (UDF)
    • Stock on hand less stock committed

    Now – this is a good start.

    However – I need to know how to write the query that will allow me to join tables so that I can only select the Onhand-Iscommited values from a specific warehouse.

    In this case I want to join the OITW table with the OITM table, then I want to have a WHERE clause to say where WhsCode = 01. Main.

    If you are able to assist me with this I would be greatly appreciated.

    Regards,

    Rick Thurlow

    (0) 

Leave a Reply