Post reply

how to create the contents of a bat file, then execute it?

  • July 7, 2008 at 11:03 pm

    #189219

    using sql 2000, I see that there is an xp_cmdshell windows program that will execute a .bat file for me (EXEC master..xp_CMDShell ‘mybat.bat’)

    However, I need to dynamically create the contents of the bat file first. I'm trying to save a bunch of URL's from a table to individual web pages, and I found a utility called URL2FILE which will allow a .bat file to be executed like this:

    URL2File http://www.chami.com/tips/file1.zip file1.zip

    URL2File http://www.chami.com/tips/file2.zip file2.zip

    URL2File http://www.chami.com/tips/file3.zip file3.zip

    So, what I'd like to do is to be able to schedule a stored procedure to run periodically:

    Select * from table

    WHERE URL is not NULL

    then have it create the bat file:

    URL2FILE filename.html

    etc.

    then have it execute the .bat file it created.

    What is the best/easiest way to accomplish this? Can I do it in 1 stored procedure, or do I need multiple steps or a view? Any help is greatly appreciated. Thanks!

  • July 8, 2008 at 7:49 am

    #839495

    if you have to do something like edit .bat files, it may be best to make a DTS package with appropriate rights so you can save your .bat file. DTS offers you the chance to use the VB scripting object to manipulate your bat file and save it. Then you can call your stored procedure (which calls your bat file) after the bat file has been changed and saved. If you are not familiar with DTS packages, you may want to take a quick look here:

    http://www.sqldts.com/278.aspx

    Also for those new to the DTS package,, you should know that where you run the package from determines which C: drive you are actually using; so if you run the package form your computer, it will act liek yuor computer is teh C drive; if its the server, the server will be the c drive. Using the proper computer name will help.

    Also Vb scripting is fairly simple; there are tons of examples on the web.

  • July 29, 2008 at 11:42 am

    #850022

    I got this working nicely, thank you very much.

  • July 29, 2008 at 12:46 pm

    #850067

    Similarly, I've called a vbscript file from xp_cmdshell and wrote the .bat file.

    DAB

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply