DTS to transfer data from different database failed

My SQL server is SQL 2000. The DTS package uses SQL anthentication to
transfer data from database A to database B. It fails. The error message
shows Windows NT user or group 'A\Guest' not found.
In DTS package, on Copy SQL Server Objects Task Properties screen, if I
don't select Create destination objects, copy data works. However the
destination tables do not have keys adn indexes.
How can I fix it? Any thoughts? Thanks.Hi
I am not sure why it will allow you to do this assuming that you use the
same users to authenticate when using copy objects and copy tables!
You may want to try scripting the tables including the indexes/pk/fks and
create them before populating them with data. If you have FKs then you will
need to load the data in the correct order if you don't want to
disable/enable the constraint.
John
"Sean" wrote:

> My SQL server is SQL 2000. The DTS package uses SQL anthentication to
> transfer data from database A to database B. It fails. The error message
> shows Windows NT user or group 'A\Guest' not found.
> In DTS package, on Copy SQL Server Objects Task Properties screen, if I
> don't select Create destination objects, copy data works. However the
> destination tables do not have keys adn indexes.
> How can I fix it? Any thoughts? Thanks.
>
>|||Please correct me if I am wrong. When I use SQL authentication for
destination database in DTS package when using copy objects and copy tables,
I thought it should be allowed. Otherwise, how do I make it work? Is there
anything missing?
Thanks.
"John Bell" wrote:
> Hi
> I am not sure why it will allow you to do this assuming that you use the
> same users to authenticate when using copy objects and copy tables!
> You may want to try scripting the tables including the indexes/pk/fks and
> create them before populating them with data. If you have FKs then you wil
l
> need to load the data in the correct order if you don't want to
> disable/enable the constraint.
> John
> "Sean" wrote:
>|||Hi
If you have permissions to create the table then you should be able to use
either method regardless of whether you use SQL Authentication or Windows
Authentication, therefore I am not sure why the copy tables worked and the
copy objects did not (assuming that you did not create the tables first). Yo
u
may want to use SQL Profile to see what is being executed on each of the
servers.
By pre-creating the table you will only need to insert the new data.
John
"Sean" wrote:
> Please correct me if I am wrong. When I use SQL authentication for
> destination database in DTS package when using copy objects and copy table
s,
> I thought it should be allowed. Otherwise, how do I make it work? Is there
> anything missing?
> Thanks.
> "John Bell" wrote:
>|||I agree with you. I assigned public and db_owner role for the SQL account on
database B. I can use that SQL account to create SQL object on database B
manually. The thing I am not sure is, when I run DTS package, why SQL serve
r
chooses 'A\Guest' account on database B.
By the way, how can I use SQL Profile to see what is being executed on each
of the servers?
Thanks.
"John Bell" wrote:
> Hi
> If you have permissions to create the table then you should be able to use
> either method regardless of whether you use SQL Authentication or Windows
> Authentication, therefore I am not sure why the copy tables worked and the
> copy objects did not (assuming that you did not create the tables first).
You
> may want to use SQL Profile to see what is being executed on each of the
> servers.
> By pre-creating the table you will only need to insert the new data.
> John
> "Sean" wrote:
>|||John,
I figured out why the DTS package did not work by using SQL Profile. I have
a user 'A\Guest' on datatase A. That account could not be created on databas
e
B. After I removed that account, the DTS package works.
Thanks for your help.
"John Bell" wrote:
> Hi
> If you have permissions to create the table then you should be able to use
> either method regardless of whether you use SQL Authentication or Windows
> Authentication, therefore I am not sure why the copy tables worked and the
> copy objects did not (assuming that you did not create the tables first).
You
> may want to use SQL Profile to see what is being executed on each of the
> servers.
> By pre-creating the table you will only need to insert the new data.
> John
> "Sean" wrote:
>