November 9, 2009 at 4:05 pm
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.Serial_Number = tmp.Serial
Where Corp.Asset_number <> tmp.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!
November 10, 2009 at 6:47 am
Here's an article[/url] that applies to your situation.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2009 at 9:01 am
Used a table variable instead of a temp table.
Works great now!!
November 10, 2009 at 9:05 am
You just need to be careful with the table variable as there are no statistics for it so the optimizer assumes 1 row. It works well with small datasets, but doesn't scale very well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply