AcquireConnection(txn) ?

In a script task I'm calling AcquireConnection(txn as Object) on a connection manager.

What is the txn parameter supposed to do or be used for?

I'm just passing an empty variable of type Object to this parameter and the method call is working fine...but I'm interested in knowing what its there for.

Thanks
JamieIt is the transaction context you want to use for any work done in that connection. Think of DTC, you need a context that can be supplied to all connections that make up that transacction so all work is marshalled together, well this is that handle.

Passing null will pretty much always work. If you have a transaction context then pass it otherwise don't worry.|||

DarrenSQLIS wrote:

It is the transaction context you want to use for any work done in that connection. Think of DTC, you need a context that can be supplied to all connections that make up that transacction so all work is marshalled together, well this is that handle.

Passing null will pretty much always work. If you have a transaction context then pass it otherwise don't worry.

Cool, that makes sense. I figured it was something I didn't have to worry about.

cheers Darren.

-Jamie|||Darren:
A follow-up on this: Ive been writitng a custom dataflow source that analyzes flatfiles by various properties (filesize, width etc.). The component works well when i have a stable flatfile connection. But when i do a for-each file in folder enumeration i cant get the updated connection information (ie filepath) from my component, it will always run against the first path passed by the connection manager. Any ideas on this?|||I've had issues with class level variables and loops since the class is only created once, that was a task though. Simple answer is I don't know, and don't have a suitable component to test with easily, but if you debug the component it should be very simple to work out what is going on. Put a break point on AcquireConnections for a start and see how many times it is called.

Another obvious test would be to add a script component, and make that consume the same connection, and just MsgBox out the connection string.

What do you get?|||

I have an SSIS package with its TransactionOption set to 'Required'. A data flow in the package has a script component that performs updates to a database table. The TransactionOption on the data flow is set to 'Supported'. However the script component does no get enlisted to the transaction because the updates made in the script component do not get rolled back when the package fails.

I'm thinking that I need to pass this 'transaction handle' to the AquireConnection method of the connection used by the script in order for the script to become part of the overall transaction.

In regard to your reply about transaction context. How do I determine it? I have no idea what value I should be passing. I am new to Sql Server beginning with Sql Server 2005. I have no background with DTC.

Thanks.

|||Please, one post will suffice. I have replied, and maybe even answered, in your new thread - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322436&SiteID=1

AcquireConnection(txn) ?

In a script task I'm calling AcquireConnection(txn as Object) on a connection manager.

What is the txn parameter supposed to do or be used for?

I'm just passing an empty variable of type Object to this parameter and the method call is working fine...but I'm interested in knowing what its there for.

Thanks
JamieIt is the transaction context you want to use for any work done in that connection. Think of DTC, you need a context that can be supplied to all connections that make up that transacction so all work is marshalled together, well this is that handle.

Passing null will pretty much always work. If you have a transaction context then pass it otherwise don't worry.|||

DarrenSQLIS wrote:

It is the transaction context you want to use for any work done in that connection. Think of DTC, you need a context that can be supplied to all connections that make up that transacction so all work is marshalled together, well this is that handle.

Passing null will pretty much always work. If you have a transaction context then pass it otherwise don't worry.

Cool, that makes sense. I figured it was something I didn't have to worry about.

cheers Darren.

-Jamie|||Darren:
A follow-up on this: Ive been writitng a custom dataflow source that analyzes flatfiles by various properties (filesize, width etc.). The component works well when i have a stable flatfile connection. But when i do a for-each file in folder enumeration i cant get the updated connection information (ie filepath) from my component, it will always run against the first path passed by the connection manager. Any ideas on this?
|||I've had issues with class level variables and loops since the class is only created once, that was a task though. Simple answer is I don't know, and don't have a suitable component to test with easily, but if you debug the component it should be very simple to work out what is going on. Put a break point on AcquireConnections for a start and see how many times it is called.

Another obvious test would be to add a script component, and make that consume the same connection, and just MsgBox out the connection string.

What do you get?|||

I have an SSIS package with its TransactionOption set to 'Required'. A data flow in the package has a script component that performs updates to a database table. The TransactionOption on the data flow is set to 'Supported'. However the script component does no get enlisted to the transaction because the updates made in the script component do not get rolled back when the package fails.

I'm thinking that I need to pass this 'transaction handle' to the AquireConnection method of the connection used by the script in order for the script to become part of the overall transaction.

In regard to your reply about transaction context. How do I determine it? I have no idea what value I should be passing. I am new to Sql Server beginning with Sql Server 2005. I have no background with DTC.

