Solved

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

Posted on 2013-05-23
6
389 Views
Last Modified: 2013-05-28
-> 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?
0
Comment
Question by:rescapacctgit
  • 3
  • 2
6 Comments
 
LVL 1

Expert Comment

by:CalvinWin
ID: 39193415
Is your temp table created in the tempdb? If so, how did you declare the temp table? Local or Global?
0
 

Author Comment

by:rescapacctgit
ID: 39194226
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
 

Author Comment

by:rescapacctgit
ID: 39194521
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39195949
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39195956
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
 

Author Comment

by:rescapacctgit
ID: 39201815
thanks - you saved me some time ... I was convinced it should work and would have continued to try.  

-T
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now