Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Error message

  1. #1
    Join Date
    Mar 2006
    Posts
    127

    Error message

    Hi All,

    I am getting an error message when I run the following script:

    EXEC master.dbo.xp_sqlmaint ' -S servername -U user -P password -D ' + @DatabaseName + ' -PlanName OptimizationPlan -Rpt C:\MaintPlanRpts\OptimizationPlan.txt -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10 '

    Here is the error message:
    Line 16: Incorrect syntax near '+'.

    What am I doing wrong?

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Try build dynamic sql for this:

    declare @cmd varchar(500)
    declare @DatabaseName sysname

    set @DatabaseName = 'db_name'
    set @cmd = 'master.dbo.xp_sqlmaint ''-S servername -U user -P password -D ' + @DatabaseName + ' -PlanName OptimizationPlan -Rpt C:\MaintPlanRpts\OptimizationPlan.txt -DelTxtRpt
    1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10'''
    exec (@cmd)

  3. #3
    Join Date
    Mar 2006
    Posts
    127

    Error message

    That worked. But now I have another problem. I am executing this stored procedure in a job step. Now I am getting the following error message:

    Procedure usp_RebuildIndexes has no parameters and arguments were supplied. [SQLSTATE 42000] (Error 8146)

    How can I fix it?

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    How did you call it in job step?

  5. #5
    Join Date
    Mar 2006
    Posts
    127
    execute sp_1 @DatabaseName = 'DBName'

  6. #6
    Join Date
    Dec 2004
    Posts
    502
    If you want to execute a stored proc within a certain database, this is the correct syntax:

    EXECUTE dbname..sp_1

  7. #7
    Join Date
    Mar 2006
    Posts
    127
    This is my statement:

    EXECUTE dbname..msdb.dbo.sp_1

    Error message:

    The procedure name 'dbname..msdb.dbo.' contains more than the maximum number of prefixes. The maximum is 3.

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    Is the sp in msdb? If so, did you call it under msdb in job step? Same sp worked in query analyzer?

  9. #9
    Join Date
    Dec 2004
    Posts
    502
    Where is your stored procedure saved? If it's not in the msdb database, there is no need to be referencing it. The following are valid syntaxes:

    EXEC servername.databasename.ownername.objectname
    (example: EXEC testserver.dbname.dbo.sp_1)

    EXEC databasename.ownername.objectname
    EXEC ownername.objectname
    EXEC databasename..objectname
    EXEC servername.databasename..objectname
    EXEC objectname

    I may have missed a combination, but those should work.

  10. #10
    Join Date
    Mar 2006
    Posts
    127
    Yes, sp is in msdb. No, I called it under dbname. I am getting the same error message in Query Analyzer.

  11. #11
    Join Date
    Sep 2002
    Posts
    5,938
    Can you post your sp and the syntax you call it?

  12. #12
    Join Date
    Mar 2006
    Posts
    127
    This sp is created in msdb.

    CREATE PROCEDURE usp_sp1

    AS

    DECLARE @cmd varchar(500)
    DECLARE @DatabaseName sysname


    set @DatabaseName = 'db_name'
    set @cmd = 'master.dbo.xp_sqlmaint ''-S server -U user -P password -D ' + @DatabaseName + ' -PlanName Optimization ' + @DatabaseName + ' -Rpt C:\MaintPlanRpts\OptimizationFor ' + @DatabaseName + ' -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '''

    exec (@cmd)

    This is how I call it in DBName in Query Analyzer:

    execute msdb.dbo.usp_sp1 'DBName'

  13. #13
    Join Date
    Sep 2002
    Posts
    5,938
    You didn't let your sp to accept variable, try following:

    CREATE PROCEDURE usp_sp1
    @DatabaseName sysname

    AS

    DECLARE @cmd varchar(500)

    set @cmd = 'master.dbo.xp_sqlmaint ''-S server -U user -P password -D ' + @DatabaseName + ' -PlanName Optimization ' + @DatabaseName + ' -Rpt C:\MaintPlanRpts\OptimizationFor ' + @DatabaseName + ' -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '''

    exec (@cmd)

  14. #14
    Join Date
    Mar 2006
    Posts
    127
    I tried that and in query analyzer I executed it like this:

    execute msdb.dbo.usp_RebuildIndexes 'DBName'

    Error message:

    (28 row(s) affected)

    Server: Msg 22029, Level 16, State 1, Line 0
    sqlmaint.exe failed.

  15. #15
    Join Date
    Sep 2002
    Posts
    5,938
    Check your plan's log file to find out why it failed. If you only like to rebuild index for the db, there is better way.

Posting Permissions

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