February 1, 2011 at 2:20 pm
Objective: Create a job to execute a SQL statement just after midnight and save the results to a file. The file will always be the same file - output will overwrite, not append.
So far I have:
Created the SQL statement.
Started creating a Job - Step 1 runs the query.
Need to learn how to:
1. Save the results to a file, i.e. \\Server2\ExportData\NCMRExport.csv
Googling brought up:
1. bcp / xp_cmdshell but it sounds like this is a manual process.
2. one msdn library entry mentioned dts, but when I searched dts in Help nothing was found.
I think I can:
Finish creating the job, i.e. scheduling portion.
Any guidance to a tutorial or instructions in your reply would be appreciated. Right now I'm confused.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
February 1, 2011 at 3:42 pm
In sql server management studio, in object explorer, find your job; click properties, click the steps page, click the step that contains the query, click edit. In job step properties, select the advanced page and there you will find you can specify the output file.
Tom
February 2, 2011 at 6:31 am
Can you make this any easier? 🙂
I wonder why I couldn't find anything when I Googled - I spent almost 3 hours searching.
Thanks so much.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
February 2, 2011 at 10:36 am
EdA ROC (2/2/2011)
Can you make this any easier? 🙂I wonder why I couldn't find anything when I Googled - I spent almost 3 hours searching.
Thanks so much.
Maybe you should brush up your search string construction skills, it's pretty easy to find.
As for making it easier, if you haven't created the job create it using SSMS, and use the step configuration - advanced page and fill in the full file path, including the filename and extension. If you already have created the job, run SSMS, in object explorer find your job, then drill down to the advanced page as described in previous post and fill in your file details. The filename can have a UNC path, so pointing to files on a different machine is straightforwards (subject to appropriate permissions, as always).
If you need the job to write different files each day, insert a first jobstep - a cmdexec jobstep - to rename yesterday's file by adding yesterday's date to the filename (before the .log extension) if the file exists, so that you just have one fixed filename in the step properties (that's what I did in SQL Server 2000 - it will still work; there may be an easier way in SQL 2008, I don't know). You can change the filename in a jobstep after writing the file instead if you work out handling failure of the other step.
If you want multiple copies of the log in different places you can add a final cmdexec jobstep to make the copies.
I don't know how to describe it in any more basic terms than that. Oh, if you are on SQL 2000 replace SSIS above with EM.
Tom
February 3, 2011 at 5:44 am
LOL ...
Tom, please accept my apologies. I was joking when I said, "Can you make it any easier?"
That's about as easy as it can get.
Thank you for taking the time and effort to elaborate. Again, my apologies for the confusion.
And I didn't express myself well when I commented on "not finding anything". I found a lot, but...
1. Nothing mentioned the Advanced page. Hence, it seemed the SSMS Job wasn't going to be the solution.
2. The 'bcp' solution appeared to be a manual process (nothing I found had instructions to make bcp a job).
3. The SQLCMD was a cmd window solution.
I'm trying to find a simple solution. Pulling data like this is new territory for me and our company. I'm certain the task is far from being unique, so it seemed that I would have found some instructions in my searches. I used a lot of variations of keywords in my searches, including:
ms sql server 2008 export query results to flat file (and used csv)
ssms ... (the ellipses represent the above key words)
ssis ...
sqlcmd ...
Is there a way to eliminate the header from the output file, leave just the data? The output file will be imported into a Lotus Notes database.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
February 3, 2011 at 7:01 am
OK, I'm going to try and build a command in SSMS (2008) Query editor using SQLCMD. From what I've read in my searches, I can use SQLCMD, I don't have to use a cmd window. But, the information leaves me at a dead end...
The following is what I've created so far. Getting error message:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'o'.
Notes:
1. I have enclosed the full path in single and double quotes without success.
2. One example included ^ at the end of lines, I added those without success.
Can this be done this way? If so, what do I have incorrect? If not, what do you suggest?
-- SQL Export File Used By Lotus Notes NCMR Import Utility
-- Query runs in agent, to output results to csv file each night at 12:01 AM
-- Feb0311 EdA v000 New
sqlcmd
-o \\ourserver\Lotus\Domino\data\AmtechExportData\NCMROrders.csv
-h -1
-s','
-q
SELECT
ORDERS.ORDER_NO AS OrderNum,
ORDERS.JOB_NUMBER AS JobNum,
ORDERS.FORM_NO AS FormNum,
CUSTOMER.CSNAME AS CustName,
isnull(SPECS.CUSTOMER_PART_NO,'') AS CustPartNum,
isnull(SPECS.CUST_IDENT,'') AS ItemIdent,
ORDERS.QTY_ORDERED AS QtyOrdered,
0 AS FullFactoryCost
FROM ORDERS
LEFT OUTER JOIN JOBS ON ORDERS.JOB_NUMBER = JOBS.JOB_NUMBER
LEFT OUTER JOIN CUSTOMER ON ORDERS.CSCODE = CUSTOMER.CSCODE
LEFT OUTER JOIN SPECS ON ORDERS.SPEC_NO = SPECS.SPEC_NO
WHERE
(ORDERS.PLT_NO = 1) AND (ORDERS.JOB_NUMBER IS NOT NULL)
AND (ORDERS.COMPLETION_FLG <> 'X')
AND (ISNULL(JOBS.JOB_CLOSE_DATE, GETDATE()) > DATEADD(month, - 3, GETDATE()))
ORDER BY OrderNum, JobNum, FormNum;
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
February 3, 2011 at 8:24 am
EdA ROC (2/3/2011)
OK, I'm going to try and build a command in SSMS (2008) Query editor using SQLCMD. From what I've read in my searches, I can use SQLCMD, I don't have to use a cmd window. But, the information leaves me at a dead end...The following is what I've created so far. Getting error message:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'o'.
Notes:
1. I have enclosed the full path in single and double quotes without success.
2. One example included ^ at the end of lines, I added those without success.
Can this be done this way? If so, what do I have incorrect? If not, what do you suggest?
-- SQL Export File Used By Lotus Notes NCMR Import Utility
-- Query runs in agent, to output results to csv file each night at 12:01 AM
-- Feb0311 EdA v000 New
sqlcmd
-o \\ourserver\Lotus\Domino\data\AmtechExportData\NCMROrders.csv
-h -1
-s','
-q
SELECT
I haven't used SQLCMD myself, but I imagine it requires command line options (including any query introduced by the -q or -Q option) on the command line, not on separate lines.so that putting the -o,-h,-s and -q command line options on separate lines isn't going to work.
Also I think you may have to use the -i option instead of -q so as to put the query in a separate file and avoid running into a command line length limit (and perhaps too the -q should be -Q if you don't change to -i: you want SQLCMD to exit after generating its output); or maybe you can have none of -i, -q, and -Q,place the query immediately after the command line (starting at the beginning of a new line) and follow the query with a line containg :EXIT (I think that if you don't specify any input file or command line query it takes input from stdin until it sees a :EXIT command).
Tom
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply