"AJ" wrote:

> ENVIRONMENT:
> RAID 10 CLUSTERs with upto 8 processors.
> SQL server 2000 SP4
> WINDOWS 2003 with latest SPs
>
> INTRO:
>
> I have a DTS which has Database context that is set thro' Dynamic Properties
> Task.
> This DTS has a bunch of transform data task to copy data from a SQL server
> DB to an Access DB.
>
> A Strored procedure exceutes this DTS using xp_cmdshell.
> It gets the Database context from the function DB_NAME()
> /************************PART OF THE SPROC******************/
> SET @DatabaseName = DB_NAME()
>
> set @string='dtsrun /S' + @@ServerName+ ' /U' + @UserID + ' /P' + @Password
> + ' /NCopyAccess /M /ADatabaseName:8=' + @DatabaseName + ' AND MORE..'
> exec @RC= master..xp_cmdshell @string, no_output
> /************************PART OF THE SPROC******************/
>
>
> Finally this SPROC is executed in a job as a step for each database I have
> on that SQL server. This job is scheduled and runs once per day.
>
>
> PROBLEM
> It works fine most of the time but once in a while it pumps data from the
> wrong database. I am getting spanked for loss of data integrity. There are no
> hardcoded database name within the DTS tasks.
>
> I have tried pretty much everything. Finally I have settled down for setting
> up a dummy database and making it the default database for the server. I also
> set the databse as DEFAULT for SQL Server Connection properties. The step
> fails for odd databases but works fine for the rest.
>
> I think this is a serious DTS problem with SQL Server 2000 that hasn't been
> reported yet.
>