MS SQL Server DTS - Strange requirement for linked server

Author

Strange requirement for linked server

Christopher Pankhurst

2005-09-22, 8:24 pm

We need to create a DTS script to push data from one sql server to another
on a scheduled basis, changing some column names in the process (we do not
want to do replication). I start creating the DTS script and click on copy
tables and views and SQL server gives an error message about a server called
PLNZ needing to be in sysservers and please exec sp_addlinkedserver.

I don't understand why it is looking for a server called PLNZ. It could be a
domain name, as the computer was once connected to a domain called PLNZ,
however that was a long time ago. Now it is connected to a different domain.
There is an sql authentication user called plnz however it does not map to
any sort of windows authentication.

There has never been a server called PLNZ and there are no users with
windows authentication and a domain of PLNZ. I have narrowed the problem
down to that server/database combination. It works fine even if I try a
different database on the same server, however pushing from a different
database would be pointless.

I tried adding the server, but that just meant a slightly different error
message (about data source doesn't support the schema needed for DTS) came
later.

I would like to know why it is looking for PLNZ and how I can stop it from
doing so.

Sorry the error messages are a bit vague, I am posting this from my memory,
I am not near the server at the moment.

Chris Pankhurst

Allan Mitchell

2005-09-22, 8:24 pm

Is it possible the server has lost its name

What is the outcome of SELECT @@SEVRVERNAME?

If it is NULL then use sp_addserver 'server name',local

If not and also not the name of your box then use sp_dropserver first.

Allan

"Christopher Pankhurst" < christopher_pankhurs
t@hotmail.com> wrote in
message news:#oD$QB9vFHA.256@TK2MSFTNGP15.phx.gbl:

> We need to create a DTS script to push data from one sql server to
> another
> on a scheduled basis, changing some column names in the process (we do
> not
> want to do replication). I start creating the DTS script and click on
> copy
> tables and views and SQL server gives an error message about a server
> called
> PLNZ needing to be in sysservers and please exec sp_addlinkedserver.
>
> I don't understand why it is looking for a server called PLNZ. It could
> be a
> domain name, as the computer was once connected to a domain called PLNZ,
> however that was a long time ago. Now it is connected to a different
> domain.
> There is an sql authentication user called plnz however it does not map
> to
> any sort of windows authentication.
>
> There has never been a server called PLNZ and there are no users with
> windows authentication and a domain of PLNZ. I have narrowed the problem
> down to that server/database combination. It works fine even if I try a
> different database on the same server, however pushing from a different
> database would be pointless.
>
> I tried adding the server, but that just meant a slightly different
> error
> message (about data source doesn't support the schema needed for DTS)
> came
> later.
>
> I would like to know why it is looking for PLNZ and how I can stop it
> from
> doing so.
>
> Sorry the error messages are a bit vague, I am posting this from my
> memory,
> I am not near the server at the moment.
>
> Chris Pankhurst

Steen Persson (DK)

2005-09-23, 9:23 am

Christopher Pankhurst wrote:
> We need to create a DTS script to push data from one sql server to another
> on a scheduled basis, changing some column names in the process (we do not
> want to do replication). I start creating the DTS script and click on copy
> tables and views and SQL server gives an error message about a server called
> PLNZ needing to be in sysservers and please exec sp_addlinkedserver.
>
> I don't understand why it is looking for a server called PLNZ. It could be a
> domain name, as the computer was once connected to a domain called PLNZ,
> however that was a long time ago. Now it is connected to a different domain.
> There is an sql authentication user called plnz however it does not map to
> any sort of windows authentication.
>
> There has never been a server called PLNZ and there are no users with
> windows authentication and a domain of PLNZ. I have narrowed the problem
> down to that server/database combination. It works fine even if I try a
> different database on the same server, however pushing from a different
> database would be pointless.
>
> I tried adding the server, but that just meant a slightly different error
> message (about data source doesn't support the schema needed for DTS) came
> later.
>
> I would like to know why it is looking for PLNZ and how I can stop it from
> doing so.
>
> Sorry the error messages are a bit vague, I am posting this from my memory,
> I am not near the server at the moment.
>
> Chris Pankhurst
>
>

Somewhere in the task you are trying to create, you are referring so a
server named PLNZ. Without any more details, it's hard to say where it
is, but try to go through all the steps involved and check all places
where PLNZ is mentioned. It could be that you're just missing a . or
something else somewhere, so SQL Server interprets PLNZ as a server name
even though you mean something else.

Regards
Steen

Christopher Pankhurst

2005-09-25, 8:23 pm

To generate the DTS script I am just right clicking the database, clicking
export and using the wizard. I would hope the wizard wouldn't make a typo.

I've checked everything visible in Enterprise Manager.

Chris Pankhurst

"Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message
news:%23RmTi5DwFHA.3688@tk2msftngp13.phx.gbl...
> Christopher Pankhurst wrote:
another[color=darkre
d]
not[color=darkred]
copy[color=darkred]
called[color=darkred
]
be a[color=darkred]
domain.[color=darkred]
to[color=darkred]
error[color=darkred]

came[color=darkred]
from[color=darkred]
memory,[color=darkre
d]
>
> Somewhere in the task you are trying to create, you are referring so a
> server named PLNZ. Without any more details, it's hard to say where it
> is, but try to go through all the steps involved and check all places
> where PLNZ is mentioned. It could be that you're just missing a . or
> something else somewhere, so SQL Server interprets PLNZ as a server name
> even though you mean something else.
>
> Regards
> Steen

Steen Persson (DK)

2005-09-26, 7:23 am

Christopher Pankhurst wrote:
> To generate the DTS script I am just right clicking the database, clicking
> export and using the wizard. I would hope the wizard wouldn't make a typo.
>
> I've checked everything visible in Enterprise Manager.
>
> Chris Pankhurst
>
> "Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message
> news:%23RmTi5DwFHA.3688@tk2msftngp13.phx.gbl...
> another
> not
> copy
> called
> be a
> domain.
> to
> error
> came
> from
> memory,
>
>
I'm not an expert in using DTS for data export, but are you sure you
specify the right server for both the Source and Destination? Maybe you
could try to do the export from/to a test database just to make sure
that you enter the right info in the Wizard. You could maybe start with
2 database on the same server and make sure that works.
From where do you start the wizard - from your desktop or from one of
the servers? If you are doing it from your dekstop, then you could maybe
try to connect to the server with Remote Desktop and then run the Wizard
from there. It might not make any difference, but I know that in some
cases you can't use a linked server unless you are connected directly to
the server.

Regards
Steen

Christopher Pankhurst

2005-09-27, 8:24 pm

SELECT @@SERVERNAME returns FCN1, which seems to be correct. It is the same
as the windows name of the computer.

Chris Pankhurst

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:e6OtHQ9vFHA.1148@TK2MSFTNGP11.phx.gbl...
> Is it possible the server has lost its name
>
> What is the outcome of SELECT @@SEVRVERNAME?
>
> If it is NULL then use sp_addserver 'server name',local
>
> If not and also not the name of your box then use sp_dropserver first.
>
>
> Allan
>
>
> "Christopher Pankhurst" < christopher_pankhurs
t@hotmail.com> wrote in
> message news:#oD$QB9vFHA.256@TK2MSFTNGP15.phx.gbl:
>
>

Christopher Pankhurst

2005-09-27, 8:24 pm

The source server is correct, because I right click on the right database in
the right server to launch the wizard.
It faults before the specification of the destination server and database.

The right database on the right server does not work but the wrong database
on the right server does work.

I tried the wizard from Enterprise manager on my laptop. I will try it from
the server itself using remote desktop and see if that helps.

Thanks,
Chris Pankhurst

"Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message
news:%23o$2viowFHA.1252@TK2MSFTNGP09.phx.gbl...
> Christopher Pankhurst wrote:
clicking[color=darkr
ed]
typo.[color=darkred]
could[color=darkred]

PLNZ,[color=darkred]

map[color=darkred]
problem[color=darkre
d]
a[color=darkred]
different[color=dark
red]
name[color=darkred]
> I'm not an expert in using DTS for data export, but are you sure you
> specify the right server for both the Source and Destination? Maybe you
> could try to do the export from/to a test database just to make sure
> that you enter the right info in the Wizard. You could maybe start with
> 2 database on the same server and make sure that works.
> From where do you start the wizard - from your desktop or from one of
> the servers? If you are doing it from your dekstop, then you could maybe
> try to connect to the server with Remote Desktop and then run the Wizard
> from there. It might not make any difference, but I know that in some
> cases you can't use a linked server unless you are connected directly to
> the server.
>
> Regards
> Steen

Sponsored Links