Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2013-05-23
6
403 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

829 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