Stored Procedure Temp Table access in SSIS 1

[SundancerKid]

I am trying to create a Stored Procedure that the columns can be accessed in SSIS and processed.

Here is an example of the code that I am trying to produce:

MS SQL 2005 and Visual Studios 2005.

CREATE PROCEDURE AIMS_sp_BuildReport04_Barcode_No_Match

AS

SET NOCOUNT ON
     CREATE TABLE #blRecs(Barcode varchar(50),
     Asset_ID                     varchar(16),
     Asset_Status                 varchar(128),
     Building_Name                varchar(100),
     Address                      varchar(100),
     Building_ID                  varchar(12),
     Floor_nbr                    varchar(65),
     Room_cube                    varchar(65),
     Serial                       varchar(50),
     MFR                          varchar(80),
     Make                         varchar(255),
     Model                        varchar(255),
     Description                  varchar(128),
     Record_owner                 varchar(50),
     Asset_owner                  varchar(50),
     Organization_name            varchar(40),
     Contract_number              char(4),
     Contract_Type_Description varchar(80)
     )

INSERT INTO #blRecs(Barcode, 
                    Asset_ID,
                    Asset_Status,
                    Building_Name,
                    Address, 
                    Building_ID, 
                    Floor_nbr, 
                    Room_cube, 
                    Serial,
	            MFR,
                    Make,
                    Model,
                    Description, 
                    Record_owner, 
                    Asset_owner,
	            Organization_name,
	            Contract_number,
	            Contract_Type_Description)

	SELECT GMA.Barcode,
           GMA.Asset_ID, 
           GMA.Asset_Status, 
           GMA.Building_Name,
           GMA.Street_Address, 
           FV.Customer_Facility_ID, 
           GMA.Flr, 
           GMA.Room, 
           GMA.Serial_Number, 
           GMA.Manufacturer, 
           GMA.Make, 
           GMA.Model, 
           GMA.Product_Type, 
           GMA.Record_owner, 
           GMA.Asset_owner, 
           GLD.Security_Group, 
           CT.GSC_Contract_Number, 
           CT.contract_type_description
	FROM   LinkedServer.gmassets.dbo.assets_all AS GMA with (nolock) INNER JOIN
		   LinkedServer.rtp.dbo.GM_Global_Location AS GLD with (nolock) ON CONVERT(VARCHAR(65), GLD.Record_Sequence_ID) = GMA.Record_Sequence_Number INNER JOIN
		   LinkedServer.aims.dbo.AIMS_CORPORATE_A1 AS CORP with (nolock) ON CORP.Asset_Number = GMA.Barcode INNER JOIN
           LinkedServer.einvrpt.dbo.eInventory_Facility_view AS FV with (nolock) ON CONVERT(varchar(25), FV.Facility_id) = GLD.RECORD_SEQUENCE_ID LEFT OUTER JOIN     
           LinkedServer.dsa.dbo.Contract_Type AS CT with (nolock) ON CT.GSC_Contract_Number = GMA.Bid_Segment 

    WHERE GMA.Serial_Number Not Like 'NA' AND
		  GMA.Serial_Number Not Like 'n/a' AND	
		  GMA.Serial_Number Is Not Null AND	
		  GMA.Serial_Number Not Like '0' AND	
		  GMA.Serial_Number Not Like '000000000' AND	
		  GMA.Serial_Number Not Like '1' AND	
		  GMA.Serial_Number Not Like '2' AND	
		  GMA.Serial_Number Not Like '001' AND	
		  GMA.Serial_Number Not Like '0010' AND	
		  GMA.Serial_Number Not Like '002' AND	
		  GMA.Serial_Number Not Like '00001' AND	
          GMA.Serial_Number Not Like '0002' AND	
		  GMA.Serial_Number Not Like '00' AND	
		  GMA.Serial_Number Not Like '0008' AND	
		  GMA.Serial_Number Not Like '0009' AND	
		  GMA.Serial_Number Not Like '000000' AND	
		  GMA.Serial_Number Not Like 'Not Available' AND
		  GMA.Product_Type Not like 'Soft%' AND
		  GMA.Country Not like 'CA'	AND
		  GMA.Country Not like 'MX'

------------- This Data Below is what I want brought back to SSIS ---------------------
	Select {fn Now()} as Date,
	tmp.Asset_ID as Inventory_ID,
	tmp.Asset_Status as Status_Name,
	tmp.Building_Name as Building_Name,
	tmp.Address,
	tmp.Building_ID,
	tmp.Floor_nbr,
    tmp.Room_cube,
	tmp.Barcode,
	Corp.Asset_Number,
	tmp.Serial as Inventory_Serial,
	Corp.Serial_number,
	tmp.MFR,
	tmp.Make,
	tmp.Model,
	tmp.Description,
	tmp.Record.owner,
	tmp.Asset_owner,
	tmp.Organization_name,
	tmp.Contract_number,
	tmp.Contract_Type_description
	From  #blRecs as tmp inner join
		  LinkedServer.aims.dbo.AIMS_CORPORATE_A1 as Corp on
		  Corp.Asset_Number = GMA.Barcode
	Where Corp.Asset_number <> GMA.Barcode

--GO	
Drop Table #blRecs

The Stored Procedure compiles fine but it is not passing any columns to the SSIS(Visual Studios 2005.

Using Ole DB Connection as Exec AIMS_sp_BuildReport04_Barcode_No_Match

Any Help would be appreciated thanks in advance!

 

[bborissov]

Did this SP works in SSMS?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SundancerKid]

No, this is new code.

 

[RiverGuy]

I have always had problems like this. Use a table variable instead of a temp table if you can.

 

[SundancerKid]

Thanks RiverGuy,

Do you have any examples of table variables?

 

[davejam]

Table variable is along the same lines as create table but is only available within the procedure you are running

DECLARE TABLE @tblRecs(Barcode varchar(50),
Asset_ID varchar(16),
Asset_Status varchar(128),
Building_Name varchar(100),
Address varchar(100),
Building_ID varchar(12),
Floor_nbr varchar(65),
Room_cube varchar(65),
Serial varchar(50),
MFR varchar(80),
Make varchar(255),
Model varchar(255),
Description varchar(128),
Record_owner varchar(50),
Asset_owner varchar(50),
Organization_name varchar(40),
Contract_number char(4),
Contract_Type_Description varchar(80)
)

then refer to it as @tblRecs, ie. Insert Into @tblRecs

daveJam

it works on my machine, so technically i win!

 

[SundancerKid]

Works Great Dave.

I gave you a Star!!