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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-02-26 : 08:11:43
|
| Jay writes "I've been tasked with making an automatic process that will on a nightly basis: 1) get a record set of training completions by students for the day, 2) build a formatted text file with the information from the record set, 3) run a PGP program on the text file to encrypt the file, and 4) establish a connection to an FTP site and put a copy of the formatted text file there.I was thinking of using Task Scheduler on the server to start a program (VB? or a script file? not sure) that would connect to the SQL database, get the record set, and build the formatted file. Then have the VB program kick off the PGP program, and finally have VB connect to the FTP site and copy the encrypted file over. But then...my boss got wind of my preliminary plans I just described and his reaction was "Why don't you do it all in SQL because you can schedule things in it?" I asked if he could be a little more informative about this and how to deal with creating an external file or involving FTP and he didn't know how of course. He just knows it ought to be able to be done in SQL.So, my question is do you know if doing all of this in SQL is possible? Or is it better to use an outside program(s) with Task Scheduler? And if possible in SQL (or preferable) could you point me in some right directions where to start digging out the information on how to do the things listed above? I'm starting to go through the SQL Bible and I'm going to check the MSDN SQL content out. My experience with SQL is "amateur." Up to this point I've basically made ASP web pages that access the SQL database. And if this really isn't possible in SQL of course I don't want to spend a lot of time finding that out the hard way.Our SQL server runs SQL 2000. We have the current site on one server and the database for the site is on the SQL server.Appreciate any help/opinion on this.Jay Tate" |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-26 : 08:22:22
|
| You can do ANYTHING in SQL. lolSeriously, with a combination of the sp_OA procs, xp_cmdshell, and job scheduler, you can do what you described using SQL only. My questions would be why would you want to though.Create a procedure that will get the recordset and dump it out to a file. Call the procedure, run your program, and ftp the file using a programming language such as VB. SQL is meant to manipulate data and data structure. You can "kluge" it together to do almost anything. That's not what it's designed for though.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-26 : 08:26:38
|
| You can definitely do this as a SQL Server job (I've done it myself), and you don't need DTS or any custom VB to do the encryption and FTP tasks, as long as you have the command line version of PGP. This outlines the steps:1. Create a stored procedure that can generate the output you're looking for. Ideally it shouldn't require any parameters to be passed to it.2. Use the bcp command-line utility to execute the stored procedure and output the results to a text file. Books Online has more details on bcp, it's very easy to use.3. Use the PGP command-line utility to encrypt the file. There are a few versions out there, including a GNU version that is completely free to use. There's a manual file that describes how to generate keys and encrypt files (getting the hang of this was the hardest part I had to face, and it wasn't that hard really)4. Windows has a command-line FTP utility that is very easy to use. You can create an FTP script file that contains the commands needed to open an FTP site, log in, change directories if needed, and send the file. The Windows help file on the start menu has all the details.As far as automating the process, you would create a new job in SQL Server and add a CmdExec step (NOT a T-SQL step) You now have two choices here: create a batch file that peforms the above steps 2 through 4. You would then call the batch file from the CmdExec step. Or, you can create separate CmdExec steps for each step 2-4. This latter option would give you a little more control over step processing, skipping steps, retrying steps, etc. than a batch file would, and allows you to be notified if a single step fails. It's probably overkill though.Once the step(s) are set up, you can schedule the job to run whenever you like. Notifications can also be set, but you may not get the expected results from a job failure notification. As far as SQL Server is concerned, as long as the batch file starts it's considered successful, even if the batch operations fail. You should play around with the batch file and run it manually to see how it responds before putting it into a SQL Server job. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-26 : 08:54:45
|
| Just out of curiosity: wouldn't it be possible to do this with task-scheduler, a .bat-file and osql?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-26 : 20:41:13
|
| Yeah, but if you already have SQL Agent running why not utilize it? Since the whole process is dependent on SQL Server data anyway, there's no point in it trying to run should SQL Server be down. And it's better to encapsulate it under one roof than to split it off under another program. You also get notifications and at least some kind of error handling with SQL Agent. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-27 : 00:40:15
|
| I guess you have a pretty good point there :) |
|
|
|
|
|
|
|
|
Automating a file download inside SQL