Post reply

Ola Hallengren backup failing with syntax error

  • September 30, 2019 at 2:43 pm

    Go to Answer

    #3684589

    Hi experts,

    Just curious if anyone has a quick answer to this. If not, I will email Ola Hallengren about it.

    I set up Ola Hallengren's Database Maintenance Solution Version date: 2019-06-14 00:05:34.

    I tested out the scheduled full user database backup. It failed with this error:

    Executed as user: ... . Incorrect syntax near '@CheckSum'. [SQLSTATE 42000] (Error 102). The step failed.

    This server is case-sensitive as is the database. I think that may be the problem, but does anyone know a general fix for this if it is? Otherwise I am going to try setting everything to match the MS docs case (i.e. lowercase for sp_... and so on).

    And if it is not, maybe I missed setting a default value somewhere?

    Thanks for any help,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • September 30, 2019 at 2:51 pm

    #3684591

    post the command you are executing , we probably can't help you without the original syntax

    MVDBA

  • September 30, 2019 at 2:52 pm

    #3684592

    It would be helpful if you could post the actual command that failed.

     

    @checksum only accepts 'Y' or 'N'.  If you passed in 'y' (lower case), the procedure will throw an error

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • September 30, 2019 at 4:39 pm

    Answer

    #3684655

    Incorrect syntax near '@CheckSum' to me implies a missing comma before, or an equals sign after;

    as others had posted, if we see the whole command you executed, it might be easier to spot.

     

    EXECUTE dba.dbo.[DatabaseBackup] 
    @Databases = N'DBA'
    ,@BackupType = N'DIFF',
    @Verify = N'Y',
    @Compress = N'Y',
    @CheckSum = N'Y',
    @LogToTable = N'Y';

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 30, 2019 at 4:41 pm

    #3684656

    Thanks, all, sorry about that. This is the comment in the failing job.

    EXECUTE [dbo].[DatabaseBackup]
    @Databases = 'USER_DATABASES',
    @Directory = N'S:\SQLBackups',
    @BackupType = 'FULL',
    @Verify = 'Y',
    @CleanupTime = 72
    @CheckSum = 'Y',
    @LogToTable = 'Y'

     

    Sure enough, I see the missing comma. Sorry to bother everyone!

    Thanks,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • September 30, 2019 at 5:01 pm

    #3684661

    Your missing a comma after the cleanuptime parameter: @CleanupTime = 72

    Sue

Viewing 6 posts - 1 through 5 (of 5 total)

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