SQL Server - executing a stored procedure in one database from a stored procedure in another database

-> Same server
-> Two databases (database A and database B)
-> 1 sql stored procedure in database A calls a stored procedure in database B and stores results in a temp table.  Then first stored procedure does a select against the temp table to aggregate and sort the results.

-> It works via query analyzer (exec Stored Procedure in A which calls SP in B)

-> when I call it using VBA (Set rs = cmd.Execute); the recordset remains closed (Operation is not allowed when the object is closed) and I cannot process results .... almost as if nothing is being returned.

-> I can execute stored procedure in B directly from VBA so it seems it is an 'execution' permission problem but I've granted exec permissions to both stored procedure for the user I am using.  

Any ideas?
rescapacctgitAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
And no you cannot execute code like this:
      select @sStoredProc = '[DatabaseB].dbo.spReport_Address'
     
      exec @sStoredProc @FredID

You will have to use dynamic SQL as in something like this:
DECLARE @SQL nvarchar(1000) = @sStoredProc + ' ' + CAST(@FredID as varchar(10))
EXEC (@SQL)
0
 
CalvinWinCommented:
Is your temp table created in the tempdb? If so, how did you declare the temp table? Local or Global?
0
 
rescapacctgitAuthor Commented:
Interesting!  Didn't think of that ... I created the temp table in SP in A using the create table #tempFred then did a insert into #tempFred .exec serverB.dbo.storedprocB  didn't know a temp table had to be declared globally.  Will try
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
rescapacctgitAuthor Commented:
didn't work ....  i took out the temp table and simply executed sp B from sp A and it still doesn't work via VBA.  This is the code of SP A.

USE [DatabaseA]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[pGet_pf_AddressDetail] (@name varchar(800))
as
Begin

      declare @FredID as int
      select @FredID = [ID] from [AdviseRMS].dbo.Address where name = @name

      declare @sStoredProc as varchar(1000)
      select @sStoredProc = '[DatabaseB].dbo.spReport_Address'
      
      exec @sStoredProc @FredID
End


Again, it works via query analyzer - so it isn't a sql error ..
0
 
Anthony PerkinsCommented:
You cannot access a temporary table from one connection to the other.  They are in a different scope.

But if you would like to post your Stored Procedure and show how it "works via query analyzer" we can suggest solutions.
0
 
rescapacctgitAuthor Commented:
thanks - you saved me some time ... I was convinced it should work and would have continued to try.  

-T
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.