InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 19:30:48 GMT20http://www.sqlservercentral.com/Forums/Topic505723-8-1.aspxcan you post your query details,so we will come to know where you went wrong?RajeshFri, 23 May 2008 04:15:56 GMTraja_saminathanhttp://www.sqlservercentral.com/Forums/Topic505723-8-1.aspxI 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.Fri, 23 May 2008 04:04:58 GMTselvalingam1978