DDQ Update issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DDQ Update issues

Author

Topic

timsweet
Starting Member

31 Posts

Posted - 2005-07-19 : 11:03:00

Thanks in advance for any advice.

I'm attempting to use a DDQ for updating only.

I don't have to check to see if the record exist, that is a given.

I'm taking data from a SQL 2000 database and adding it to an ACCESS 03 database.

The DDQ runs fine with no errrors - but it does not update the record in the database.

Below is my code.

my update query:

UPDATE tblPeople
SET Address1 = Address1,
Address2 = Address2,
Address3 = Address3,
City =City,
State =State,
PostalCode =PostalCode,
Country =Country
WHERE ABR_ID = ABR_ID

MY ACTIVEX SCRIPT

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

' Copy each source column to the destination column
Function Main()
DTSDestination("Country") = DTSSource("country")
DTSDestination("PostalCode") = DTSSource("postalcode")
DTSDestination("State") = DTSSource("State")
DTSDestination("City") = DTSSource("city")
DTSDestination("Address3") = DTSSource("address3")
DTSDestination("Address2") = DTSSource("address2")
DTSDestination("Address1") = DTSSource("address1")
Main = DTSTransformstat_UpdateQuery
End Function

MY SOURCE QUERY:

Select *
from tempaddress2

Any suggestions will be appreciated.

thebruins
Starting Member

31 Posts

Posted - 2005-07-19 : 11:11:40

shouldn't the UPDATE query (in the 'queries' tab) be something like

UPDATE tblPeople SET address1 = ?, address2 = ? WHERE ABR_ID = ?

?
and then set the first parameter to be address1, the 2nd address2 etc... and the final one to be ID?

timsweet
Starting Member

31 Posts

Posted - 2005-07-19 : 15:42:23

I've inhereited this mess and haven't done a DDQ before.

so something like

Update tblPeople
@Address1, nvarchar
@Address2, nvarchar

or to I use ?

?Address2, nvarchar

Thanks

timsweet
Starting Member

31 Posts

Posted - 2005-07-19 : 16:48:41

I've used the following update query
UPDATE tblPeople
@abr_id as nvarchar(5),
@address1 as nvarchar(40),
@address2 as nvarchar(40),
@address3 as nvarchar(40),
@city as nvarchar(25),
@State as nvarchar(2),
@postalcode as nvarchar(10),
@country as nvarchar(15)

as

SET Address1 = ?,
Address2 = ?,
Address3 = ?,
City =?,
State =?,
PostalCode =?,
Country = ?
WHERE ABR_ID = ?

It runs then give me an update query syntex areas...any thoughts?

regards
Tim Sweet

thebruins
Starting Member

31 Posts

Posted - 2005-07-19 : 17:22:48

I take it you're doing this DDQ in a DTS package?

timsweet
Starting Member

31 Posts

Posted - 2005-07-19 : 17:44:14

Correct.

thebruins
Starting Member

31 Posts

Posted - 2005-07-19 : 17:47:07

so when you get the properties of the DDQ you get like 5 tabs or so? one of them is 'queries'?

timsweet
Starting Member

31 Posts

Posted - 2005-07-19 : 17:53:46

correct again.

timsweet
Starting Member

31 Posts

Posted - 2005-07-19 : 17:55:15

Select the queries tab and there is a drop down select where pick the type of query you want the DDQ to run

thebruins
Starting Member

31 Posts

Posted - 2005-07-19 : 17:59:08

ok. as you'll only need to update stuff, select update and enter the query

UPDATE tblPeople
SET Address1 = ?,
Address2 = ?,
Address3 = ?,
City =?,
State =?,
PostalCode =?,
Country =?
WHERE ABR_ID = ?

below the query you can setup the parameters for the query. the 1st one is address1, and so on...the final one being abr_id.

timsweet
Starting Member

31 Posts

Posted - 2005-07-19 : 18:04:07

The parameters are coming from a select query that is the source (I'm going from SQL to ACCESS 03 in this case)

timsweet
Starting Member

31 Posts

Posted - 2005-07-19 : 18:08:30

What would be the syntax for that?

thebruins
Starting Member

31 Posts

Posted - 2005-07-19 : 18:14:59

your activex script fills the destination fields using the source fields and tells MSSQL always to do an update. so in the 'queries'-tab you define that update, by specifying a query with all values replaced by a '?' and at the bottom of the window by telling which parameter (numbered 1, 2...) represents which value

timsweet
Starting Member

31 Posts

Posted - 2005-07-20 : 10:38:03

I appreciate the assistance.

What might the syntax be for the parameters...my select statement?

thebruins
Starting Member

31 Posts

Posted - 2005-07-20 : 11:51:02

I'm confused what you mean by syntax for the parameters?

have you set a source by specifying a table, and a destination?

timsweet
Starting Member

31 Posts

Posted - 2005-07-20 : 12:15:37

Sure...my source is set...the query is

select abr_id, address1, address2, address3, city, state, postalcode, country
from tempaddress2

My activex script

Function Main()
DTSDestination("ABR_ID") = DTSSource("abr_id")
DTSDestination("address1") = DTSSource("address1")
DTSDestination("address2") = DTSSource("address2")
DTSDestination("address3") = DTSSource("address3")
DTSDestination("City") = DTSSource("city")
DTSDestination("State") = DTSSource("state")
DTSDestination("Postalcode") = DTSSource("postalcode")
DTSDestination("country") = DTSSource("country")
Main = DTSTransformstat_UpdateQuery

and under the Queries tab my update code:

UPDATE tblPeople
SET Address1 = ?,
Address2 = ?,
Address3 = ?,
City =?,
State =?,
PostalCode =?,
Country =?
WHERE ABR_ID = ?

Now I thought, through my reading that the activex would be used to show the paramaters

timsweet
Starting Member

31 Posts

Posted - 2005-07-20 : 12:28:13

Once I get this finished and working..I'm going to post example everywhere...there just isn't a lot of references on DDQ

thebruins
Starting Member

31 Posts

Posted - 2005-07-21 : 03:43:45

:) seems to me you're almost there

you've got 2 connections in the package (one to MSSQL, other to Access) and a DDQ.
source is set in DDQ (thru the 1st connection), binding table too (thru 2nd connection). activex performs transformations resulting in an update query, which is specified in the queries tab. when you press 'parse/show parameters' in that tab, does a list of parameters appear?

timsweet
Starting Member

31 Posts

Posted - 2005-07-21 : 10:06:30

To "thebruins" Thanks for all the assistance. I found the problem it was easier then I expected. I was looking to script the parameters, when All I really need to do was to populate them in the window you mentioned. I kept say..it can't be that easy!!!!

Thanks again

Best Regrads
Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources