I am trying to update statistics calling a store procedure that resides on a admin database, located on the same instance. But for some reason, when it runs, can't find the table.
Here's the code:
ALTER PROCEDURE [dbo].[Man_UpdateStats]
DECLARE @name NVARCHAR(50)
DECLARE @sql nvarchar(255);
SET @name = DB_NAME()
SET @sql = 'USE '+ @name+'; EXECUTE [ABC].[dbo].[UpdateStats_v3];';
EXECUTE sp_executesql @sql
Below the actual store procedure that will update the statistics and resides on database ABC:
ALTER PROCEDURE [dbo].[UpdateStats_v3]
UPDATE STATISTICS MyTable
I don't understand why even when using dynamic sql, the store procedure on ABC can't find the table. Any ideas?
"MyTable" is on the source database, not on ABC.
By the way, I know I can accomplish the same using a user on the source database with limited privileges and create a store procedure that does the same ;-), but I don't want another user on the client's table, I want to invoke the UPDATE STATISTICS command from another database.