Hello,

Is it possible to change the servername in a scheduled job that executes a
dts package?

I have 2 sql2000 servers running and I want to move all databases from
server A to Server B. I restored all databases, dts packages and jobs.
(I've created the jobs using a exported sql script) about 100 jobs
But when I start a Job it fails. (I think it fails because its using the
dtsrun ~xxxxxxxxxxxxx command. and the servername / is somewhere encrypted.)

How can I easily modify all jobs without to modify each manually?

Hello YRREB,

Do you have an error message?

It could be the encrypted DTSRun command but then again it may not be. When
you created the job scripts did you change the servername in them?

Allan

This is the error message: (for all jobs)

Executed as user: domain\user. DTSRun: Loading... Error: -2147217843
(80040E4D); Provider Error: 18456 (4818) Error string: Login failed
for user 'domain\user'. Error source: Microsoft OLE DB Provider for SQL
Server Help file: Help context: 0. Process Exit Code 1. The
step failed.

I did a generate sql script on the jobs and copied the preview. then I
executed the sql query on the new server. (this server has a different server
name).
the automaticly generated script contains dtsrun ~xxxxx. (I can't change it
in here)
is it possible to change the servername for all jobs?




"Allan Mitchell" wrote:

What i do to avoid that situation is the following :-

DTSRUN /S "(local)" /E /N "DTS Package name here"

That way if we ever loose the server that runs the jobs it's easy to move to
another box as it looks for the (local) server not a named machine, all our
DTS packages make DB connections using UDL's, these UDL's also exist on
every machine.

--
HTH. Ryan
"YRREB" <YRREB (AT) discussions (DOT) microsoft.com> wrote

Hello YRREB,

OK so this error

Login failed for user 'domain\user'. Error source: Microsoft OLE DB Provider
for SQL Server

would seem to tell me that you are having issues with a SQL Server connection
within the package. You are calling the package fine.

There is a very distinct difference between interactive execution and job
execution. Because you have moved servers I would take a look at the difference
in server service accounts between your two servers. Chances are they are
not the same.


http://support.microsoft.com/?kbid=269074


I generally dislike the DTSRun ~Z syntax generated by EM. I like to spell
out my DTSRun line myself.




Allan