Hello, I am new to MS-SQL server and am trying to do something some of you have done before.

I am attempting to build a DTC that will run two sql statements in one transaction.

First SQL statement-clear the table I am working with
delete from APPDEV.IVRtrst

Second SQL statement-grab data from another datasource(mySQL) and insert it into table I am working with
insert into APPDEV.IVRtrst (tknum, cunum, tkstat) values (select tt.ttid, tt.cunum, case when tk.tkstatcode = 'C' then 'CLOSED' else 'OPEN' end as STATUS from ticket tt inner join task tk on(tt.ttid = tk.ttid)

I first tried this with the following code:
transation;
first sql statement;
second sql statement;
commit;

then I read about the GO statement and tried this:
first sql statement
GO
second sql statement

I get an error now
Error Source: Microsoft OLE DB Provider for ODBC Drivers

Error Description:[MySQL][ODBC 3.51 Driver][mysqld-3.23.58-log]You have an error in your SQL syntax near 'GO

insert into APPDEV.IVRtrst (select tt.ttid, tt.cunum, case when tk.tkstat' at line 3

Context: Error calling GetColumnInfo. Your provider does not support all the interfaces/methods required by DTS.

Can anyone help, I am still trying some differnt things, but really need to get this working and I feel that it can, Thanks, calvin

Remember not ALL drivers support transactions so I would do this

1 * ExecuteSQL task with your first statement (No GO)
1 * ExecuteSQL task with your second statement (No GO)

Join the two together with an On Success Constraint

In the workflow properties for each task

Check Join transaction if present and Rollback transaction on failure

at the package level in the properties enable transactions.




--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"calvin23" <anonymous (AT) discussions (DOT) microsoft.com> wrote

STATUS from ticket tt inner join task tk on(tt.ttid = tk.ttid)

Allan, I have figured more out since the last email. But still have a problem with an Execute SQL statement using two connections.

I figured how to
-join the two together wth an On Sucess Constraint
-in workflow properties for each task, check Join Transaction if present AND Rollback transaction on failure
-at the package level I enabled transactions by checking Use transactions AND Commit on successful package completion on the Advanced tab of the DTS package properties

I do get an error message when I attempt to execute the DTS though. I think I need to turn on a service on the box. Here is my error message:

Error Source : Microsoft Data Transformation Services (DTS) Package

Error Description : The Microsoft Distributed Transaction Coordinator (MSDTC service) is not available.

I will comb through my server to make sure this service is started.

Any idea's on the Execute SQL task using two connections would be appreciated though. Thanks and have a good Wednesday, calvin

----- Allan Mitchell wrote: -----

Remember not ALL drivers support transactions so I would do this

1 * ExecuteSQL task with your first statement (No GO)
1 * ExecuteSQL task with your second statement (No GO)

Join the two together with an On Success Constraint

In the workflow properties for each task

Check Join transaction if present and Rollback transaction on failure

at the package level in the properties enable transactions.




--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"calvin23" <anonymous (AT) discussions (DOT) microsoft.com> wrote

STATUS from ticket tt inner join task tk on(tt.ttid = tk.ttid)

You need to look towards linked servers then

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"calvin23" <rduke (AT) cavtel (DOT) com> wrote

connections(insert into APPDEV.IVRtrst(table on MSSQL box) select a,b,c from
ticket (table on mySQL box) so the Execute SQL won't work as I can tell. Is
there another tool for this type of operation?

Yep DTC needs to be started

EM | Support Services |Distributed Transaction CoOrdinator

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"calvin23" <anonymous (AT) discussions (DOT) microsoft.com> wrote

package properties