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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy