I recently had an experience with a database which was mysteriously ERASED.
That is every table was suddenly purged of all data. This is not something
which could have been done casually. Unless deliberately (and maliciously)
sequenced, a series of DELETE FROM statements would have failed sooner or
later due to key constraints. This just didn't make sense...

I think I've tracked this down to the use of (local) in a DTS package. But
if my conclusion is correct, this makes this type of syntax so dangerous
that I can't believe I haven't come across this before. If you can confirm
or disprove the following, please let me know.

We have two database servers, ServerA (our primary server) and ServerB (our
backup server). We have a DTS package defined on ServerB which copies
ServerA\OurDB to (local)\OurDB. This package normally runs unattended,
transferring OurDB from ServerA to ServerB. If you log on to ServerB and
bring up Enterprise Manager, you can manually execute the package which will
transfer OurDB from ServerA to ServerB (local). However, if you log in to
ServerA, bring up Enterprise Manager and run the same DTS package, then the
primary database on ServerA is ERASED! Seemingly, the (local) syntax refers
to the Server which is local to the current session (ServerA), rather than
the server which is local to the DTS Package itself. Is this correct? This
seems incredibly dangerous to me that a DTS package will behave differently
depending on the session in which it is accessed! I can see a theoretical
purpose for this, but for our purposes I'm considering imposing a standard
in our shop that we do not normally use (local), rather we explicitly
designate the server name. This should eliminate this type of dangerous
ambiguity.

If you've had similar experience with the use of (local) in DTS, I welcome
your comments.

Thanks,

- Joe Geretz -

Seems reasonable

DTS executes from wherever it is called so therefore (local) means local.
The only way to avoid this is to use the names of servers as the names of
servers.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Joseph Geretz" <jgeretz (AT) nospam (DOT) com> wrote

Yes, DTS is like any other client application. When you specify (local),
this refers to the default instance on the local server. It is important to
understand that a DTS package runs always on the executing machine
regardless of how it is invoked (EM, DTSRUN or client app). The location of
the package repository is not relevant.

It's certainly safer to explicitly the desired server/instance names. A
consideration is that you'll need to make the names configurable if you want
to run the same package with different sources or targets.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Joseph Geretz" <jgeretz (AT) nospam (DOT) com> wrote

(local) is shorthand for your (local) SQL Server. The meaning of (local)
changes, depending on which SQL Server you're running your DTS package on.
This is obviously very useful if you want to run the same DTS package on
multiple SQL Server, but don't want to hard-wire the Server name into the
DTS package for every single SQL Server you'll run it on.

If you have "Drop Destination Objects First" set on your DTS package and
you're running a DTS package that on ServerA that copies the objects from
ServerA to (local), then obviously all your objects will be dropped on
(local) before it gets to the copy portion of the DTS package. In your
case, since you have 2 servers *and* the DTS package should *always* copy
from ServerA to ServerB *and* you want to be able to run it on either
server, you need to specify the servers *by name* or you risk losing your
data due to the Drop Destination Objects First option.

Using (local) usually makes sense if you want the operation (DTS package,
etc.) to always affect the local database - regardless of which server it's
run on. Your DTS package does not meet that criteria, since you always want
to update ServerB from ServerA -- no matter if the DTS package is run on
ServerA, ServerB or even ServerZ.

Thanks,
Michael C.

"Joseph Geretz" <jgeretz (AT) nospam (DOT) com> wrote

Thanks to all for responding and confirming what I've discovered. When you
understand how it works it all seems obvious, but beleive me it was quite a
shock to all of us when we discovered our data had simply all vanished!

It definitely makes sense, and now that I understand how (local) works we'll
use it when appropriate, but we'll eschew this most of the time in favor of
explicitly coded server names.

Thanks!

- Joe Geretz -

"Michael C" <nospam (AT) lol (DOT) net> wrote

The only comment I'd like to add, is that since (local) and ServerA are
functionally different, it would be nice if the Server drop down box would
show the name of the local database server.

(local)
ServerA {This entry not shown on ServerA}
ServerB
etc...

- Joe Geretz -

"Joseph Geretz" <jgeretz (AT) nospam (DOT) com> wrote