sql server (alternate): Now it says
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SPlant5_MODEL..sp_RebuildIndexesSub'.

The stored procedure are setup in the master db. That's why I'm using
the dbname..spname to change db context.

thanks
Sunit

Hello
I have 2 procedures setup in master database, sp_RebuildIndexesMain and
sp_RebuildIndexesSub

The Sub just shows and execute DBCC commands for passed database
context

sp_RebuildIndexesSub(@listOnly bit=0, @maxfrag Decimal=30.0)

This runs fine if I do pubs..sp_RebuildIndexesSub
However when run thru. the Main proc, I get Incorrect syntax near
'pubs'.
The main proc is

Create Proc sp_RebuildIndexesMain(@dbName sysname, @listOnly bit=0,
@maxFrag Decimal=30.0)
As
Begin
Set NOCOUNT ON

Declare crDbs CURSOR For
Select CATALOG_NAME From INFORMATION_SCHEMA.SCHEMATA
Where CATALOG_NAME NOT IN ('tempdb', 'master', 'msdb', 'model',
'distribution', 'Northwind', 'pubs')
And CATALOG_NAME Like @dbName

Declare @execstr nvarchar(2000)

Open crDbs
Fetch crDbs INTO @dbName
If (@@FETCH_STATUS<>0) --Then no matching databases
Begin
Close crDbs
Deallocate CrDbs
Print 'No databases were found that match ''' + @dbName + ''''
Return -1
End

While(@@FETCH_STATUS=0)
Begin
Print Char(13) + 'Rebuilding indexes on ' + @dbName
Print Char(13)
Set @execstr = @dbName + '..sp_RebuildIndexesSub '
EXEC sp_executesql @execstr, N'@listOnly bit, @maxFrag Decimal',
@listOnly, @maxFrag
Fetch crDbs INTO @dbName
End
Close crDbs
Deallocate CrDbs
Return 0
End

thanks
Sunit
sunitjoshi@netzero.com

I believe if you change:
Set @execstr = @dbName + '..sp_RebuildIndexesSub '
to
Set @execstr = '[' + @dbName + '..sp_RebuildIndexesSub] '

it should work.

Personally, instead of creating sp_RebuildIndexesSub in each database,
you should just create it in the master database. Then run a job like
so:

sp_msforeachdb 'USE ? if db_id(''?'') > 4
BEGIN
Print Char(13) + 'Rebuilding indexes on ' + ?
exec sp_RebuildIndexesSub 0, 30.0
END'

Be sure not to run "exec master..sp_RebuildIndexesSub 0, 30.0" or else
it will only run the master database during each loop.

Modify to your heart's content.

Don't use sp_executesql. The problem stems from you trying to run a
stored procedure through a stored procedure. So instead, build your
string first and run it by using EXEC(@execstr).

SET @execstr = 'USE ' + @dbname + ' exec sp_RebuildIndexesSub ' +
RTRIM(@listOnly) + ',' + RTRIM(@maxFrag)
EXEC (@execstr)

Got it. Had to change to this

Set @execstr = @dbName + '..sp_RebuildIndexesSub'
Exec @execstr @listOnly, @maxFrag

thanks
Sunit

You are right. Your code is much cleaner :)