Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to return a recordset execute by SP within the SP Expand / Collapse
Author
Message
Posted Friday, May 23, 2008 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 08, 2009 1:07 AM
Points: 1, Visits: 6
I am using Exec SP B within a SP A. The Exec SP return a recordset.

If i execute SP A its not returning the recordset of SP B?

How can i acheive it?

Any help will greatly help me.
====== SQL Text =====
Alter PROCEDURE dbo.sp_local_kpi_p2010_get_tm_data
--declare
@header_id int
AS

Declare
@StartTime datetime,
@asset_type nvarchar(20),
@mill_id nvarchar(12),
@machine_line_id nvarchar(100),
@machine_line_type_id nvarchar(100)

Set ANSI_WARNINGS OFF

set ANSI_Padding ON
set ANSI_Warnings ON
set quoted_identifier ON
set ANSI_Null_Dflt_On ON

Set NOCOUNT ON

SET @StartTime = '2008-01-01 07:00:00'
SET @header_id =10653
SET @asset_type = 'TM'
SET @mill_id = 'BAR'
SET @machine_line_type_id = 'LDC'
SET @machine_line_id = '#1'


--IF (select count(*) from kpi_form_detail_tissue where header_id=@header_id)=0
--Begin
Declare @PIMSPU_ID int, @DataSource varchar(20), @dbname varchar(20), @UserName varchar(20), @Password varchar(20)
Declare @spname varchar(50)

Select @PIMSPU_ID=Pimspu_id,@DataSource=Datasource,@dbname=dbname,@UserName=Username,@Password=[Password],@spname=Spname
from kpi_Map_PIMS_Machineline where assest_type_id=@asset_type and mill_id=@mill_id and machine_line_type_id=@machine_line_type_id and Machine_line_id=@machine_line_id

Declare @ServerName Varchar(1000)
Declare @Sql Varchar(1000)

Select @ServerName = 'OPENDATASOURCE('''
Select @ServerName = @ServerName + 'SQLOLEDB''' + ',''' + 'Data Source=' + rtrim(@datasource) +
+ ';User ID=' + rtrim(@UserName) + ';Password=' + rtrim(@Password)
Select @ServerName = @ServerName + ''')'

select @Sql ='Exec ' + @ServerName +'.'+ @dbname + '.dbo.'+ @spname +' ''' + convert(varchar(20), @StartTime) + ''',' + convert(varchar,@PIMSPU_ID) +','+'''ENG'''

execute (@Sql)
======

I am executing another SP inside the above SP which fetches the data from another server.

When a compile the SP and execute it the recorset retuned by the SP used within is not retrived in the mail SP.

Could u please help me out.
Post #505723
Posted Friday, May 23, 2008 4:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 25, 2010 10:52 PM
Points: 89, Visits: 273
can you post your query details,so we will come to know where you went wrong?


Rajesh
Post #505733
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse