Solved

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

Posted on 2013-05-23
6
405 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
[X]
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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create a Calendar table 29 38
T-SQL: Wrong Result 7 34
Use SSRS to email customers? 4 23
Search Text in Views 2 24
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

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