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
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Help Creating SQL Server Agent Job
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Srgould41
Starting Member

USA
3 Posts

Posted - 11/02/2012 :  11:50:07  Show Profile  Reply with Quote
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?


Thanks

Steve

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/02/2012 :  12:13:21  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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 job
Next 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.
Go to Top of Page

Srgould41
Starting Member

USA
3 Posts

Posted - 11/02/2012 :  12:25:13  Show Profile  Reply with Quote
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 job
Next 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.

Go to Top of Page

Srgould41
Starting Member

USA
3 Posts

Posted - 11/02/2012 :  13:23:11  Show Profile  Reply with Quote
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.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/03/2012 :  10:40:11  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
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.06 seconds. Powered By: Snitz Forums 2000