Results 1 to 8 of 8

Thread: Sql Query Help

  1. #1
    Join Date
    Jul 2008
    Posts
    10

    Unanswered: Sql Query Help

    Help SQl 2000 queries and I'm trying to write a proc that will check the system in the mornings from backups to replication and logs etc. I don't know how to check replication and the current proc I've created errors.

    CREATE PROCEDURE p_MorningChecks
    --drop proc p_MorningChecks
    AS

    PRINT ' '
    PRINT '---SQL Daily Checklist for SERVER---'
    PRINT ' '


    /* generates most recent db and t-log backup dates */
    SET NOCOUNT ON

    -- temp table for storing stats
    CREATE TABLE #db_backup_status (dbname varchar(30),
    last_full_backup datetime,
    last_diff_backup datetime,
    last_tlog_backup datetime)

    CREATE TABLE #temp_temp (dbname varchar(30),
    max_date datetime)

    -- pull info on full backups into storage table
    INSERT INTO #db_backup_status
    SELECT b.database_name,
    max(b.backup_finish_date),
    NULL
    FROM msdb..backupset b
    JOIN master..sysdatabases s on b.database_name = s.name
    WHERE b.type = 'D'
    GROUP BY b.database_name

    -- pull info on Differential backups into storage table
    INSERT INTO #db_backup_status
    SELECT b.database_name,
    max(b.backup_finish_date),
    NULL
    FROM msdb..backupset b
    JOIN master..sysdatabases s on b.database_name = s.name
    WHERE b.type = 'I'

    -- can't use aggregate function in an update statement, so instead
    -- pull aggregate data into temp table, then update from there
    INSERT INTO #temp_temp(dbname, max_date)
    SELECT b.database_name,
    max(b.backup_finish_date)
    FROM msdb..backupset b
    JOIN master..sysdatabases s on b.database_name = s.name
    WHERE b.type = 'L'
    GROUP BY b.database_name

    UPDATE #db_backup_status
    SET last_tlog_backup = t.max_date
    FROM #temp_temp t,
    #db_backup_status s
    WHERE s.dbname = t.dbname

    INSERT INTO #db_backup_status
    SELECT s.name, NULL, NULL, NULL FROM master..sysdatabases s
    LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name
    WHERE b.database_name IS NULL

    -- output results
    PRINT ' '
    PRINT '---Overnight Backups---'
    SELECT * FROM #db_backup_status
    WHERE dbname NOT IN ('model','tempdb')
    ORDER BY last_full_backup DESC

    -- clean up
    DROP TABLE #db_backup_status
    DROP TABLE #temp_temp


    PRINT ' '
    PRINT '---Hard Disk Space Used---'
    EXEC master..xp_fixeddrives


    PRINT ' '
    PRINT '---ErrorLog---'
    SET NOCOUNT ON

    --Create temp table
    CREATE TABLE #tmperrorlog (ERRORLOG VARCHAR (960),
    ContinuationRow INT)


    --Populating the temp table using sp_readerrorlog
    INSERT INTO #tmperrorlog
    EXEC master..sp_readerrorlog


    --This will remove the header from the errolog
    SET ROWCOUNT 4
    DELETE #tmperrorlog
    SET ROWCOUNT 0
    --**

    --Create temp table
    CREATE TABLE #errorlog (Date DATETIME,
    Source VARCHAR(10),
    Message VARCHAR(960))


    --Populate the temp table
    INSERT INTO #errorlog

    SELECT LEFT(ERRORLOG,22) [Date],
    SUBSTRING(ERRORLOG,24,10) [Source],
    RIGHT(ERRORLOG,LEN(ERRORLOG) - 33) [Message]
    FROM #tmperrorlog
    WHERE ContinuationRow = 0
    --**

    --Output results
    SELECT *
    FROM #errorlog


    --Clean up
    DROP TABLE #tmperrorlog
    DROP TABLE #errorlog
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO


    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO


    PLease assist and let me know where I've gone wrong, so I can learn by it

    ERROR

    Msg 213, Level 16, State 4, Line 11
    Insert Error: Column name or number of supplied values does not match table definition.
    Msg 213, Level 16, State 4, Line 21
    Insert Error: Column name or number of supplied values does not match table definition.

    Thanks in advance
    Last edited by MCSC1821; 07-11-08 at 12:28.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm gonna guess that the column name or number of supplied values does not match table definition on lines 11 and 21. Just a hunch
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just so we know - is it SQL Server you are new to or SQL in general?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jul 2008
    Posts
    10

    SQl Help

    T-sql this is my attempt at a complex script

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes but do you have any prior experience of any other brand of SQL? I know you are new to T-SQL, I just don't know if you are a hardened Oracle pro or in the first days of your first database job.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2008
    Posts
    10
    No just SQL Admin before hand basic stuff, no Oracle at all.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - problem #1 is on line 11. The number of supplied values does not match table definition.

    If you look at line 11 you will see the first insert statement in the procedure. It is incorrect - refer to the table it is trying to insert into and bear in mind the error message.

    A very useful resource:
    http://www.w3schools.com/sql/sql_insert.asp

    The error on line #21 is identical.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2008
    Posts
    10

    Smile SQl help

    Thanks I have corrected it and it's now working.

Posting Permissions

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