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.
| Author |
Topic |
|
Srgould41
Starting Member
3 Posts |
 Posted - 2012-11-02 : 11:50:07
|
| Our SQL DBA just left and the boss said "I'm it". I don't have much SQL experience yet, but I'm learning. :)So here is the request I'm working on. The DBA created a .sql file to process a query against a number of tables. Once run he would export the query to a CSV file and FTP it. The request came in to automate this report daily.I am trying to create a job in SQL Server Agent to do this.I need to have a working job that will run the query, convert it to CSV, write out the file then FTP it. I have found the FTP steps and it seems to work now (hard to tell as the account was denied write which I am working to resolve with that department). I created the job step to run the query and output to a file using the Advanced - Output file option. It produces a nice plain text file, but not in CSV format.Next I tried this:EXEC master..xp_cmdshell 'M:\SQLScript\Dashboard.sql -o M:\outputfiles\windows_server.csv -c -t, -T -S'As a step it runs and never quits and does not produce an output.I would like to back track and ask for the best method to make this happen. The servers involved are internal so I'm not too worried with security, but I also like to stay security concious.Any suggestions, corrections, kick in the rear? ThanksSteve |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-11-02 : 12:13:21
|
| Depends really what the query is doing.Can you make it into a stored procedure which populates a table with the output - that would be the first step in a jobNext step is to output to a file - bcp would be simplest but you might like SSIS.Then you can upload to the ftp site in another step - you can use SSIS for that again or a .bat file might be simpler.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Srgould41
Starting Member
3 Posts |
Posted - 2012-11-02 : 12:25:13
|
I wouldn't mind turning it into a stored procedure. I was already thinking about that. I created a stored procedure for the FTP step. I would need the table to be temporary and in a different database. I do NOT want to mess with the source DB because it is a Systems Center Configuration Manager database. If you have suggestions where I could read up on these steps I would be greatful. I have been reading for a couple days now and am just running in loops finding the same articles and posts.quote:
Originally posted by nigelrivett
Depends really what the query is doing.Can you make it into a stored procedure which populates a table with the output - that would be the first step in a jobNext step is to output to a file - bcp would be simplest but you might like SSIS.Then you can upload to the ftp site in another step - you can use SSIS for that again or a .bat file might be simpler.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
Srgould41
Starting Member
3 Posts |
Posted - 2012-11-02 : 13:23:11
|
| Our old DBA just replied to my request for ideas. He intended to create an SSIS package for this so I am going to read up on that method instead. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-11-03 : 10:40:11
|
| People have a tendancy to put everything in a single package - I would resist that as it means moving control to the package and makes it difficult to maintain and diagnose problems. Put things in packages that woud benefit but keep them small and put sequantial tasks as different steps in a job.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Help Creating SQL Server Agent Job