I have 2 databases on server A ("srvA") that I use to build a data cube on server B ("srvB"). It's taken me an embarrassingly long time to discover that populating my cubes fails because the batch file opens my script on the first server and it is not directed toward the server B for the second half of the scripts. I have:
DECLARE @theDay dateTime
Set @theDay = GetDate()
EXEC [db1].[dbo].[sp_deltas] @theDay
EXEC [db2].[dbo].[sp_deltas] @theDay
GO
--doesn't work
--Use [srvB].[cubeDB]
--Go
--works if I open as a new query in server B
DECLARE @theDay dateTime
Set @theDay = GetDate()
EXEC [dbo].[sp_CUBEpop]
@DBname = 'db1',
@startDate = @theDay,
@endDate = @theDay
EXEC [dbo].[sp_CUBEpop]
@DBname = 'db2',
@startDate = @theDay,
@endDate = @theDay
GO
The sp_deltas stored procedure runs just fine, but the "Use [srvB].[cubeDB]" statement doesn't work, and I suspect that having a linked server names "srvB" may not be helping matters. The login I am using can run the stored procedures just fine on srvB as long as I navigate there manually and open a new query. How can I run them all in the same script so I am sure that the actions happen sequentially?
[srvB].[cubeDB].[dbo].[sp_CUBEpop]
– Shawn Melton yesterday'Msg 7411, Level 16, State 1, Line 5 Server 'srv2' is not configured for RPC.'
– n8. yesterday