EXECUTE WITH RESULT SET (OleDBCommand, trying to execute stored procedure)

Question

EXECUTE WITH RESULT SET (OleDBCommand, trying to execute stored procedure)

coreyjbaum

SSC Eights!

Points: 922
More actions
August 18, 2014 at 4:09 pm

#294558

Im trying to use and OleDBCommand to execute a stored procedure with a bunch of parameters. After scouring the internet i tried using WITH RESULT SETS NONE at the end of my query and that got rid of the error in SSIS, but using the profiler tool i found that its adding an additional parameter @p1 = 1 in from of my other parameters.
How to i run a stored procedure with parameters, or how do i get rid of the extra parameter, or correctly use the WITH RESULT SET ... ?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

coreyjbaum (8/18/2014)
Im trying to use and OleDBCommand to execute a stored procedure with a bunch of parameters. After scouring the internet i tried using WITH RESULT SETS NONE at the end of my query and that got rid of the error in SSIS, but using the profiler tool i found that its adding an additional parameter @p1 = 1 in from of my other parameters.
How to i run a stored procedure with parameters, or how do i get rid of the extra parameter, or correctly use the WITH RESULT SET ... ?

So you're calling a stored proc which uses temp tables inside a data flow, is that correct? Are you returning data from the proc? This seems like it would be a very slow way of working ... are you open to a bit of re-engineering?

I'm afraid that I don't know the answer to your specific question though - I try never to use the OleDbCommand because of its RBAR nature.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

coreyjbaum SSC Eights! Points: 922 More actions · Answer 2

Thanks for the reply. The stored procedure im calling isnt using temp tables but it is generating a guid as a result. The stored procedure is apart of another financial system and im trying to leverage current functionality. The procedure takes an input and insert records into a few different tables. You can think of it like customers. For every new customer, pass the parameters and execute the store proc.

Phil Parkin SSC Guru Points: 246970 More actions · Answer 3

coreyjbaum (8/19/2014)
Thanks for the reply. The stored procedure im calling isnt using temp tables but it is generating a guid as a result. The stored procedure is apart of another financial system and im trying to leverage current functionality. The procedure takes an input and insert records into a few different tables. You can think of it like customers. For every new customer, pass the parameters and execute the store proc.

Right - that does make sense.

I presume you've been through all the properties of the OleDbCommand with a fine-tooth comb, looking for that extra pesky param hidden somewhere?

I also presume that the GUID is an output parameter, not a SELECTed column?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

coreyjbaum SSC Eights! Points: 922 More actions · Answer 4

The guid generated in the beginning of the statement is used later in the statement during an insert. I believe ive checked everything, but no results ?

Phil Parkin SSC Guru Points: 246970 More actions · Answer 5

What do you mean by 'statement'? Package? Proc?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

coreyjbaum SSC Eights! Points: 922 More actions · Answer 6

Here is the stored procedure. I cant execute a OleDbCommand against it ?

/****** Object: StoredProcedure [dbo].[spInsertMtnVen] Script Date: 8/20/2014 9:01:15 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[spInsertMtnVen]

( @sVendorID varchar(30), --UDT

@sStatus varchar(2),

@sName varchar(80), --UDT

@sNameControl varchar(4),

@sClass varchar(25), --UDT

@sVendorAccount varchar(30),

@sType varchar(25), --UDT

@sCurrencyIDf char(3),

@ysnForceCurType bit,

@sMainAddress varchar(80),

@sMainCity varchar(30),

@sMainState varchar(6),

@sMainZip varchar(10),

@sMainCountry varchar(35),

@sMainPrintedAddress varchar(255),

@sMainAddrTemplate varchar(255),

@sMainPhone varchar(30),

@sMainFax varchar(30),

@sMainContactFirstName varchar(30),

@sMainContactMiddleInitial varchar(1),

@sMainContactLastName varchar(30),

@sMainContactTitle varchar(10),

@sMainContactPosition varchar(30),

@sMainContactEMail varchar(50),

@sWebSite varchar(50),

@nPOSameAs smallint,

@sPOAddress varchar(80),

@sPOCity varchar(30),

@sPOState varchar(6),

@sPOZip varchar(10),

@sPOCountry varchar(35),

@sPOPrintedAddress varchar(255),

@sPOAddrTemplate varchar(255),

@sPOVoicePhone varchar(30),

@sPOFaxPhone varchar(30),

@sPOContactFirstName varchar(30),

@sPOContactMiddleInitial varchar(1),

@sPOContactLastName varchar(30),

@sPOContactTitle varchar(10),

@sPOContactPosition varchar(30),

@sPOContactEMail varchar(50),

@ysnHoldPmt bit,

@ysnPrintSepCheck bit,

@sCheckStubComment varchar(60),

@dDisc1 float,

@dDisc2 float,

@nDaysDisc1 smallint,

@nDaysDisc2 smallint,

@nNetDaysDue smallint,

@sCodeIDf_0 char(6), --UDT

@sDistCodeIDf char(30), --UDT

@ysnIssue1099 bit,

@sTaxIDType varchar(2),

@sIDFedTax varchar(11),

@ysnStateTaxWithheld bit,

@s1099State varchar(2),

@sIDStateTax varchar(25),

@sDef1099Type varchar(5),

@sDef1099BoxNum varchar(10),

@s1099Proprietor varchar(30),

@sUserId varchar(255),

@memNotes varchar(5000),

@sBox15 varchar (60),

@ysnElectronicPayment bit,

@sRoutingNumber varchar(10),

@sAccountNumber varchar(30),

@ysnDisablePrenote bit,

@sAccountType varchar(10),

@dtmEPLastChanged datetime,

@dtmEPPreNote datetime,

@dtmEPAvailable datetime,

@dtmEPPNLastChanged datetime,

@sEFTEmail varchar(255),

@ysnNoEmail bit,

@ysnPersonalAccount bit,

@ysnForeignindicator bit,

@guidUserIDf uniqueidentifier -- ID of user making the change to the vendor

)

AS

SET NOCOUNT ON

BEGIN

BEGIN TRANSACTION

DECLARE @guidMemNotesID uniqueidentifier

SET @guidMemNotesID = newid()

INSERT INTO tblAPVendor

(sVendorID,

sStatus,

sName,

sNameControl,

sClass,

sVendorAccount,

sType,

sCurrencyIDf,

ysnForceCurType,

sMainAddress,

sMainCity,

sMainState,

sMainZip,

sMainCountry,

sMainPrintedAddress,

sMainAddrTemplate,

sMainPhone,

sMainFax,

sMainContactFirstName,

sMainContactMiddleInitial,

sMainContactLastName,

sMainContactTitle,

sMainContactPosition,

sMainContactEMail,

sWebSite,

nPOSameAs,

sPOAddress,

sPOCity,

sPOState,

sPOZip,

sPOCountry,

sPOPrintedAddress,

sPOAddrTemplate,

sPOVoicePhone,

sPOFaxPhone,

sPOContactFirstName,

sPOContactMiddleInitial,

sPOContactLastName,

sPOContactTitle,

sPOContactPosition,

sPOContactEMail,

ysnHoldPmt,

ysnPrintSepCheck,

sCheckStubComment,

dDisc1,

dDisc2,

nDaysDisc1,

nDaysDisc2,

nNetDaysDue,

sCodeIDf_0,

sDistCodeIDf,

ysnIssue1099,

sTaxIDType,

sIDFedTax,

ysnStateTaxWithheld,

s1099State,

sIDStateTax,

sDef1099Type,

sDef1099BoxNum,

s1099Proprietor,

--memNotes,

sBox15,

ysnElectronicPayment,

sRoutingNumber,

sAccountNumber,

ysnDisablePrenote,

sAccountType,

dtmEPLastChanged,

dtmEPPreNote,

dtmEPAvailable,

dtmEPPNLastChanged,

sEFTEmail,

ysnNoEmail,

ysnPersonalAccount,

ysnForeignindicator

,guidMemNotesIDf

)

VALUES (@sVendorID,

@sStatus,

@sName,

@sNameControl,

@sClass,

@sVendorAccount,

@sType,

@sCurrencyIDf,

@ysnForceCurType,

@sMainAddress,

@sMainCity,

@sMainState,

@sMainZip,

@sMainCountry,

@sMainPrintedAddress,

@sMainAddrTemplate,

@sMainPhone,

@sMainFax,

@sMainContactFirstName,

@sMainContactMiddleInitial,

@sMainContactLastName,

@sMainContactTitle,

@sMainContactPosition,

@sMainContactEMail,

@sWebSite,

@nPOSameAs,

@sPOAddress,

@sPOCity,

@sPOState,

@sPOZip,

@sPOCountry,

@sPOPrintedAddress,

@sPOAddrTemplate,

@sPOVoicePhone,

@sPOFaxPhone,

@sPOContactFirstName,

@sPOContactMiddleInitial,

@sPOContactLastName,

@sPOContactTitle,

@sPOContactPosition,

@sPOContactEMail,

@ysnHoldPmt,

@ysnPrintSepCheck,

@sCheckStubComment,

@dDisc1,

@dDisc2,

@nDaysDisc1,

@nDaysDisc2,

@nNetDaysDue,

@sCodeIDf_0,

@sDistCodeIDf,

@ysnIssue1099,

@sTaxIDType,

@sIDFedTax,

@ysnStateTaxWithheld,

@s1099State,

@sIDStateTax,

@sDef1099Type,

@sDef1099BoxNum,

@s1099Proprietor,

--@memNotes,

@sBox15,

@ysnElectronicPayment,

@sRoutingNumber,

@sAccountNumber,

@ysnDisablePrenote,

@sAccountType,

@dtmEPLastChanged,

@dtmEPPreNote,

@dtmEPAvailable,

@dtmEPPNLastChanged,

@sEFTEmail,

@ysnNoEmail,

@ysnPersonalAccount,

@ysnForeignindicator

,@guidMemNotesID

)

-- Update notes table

INSERT INTO tblMemNotes (guidMemNotesID, dtmLastUpdated, guidUserIDf, memNotes)

VALUES (@guidMemNotesID, GetDate(), @guidUserIDf, @memNotes)

-- This code updates the the trend analysis tables

IF Exists(SELECT tblLookUpTrends.sKeyValue

FROM tblLookupTrends

WHERE tblLookUpTrends.sUserId = @sUserId AND

tblLookUpTrends.sKeyValue = @sVendorID AND

tblLookUpTrends.sKeyType = 'V')

BEGIN

UPDATE tblLookUpTrends

SET tblLookUpTrends.dtmDateLastUsed = CURRENT_TIMESTAMP

WHERE tblLookUpTrends.sUserId = @sUserId AND tblLookUpTrends.sKeyValue = @sVendorID AND tblLookUpTrends.sKeyType = 'V'

END

ELSE

BEGIN

INSERT INTO tblLookUpTrends

(tblLookUpTrends.sUserId,

tblLookUpTrends.sKeyValue,

tblLookUpTrends.sKeyType,

tblLookUpTrends.dtmDateLastUsed)

VALUES (@sUserId,

@sVendorID,

'V',

CURRENT_TIMESTAMP)

END

IF (@@error <> 0)

BEGIN

RAISERROR ( 'Procedure spInsertMtnVen: Cannot perform insert in tblAPVendor',16,1)

ROLLBACK TRANSACTION

RETURN(1)

END

ELSE

COMMIT TRANSACTION

RETURN (0)

END

GO