SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Mar 2005
    Location
    Cairns, Queensland, Australia
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Editing job created with SQL Server Web Assistant

    Hi,

    I came across an excellent article at http://www.sitepoint.com/article/ser...sistant-wizard which shows how to use the SQL Server Web Assistant to *create* Web pages/reports from SQL Server database data.

    We have a situation where a previous employee used the Web Assistant to do this, but now we don't know how to edit the setup.

    We need to change the file path for the output file for existing Web Assistant Jobs... but I am not too sure how to do this: is there a way to use the Web Assistant Wizard to *edit* an existing job, rather than creating, like the article referred above demonstrates?

    Or if not, can anyone help advise what I need to do to change the path of the output file for existing Web Assistant jobs?

    Thanks
    Kevin

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, there is no way to use the web assistant to modify an existing web task.

    You have the following options:
    Option A
    1) Delete the task which creates the web page(s). You'll find that in the Enterprise Manager, under Management, "SQL Server Agent". One of the tasks is responsible for creating the html page ot regular intervals.

    2) Create a new web task using the web assistant

    or
    Option B
    List the details of all currently stored web tasks:

    Code:
    select * from msdb.dbo.mswebtasks
    and then use an SQL Update statement to change the path of the task(s) you want changing.

    HTH
    Richard

  3. #3
    SitePoint Member
    Join Date
    Mar 2005
    Location
    Cairns, Queensland, Australia
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thanks for the reply... it has helped somewhat...

    Yep.. was aware of the Job/task that runs. I guess I was hoping not to have to re-create them all. But it looks like I'm going to have to.

    Just one note: I did try modifying the output file path in the table you referenced (mswebtasks) (thanks for the pointer), but when I re-execute the job/task, it doesn't take notice of the new path. Any other thoughts/suggestions there?

    Thanks
    Kevin

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by smitk
    Yep.. was aware of the Job/task that runs. I guess I was hoping not to have to re-create them all. But it looks like I'm going to have to.
    So what exacty is the step performed by the job?
    If you go to the job's properties, and edit step 1, it will be something like:

    Code:
    exec sp_runwebtask N'task naem', 'OUTPUT FILE'
    What does your job specify? And have you changed the output file parameter yet?

  5. #5
    SitePoint Member
    Join Date
    Mar 2005
    Location
    Cairns, Queensland, Australia
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the scenario: a previous employee set up some reports using the Web Assistant wizard which are automatically generated out of one of our databases and these are created in a folder which is then meant to be accessed on our Intranet.

    We need to change the physical path that the files are output into.

    Here is one example:
    The Job name is : "FND Airport Building Services Report"
    It has one TSQL step and the code is:
    Code:
    EXEC sp_runwebtask @procname = N'FND Airport Building Services Report'
    (note that it doesn't have the output file here as a parameter)

    That procedure has the following code:
    Code:
    CREATE PROCEDURE [FND Airport Building Services Report]  AS EXECUTE [FND_AirportBuildingServices_Open]
    FND_AirportBuildingServices_Open is another Stored procedure that actually does the Select SQL to produce the report.

    The entry in mswebtasks for this one is:
    procname: FND Airport Building Services Report
    outputfile: c:\webpage1.htm
    taskstat: 1
    wparams: <Binary>
    trigflags: 0
    taskid: 1

    I execute the job and the web page is produced and placed into c:\. This is the file name and path that was specified during the Web Assistant wizard.

    Now, if I want to change the output path, say to "c:\reports\webpage1.htm" (and assuming the folder reports does exist), I change the value in the table as you suggested. When I re-run the job, the report does not get placed into the new path, but is created in the old path still.

    I am also wondering if the "wparams" is impacting on this: perhaps the output file path is stored in the wparams as well?

    We have about 8 of these reports (plus some more in another database) that we need to change the output path for, and I didn't want to have to re-create them all initially. But it is looking like I might just have to if I can't get the output file path to change. Any further suggestions?

    Thanks
    Kevin


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •