Hello,
I need some VBScript code. This is being used to transform published
data in a SQL 2000 transactional replication procedure. I just need a
few lines inserted as follows. I will fill in the details of the table
and field names. I don't even know if it is possible. $20 dollar tip
if answered before 3:30 on tuesday april 27'th.

Function Main()
	DTSDestination("AccountNumber") = DTSSource("AccountNumber")
	DTSDestination("CardNumber") = DTSSource("CardNumber")
	DTSDestination("StreetNumber") = DTSSource("StreetNumber")
	
        DTSDestination("OwnerName") = NEED CODE TO EXECUTE QUERY
                                      SELECT Ownername
                                      FROM server.table
	                              WHERE server.table.field
                                             = server.table.firld

        Main = DTSTransformStat_OK
End Function

---

Request for Question Clarification by mathtalk-ga on 27 Apr 2004 10:14 PDT

Hi, sucker5-ga:

When I want to use VBScript in DTS, I first try to generate something
close to what I want with the DTS "wizard", then save the "package" as
VB code.  The VB code can then be edited by hand.

Have you tried this approach?

regards, mathtalk-ga

---

Clarification of Question by sucker5-ga on 27 Apr 2004 10:38 PDT

Hello mathtalk-ga,
I have tried this approach but I can't seem to find any commands to
execute SQL queries.

---

Request for Question Clarification by mathtalk-ga on 27 Apr 2004 12:16 PDT

Perhaps I'm misunderstanding the problem's context, but if the table
is available on the "source", cannot the Ownername field be
incorporated in a View?

The "source" can then be this View rather than a Table per se.

Where VB code would come in handy is in defining some string
manipulation or other custom functions to do transformation.

If the Ownername field cannot be obtained from the "source" by any
reasonable SQL query (used to define a View), then I'd say the DTS
approach is probably too constrictive.  I'd look at writing a small VB
application using ADO (or VB.Net using ADO.Net) to gather all the
necessary data and perform the required inserts or updates.

I get the feeling that you might be trying to treat the table name
and/or field name from which Ownername is obtained as parameters.  If
so, you are probably going to find that constructing the required SQL
query as a string in VB (and executing it through ADO or ADO.Net) is
much less frustrating than trying to coax this behavior from a pure
SQL implementation.  It's not obvious to me that DTS would help much
in obtaining that kind of indirection.

regards, mathtalk-ga

---

Clarification of Question by sucker5-ga on 27 Apr 2004 12:35 PDT

Hello mathtalk-ga,
I tried using a view before but there were problems with the
transactional replication. SQL Server will not let me publish a view
if a DTS is defined for the publication. thanks.

nick_name-ga,
I have not tried using a join clause in that area yet. I will try that. thanks.

---

Clarification of Question by sucker5-ga on 28 Apr 2004 07:35 PDT

I suppose some more context would be in order. 
I have 1 destination table that I cannot modify sitting at one site.
This table autogenerates its primary key when records are inserted. I
have 6 source tables sitting at another site. Getting all the required
data from the 6 source tables needs a fairly complicated SQL query but
it is completed. I need to implement transactional replication to get
this data into my 1 destination table but if I use a view to get the
data from the 6 source tables, I cannot publish directly to my
destination table. SQL will not let me publish views if a DTS package
is defined for my replication. If none is defined, then I will just
get the same view on the destination side but I need the data in the
destination table. Also I cannot use the DTS query that nick_name-ga
suggested becuase SQL does not give that option when defining a DTS
package for replication. I appreciate all the help though. Any
thoughts?
Thank You.

---

Clarification of Question by sucker5-ga on 30 Apr 2004 08:24 PDT

mathtalk-ga,
I figured it out. Thanks. Please feel free to post your previous
comments as an answer so you can get paid.

---

Request for Question Clarification by mathtalk-ga on 30 Apr 2004 11:07 PDT

Hi, sucker5-ga:

I'm really grateful for your encouragement.  Would it be okay if I
took the weekend to see if I can reverse engineer your solution, or
failing that, at least put together a nice note on replication and DTS
packages in SQL2K?

Then maybe you can added a Comment about your solution!

regards, mathtalk-ga

---

Clarification of Question by sucker5-ga on 30 Apr 2004 12:27 PDT

Hello mathtalk-ga,
That is fine with me. I didn't really find a solution in VBScript but
I found a work around using a view.

Have you tried using the DTS wizard and selecting *Use a query to
specify the data to transfer* (avail. after defining source &
destination)?  It sounds like you may just need to use a join query as
your data source.  Hope this helps...