Combine select from different platforms

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combine select from different platforms

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-11 : 19:05:19

I need help getting this to work. Hope I have explained the scope well enough.

I have an asp application that that connects to a sp on SQL Server 2005 that again calls a sp on SQL Server 2000 (using linked server) to select production data. I use a SqlDataAdapter to fill a DataSet and consequently a GridView.

Existing SP code on Sequel Server 2005:



CREATE PROCEDURE sp_Vecellio_ProductionQtyCompare  
@JobNumber int
as
exec [VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_ProductionInquiry @JobNumber 
GO

Result will return a Cost Code and Quantity

Note: This procedure I need to expand with an additional select from a db2 table (cost qty table on an AS400). The objective is to compare production date vs accounting data. The select element is @JobNumber for both tables. I'm not sure if need to use a temp table. There could be entries in either table that may not exists in the other.

Existing SP code on Sequel Server 2000:

CREATE PROCEDURE sp_Vecellio_ProductionInquiry  
@JobNumber int
as
SELECT         distinct(dbo.Item.CompanyItemId), substring(dbo.Item.Name, 1, 15) as description, 
                       case dbo.SourceType.CompanySourceTypeId
                       when 'PR' then SUM(dbo.ProductionEvent.Quantity)
                       end
                       AS Ttl_Qty 
FROM             dbo.Batch INNER JOIN
                       dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
                       dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
                       dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
                       dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
                       dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
                       dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
                       dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
      	               dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid
WHERE                  dbo.job.CompanyJobId = @JobNumber and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY               dbo.Item.CompanyItemId,  dbo.SourceType.CompanySourceTypeId, dbo.Item.Name
GO

SP code that will call procedure on the db2/400 (should be part of "sp_Vecellio_ProductionQtyCompare"

declare @JobNumber char(12)
Set @JobNumber ='     1100281'  
Exec ('Call QGPL.get_jobqty(?)', @JobNumber) AT AS400SRV_MSDASQL

SP code on db2/AS400 (that will return cost code and qty)

create procedure get_jobqty                                             
                (in @JobNumber varchar(12))                                  
                result set 1                                             
                language sql                                             
                reads sql data                                           
 begin                                                                 
                declare c1 scroll cursor with return for                       
                select gbsub, DECIMAL(SUM(gban01 + gban02 + gban03 +   
                gban04 + gban05 + gban06 + gban07 + gban08 +                   
                gban09 + gban10 + gban11 + gban12 + gban13 +                   
                gban14)/FLOAT(100.00),38,2) as sum_qty                         
                from vgiprddta/f0902lc where gbmcu = @JobNumber and  
                gblt = 'AU'                                   
                GROUP BY gbsub;                        
                open c1;                                      
                set result sets cursor c1;                    
  end;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 23:51:27

i think what you need is to use OPENROWSET to select from db2 table and then use it inside the stored procedure

http://msdn.microsoft.com/en-us/library/ms190312.aspx

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-12 : 08:18:39

The reason I am calling a sp on the db2 is that the table has more than 17 mill records and I am using a predefined access path in my sp. I tried to use use "select" from SQL server 2005 but the response time is more than one hour (it reads records sequentially). So, I still think I need to use what I have. I also understand "openrowset" is an ad-hoc or one time thing.

So using what I have, can I join the 2 sp's or do I need a temp table. Can anyone give me a head start? Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources