Copy Database Wizard and DTS Transfer Database Task don't work

Hello,
I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
2000. Both belong to the same domain and can see each other without
problems. A mutual registration of the other sql server in Enterprise
Manager is no problem.
One Server's operating system is win2003Server, the other one's is
Win2000Server.
In both servers the domain account I use for logging in and for
authentification purposes is a member of the server's local
administrators group. And the local administrators have sysadmin rights
in sql server 2000 via builtin\administrators.
After some tests I added my windows domain account explicitly as
sysadmin in both SQL-Servers.
The security mode is "mixed" (SQL-Login and Windows).
Trying to transfer a Test database with 1MB space fails. No matter
whether I use a Transfer Database Task or whether I use the Copy
Database Wizard (which also uses DTS in the background).
The Transfer Database Task needs FIVE minutes for the dialog window
"Transfer Database Properties" to reappear after clicking on the
tab-card [file locations].
Below the grid is a green check mark: Files are ready to be moved or
copied.".
Below that is a red X: Files on the source have the same name as on the
destination or there is not enough free disk space on the destination.
On both servers there is plenty of space (dozens of GB).
In the end I only get the error description "Unspecified error".
Doing the same with the Copy Database wizard" in the Grid beside the
..mdf file and the .ldf file shows the STATUS: OK
Below, again two error messages:
Name conflict: Type a new name into the Destination Files fields
Disk space: Change the destination or free disk space
At the same time is shown the space needed and available:
E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
So I run the job in the wizard immediately and get the following
errors:
Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
Server]Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection.
Step Error code: 80004005
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:700
Apparently DTS uses odbc and can not establish the connection despite
Sysadmin rights on both servers. I tried to specify the account sa of
sql server for authentication instead of using windows authentication
but that yields the same result.
Would someone know the solution?
Thank you.
Andreas.meyer@.dmc-group.de wrote:
> Hello,
> I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
> 2000. Both belong to the same domain and can see each other without
> problems. A mutual registration of the other sql server in Enterprise
> Manager is no problem.
> One Server's operating system is win2003Server, the other one's is
> Win2000Server.
> In both servers the domain account I use for logging in and for
> authentification purposes is a member of the server's local
> administrators group. And the local administrators have sysadmin rights
> in sql server 2000 via builtin\administrators.
> After some tests I added my windows domain account explicitly as
> sysadmin in both SQL-Servers.
> The security mode is "mixed" (SQL-Login and Windows).
> Trying to transfer a Test database with 1MB space fails. No matter
> whether I use a Transfer Database Task or whether I use the Copy
> Database Wizard (which also uses DTS in the background).
> The Transfer Database Task needs FIVE minutes for the dialog window
> "Transfer Database Properties" to reappear after clicking on the
> tab-card [file locations].
> Below the grid is a green check mark: Files are ready to be moved or
> copied.".
> Below that is a red X: Files on the source have the same name as on the
> destination or there is not enough free disk space on the destination.
> On both servers there is plenty of space (dozens of GB).
> In the end I only get the error description "Unspecified error".
> Doing the same with the Copy Database wizard" in the Grid beside the
> .mdf file and the .ldf file shows the STATUS: OK
> Below, again two error messages:
> Name conflict: Type a new name into the Destination Files fields
> Disk space: Change the destination or free disk space
> At the same time is shown the space needed and available:
> E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
> So I run the job in the wizard immediately and get the following
> errors:
> Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
> Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Login failed for user '(null)'. Reason: Not associated with a
> trusted SQL Server connection.
> Step Error code: 80004005
> Step Error Help File:SQLDMO80.hlp
> Step Error Help Context ID:700
> Apparently DTS uses odbc and can not establish the connection despite
> Sysadmin rights on both servers. I tried to specify the account sa of
> sql server for authentication instead of using windows authentication
> but that yields the same result.
> Would someone know the solution?
> Thank you.
>
Save yourself the headache and stop using these wizards for such a
simple task. Simply take a backup of the source database, then restore
that onto the destination server. The syntax for the BACKUP and RESTORE
commands is pretty simple, see Books Online for details.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Tracy,
of course, I did already.
However, there is so much fuss being made of what sql server 2000 can
do.
Also it is so much stressed in the exams: and dare you you wouldn't
know what the copy database wizard or DTS is able to to and that to use
it is so much easier and surely the best solution when asked for "the
least administrative effort", etc.
Then you try with one of the simplest tasks and in practice it just
fails in an environment where it is truly hard that it wouldn't work.
Yet it doesn't.
Nothing left any more of all the glory...
This is annoying.
Then again, I would like to know the reason for the error message in
odbc.
My impression is: a lot is just marketing but not working, really.
Sincerely
Andreas
Tracy McKibben schrieb:

> Andreas.meyer@.dmc-group.de wrote:
> Save yourself the headache and stop using these wizards for such a
> simple task. Simply take a backup of the source database, then restore
> that onto the destination server. The syntax for the BACKUP and RESTORE
> commands is pretty simple, see Books Online for details.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copy Database Wizard and DTS Transfer Database Task don't work

Hello,
I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
2000. Both belong to the same domain and can see each other without
problems. A mutual registration of the other sql server in Enterprise
Manager is no problem.
One Server's operating system is win2003Server, the other one's is
Win2000Server.
In both servers the domain account I use for logging in and for
authentification purposes is a member of the server's local
administrators group. And the local administrators have sysadmin rights
in sql server 2000 via builtin\administrators.
After some tests I added my windows domain account explicitly as
sysadmin in both SQL-Servers.
The security mode is "mixed" (SQL-Login and Windows).
Trying to transfer a Test database with 1MB space fails. No matter
whether I use a Transfer Database Task or whether I use the Copy
Database Wizard (which also uses DTS in the background).
The Transfer Database Task needs FIVE minutes for the dialog window
"Transfer Database Properties" to reappear after clicking on the
tab-card [file locations].
Below the grid is a green check mark: Files are ready to be moved or
copied.".
Below that is a red X: Files on the source have the same name as on the
destination or there is not enough free disk space on the destination.
On both servers there is plenty of space (dozens of GB).
In the end I only get the error description "Unspecified error".
Doing the same with the Copy Database wizard" in the Grid beside the
.mdf file and the .ldf file shows the STATUS: OK
Below, again two error messages:
Name conflict: Type a new name into the Destination Files fields
Disk space: Change the destination or free disk space
At the same time is shown the space needed and available:
E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
So I run the job in the wizard immediately and get the following
errors:
Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
Server]Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection.
Step Error code: 80004005
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:700
Apparently DTS uses odbc and can not establish the connection despite
Sysadmin rights on both servers. I tried to specify the account sa of
sql server for authentication instead of using windows authentication
but that yields the same result.
Would someone know the solution?
Thank you.Andreas.meyer@.dmc-group.de wrote:
> Hello,
> I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
> 2000. Both belong to the same domain and can see each other without
> problems. A mutual registration of the other sql server in Enterprise
> Manager is no problem.
> One Server's operating system is win2003Server, the other one's is
> Win2000Server.
> In both servers the domain account I use for logging in and for
> authentification purposes is a member of the server's local
> administrators group. And the local administrators have sysadmin rights
> in sql server 2000 via builtin\administrators.
> After some tests I added my windows domain account explicitly as
> sysadmin in both SQL-Servers.
> The security mode is "mixed" (SQL-Login and Windows).
> Trying to transfer a Test database with 1MB space fails. No matter
> whether I use a Transfer Database Task or whether I use the Copy
> Database Wizard (which also uses DTS in the background).
> The Transfer Database Task needs FIVE minutes for the dialog window
> "Transfer Database Properties" to reappear after clicking on the
> tab-card [file locations].
> Below the grid is a green check mark: Files are ready to be moved or
> copied.".
> Below that is a red X: Files on the source have the same name as on the
> destination or there is not enough free disk space on the destination.
> On both servers there is plenty of space (dozens of GB).
> In the end I only get the error description "Unspecified error".
> Doing the same with the Copy Database wizard" in the Grid beside the
> .mdf file and the .ldf file shows the STATUS: OK
> Below, again two error messages:
> Name conflict: Type a new name into the Destination Files fields
> Disk space: Change the destination or free disk space
> At the same time is shown the space needed and available:
> E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
> So I run the job in the wizard immediately and get the following
> errors:
> Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
> Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Login failed for user '(null)'. Reason: Not associated with a
> trusted SQL Server connection.
> Step Error code: 80004005
> Step Error Help File:SQLDMO80.hlp
> Step Error Help Context ID:700
> Apparently DTS uses odbc and can not establish the connection despite
> Sysadmin rights on both servers. I tried to specify the account sa of
> sql server for authentication instead of using windows authentication
> but that yields the same result.
> Would someone know the solution?
> Thank you.
>
Save yourself the headache and stop using these wizards for such a
simple task. Simply take a backup of the source database, then restore
that onto the destination server. The syntax for the BACKUP and RESTORE
commands is pretty simple, see Books Online for details.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
of course, I did already.
However, there is so much fuss being made of what sql server 2000 can
do.
Also it is so much stressed in the exams: and dare you you wouldn't
know what the copy database wizard or DTS is able to to and that to use
it is so much easier and surely the best solution when asked for "the
least administrative effort", etc.
Then you try with one of the simplest tasks and in practice it just
fails in an environment where it is truly hard that it wouldn't work.
Yet it doesn't.
Nothing left any more of all the glory...
This is annoying.
Then again, I would like to know the reason for the error message in
odbc.
My impression is: a lot is just marketing but not working, really.
Sincerely
Andreas
Tracy McKibben schrieb:

> Andreas.meyer@.dmc-group.de wrote:
> Save yourself the headache and stop using these wizards for such a
> simple task. Simply take a backup of the source database, then restore
> that onto the destination server. The syntax for the BACKUP and RESTORE
> commands is pretty simple, see Books Online for details.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copy Database Wizard and DTS Transfer Database Task don't work

Hello,
I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
2000. Both belong to the same domain and can see each other without
problems. A mutual registration of the other sql server in Enterprise
Manager is no problem.
One Server's operating system is win2003Server, the other one's is
Win2000Server.
In both servers the domain account I use for logging in and for
authentification purposes is a member of the server's local
administrators group. And the local administrators have sysadmin rights
in sql server 2000 via builtin\administrators.
After some tests I added my windows domain account explicitly as
sysadmin in both SQL-Servers.
The security mode is "mixed" (SQL-Login and Windows).
Trying to transfer a Test database with 1MB space fails. No matter
whether I use a Transfer Database Task or whether I use the Copy
Database Wizard (which also uses DTS in the background).
The Transfer Database Task needs FIVE minutes for the dialog window
"Transfer Database Properties" to reappear after clicking on the
tab-card [file locations].
Below the grid is a green check mark: Files are ready to be moved or
copied.".
Below that is a red X: Files on the source have the same name as on the
destination or there is not enough free disk space on the destination.
On both servers there is plenty of space (dozens of GB).
In the end I only get the error description "Unspecified error".
Doing the same with the Copy Database wizard" in the Grid beside the
.mdf file and the .ldf file shows the STATUS: OK
Below, again two error messages:
Name conflict: Type a new name into the Destination Files fields
Disk space: Change the destination or free disk space
At the same time is shown the space needed and available:
E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
So I run the job in the wizard immediately and get the following
errors:
Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
Server]Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection.
Step Error code: 80004005
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:700
Apparently DTS uses odbc and can not establish the connection despite
Sysadmin rights on both servers. I tried to specify the account sa of
sql server for authentication instead of using windows authentication
but that yields the same result.
Would someone know the solution?
Thank you.Andreas.meyer@.dmc-group.de wrote:
> Hello,
> I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
> 2000. Both belong to the same domain and can see each other without
> problems. A mutual registration of the other sql server in Enterprise
> Manager is no problem.
> One Server's operating system is win2003Server, the other one's is
> Win2000Server.
> In both servers the domain account I use for logging in and for
> authentification purposes is a member of the server's local
> administrators group. And the local administrators have sysadmin rights
> in sql server 2000 via builtin\administrators.
> After some tests I added my windows domain account explicitly as
> sysadmin in both SQL-Servers.
> The security mode is "mixed" (SQL-Login and Windows).
> Trying to transfer a Test database with 1MB space fails. No matter
> whether I use a Transfer Database Task or whether I use the Copy
> Database Wizard (which also uses DTS in the background).
> The Transfer Database Task needs FIVE minutes for the dialog window
> "Transfer Database Properties" to reappear after clicking on the
> tab-card [file locations].
> Below the grid is a green check mark: Files are ready to be moved or
> copied.".
> Below that is a red X: Files on the source have the same name as on the
> destination or there is not enough free disk space on the destination.
> On both servers there is plenty of space (dozens of GB).
> In the end I only get the error description "Unspecified error".
> Doing the same with the Copy Database wizard" in the Grid beside the
> .mdf file and the .ldf file shows the STATUS: OK
> Below, again two error messages:
> Name conflict: Type a new name into the Destination Files fields
> Disk space: Change the destination or free disk space
> At the same time is shown the space needed and available:
> E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
> So I run the job in the wizard immediately and get the following
> errors:
> Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
> Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Login failed for user '(null)'. Reason: Not associated with a
> trusted SQL Server connection.
> Step Error code: 80004005
> Step Error Help File:SQLDMO80.hlp
> Step Error Help Context ID:700
> Apparently DTS uses odbc and can not establish the connection despite
> Sysadmin rights on both servers. I tried to specify the account sa of
> sql server for authentication instead of using windows authentication
> but that yields the same result.
> Would someone know the solution?
> Thank you.
>
Save yourself the headache and stop using these wizards for such a
simple task. Simply take a backup of the source database, then restore
that onto the destination server. The syntax for the BACKUP and RESTORE
commands is pretty simple, see Books Online for details.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
of course, I did already.
However, there is so much fuss being made of what sql server 2000 can
do.
Also it is so much stressed in the exams: and dare you you wouldn't
know what the copy database wizard or DTS is able to to and that to use
it is so much easier and surely the best solution when asked for "the
least administrative effort", etc.
Then you try with one of the simplest tasks and in practice it just
fails in an environment where it is truly hard that it wouldn't work.
Yet it doesn't.
Nothing left any more of all the glory...
This is annoying.
Then again, I would like to know the reason for the error message in
odbc.
My impression is: a lot is just marketing but not working, really.
Sincerely
Andreas
Tracy McKibben schrieb:
> Andreas.meyer@.dmc-group.de wrote:
> > Hello,
> > I work with two SQL-Server 2000 computers. Both hav SP4 for SQL Server
> > 2000. Both belong to the same domain and can see each other without
> > problems. A mutual registration of the other sql server in Enterprise
> > Manager is no problem.
> > One Server's operating system is win2003Server, the other one's is
> > Win2000Server.
> > In both servers the domain account I use for logging in and for
> > authentification purposes is a member of the server's local
> > administrators group. And the local administrators have sysadmin rights
> > in sql server 2000 via builtin\administrators.
> > After some tests I added my windows domain account explicitly as
> > sysadmin in both SQL-Servers.
> > The security mode is "mixed" (SQL-Login and Windows).
> >
> > Trying to transfer a Test database with 1MB space fails. No matter
> > whether I use a Transfer Database Task or whether I use the Copy
> > Database Wizard (which also uses DTS in the background).
> > The Transfer Database Task needs FIVE minutes for the dialog window
> > "Transfer Database Properties" to reappear after clicking on the
> > tab-card [file locations].
> > Below the grid is a green check mark: Files are ready to be moved or
> > copied.".
> > Below that is a red X: Files on the source have the same name as on the
> > destination or there is not enough free disk space on the destination.
> >
> > On both servers there is plenty of space (dozens of GB).
> > In the end I only get the error description "Unspecified error".
> >
> > Doing the same with the Copy Database wizard" in the Grid beside the
> > .mdf file and the .ldf file shows the STATUS: OK
> > Below, again two error messages:
> >
> > Name conflict: Type a new name into the Destination Files fields
> > Disk space: Change the destination or free disk space
> >
> > At the same time is shown the space needed and available:
> > E: Available Space: 56956,00 MB, Required Space: 2,00 MB.
> >
> > So I run the job in the wizard immediately and get the following
> > errors:
> >
> > Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
> > Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL
> > Server]Login failed for user '(null)'. Reason: Not associated with a
> > trusted SQL Server connection.
> > Step Error code: 80004005
> > Step Error Help File:SQLDMO80.hlp
> > Step Error Help Context ID:700
> >
> > Apparently DTS uses odbc and can not establish the connection despite
> > Sysadmin rights on both servers. I tried to specify the account sa of
> > sql server for authentication instead of using windows authentication
> > but that yields the same result.
> > Would someone know the solution?
> >
> > Thank you.
> >
> Save yourself the headache and stop using these wizards for such a
> simple task. Simply take a backup of the source database, then restore
> that onto the destination server. The syntax for the BACKUP and RESTORE
> commands is pretty simple, see Books Online for details.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copy data from 2000 to 2005 using Express Edition

Hi all

I am using Windows XP Prof, SP2 and having Sql Server 2000, SP4 and SQL Server 2005 Express Edition with Advanced Services SP1, and SQL Server 2005 Express Edition Toolkit SP1 installed. The toolkit is the one which created me the DTS folder in the root with this applicattion:

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe

I start now this Wizard and choose from Sql server - which is my 2000 version and to my sql express version, id/pw or windows authentication. I receive then the message:

Cannot continue for the following reason:

1) You have chosen a server that does not contain any packages.

2) The server you have chosen is neither a sql server 2000 nor sql server 7

The SSIS Migration Wizard can only load packages from those servers.

Nice. I am having Sql server 2000 and want to go to 2005.

What I am doing from ? Do I need to create some packages on Sql server 2000 to be able to proceed this task ?

Regards

Chrs

hi,

while SQL Server 2005 SSIS supports for sure older SQL Server versions, probably the DTS wizard is not supported for SQL Server 2005 scenarios, as lot of changes has occured in the metadata of SQL Server's handling...

you have to migrate your data to SQLExpress "the hard way", that's to say without designer support..

you can so use INSERT ... SELECT statements to move data from a linked SQL Server 2000 server (http://msdn2.microsoft.com/en-us/library/ms190479.aspx) or the like...

regards

|||

I finaly removed the express edition and downloaded the trial version. Here all is working fine. I will get the developer edition to test my issues and to learn more about the server. I think the express edition is really only for users with a small db and only connecting with a application, but nothing to learn or really work with.

Regards

|||

You can also just simply detach your file from SQL 2000 and then attach it to SQL 2005. The file will automatically be upgraded and you can start using it.

Mike

copy a job

Hi,
I need to copy a job to the same istance of Microsoft SQL 2000 (SP4). I'd
like to rename it modify some parameters and re-schedule the database.
I can't find nothing for copy or duplicate the job.. any help? by Enterprise
Manager and Transact-SQL?
Thank's
AndreaRight-click the job, select All Tasks > Generate SQL script. Specify a
filename and click OK.
This generates a script which you can modify and then run to create a
new job.
David Portas
SQL Server MVP
--

copy a job

Hi,
I need to copy a job to the same istance of Microsoft SQL 2000 (SP4). I'd
like to rename it modify some parameters and re-schedule the database.
I can't find nothing for copy or duplicate the job.. any help? by Enterprise
Manager and Transact-SQL?
Thank's
Andrea
Right-click the job, select All Tasks > Generate SQL script. Specify a
filename and click OK.
This generates a script which you can modify and then run to create a
new job.
David Portas
SQL Server MVP

copy a job

Hi,
I need to copy a job to the same istance of Microsoft SQL 2000 (SP4). I'd
like to rename it modify some parameters and re-schedule the database.
I can't find nothing for copy or duplicate the job.. any help? by Enterprise
Manager and Transact-SQL?
Thank's
AndreaRight-click the job, select All Tasks > Generate SQL script. Specify a
filename and click OK.
This generates a script which you can modify and then run to create a
new job.
--
David Portas
SQL Server MVP
--