I want to implement something as simple as
[vba]
USE MyDatabase
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
[/vba]
in ADO for SQL Server. What I would like is a stored proc, I could call via an ADO Connection.Execute("EXEC [mystoredproc] 'dbname'")
so then as a stored proc
[vba]
USE @dbname
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
[/vba]
Of course, USE cannot be used in stored procs, and DBCC commands cannot be called directly were I to execute as a Command Object.
I realize that for SQL Server 2005 the suggested method is ALTER INDEX... REBUILD, but when trying that from ADO I run into security errors.
This all may have to do with the ONLINE= option in TSQL... dunno
Just looking for direction. Stan