Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Have you tried four part name instead? [srvB].[cubeDB].[dbo].[sp_CUBEpop] –  Shawn Melton yesterday
    
Yes, it doesn't work. I thought it should, but I suspect the linked server of the same name caused that to fail. –  n8. yesterday
    
Linked server has to include the database you want to connect with, if it doesn't then no it will not work. If you can modify the linked server to include the database then it will. –  Shawn Melton yesterday
    
What happens when you try the method Shawn suggested? Do you get an error message; what is the message? –  Max Vernon yesterday
    
It does, but I can't see the stored procedures. I can see tables and views, but no functions or sprocs. The error message is 'Msg 7411, Level 16, State 1, Line 5 Server 'srv2' is not configured for RPC.' –  n8. yesterday

1 Answer 1

You can't use USE to change server context. You can use four-part naming for your objects, like Shawn suggested, if srvB is a linked server.

Another option is using SQLCMD mode, then you can say:

-- do stuff on server A
:CONNECT srvB
-- do stuff on server B

But that doesn't work well if you are trying to share data etc. between the two batches. For variables you can use SQLCMD variables instead.

More info here.

share|improve this answer
    
Thanks, this looks promising, I'm exploring it now. –  n8. yesterday

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.