I have a DTS package set up to copy data from one SQL2K server to
another SQL2K server. I'm using the copy sqlserver objects task and
have specified individual tables. The job has been running fine for a
couple of months, but now it is causing blocking in the source
database. I'm only seeing the problem on one table ~150000 records.
Is there some way to set the transaction isolation level, I don't see
any options to specify it. I'm pretty new to using DTS, should I
switch from this form of data copy to linked servers and writing the
code to do the copy?

The transaction isolation level of the package can be set in the package
properties
(Right Click Whitespace | Package Properties | Advanced)

I personally do not like this task.

If I want to transfer all my tables etc from A-B then I use BACKUP RESTORE
If i want to move a subset then I Should have the tabledefs on the
destination already so simply use a DataPump task to move the data.


What is causing the blocking. People can read the same data at the same
time so maybe the lock that is causing the problem is the lock taken to
secure the schema definitions. The reading of that table then should not be
being done in the task as well though so is anything else trying to use the
DB at that time ?



--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"McKiv" <mmckivigan (AT) netscape (DOT) net> wrote

Have you recently installed a Security update or a Service
pack of SQL server?

I have experienced the same problem when copying objects
from a DB server to an other DB server with a DTS package
after installing a security update - don't remember which
one.

I then found out that you need to have exactly the same DB
users created in both database servers, to have you DBs
properly replicated.

So if there are some users - SQL server users or Windows
users - that exist on your server A and that do not exist
on your server B, you have to recreate your A-users on
server B.

If your 2 servers qre running under a Domain account - not
Windows System Account - and your domain account is the
same on both servers or has the same rights, you can use a
DTS package to copy users from a machine to the other. But
try it first on a Test environement, cause the transfer
login task of DTS can have unexpected behaviour.