DTS Active X transform script error

I can't for the life of me figure out why I am getting an error with this simple script. The error I get is "expected end" on line "22" which is the first "elseif" in the nested if statement. This makes no sense as obviously I don't want an end if statement there. Any ideas where my syntax is wrong would be greatly appreciated.

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()

dim txncode
dim cashin

txncode = Trim(DTSSource("Col004"))
Cashin = Trim(DTSSource("Col005"))

if txncode = "" or IsNull(txncode) then
Main = DTSTransformStat_SkipRow
exit Function
end if

select case txncode
case "210"
if (Cashin > "0" and Cashin < "1000") then txncode = "210-1000"
'msgbox txncode
elseif ("1000" < Cashin and Cashin < "5000") then txncode = "210-1000-5000"
elseif (Cashin > "5000") then txncode = "210-5000"
else txncode = "210"
end if
msgbox txncode
Main = DTSTransformStat_OK
exit function

case else
Main = DTSTransformStat_OK
exit function

end select

DTSDestination("TransactionCode") = txncode
Main = DTSTransformStat_OK
End Functiongtheo, I hope someone is able to answer your question, but most of the heavy posters on this forum long ago stopped putting logic into DTS. Use it to load staging tables and the put your transform logic into a sproc.|||Problem Solved - it was twofold.

1) inline if-then made it expect it to have an end, if using multiple else you need then on next line.
2) quotes around numbers made them evaluate as text.

sproc solution untenable as that would entail schema changes, a no-no in a big bad corporate world development environment where I am on the client side, not dev!|||Glad you found the solution, but...

huh?

Why would a sproc solution imply schema changes? Are you talking about the staging tables? If necessary, those can be maintained in a separate database on the same server. As a consultant, I dip into a dozen or more big bad corporate worlds every year and there is always a way to implement this.|||The problem is an immature product that means a fat client (including full SQL Client) and no separation between an "application service user" and the end user. So the end user has to execute the DTS - meaning they have to create any temp tables, etc that are used; also I'd be discouraged from creating a separate database if not forbidden by the bank clients. They don't want anything that is not an "offical release". So, adding objects is not advised, and a separate database would be thrown back at me. So the easiest thing to do is have a structured storage file DTS Package that sits on the client and executes the logic inside itself. This is not viewed as custom code per se. Makes no sense to me but it's the way I get around them.|||Foolish people. Make sure then have your contact information so they can easily get in touch with you to rewrite the whole thing when they upgrade to 2005.

DTS - Execute Data-pump Task

