Re: Rows not transferring

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 09/25/04 

Date: Sat, 25 Sep 2004 12:17:20 +0100

Datapump from what to where. Some connection types do not support
transactions 

-- 
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com
"lennymm0" <lenny@mm0.com> wrote in message 
news:e1EzCsnoEHA.3428@TK2MSFTNGP11.phx.gbl...
> Allan,
> Thanks for the link and the help. I called the package as the job would,
> while logged in as the sysadmin/agent service account, and I started 
> getting
> errors in the log file that I was not getting before (primary key
> violations). That was because one of the connections was not referencing
> what I thought it was. So I fixed that by adding the connection properties
> to one of the dynamic properties tasks, and now I'm getting data pump 
> errors
> similar to those from before (earlier thread). The 2nd data pump task 
> fails
> (Task 'DTSTask_DTSDataPumpTask_3' does not support joining distributed
> transactions or failed when attempting to join.  (Microsoft OLE DB 
> Provider
> for SQL Server (8004d00a): New transaction cannot enlist in the specified
> transaction coordinator.) It works when I test it with few rows, think 
> it's
> OK and give it to someone to test, they test with a larger rowcount and it
> fails. I'm going nuts with this. Any ideas? Thanks!
> -lenny
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:42580D1E-F6B5-4D44-8403-E05BD5C322A6@microsoft.com...
>> Have a look at this article and see if it helps
>>
>> http://support.microsoft.com/?kbid=269074
>>
>> Here's something to try
>>
>> Log in as the Account which would be doing the execution if you were a 
>> job
>> (job owner = sysadmin then the SQL Server Agent service account, not a
>> sysadmin then the proxy account)
>> Now manually call the package yourself as the job would.  Still not work?
>>
>> "lennymm0" wrote:
>>
>> > Hi Allan. Thanks for the assistance.
>> > 1. I'm using xp_cmdshell through a job.
>> > 2. Yes, fail on first error is set.
>> > 3. I'm using the package logging and error handling properties to trap
> the
>> > info and, in the file, all steps have succeeded. All steps but one have
> a
>> > progress count of 0--even though more than one step is actually
> modifying
>> > data successfully.
>> >
>> > Also found Knowledge Base Article - 302828, but the user IS a system
>> > administrator, and both databases are on SP3 (problem corrected with
> SP2).
>> >
>> >
>> > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
>> > news:eYF1L%23ZoEHA.644@tk2msftngp13.phx.gbl...
>> > > When you use xp_cmdshell is that interactively or through a job?
>> > >
>> > > Have you set the package to fail on first error?
>> > >
>> > > Have you set the tasks to log any errors?
>> > >
>> > >
>> > >
>> > > "lennymm0" <lenny@mm0.com> wrote in message
>> > > news:OzWqj4ZoEHA.3392@TK2MSFTNGP15.phx.gbl...
>> > > > I'm using xp_cmdshell/dtsrun to execute a package that
> (sequentially)
>> > > > modifies data in one SQL Server database, transfers rows to a 
>> > > > second
> SQL
>> > > > Server database, and modifies data again in the first database. 
>> > > > When
> it
>> > > > successfully completes, all data modifications are made to the 
>> > > > first
>> > > > database, but no rows are transferred to the second. Any ideas what
>> > might
>> > > > cause this?
>> > > > Thanks!
>> > > > -Lenny
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>> >
>
>

Relevant Pages

  • Re: Cross Transactions between ADO & ADO.Net
    ... MARS session pool limited to one connection?). ... > There is one thing that might help here, if you're using SQL Server. ... We even call ADO code from ... >> the DTC cordinator from COM+ to handle the transactions between ADO.Net ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Rows not transferring
    ... Both source and destination are 'Microsoft OLE DB Provider for SQL Server' ... So I fixed that by adding the connection ...
    (microsoft.public.sqlserver.dts)
  • Re: Problem using transactions in a package
    ... A connection to the same server, using OLEDB provider for SQL Server. ... When I don't use transactions all package works but with the problem you ... >> FROM CONTRATANTE C ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS connection & BulkInsertTask
    ... Will this make a TextFile Connection to Sql Server 2000? ... as first parameter ie Servername? ... Allan Mitchell MCSE,MCDBA, ...
    (microsoft.public.sqlserver.dts)
  • Re: Why data could not be committed into table?
    ... Hi Jonathan, ... SQL Server will automatically perform a ... rollback if a connection is lost that still has transactions open. ... Even if the application doesn't explicitly begin any transactions, ...
    (microsoft.public.sqlserver.programming)