# Miscellaneous > General Database Discussions >  Error message

## inet

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?

----------


## rmiao

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)

----------


## inet

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?

----------


## rmiao

How did you call it in job step?

----------


## inet

execute sp_1 @DatabaseName = 'DBName'

----------


## nosepicker

If you want to execute a stored proc within a certain database, this is the correct syntax:

EXECUTE dbname..sp_1

----------


## inet

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.

----------


## rmiao

Is the sp in msdb? If so, did you call it under msdb in job step? Same sp worked in query analyzer?

----------


## nosepicker

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.

----------


## inet

Yes, sp is in msdb.  No, I called it under dbname.  I am getting the same error message in Query Analyzer.

----------


## rmiao

Can you post your sp and the syntax you call it?

----------


## inet

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'

----------


## rmiao

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)

----------


## inet

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.

----------


## rmiao

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.

----------


## inet

I have modified my sp a little bit and I am getting a different error.

Sp:




ALTER      PROCEDURE usp_sp1		
@DatabaseName varchar(20)


AS

DECLARE @cmd varchar(500)


set @cmd = 'master.dbo.xp_sqlmaint -Rpt "C:\MaintPlanRpts\OptimizationFor ' + @DatabaseName + '" -DelTxtRpt 1DAYS -WriteHistory  -RebldIdx 10 -RmUnusedSpace 50 10'
exec (@cmd)

Error msg:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Rpt'.

----------


## rmiao

Add 'print @cmd' in the sp to see what does @cmd look like.

----------


## inet

Here is the results:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Rpt'.
master.dbo.xp_sqlmaint -Rpt "N:\MaintPlanRpts\Optimization For VCSPortalDev.txt" -DelTxtRpt 1DAYS -WriteHistory  -RebldIdx 10 -RmUnusedSpace 50 10

----------


## rmiao

You should check syntax in books online (search for 'sqlmaint Utility')  before making changes. The other way is build plan in em and check how the command looks like in sql job.

----------