SQL 2K
Using Active X script , created the source SQL statement for data-pump task
and it worked.
Inside Active X script I am looping thru to change the source SQL
statements, how can I again execute this data-pump task.
When I look the source SQL in data-pump task it got changed , but data-pump
task executed only once.
So my question is, how to execute a data-pump task inside Active-X script '
Thx
DUWhat are you using to determine your new connections ?
Why do you need to reset this during the package?
You can easily loop to do this but you have to loop on something.
How are you calling the package ?
Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)
How to loop through a global variable Rowset
(http://www.sqldts.com/Default.aspx?298)
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"MS User" <sqlman@.sql.com> wrote in message
news:%233nQmIbnDHA.744@.tk2msftngp13.phx.gbl...
> SQL 2K
> Using Active X script , created the source SQL statement for data-pump
task
> and it worked.
> Inside Active X script I am looping thru to change the source SQL
> statements, how can I again execute this data-pump task.
> When I look the source SQL in data-pump task it got changed , but
data-pump
> task executed only once.
> So my question is, how to execute a data-pump task inside Active-X script
'
> Thx
> DU
>

DTCXact transaction in DBCC openTran

I keep seeing this return from running a DBCC OpenTran:

Transaction information for database 'Live_App'.

Oldest active transaction:
SPID (server process ID) : 92
UID (user ID) : 1
Name : DTCXact
LSN : (12837:1924:1)
Start time : Oct 4 2004 8:54:03:570AM
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

I don't see anywhere in code that begins a transaction with the name
DTCXact explicitly. Is this a generic name for any transaction that is
opened without an explicit name? The problem I am having with this is
that sometime it will start and may not get commited or rolledback for
quite some time. I have seen it remain for over 1 1/2 hours before.
Would that be caused by the application not cleaning it up?
Your help in explaining the source of this will be appreciated. I did
find an entry on Microsoft.com that used the word DTCXact. It was
talking about Transaction Propagation from Resource Manager To
Application. I'm not sure if this applies to what I am seeing here or
not.

Thank you.
Kalvin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Kalvin" <ktXXDelMeXXuel@.streck.com> wrote in message
news:4161599e$0$26142$c397aba@.news.newsgroups.ws.. .
> I keep seeing this return from running a DBCC OpenTran:
> Transaction information for database 'Live_App'.
> Oldest active transaction:
> SPID (server process ID) : 92
> UID (user ID) : 1
> Name : DTCXact
> LSN : (12837:1924:1)
> Start time : Oct 4 2004 8:54:03:570AM
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> I don't see anywhere in code that begins a transaction with the name
> DTCXact explicitly. Is this a generic name for any transaction that is
> opened without an explicit name? The problem I am having with this is
> that sometime it will start and may not get commited or rolledback for
> quite some time. I have seen it remain for over 1 1/2 hours before.
> Would that be caused by the application not cleaning it up?
> Your help in explaining the source of this will be appreciated. I did
> find an entry on Microsoft.com that used the word DTCXact. It was
> talking about Transaction Propagation from Resource Manager To
> Application. I'm not sure if this applies to what I am seeing here or
> not.
> Thank you.
> Kalvin
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

It looks like a distributed query, ie. a query or stored procedure being
executed on another server via a linked server (DTC is the Distributed
Transaction Coordinator service). You can use DBCC INPUTBUFFER,
fn_get_sql(), or Profiler to see exactly what the SPID is doing.

Simon

DTC on ACTIVE ACTIVE cluster - urgent

Greetings, we have 4 node cluster setup. 3 nodes are active and one is
passive. As I understood, MS DTC will run only one node and hence will be
available to the SQL instances running on that node. Can I run DTC on
multiple nodes in a cluster?
I have created one DTC resource prior to the SQL install. I can not add
addional DTC resource. Our apps need to have DTC on most of the instances
which we can not put them on one node. Any suggestions?
Thanks in advance.
The single DTC instance will support all SQL instances in the cluster,
regardless of their current host node. You do not need a unique DTC
instance for each SQL instance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Mintz" <Mintz@.discussions.microsoft.com> wrote in message
news:8DBA18AB-FA21-474D-B630-78F0CA8B910D@.microsoft.com...
> Greetings, we have 4 node cluster setup. 3 nodes are active and one is
> passive. As I understood, MS DTC will run only one node and hence will be
> available to the SQL instances running on that node. Can I run DTC on
> multiple nodes in a cluster?
> I have created one DTC resource prior to the SQL install. I can not add
> addional DTC resource. Our apps need to have DTC on most of the instances
> which we can not put them on one node. Any suggestions?
> Thanks in advance.
|||I agree with Geoff but the key to DTC working properly is to enable DTC for
network access. You can do this from Control panel/Add remove programs/add
remove components/Application Server. You have to do this on all nodes in
your cluster.
Also refer to the following articles
http://support.microsoft.com/default...b;en-us;301600
http://support.microsoft.com/default.aspx?kbid=817064
"Geoff N. Hiten" wrote:

> The single DTC instance will support all SQL instances in the cluster,
> regardless of their current host node. You do not need a unique DTC
> instance for each SQL instance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Mintz" <Mintz@.discussions.microsoft.com> wrote in message
> news:8DBA18AB-FA21-474D-B630-78F0CA8B910D@.microsoft.com...
>
>
|||i stuck at step 4. so my questions are
1. do i need an separate IP address? can i use the ip in cluster group
2. for physical disk, do i have to create another physical disk which
is shared by both nodes? can i use drive "Q:\"?
right now i did not follow the doc and created MSDTC in cluster group
using ip, physical disk and network name in that group (created
automatically). only one node can see msdtc which is not what i
expect.
thanks,
jj
"Rony Thomas" <rony_p_thomas@.hotmail.com> wrote in message news:<4C07737F-248A-42DE-87DA-F1D1590C44E8@.microsoft.com>...[vbcol=seagreen]
> I agree with Geoff but the key to DTC working properly is to enable DTC for
> network access. You can do this from Control panel/Add remove programs/add
> remove components/Application Server. You have to do this on all nodes in
> your cluster.
> Also refer to the following articles
> http://support.microsoft.com/default...b;en-us;301600
> http://support.microsoft.com/default.aspx?kbid=817064
>
> "Geoff N. Hiten" wrote:
|||try creating another group with resources for DTC and make sure you enable
network access
"jj" wrote:

> i stuck at step 4. so my questions are
> 1. do i need an separate IP address? can i use the ip in cluster group
> 2. for physical disk, do i have to create another physical disk which
> is shared by both nodes? can i use drive "Q:\"?
> right now i did not follow the doc and created MSDTC in cluster group
> using ip, physical disk and network name in that group (created
> automatically). only one node can see msdtc which is not what i
> expect.
> thanks,
> jj
> "Rony Thomas" <rony_p_thomas@.hotmail.com> wrote in message news:<4C07737F-248A-42DE-87DA-F1D1590C44E8@.microsoft.com>...
>