VB.NET 2003 and SQL 2000

[gregind]

I am currently trying to pick up the pieces of a program that was left in disarry by someone else. I have fixed severa things and have gotten it running smoothly but now I was asked to help finish the export/import system so that the data we have been working on can be uploaded to a live server that the client has. The clients server is 6.5 and ours is 2000 so it was decided to download our data into an access DB then upload it to their server from access.

Now the programmer before me was better with sql that I, or at least he was better with stored procedures. Most of the work I have doena has been with calling SQL statements from inside the vb.net code. I have a huge snag now. The programmer before me left two stored procedures behind, one tranfers that data into three tables that are modeled after the clients tables and the other one runs a DTS Package stored on the server that puts those tables into an access DB. I need to know how to run these procedures from VB.NET and also how to track when they finish their run. H e has no return values in either one. I will include the procedures below. Any help would be great!!!

CREATE PROCEDURE dbo.sos_RunTransferDTSPackage AS
DECLARE @object int
DECLARE @hr int
--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
    print 'error create DTS.Package'
    RETURN
END
EXEC @hr = sp_OAMethod @object, 'LoadFromStorageFile',
     NULL, 'C:\SOSTools\sos_Production_Transfer.dts', ''
IF @hr <> 0
BEGIN
    print 'error LoadFromStorageFile'
    RETURN
END
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
  print 'Execute failed'
  RETURN
END

CREATE PROCEDURE dbo.sos_TransferCompletedData AS
--Stored procedure to fill Transfer tables with data from completed batches
--Export Table
DELETE FROM Export WHERE [id] IS NOT NULL
INSERT INTO Export (ExportDate) VALUES (CONVERT(varchar, GetDate(), 10))
--Image Table
DELETE FROM Corp_Image_Transfer WHERE Original_Cn IS NOT NULL
INSERT INTO Corp_Image_Transfer 
select 
 PacketID AS Original_Cn,  
 TransID AS DCN,
 PageNumber as ImageSeqNumber,
 1 as VolumePathId,
 replace(FilePath, 'E:\export\', '') as Subdirectory,
 FileName as Filename
 
from importedpages 
where 
 batchid in (
   select batchid from batch where complete = 1 and exported = 0
   ) AND
 PageNumber <> 0 AND
 IsBlankPage = 0 AND
 IsCoverPage = 0
order by Original_Cn, ImageSeqNumber
--Stored procedure to create Transfer
--Data Table
delete from Corp_Data_Transfer where Original_Cn is not null
insert into Corp_Data_Transfer 
select distinct
 i.PacketID AS Original_Cn,  
 i.TransID AS DCN, 
 cd.Name,
 cd.CorpType,
 cd.Status,
 cd.FilingAct,
 cd.Creation_Date,
 cd.Expiration_Date,
 cd.Inactive_Date,
 cd.Address_Id,
 cd.Orig_Inc_State,
 cd.Orig_Inc_Date,
 cd.FedId,
 cd.Fict_Name,
 cd.Managers,
 cd.Owner_Name,
 cd.Corp_Names_Count,
 cd.Corp_Officers_Count
 
from importedpages i
left join Corp_Data cd on PacketID = Original_Cn
where 
 i.batchid in (
   select batchid from batch where complete = 1 and exported = 0
   ) AND
 PageNumber <> 0 AND
 IsBlankPage = 0 AND
 IsCoverPage = 0
--Stored procedure to create Transfer
--Filing Table
DELETE FROM Corp_Filing_Transfer WHERE Original_Cn IS NOT NULL
INSERT INTO Corp_Filing_Transfer
select distinct
 i.TransID AS DCN,
 i.PacketID AS Original_Cn,  
 cf.Filing_Type_Id,
 cf.Entry_Date,
 cf.Filing_Date,
 cf.Effective_Date,
 cf.Comment,
 cf.Affected_DCN,
 cf.Create_User,
 cf.RctNumber,
 cf.Legacy
 
from importedpages i
left join Corp_Filing cf on i.TransID = cf.DCN
where 
 i.batchid in (
   select batchid from batch where complete = 1 and exported = 0
   ) AND
 PageNumber <> 0 AND
 IsBlankPage = 0 AND
 IsCoverPage = 0
DECLARE @PageNumber int
DECLARE @ImageSeqNumber int
DECLARE @OriginalCn VARCHAR(16)
DECLARE OriginalCn_cursor CURSOR FOR
 SELECT Original_Cn, ImageSeqNumber FROM Corp_Image_Transfer ORDER BY Original_Cn, ImageSeqNumber
OPEN OriginalCn_cursor
-- Get the first original cn number
FETCH NEXT FROM OriginalCn_cursor INTO @OriginalCn, @ImageSeqNumber
-- Check @@FETCH_STATUS to see if there are any more rows to fetch and loop thru
WHILE @@FETCH_STATUS = 0
BEGIN
 -- If this is the first page number, set the page number var back to 1
 IF @ImageSeqNumber = 3
  BEGIN
   SET @PageNumber = 1
  END
 -- Update the record
 UPDATE Corp_Image_Transfer SET ImageSeqNumber = @PageNumber WHERE Original_Cn = @OriginalCn AND ImageSeqNumber = @ImageSeqNumber
 -- Increment the PageNumber variable
 SET @PageNumber = @PageNumber + 1
    -- Fetch the next row
 FETCH NEXT FROM OriginalCn_cursor INTO @OriginalCn, @ImageSeqNumber
END
CLOSE OriginalCn_cursor
DEALLOCATE OriginalCn_cursor
-- Update the batch table to reflect all these items exported
--UPDATE batch SET Exported = 1, ExportedDate = GetDate() WHERE complete = 1 and exported = 0
GO