[MSSQL] sp_makewebtask probleem

[Gé Brander]

Ik heb een probleem wat ik niet goed kan plaatsen met de sp_makewebtask functionaliteit van SQL Server 2000. Ik gebruik de mogelijkheid al geruime tijd naar behoren, maar ik loop tegen een wat vreemde eigenschap aan:

In den beginne maakte ik de webtask aan in de msdb database. Aangezien we daar geen user objects in willen hebben, heb ik besloten daar een aparte database voor te maken, met daarin nog andere beheer objecten.

De webtask laat zien of er job's foutgelopen zijn, wat de vrije schijfruimte is op de SQL Server 2000 computer, of er packet error's zijn opgetreden en nog wat andere info over de server.

De onderstaande webtask draait prima en zonder problemen in de msdb database, maar geeft een wat vreemde output (te weinig) als ik het in de andere database (da_dbb) aanmaak.

In de query worden er enkele tabellen in de da_dbb database aangesproken en enkele tabellen in de msdb database aangesproken.

Vanuit de msdb database kan de webtask alles weergeven wat de bedoeling is, dus ook de informatie vanuit de da_dbb database. Geen enkel probleem.

Vanuit de da_dbb database kan de webtask alleen de gegevens vanuit de da_dbb database uitlezen, en de packet error's weergeven, maar alle info uit de msdb database wordt niet weergegeven. Ik krijg ook geen foutmelding.

Als ik de code van de query in Query Analyzer run, dan werkt het vanuit beide databases goed! Het maakt dan ook niet uit onder welke user ik dan connectie maak, het werkt altijd. Ook als ik Query Analyzer run als de service account van de SQL Server of SQL Agent service, dan werkt het goed in beide gevallen.

Ik weet dus even echt niet meer waar ik nu moet zoeken. Misschien heeft iemand hier dit al eens bij de hand gehad.
Ik heb gezocht op Google, Microsoft en vel andere SQL sites, maar niets meer gevonden dan de standaardzaken.

De code van de webtask:

code:

SP_dropwebtask '_JOB_dbb_web_tsk'
GO

DECLARE @ins_nam AS VARCHAR(50)
DECLARE @vir_srv_nam AS VARCHAR(50)
DECLARE @mch_nam AS VARCHAR(10)
DECLARE @out_fil AS VARCHAR(1024)
DECLARE @web_pag_ttl AS VARCHAR(1024)
DECLARE @res_ttl AS VARCHAR(1024)
DECLARE @web_adr AS VARCHAR(1024)
DECLARE @ref_txt AS VARCHAR(1024)
DECLARE @dbs_nam AS VARCHAR(50)
DECLARE @run_tyd_job_his_web_tsk AS INT
DECLARE @prc_nam AS VARCHAR(50)

-- Plaats de instance naam in de variabele @srv_nam vanuit 
-- de tabel _Tbl_dbb_ins
SET @ins_nam = (
    SELECT [ins_nam] 
    FROM [da_dbb].[dbo].[_Tbl_dbb_ins] 
    WHERE [srv_nam] = @@SERVERNAME)

-- Plaats de machine naam in de variabele @vir_srv_nam vanuit 
-- de tabel _Tbl_dbb_ins
SET @vir_srv_nam = (
    SELECT mch_nam 
    FROM [da_dbb].[dbo].[_Tbl_dbb_mch] 
    WHERE [mch_idf] = (
        SELECT mch_idf 
        FROM [da_dbb].[dbo].[_Tbl_dbb_ins] 
        WHERE [srv_nam] = @@SERVERNAME))

-- Indien de kolom vir_srv_nam in de tabel _Tbl_dbb_ins niet NULL 
-- is dan dient de variabele @vir_srv_nam overschreven te worden 
-- met deze waarde
IF (
    SELECT [vir_srv_nam] 
    FROM [da_dbb].[dbo].[_Tbl_dbb_ins] 
    WHERE [srv_nam] = @@SERVERNAME) IS NOT NULL
SET @vir_srv_nam = (
    SELECT [vir_srv_nam] 
    FROM [da_dbb].[dbo].[_Tbl_dbb_ins] 
    WHERE [srv_nam] = @@SERVERNAME)

-- Bepaal de waarde voor de variabele @outputfile in de sp_makewebtask
SET @out_fil = '\\nt65\wwwroot$\SQLServer\Results\' + @vir_srv_nam 
                            + '\' + @ins_nam + '.htm'

-- Bepaal webpagina titel
SET @web_pag_ttl = 'Microsoft SQL Server Web Assistant'

-- Bepaal resultaat titel
SET @res_ttl = @vir_srv_nam + '\' + @ins_nam + ' Server informatie.'

-- Bepaal web adres (url) op de pagina
SET @web_adr = 'http://nt65/SQLServer/jobhistory.htm'

-- Bepaal referentie text
SET @ref_txt = 'Terug naar overzicht'

-- Bepaal database voor de task
SET @dbs_nam = 'da_dbb'

-- Bepaal de tijd van starten van de schedule
SET @run_tyd_job_his_web_tsk = (
    SELECT [run_tyd_job_his_web_tsk] 
    FROM [da_dbb].[dbo].[_Tbl_dbb_ins] 
    WHERE [srv_nam] = @@SERVERNAME)
-- Bepaal de Jobnaam
SET @prc_nam = '_JOB_dbb_web_tsk'

EXECUTE sp_makewebtask @outputfile = @out_fil
    , @query=N'SELECT [omg] AS [Omgeving],
        [srv_nam] AS [Servernaam],
        [srv_inf] AS [Serverinformatie],
        [mch_nam] AS [Machinenaam],
        [prt_num] AS [Poortnummer],
        [vir_srv_nam] AS [Virtuele servernaam],
        [cls_nam] AS [Clusternaam]
    FROM [da_dbb].[dbo].[_Tbl_dbb_srv_inf] 
    WHERE [dtm_get_inf] = (SELECT MAX(dtm_get_inf) 
        FROM [da_dbb].[dbo].[_Tbl_dbb_srv_inf])
    
    SELECT @@PACKET_ERRORS AS [Aantal packet errors sinds laatste start]
    
    SELECT @@TOTAL_ERRORS AS [Totaal aantal errors op deze machine]
    
    SELECT @@TOTAL_READ AS [Aantal Reads]
        , @@TOTAL_WRITE AS [Aantal Writes]
        , GETDATE() AS [Op datum-tijdstip]
    
    SELECT TOP 5 [sch] as [Operating System schijf]
            , [rmt_vry] as [Vrije ruimte]
            , [dtm] as [Datum] 
        FROM [da_dbb].[dbo].[_Tbl_dbb_sch_rmt_vry] 
        WHERE [sch] = (
            SELECT [sys_sch] 
            FROM [da_dbb].[dbo].[_Tbl_dbb_ins] 
            WHERE [srv_nam] = @@SERVERNAME)
        ORDER BY [sch]
            , [dtm] DESC
    
    SELECT TOP 5 [sch] as [Program Files schijf]
            , [rmt_vry] as [Vrije ruimte]
            , [dtm] as [Datum] 
        FROM [da_dbb].[dbo].[_Tbl_dbb_sch_rmt_vry] 
        WHERE [sch] = (
            SELECT [prg_sch] 
            FROM [da_dbb].[dbo].[_Tbl_dbb_ins] 
            WHERE [srv_nam] = @@SERVERNAME)
        ORDER BY [sch]
            , [dtm] DESC
    
    SELECT TOP 5 [sch] as [Data en index schijf]
            , [rmt_vry] as [Vrije ruimte]
            , [dtm] as [Datum] 
        FROM [da_dbb].[dbo].[_Tbl_dbb_sch_rmt_vry] 
        WHERE [sch] = (
            SELECT [dat_sch] 
            FROM [da_dbb].[dbo].[_Tbl_dbb_ins] 
            WHERE [srv_nam] = @@SERVERNAME)
        ORDER BY [sch]
            , [dtm] DESC
    
    SELECT TOP 5 [sch] as [Log schijf]
            , [rmt_vry] as [Vrije ruimte]
            , [dtm] as [Datum] 
        FROM [da_dbb].[dbo].[_Tbl_dbb_sch_rmt_vry] 
        WHERE [sch] = (
            SELECT [log_sch] 
            FROM [da_dbb].[dbo].[_Tbl_dbb_ins] 
            WHERE [srv_nam] = @@SERVERNAME)
        ORDER BY [sch]
            , [dtm] DESC
    
    SELECT TOP 5 [sch] as [Dump en errorlog schijf]
            , [rmt_vry] as [Vrije ruimte]
            , [dtm] as [Datum] 
        FROM [da_dbb].[dbo].[_Tbl_dbb_sch_rmt_vry] 
        WHERE [sch] = (
            SELECT [dmp_sch] 
            FROM [da_dbb].[dbo].[_Tbl_dbb_ins] 
            WHERE [srv_nam] = @@SERVERNAME)
        ORDER BY [sch]
            , [dtm] DESC
    
    SELECT GETDATE() AS [Server options]

    EXEC SP_CONFIGURE

    SELECT [H].[server] AS [Servernaam],
            [J].[name] AS [Foutgelopen jobs van de afgelopen drie dagen],
            [H].[run_status] AS [Status],
            [da_dbb].[dbo].[dtm_cnv] ([H].[run_date]) AS [Datum],
            [da_dbb].[dbo].[tyd_cnv] ([H].[run_time]) AS [Tijd],
            [da_dbb].[dbo].[tyd_cnv] ([H].[run_duration]) AS [Duur],
            [H].[message] AS [Bericht],
            [J].[enabled] AS [Actief],
            [H].[retries_attempted] AS [Herkansingen]
    FROM    [msdb].[dbo].[sysjobhistory] AS [H],
            [msdb].[dbo].[sysjobs] AS [J]
    WHERE   [H].[job_id] = [J].[job_id]
    AND     [H].[run_status] <> 1
    AND (   
            [H].[run_date] = [da_dbb].[dbo].[dtm_prt](getdate())
        OR  [H].[run_date] = [da_dbb].[dbo].[dtm_prt](getdate()-1)
        OR  [H].[run_date] = [da_dbb].[dbo].[dtm_prt](getdate()-2)
        )
    ORDER BY    [H].[run_date] DESC,
            [H].[run_time] DESC
    
    SELECT [H].[server] AS [Servernaam],
            SUBSTRING([J].[name],1,60) AS [Alle jobs van vandaag tot nu],
            [da_dbb].[dbo].[dtm_cnv] ([H].[run_date]) AS Datum,
            [da_dbb].[dbo].[tyd_cnv] ([H].[run_time]) AS Tijd,
            [da_dbb].[dbo].[tyd_cnv] ([H].[run_duration]) AS Duur,
            [H].[run_status] AS Status
    FROM    [msdb].[dbo].[sysjobhistory] AS [H]
    JOIN        [msdb].[dbo].[sysjobs] AS [J]
    ON  (   
            [H].[job_id] = [J].[job_id]
        )
    AND     [H].[run_date] = [da_dbb].[dbo].[dtm_prt](getdate())
    AND     [H].[step_id] = 0
    AND     [H].[run_time]  BETWEEN 000000 AND 235959
    ORDER BY    [H].[run_time]  DESC
    
    SELECT [H].[server] AS [Servernaam],
            SUBSTRING([J].[name],1,60) AS [Alle jobs van gisteren],
            [da_dbb].[dbo].[dtm_cnv] ([H].[run_date]) AS Datum,
            [da_dbb].[dbo].[tyd_cnv] ([H].[run_time]) AS Tijd,
            [da_dbb].[dbo].[tyd_cnv] ([H].[run_duration]) AS Duur,
            [H].[run_status] AS Status
    FROM    [msdb].[dbo].[sysjobhistory] AS [H]
    JOIN        [msdb].[dbo].[sysjobs] AS [J]
    ON  (
            [H].[job_id] = [J].[job_id]
        )
    AND     [H].[run_date] = [da_dbb].[dbo].[dtm_prt](getdate()-1)
    AND     [H].[step_id] = 0
    AND     [H].[run_time] BETWEEN 000000 AND 235959
    ORDER BY    [H].[run_time] DESC
    
    SELECT [H].[server] AS [Servernaam],
            SUBSTRING([J].[name],1,60) AS [Alle jobs van eergisteren],
            [da_dbb].[dbo].[dtm_cnv] ([H].[run_date]) AS Datum,
            [da_dbb].[dbo].[tyd_cnv] ([H].[run_time]) AS Tijd,
            [da_dbb].[dbo].[tyd_cnv] ([H].[run_duration]) AS Duur,
            [H].[run_status] AS Status
    FROM    [msdb].[dbo].[sysjobhistory] AS [H]
    JOIN        [msdb].[dbo].[sysjobs] AS [J]
    ON  (
            [H].[job_id] = [J].[job_id]
        )
    AND     [H].[run_date] = [da_dbb].[dbo].[dtm_prt](getdate()-2)
    AND     [H].[step_id] = 0
    AND     [H].[run_time]  BETWEEN 000000 AND 235959
    ORDER BY    [H].[run_time]  DESC'
    , @fixedfont = 1
    , @HTMLheader = 3

    , @webpagetitle = @web_pag_ttl
    , @resultstitle = @res_ttl
    , @URL = @web_adr
    , @reftext = @ref_txt
    , @dbname = @dbs_nam
    , @whentype = 4
    , @targetdate = 20031205
    , @targettime = @run_tyd_job_his_web_tsk
    , @numunits = 3
    , @unittype = 1
    , @procname = @prc_nam
    , @codepage = 65001
    , @charset = N'utf-8'
GO

Het gaat goed tot en met EXEC SP_CONFIGURE. Daarna wordt er niets meer weergegeven als de webtask sp in de da_dbb database staat. Als de webtask in de msdb database staat gaag het dus goed. Ik begrijp het niet helemaal, ik gebruik de volledige namen ([msdb].[dbo].[sysjobs]) en toch haalt hij de gegevens niet op.

De gebruiker sa runt de job en het gaat fout. Als ik de job run onder het service account van de server of agent gaat het ook fout.

Ik heb tevens views aangemaakt in de da_dbb database verwijzend naar de tabellen in de msdb database ([da_dbb].[dbo].[_Vw_dbb_sysjobs]) en zelfs dan werkt het niet.

Als ik de volgorde van de select statements verander, zodat de probleem gevallen bovenaan staan, dan krijg ik helemaal niet terug in mijn Html file. Het lijkt er dus op of de sp afgebroken wordt. Ik krijg geen foutmeldingen in wat voor log files dan ook.

Ik ben volledig de draad kwijt waarom dit toch niet wil lukken. Iemand een idee?

[ Voor 17% gewijzigd door Gé Brander op 11-08-2004 10:50 ]

[Gé Brander]

Probleem opgelost.
De da_dbb database had als owner 'dbo' en de msdb heeft als owner 'sa'. Vanuit msdb is met sa rechten dus probleemloos de da_dbb uit te lezen, maar andersom niet. De login die gekoppeld was aan dbo user in de da_dbb database was dus niet 'sa' en deze login bestond ook niet als user in de msdb database.

Om hier niets aan te hoeven wijzigen heb ik de volgende actie uitgevoerd op de Job:
Dubbelklik op de webtask job
Wijzig 'owner' in de SQL Server Agent account
Selecteer de tab 'Steps'
Selecteer 'Step 1' en klik op 'Edit'
Selecteer de tab 'Advanced'
Wijzig 'run as user' in '(Self)'

Nu werkt het dus goed.