Solved

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

Posted on 2013-05-23
6
404 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

740 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