Multiple update commands in one statment?

  • Thread starter Thread starter Doug Swanson
  • Start date Start date
D

Doug Swanson

I'm trying to execute multiple UPDATE statements from my DTS Package via an
ODBC connection to my access database. What I'm trying to avoid is having
multiple Execute SQL Task objects. Here is the sql I'm trying to run...

UPDATE imcoil_inventory set imcoil_inventory.process_ind = 'P'
WHERE imcoil_inventory.process_ind = 'Y'

UPDATE rmcoil_inventory set rmcoil_inventory.process_ind = 'P'
WHERE rmcoil_inventory.process_ind = 'Y'

UPDATE fgspec_inventory set fgspec_inventory.process_ind = 'P'
WHERE fgspec_inventory.process_ind = 'Y'


I've tried putting 'GO' after every update statment but I still can't get it
to work...any ideas?

thanks
Doug
 
Doug said:
I'm trying to execute multiple UPDATE statements from my DTS Package via an
ODBC connection to my access database. What I'm trying to avoid is having
multiple Execute SQL Task objects. Here is the sql I'm trying to run...

UPDATE imcoil_inventory set imcoil_inventory.process_ind = 'P'
WHERE imcoil_inventory.process_ind = 'Y'

UPDATE rmcoil_inventory set rmcoil_inventory.process_ind = 'P'
WHERE rmcoil_inventory.process_ind = 'Y'

UPDATE fgspec_inventory set fgspec_inventory.process_ind = 'P'
WHERE fgspec_inventory.process_ind = 'Y'


I've tried putting 'GO' after every update statment but I still can't get it
to work...any ideas?

thanks
Doug
Click to expand...

Perhaps you could put the statements in a stored procedure & call the SP
from the DTS - Exec SP_Name
 
Does Access have such a thing as stored procedures? My call is coming from
SQLServer (dts) back to Access....I didn't see anything...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

IOW, you're transfering data from SQL'r (pronounced squealer) to
Access - and you want the Update to be in the Access table not the
SQL'r table? OK, I thought it was the other way around.

You can write a VBS routine in the transformation section of the
object that copies the data from the SQL'r table(s) to the Access
table(s).

If you can't do the above - Access only allows one SQL statement per
query. Since you're running the UPDATE query in Access you'll have to
have separate SQL Tasks for each UPDATE.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQBCWY4echKqOuFEgEQJu5wCgl46qo3/lfcIgrPKbnnfT94cxKqUAoI6w
m+q7RIQr064cCK13V2I9z82q
=Ufzg
-----END PGP SIGNATURE-----
 
Back
Top