Thanks.

|||Please, one post will suffice. I have replied, and maybe even answered, in your new thread - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322436&SiteID=1

Accounting procedure

Hi,
I have the following procedure that I am trying to run on a data warehouse -
it is supposed to increase or decrease the running premium balance and the
fire fee balance based on the type of transaction, but it seems to just be
increasing the running balance - do you see where I am going wrong?
Thanks in advance
-- Local Variables
DECLARE
@.PolicyKeyID INT,
@.Policy_Number VARCHAR (10),
@.Trans_Type VARCHAR(40),
@.Billed_Premium DECIMAL(10,2),
@.Billed_Fire_Fee DECIMAL(10,2),
@.Pymt_Recvd DECIMAL(10,2),
@.SC_Recvd DECIMAL(10,2),
@.Transcode VARCHAR (4),
@.Portfolio_Set VARCHAR (4),
@.Type VARCHAR (25),
@.Typecode VARCHAR (4),
@.Previous_Policy_Number VARCHAR (10),
@.Running_Premium DECIMAL(10,2),
@.Running_Fire_Fee DECIMAL(10,2),
@.Error_Code_Tran INT,
@.Error_Code_Proc INT,
-- CONSTANTS declared for Transaction Types
@.NEW_BILL VARCHAR(15),
@.RENEWAL VARCHAR(20),
@.CANCELLATION VARCHAR(35),
@.CHANGE VARCHAR(15),
@.CASH_WITH_APP VARCHAR(25),
@.RETURNED_CHECK VARCHAR(35),
@.PAYMENT VARCHAR(35),
@.PYMT_REVERSAL VARCHAR(25),
@.DISBURSEMENT VARCHAR(25),
@.CANCEL_DISBURSEMENT VARCHAR(35)
-- Assign CONSTANTS
SET @.NEW_BILL = 'New'
SET @.RENEWAL = 'Renewal'
SET @.CANCELLATION = 'Cancellation'
SET @.CHANGE = 'Change'
SET @.CASH_WITH_APP = 'Cash With App'
SET @.RETURNED_CHECK = 'Returned Check'
SET @.PAYMENT = 'Payment'
SET @.PYMT_REVERSAL = 'Payment Reversal'
SET @.DISBURSEMENT = 'Disbursement'
SET @.CANCEL_DISBURSEMENT = 'Cancel Disbursement'
-- Set Error Codes
SET @.Error_Code_Tran = 0
SET @.Error_Code_Proc = 0
SET NOCOUNT ON
DECLARE crsrTransactions CURSOR LOCAL STATIC FOR
SELECT PolicyKeyID, Policy_Number, Portfolio_Set, Billed_Premium,
Billed_Fire_Fee, Pymt_Recvd, SC_Recvd,Trans_Type,Transcode,Type,Typec
ode
FROM Stage_Fire_Fee
ORDER BY Policy_Number, Policy_Date_Time ASC
OPEN crsrTransactions
FETCH NEXT FROM crsrTransactions INTO @.PolicyKeyID, @.Policy_Number,
@.Portfolio_Set, @.Billed_Premium, @.Billed_Fire_Fee, @.Pymt_Recvd,
@.SC_Recvd,@.Trans_Type,@.Transcode,@.Type,@.
Typecode
-- Outer Loop for all Transactions
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Initialize Running totals, reinitialize for each Policy #
SET @.Running_Premium = 0
SET @.Running_Fire_Fee = 0
SET @.Previous_Policy_Number = @.Policy_Number
IF @.Trans_Type IN
(@.NEW_BILL,@.RENEWAL,@.CANCELLATION,@.CHANG
E,@.RETURNED_CHECK,@.DISBURSEMENT,@.CAN
CEL_DISBURSEMENT,@.PYMT_REVERSAL)
-- Found basic Billed, so increment our running Premium and Fire Fee totals
BEGIN
SET @.Running_Premium = @.Running_Premium + @.Billed_Premium
SET @.Running_Fire_Fee = @.Running_Fire_Fee + @.Billed_Fire_Fee
END
ELSE IF @.Trans_Type = (@.PAYMENT)
-- Found Payment transaction, so subtract the paymnet from the total
amounts. Apply as much of the payment as possible to the Fire Fee Balance
BEGIN
SET @.Running_Premium = (@.Running_Fire_Fee + @.Running_Premium) -
@.Pymt_Recvd
IF @.Running_Fire_Fee <= @.Pymt_Recvd SET @.Running_Fire_Fee = 0
ELSE SET @.Running_Fire_Fee = @.Running_Fire_Fee - @.Pymt_Recvd
END
ELSE IF @.Trans_Type = (@.CASH_WITH_APP)
-- Found Payment transaction, so subtract the paymnet from the total
amounts. Apply as much of the payment as possible to the Fire Fee Balance
BEGIN
SET @.Running_Premium = (@.Running_Fire_Fee + @.Running_Premium) -
@.Pymt_Recvd
IF @.Running_Fire_Fee <= @.Pymt_Recvd SET @.Running_Fire_Fee = 0
ELSE SET @.Running_Fire_Fee = @.Running_Fire_Fee - @.Pymt_Recvd
END
ELSE
BEGIN
RAISERROR ('Unknown Transaction Type: %s', 0, 1, @.Trans_Type)
SET @.Error_Code_Tran = 1
SET @.Error_Code_Proc = 1
END
IF @.Error_Code_Tran = 0 UPDATE Stage_Fire_Fee SET Stage_Premium_Bal =
@.Running_Premium, Stage_Fire_Fee_Bal = @.Running_Fire_Fee WHERE PolicyKeyID =
@.PolicyKeyID
SET @.Previous_Policy_Number = @.Policy_Number
FETCH NEXT FROM crsrTransactions INTO @.PolicyKeyID, @.Policy_Number,
@.Portfolio_Set, @.Billed_Premium, @.Billed_Fire_Fee, @.Pymt_Recvd,
@.SC_Recvd,@.Trans_Type,@.Transcode,@.Type,@.
Typecode
SET @.Error_Code_Tran = 0
END
ENDOn Sun, 6 Nov 2005 16:44:36 -0800, Patrice wrote:

>Hi,
>I have the following procedure that I am trying to run on a data warehouse
-
>it is supposed to increase or decrease the running premium balance and the
>fire fee balance based on the type of transaction, but it seems to just be
>increasing the running balance - do you see where I am going wrong?
>Thanks in advance
Hi Patrice,
First some general advice:
1. Generally, don't store data that can be calculated in the database.
Each time the base data changes, you'll have to re-do all calculations.
Or you can choose to recalculate periodically, but then, the stored data
might be incorrect when you query the table.
2. Try to avoid cursors. They should only be used when all else fails,
or when you can prove that there is no reasonable set-based alternative.
On to your code.
It's hard to see what's going wrong, becuase you didn't include all
information needed to troubleshoot. Please post:
- The table structures, as CREATE TABLE statements (including all
constraints and properties, but excluding irrelevant columns),
- Some rows of sample data to illustrate the problem, as INSERT
statements,
- The expected output, and
- The output you are actually seeing.
Check out www.aspfaq.com/5006 for more suggestions on how to provide the
information we need in order to help you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Accessing Stored Procedure from IIS

I have a stored procedure that is supposed to
1. Increment a counter in Table A via a transaction
2. Use this value as the primary key to add in an address to customers
Table B
(Referenced as a "DECLARE @.CustomerID INT" just after the AS
clause)
3. Return the primary key.
This works perfectly when being called from Query Analyzer supplying values
in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
execution falls right through without adding the customer or incrementing
the counter or giving an error. All conditional routines are executed, but
no work is being done.
Is there anything I can do to raise some sort of error to let me know what
is or isn't happening?
adovbs.inc is linked and the "conditional code" I refer to swaps the stored
procedure name (for add/edit) to add in one more parameter needed for
editing records. The parameters are referenced in exactly the same order as
they are in the procedures, with the return value being mentioned first.
The append parameters lines have been rewritten in short form, long form,
and in a "with" block as shown.
for example:
(Blocked within conditional code)
adocmd.CommandType = adCmdStoredProc
adocmd.CommandText = "spr_AddCustomer"
adocmd.ActiveConnection = conn.ConnectionObject
set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
adParamReturnValue, 0)
adocmd.parameters.append param
(Conditional code end)
With adocmd
set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
company)
.parameters.append param
set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
firstname)
.parameters.append param
set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
1, middleinitial)
.parameters.append param
set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
lastname)
.parameters.append param
... (continuing to add parameters in the same order as SP)
.execute lngRecs,,adexecutenorecords
CustomerId = .Parameters("@.RETURN_VALUE").Value
End WithMake sure you disable "on error resume next" in your ASP page.
Make sure the stored procedure has SET NOCOUNT ON at the beginning.
Have a look at http://www.aspfaq.com/2201
http://www.aspfaq.com/
(Reverse address to reply.)
"stjulian" <anonymous@.discussions.microsoft.com> wrote in message
news:#l$GjY2GFHA.2736@.TK2MSFTNGP09.phx.gbl...
> I have a stored procedure that is supposed to
> 1. Increment a counter in Table A via a transaction
> 2. Use this value as the primary key to add in an address to customers
> Table B
> (Referenced as a "DECLARE @.CustomerID INT" just after the AS
> clause)
> 3. Return the primary key.
> This works perfectly when being called from Query Analyzer supplying
values
> in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
> execution falls right through without adding the customer or incrementing
> the counter or giving an error. All conditional routines are executed, but
> no work is being done.
> Is there anything I can do to raise some sort of error to let me know what
> is or isn't happening?
> adovbs.inc is linked and the "conditional code" I refer to swaps the
stored
> procedure name (for add/edit) to add in one more parameter needed for
> editing records. The parameters are referenced in exactly the same order
as
> they are in the procedures, with the return value being mentioned first.
> The append parameters lines have been rewritten in short form, long form,
> and in a "with" block as shown.
> for example:
> (Blocked within conditional code)
> adocmd.CommandType = adCmdStoredProc
> adocmd.CommandText = "spr_AddCustomer"
> adocmd.ActiveConnection = conn.ConnectionObject
> set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
> adParamReturnValue, 0)
> adocmd.parameters.append param
> (Conditional code end)
> With adocmd
> set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
> company)
> .parameters.append param
> set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
> firstname)
> .parameters.append param
> set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
> 1, middleinitial)
> .parameters.append param
> set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
> lastname)
> .parameters.append param
> ... (continuing to add parameters in the same order as SP)
> .execute lngRecs,,adexecutenorecords
> CustomerId = .Parameters("@.RETURN_VALUE").Value
> End With
>|||Hi
You don't provide DDL for the procedure.
http://www.aspfaq.com/etiquette.asp?id=5006
You may want to check what is happening using profiler, and make sure that
NOCOUNT is ON.
John
"stjulian" wrote:

> I have a stored procedure that is supposed to
> 1. Increment a counter in Table A via a transaction
> 2. Use this value as the primary key to add in an address to customers
> Table B
> (Referenced as a "DECLARE @.CustomerID INT" just after the AS
> clause)
> 3. Return the primary key.
> This works perfectly when being called from Query Analyzer supplying value
s
> in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
> execution falls right through without adding the customer or incrementing
> the counter or giving an error. All conditional routines are executed, but
> no work is being done.
> Is there anything I can do to raise some sort of error to let me know what
> is or isn't happening?
> adovbs.inc is linked and the "conditional code" I refer to swaps the store
d
> procedure name (for add/edit) to add in one more parameter needed for
> editing records. The parameters are referenced in exactly the same order a
s
> they are in the procedures, with the return value being mentioned first.
> The append parameters lines have been rewritten in short form, long form,
> and in a "with" block as shown.
> for example:
> (Blocked within conditional code)
> adocmd.CommandType = adCmdStoredProc
> adocmd.CommandText = "spr_AddCustomer"
> adocmd.ActiveConnection = conn.ConnectionObject
> set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
> adParamReturnValue, 0)
> adocmd.parameters.append param
> (Conditional code end)
> With adocmd
> set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
> company)
> .parameters.append param
> set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
> firstname)
> .parameters.append param
> set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
> 1, middleinitial)
> .parameters.append param
> set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
> lastname)
> .parameters.append param
> ... (continuing to add parameters in the same order as SP)
> .execute lngRecs,,adexecutenorecords
> CustomerId = .Parameters("@.RETURN_VALUE").Value
> End With
>
>|||Thank you both for your attention...
DDL follows
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.spr_WriteCustomers Created: 2/24/05
JS ******/
CREATE PROCEDURE spr_WriteCustomers
@.Company varchar(40) = NULL,
@.FirstName varchar(15) = NULL,
@.MiddleInitial varchar(1) = NULL,
@.LastName varchar(20) = NULL,
@.Title varchar(30) = NULL,
@.BillingAttnLine varchar(40) = NULL,
@.BillingAddress1 varchar(40) = NULL,
@.BillingAddress2 varchar(40) = NULL,
@.BillingCity varchar(20) = NULL,
@.BillingState varchar(3) = NULL,
@.BillingZip varchar(10) = NULL,
@.FK_CountryCode varchar(3) = NULL,
@.BillingCountry varchar(25) = NULL,
@.BillingPhone varchar(25) = NULL,
@.BillingFax varchar(15) = NULL,
@.ShippingFirstName varchar(15) = NULL,
@.ShippingLastName varchar(20) = NULL,
@.ShippingCompany varchar(40) = NULL,
@.ShippingTitle varchar(40) = NULL,
@.ShippingAttnLine varchar(40) = NULL,
@.ShippingAddress1 varchar(40) = NULL,
@.ShippingAddress2 varchar(40) = NULL,
@.ShippingCity varchar(20) = NULL,
@.ShippingState varchar(3) = NULL,
@.ShippingZip varchar(10) = NULL,
@.FK_SCountryCode varchar(3) = NULL,
@.ShippingCountry varchar(25) = NULL,
@.ShippingPhone varchar(25) = NULL,
@.ShippingFax varchar(15) = NULL,
@.FK_CustomerTierID int = 0,
@.UserName varchar(45) = NULL,
@.Password varchar(20) = NULL,
@.EMail varchar(45) = NULL,
@.TaxExempt bit = 0,
@.NoEmail bit= 0,
@.GREETING1 varchar(35) = NULL,
@.GREETING2 varchar(35) = NULL,
@.BelongsTo int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.custid INT
DECLARE @.CREATEDATE DATETIME
-- Begin process
--Get New CustomerID
BEGIN TRAN
SELECT @.custid = nextid
FROM tblAutoNumber
WHERE TableName = 'tblCustomers'
UPDATE tblAutoNumber
SET nextid = @.custid + 1
WHERE TableName = 'tblCustomers'
COMMIT TRAN
SELECT @.CREATEDATE = getdate()
BEGIN
INSERT INTO tblCustomers
(PK_ID,
Company,
FirstName,
MiddleInitial,
LastName,
Title,
BillingAttnLine,
BillingAddress1,
BillingAddress2,
BillingCity,
BillingState,
BillingZip,
FK_CountryCode,
BillingCountry,
ShippingFirstName,
ShippingLastName,
ShippingCompany,
ShippingTitle,
ShippingAttnLine,
ShippingAddress1,
ShippingAddress2,
ShippingCity,
ShippingState,
ShippingZip,
FK_SCountryCode,
ShippingCountry,
FK_CustomerTierID,
UserName,
Password,
Email,
BillingPhone,
ShippingPhone,
BillingFax,
ShippingFax,
LeaseStatus,
LeaseCreditLimit,
FK_CurrencyId,
DisableLogin,
LastModified,
Created,
TaxExempt,
NoEmail,
GREETING1,
GREETING2,
TaxExemptVerified,
AutoCancel,
LastLogin,
BelongsTo)
VALUES(
@.custid,
@.Company,
@.FirstName,
@.MiddleInitial,
@.LastName,
@.Title,
@.BillingAttnLine,
@.BillingAddress1,
@.BillingAddress2,
@.BillingCity,
@.BillingState,
@.BillingZip,
@.FK_CountryCode,
@.BillingCountry,
@.ShippingFirstName,
@.ShippingLastName,
@.ShippingCompany,
@.ShippingTitle,
@.ShippingAttnLine,
@.ShippingAddress1,
@.ShippingAddress2,
@.ShippingCity,
@.ShippingState,
@.ShippingZip,
@.FK_SCountryCode,
@.ShippingCountry,
@.FK_CustomerTierID,
@.UserName,
@.Password,
@.Email,
@.BillingPhone,
@.ShippingPhone,
@.BillingFax,
@.ShippingFax,
'',
0,
0,
0,
@.CREATEDATE,
@.CREATEDATE,
@.TaxExempt,
@.NoEmail,
@.GREETING1,
@.GREETING2,
0,
0,
@.CREATEDATE,
@.BelongsTo)
END
RETURN @.custid
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:A7EC9405-C84D-4ABC-B9D7-34ED6A4A1BC6@.microsoft.com...
> Hi
> You don't provide DDL for the procedure.
> http://www.aspfaq.com/etiquette.asp?id=5006
> You may want to check what is happening using profiler, and make sure that
> NOCOUNT is ON.
> John
>
> "stjulian" wrote:
>|||Wait, I think I got it ... The On Error was in an include file.
Thank you all for your help.
Julian
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:A7EC9405-C84D-4ABC-B9D7-34ED6A4A1BC6@.microsoft.com...
> Hi
> You don't provide DDL for the procedure.
> http://www.aspfaq.com/etiquette.asp?id=5006
> You may want to check what is happening using profiler, and make sure that
> NOCOUNT is ON.
> John
>
> "stjulian" wrote:
>