-
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?
-
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)
-
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?
-
How did you call it in job step?
-
execute sp_1 @DatabaseName = 'DBName'
-
If you want to execute a stored proc within a certain database, this is the correct syntax:
EXECUTE dbname..sp_1
-
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.
-
Is the sp in msdb? If so, did you call it under msdb in job step? Same sp worked in query analyzer?
-
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.
-
Yes, sp is in msdb. No, I called it under dbname. I am getting the same error message in Query Analyzer.
-
Can you post your sp and the syntax you call it?
-
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'
-
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)
-
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.
-
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
-
Forum Rules
|
|