May 23, 2008 at 4:04 am
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.
May 23, 2008 at 4:15 am
can you post your query details,so we will come to know where you went wrong?
Rajesh
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply