Copy DB fails (at Create Package step) SQL 2005

Copying a db is an easy task under 2000, but 05...
New install of SQL05 on Windows03R2, the machine is a AD domain controller
(one and only in a test forest), SQL services run under an account with
Domain Admin and built-in\Administrators rights. All components (engine,
Integration services, etc) are installed to an E: volume and all services
run under the SQL-Services account I created. No errors anywhere, creating
a test db is fine.
Using the "Copy Database Wizard" fails at the "Create Package" step no
matter if "detach and attach" or "SQL mgnt object" method is used with the
same error (see below). Just a simple one table test db can not be copied
to the same server using the "copy db wizard" in SQL Server 2005.
It looks like the copy package can not be written to the MSDB. The create
package is step 3 of 5, step 1 (add log) and 2 (add task) work ok. I have
tried all kind of things - Giving the SQL-Services account db_owner rights
to MSDB (it should already have this from its sysadmin role and membership
in AD/Domain Admins built-in\admins). If there is something wrong with
using the "SQL Server Agent Service Account" as the Integration Services
Proxy account, I created security credentials and a Agent Proxy - but no
go - the wizard always fails at the create package step.
Please help, what am I missing?
===================================
No description found (Copy Database Wizard)
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveTo SqlServerAs(Package
package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword)
at
Microsoft.SqlServer.Management.CopyDatabaseWizard. PackageCreator.SavePackage()
A little more investigation revealed that SS Business Intelligence DS will
not save a package to the server (it gets a similar error – see below) and
the maintenance plan designer will not save a plan (it gets a "no description
found" error).
So there must be something seriously wrong with the SQL Server 2005
installation routine.
Maybe the installation failed (without error) because I at first installed
only some components to the E: drive and the rest of the components to the C:
drive. The installation screens are VERY poor in clearly describing what
will be installed where. So after I realized a bunch of components got
installed to C: and that the copy of Visual Studio that comes with SQL server
05 was installed to C: and that this would then FORCE the full version of VS
to C: I completely uninstalled SQL 2005.
I reinstalled all components to E: and then installed the full version of VS
(also to E – all without error.
It is likely that the SQL Server 2005 installation routine is fragile – and
it does not tell you about all errors it encounters. This is in complete
contrast to the SQL Server 2000 bullet proof installation routine.
I will do another complete uninstall of SQL 2005 and reinstall to see if
this helps.
Note, others have also reported this problem but not first with the copy db
wizard. Here is one:
http://forums.microsoft.com/MSDN/Sho...29472&SiteID=1
MikeC
Error from create SSIS package:
===================================
No description found (Microsoft Visual Studio)
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveTo SqlServerAs(Package
package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword)
at
Microsoft.DataTransformationServices.Controls.Pack ageLocationControl.SavePackage(Package package)
at
Microsoft.DataTransformationServices.Design.Contro ls.PackageSaveCopyForm.PackageSaveCopyForm_FormClo sing(Object sender, FormClosingEventArgs e)
"MikeC" wrote:

> Copying a db is an easy task under 2000, but 05...
> New install of SQL05 on Windows03R2, the machine is a AD domain controller
> (one and only in a test forest), SQL services run under an account with
> Domain Admin and built-in\Administrators rights. All components (engine,
> Integration services, etc) are installed to an E: volume and all services
> run under the SQL-Services account I created. No errors anywhere, creating
> a test db is fine.
> Using the "Copy Database Wizard" fails at the "Create Package" step no
> matter if "detach and attach" or "SQL mgnt object" method is used with the
> same error (see below). Just a simple one table test db can not be copied
> to the same server using the "copy db wizard" in SQL Server 2005.
> It looks like the copy package can not be written to the MSDB. The create
> package is step 3 of 5, step 1 (add log) and 2 (add task) work ok. I have
> tried all kind of things - Giving the SQL-Services account db_owner rights
> to MSDB (it should already have this from its sysadmin role and membership
> in AD/Domain Admins built-in\admins). If there is something wrong with
> using the "SQL Server Agent Service Account" as the Integration Services
> Proxy account, I created security credentials and a Agent Proxy - but no
> go - the wizard always fails at the create package step.
> Please help, what am I missing?
>
> ===================================
> No description found (Copy Database Wizard)
> --
> Program Location:
> at Microsoft.SqlServer.Dts.Runtime.Application.SaveTo SqlServerAs(Package
> package, IDTSEvents events, String packagePath, String serverName, String
> serverUserName, String serverPassword)
> at
> Microsoft.SqlServer.Management.CopyDatabaseWizard. PackageCreator.SavePackage()
>
>
|||I completely uninstalled SQL Server 2005 and all components - AGAIN.
This time, I deleted all remnants of SQL from the registry and file system -
SQL 2005 uninstall apparently is not as good as the SQL 2000 uninstall...
I reinstalled all components - and now saving packages works, including the
db copy wizard. Why? SQL 2005 install must have an intermittent bug or
something…
Now, have noticed a small bug with service status display, but it is minor.
When will we see SQL 2005 service pack one?
MikeC
|||The failure to create a package on Win03R2/SQL05 is back!
This time, SSIS package saving worked fine for a while. I copied a few
development dbs over to 2005, then went away and did some other work on
SharePoint Services setup.
The server was rebooted and I ran the SQL setup - only to the point of
looking at what was installed - oddly the complete list of installed
components in setup is un-checked. I was looking for the SQL Server Mobile
components - they are not installed and I did not install them.
Anyway the problem is back. So it it not really an install problem. More
trouble-shooting is in order - the absolutely minimal error information
provided does not make it easy.
Have you seen this problem? Help!
MikeC
|||It looks like reinstalling VS05pro after installing SQL05dev on Win03R2
CORRUPTS SQL Server 2005 Integration Services (a package cannot be saved to
server).
If I do the SQL05 install last, I avoid the problem. Now the question is -
what exactly gets corrupted and will this problem reappear when the VS SP1
comes out?
I think SQL Server 2000 was one of the best products that Microsoft created.
The SQL 2000 install was so simple and well behaved. Now, M$ has made SQL
Server into a monsters mess of DCOM linkages and registry bloat. Is it too
much to ask that M$ provide a simple install routine, one that displays a
manifest of files installed, and a list of registry changes? Maybe they are
ashamed of the mass of horrific registry db linkages put in to prop-up their
database “programming” model and want to pretend its not there…
MikeC
|||SQL05 creators - you must be joking - when you choose the Copy Database
""Wizard"" by right clicking on an existing db and then connect to another
server as a *source* you automatically select the local database as a copy
source **IN-ADDITION** to the db that a user needs to select on the remote
server.
In this case the remote db gets copied AND the local db gets copied - two
copies at the same time, so wacky! Just put in a copy db button to fix your
dangerous db copy interface.
MikeC
sqlsql

Copy DB fails (at Create Package step) SQL 2005

Copying a db is an easy task under 2000, but 05...
New install of SQL05 on Windows03R2, the machine is a AD domain controller
(one and only in a test forest), SQL services run under an account with
Domain Admin and built-in\Administrators rights. All components (engine,
Integration services, etc) are installed to an E: volume and all services
run under the SQL-Services account I created. No errors anywhere, creating
a test db is fine.
Using the "Copy Database Wizard" fails at the "Create Package" step no
matter if "detach and attach" or "SQL mgnt object" method is used with the
same error (see below). Just a simple one table test db can not be copied
to the same server using the "copy db wizard" in SQL Server 2005.
It looks like the copy package can not be written to the MSDB. The create
package is step 3 of 5, step 1 (add log) and 2 (add task) work ok. I have
tried all kind of things - Giving the SQL-Services account db_owner rights
to MSDB (it should already have this from its sysadmin role and membership
in AD/Domain Admins built-in\admins). If there is something wrong with
using the "SQL Server Agent Service Account" as the Integration Services
Proxy account, I created security credentials and a Agent Proxy - but no
go - the wizard always fails at the create package step.
Please help, what am I missing?
===================================
No description found (Copy Database Wizard)
--
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package
package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword)
at
Microsoft.SqlServer.Management.CopyDatabaseWizard.PackageCreator.SavePackage
()A little more investigation revealed that SS Business Intelligence DS will
not save a package to the server (it gets a similar error – see below) and
the maintenance plan designer will not save a plan (it gets a "no descriptio
n
found" error).
So there must be something seriously wrong with the SQL Server 2005
installation routine.
Maybe the installation failed (without error) because I at first installed
only some components to the E: drive and the rest of the components to the C
:
drive. The installation screens are VERY poor in clearly describing what
will be installed where. So after I realized a bunch of components got
installed to C: and that the copy of Visual Studio that comes with SQL serve
r
05 was installed to C: and that this would then FORCE the full version of VS
to C: I completely uninstalled SQL 2005.
I reinstalled all components to E: and then installed the full version of VS
(also to E – all without error.
It is likely that the SQL Server 2005 installation routine is fragile – an
d
it does not tell you about all errors it encounters. This is in complete
contrast to the SQL Server 2000 bullet proof installation routine.
I will do another complete uninstall of SQL 2005 and reinstall to see if
this helps.
Note, others have also reported this problem but not first with the copy db
wizard. Here is one:
http://forums.microsoft.com/MSDN/Sh...129472&SiteID=1
MikeC
Error from create SSIS package:
===================================
No description found (Microsoft Visual Studio)
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package
package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword)
at
Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePac
kage(Package package)
at
Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.Pac
kageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)
"MikeC" wrote:

> Copying a db is an easy task under 2000, but 05...
> New install of SQL05 on Windows03R2, the machine is a AD domain controller
> (one and only in a test forest), SQL services run under an account with
> Domain Admin and built-in\Administrators rights. All components (engine,
> Integration services, etc) are installed to an E: volume and all services
> run under the SQL-Services account I created. No errors anywhere, creatin
g
> a test db is fine.
> Using the "Copy Database Wizard" fails at the "Create Package" step no
> matter if "detach and attach" or "SQL mgnt object" method is used with the
> same error (see below). Just a simple one table test db can not be copied
> to the same server using the "copy db wizard" in SQL Server 2005.
> It looks like the copy package can not be written to the MSDB. The create
> package is step 3 of 5, step 1 (add log) and 2 (add task) work ok. I hav
e
> tried all kind of things - Giving the SQL-Services account db_owner rights
> to MSDB (it should already have this from its sysadmin role and membership
> in AD/Domain Admins built-in\admins). If there is something wrong with
> using the "SQL Server Agent Service Account" as the Integration Services
> Proxy account, I created security credentials and a Agent Proxy - but no
> go - the wizard always fails at the create package step.
> Please help, what am I missing?
>
> ===================================
> No description found (Copy Database Wizard)
> --
> Program Location:
> at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Packag
e
> package, IDTSEvents events, String packagePath, String serverName, String
> serverUserName, String serverPassword)
> at
> Microsoft.SqlServer.Management.CopyDatabaseWizard.PackageCreator.SavePacka
ge()
>
>|||I completely uninstalled SQL Server 2005 and all components - AGAIN.
This time, I deleted all remnants of SQL from the registry and file system -
SQL 2005 uninstall apparently is not as good as the SQL 2000 uninstall...
I reinstalled all components - and now saving packages works, including the
db copy wizard. Why? SQL 2005 install must have an intermittent bug or
something…
Now, have noticed a small bug with service status display, but it is minor.
When will we see SQL 2005 service pack one?
MikeC|||The failure to create a package on Win03R2/SQL05 is back!
This time, SSIS package saving worked fine for a while. I copied a few
development dbs over to 2005, then went away and did some other work on
SharePoint Services setup.
The server was rebooted and I ran the SQL setup - only to the point of
looking at what was installed - oddly the complete list of installed
components in setup is un-checked. I was looking for the SQL Server Mobile
components - they are not installed and I did not install them.
Anyway the problem is back. So it it not really an install problem. More
trouble-shooting is in order - the absolutely minimal error information
provided does not make it easy.
Have you seen this problem? Help!
MikeC|||It looks like reinstalling VS05pro after installing SQL05dev on Win03R2
CORRUPTS SQL Server 2005 Integration Services (a package cannot be saved to
server).
If I do the SQL05 install last, I avoid the problem. Now the question is -
what exactly gets corrupted and will this problem reappear when the VS SP1
comes out?
I think SQL Server 2000 was one of the best products that Microsoft created.
The SQL 2000 install was so simple and well behaved. Now, M$ has made SQL
Server into a monsters mess of DCOM linkages and registry bloat. Is it too
much to ask that M$ provide a simple install routine, one that displays a
manifest of files installed, and a list of registry changes? Maybe they are
ashamed of the mass of horrific registry db linkages put in to prop-up their
database “programming” model and want to pretend its not there…
MikeC|||SQL05 creators - you must be joking - when you choose the Copy Database
""Wizard"" by right clicking on an existing db and then connect to another
server as a *source* you automatically select the local database as a copy
source **IN-ADDITION** to the db that a user needs to select on the remote
server.
In this case the remote db gets copied AND the local db gets copied - two
copies at the same time, so wacky! Just put in a copy db button to fix your
dangerous db copy interface.
MikeC

Copy DB fails (at Create Package step) SQL 2005

Copying a db is an easy task under 2000, but 05...
New install of SQL05 on Windows03R2, the machine is a AD domain controller
(one and only in a test forest), SQL services run under an account with
Domain Admin and built-in\Administrators rights. All components (engine,
Integration services, etc) are installed to an E: volume and all services
run under the SQL-Services account I created. No errors anywhere, creating
a test db is fine.
Using the "Copy Database Wizard" fails at the "Create Package" step no
matter if "detach and attach" or "SQL mgnt object" method is used with the
same error (see below). Just a simple one table test db can not be copied
to the same server using the "copy db wizard" in SQL Server 2005.
It looks like the copy package can not be written to the MSDB. The create
package is step 3 of 5, step 1 (add log) and 2 (add task) work ok. I have
tried all kind of things - Giving the SQL-Services account db_owner rights
to MSDB (it should already have this from its sysadmin role and membership
in AD/Domain Admins built-in\admins). If there is something wrong with
using the "SQL Server Agent Service Account" as the Integration Services
Proxy account, I created security credentials and a Agent Proxy - but no
go - the wizard always fails at the create package step.
Please help, what am I missing?
=================================== No description found (Copy Database Wizard)
--
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package
package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword)
at
Microsoft.SqlServer.Management.CopyDatabaseWizard.PackageCreator.SavePackage()A little more investigation revealed that SS Business Intelligence DS will
not save a package to the server (it gets a similar error â' see below) and
the maintenance plan designer will not save a plan (it gets a "no description
found" error).
So there must be something seriously wrong with the SQL Server 2005
installation routine.
Maybe the installation failed (without error) because I at first installed
only some components to the E: drive and the rest of the components to the C:
drive. The installation screens are VERY poor in clearly describing what
will be installed where. So after I realized a bunch of components got
installed to C: and that the copy of Visual Studio that comes with SQL server
05 was installed to C: and that this would then FORCE the full version of VS
to C: I completely uninstalled SQL 2005.
I reinstalled all components to E: and then installed the full version of VS
(also to E:) â' all without error.
It is likely that the SQL Server 2005 installation routine is fragile â' and
it does not tell you about all errors it encounters. This is in complete
contrast to the SQL Server 2000 bullet proof installation routine.
I will do another complete uninstall of SQL 2005 and reinstall to see if
this helps.
Note, others have also reported this problem but not first with the copy db
wizard. Here is one:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=129472&SiteID=1
MikeC
Error from create SSIS package:
===================================
No description found (Microsoft Visual Studio)
--
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package
package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword)
at
Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePackage(Package package)
at
Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.PackageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)
"MikeC" wrote:
> Copying a db is an easy task under 2000, but 05...
> New install of SQL05 on Windows03R2, the machine is a AD domain controller
> (one and only in a test forest), SQL services run under an account with
> Domain Admin and built-in\Administrators rights. All components (engine,
> Integration services, etc) are installed to an E: volume and all services
> run under the SQL-Services account I created. No errors anywhere, creating
> a test db is fine.
> Using the "Copy Database Wizard" fails at the "Create Package" step no
> matter if "detach and attach" or "SQL mgnt object" method is used with the
> same error (see below). Just a simple one table test db can not be copied
> to the same server using the "copy db wizard" in SQL Server 2005.
> It looks like the copy package can not be written to the MSDB. The create
> package is step 3 of 5, step 1 (add log) and 2 (add task) work ok. I have
> tried all kind of things - Giving the SQL-Services account db_owner rights
> to MSDB (it should already have this from its sysadmin role and membership
> in AD/Domain Admins built-in\admins). If there is something wrong with
> using the "SQL Server Agent Service Account" as the Integration Services
> Proxy account, I created security credentials and a Agent Proxy - but no
> go - the wizard always fails at the create package step.
> Please help, what am I missing?
>
> ===================================> No description found (Copy Database Wizard)
> --
> Program Location:
> at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package
> package, IDTSEvents events, String packagePath, String serverName, String
> serverUserName, String serverPassword)
> at
> Microsoft.SqlServer.Management.CopyDatabaseWizard.PackageCreator.SavePackage()
>
>|||I completely uninstalled SQL Server 2005 and all components - AGAIN.
This time, I deleted all remnants of SQL from the registry and file system -
SQL 2005 uninstall apparently is not as good as the SQL 2000 uninstall...
I reinstalled all components - and now saving packages works, including the
db copy wizard. Why? SQL 2005 install must have an intermittent bug or
somethingâ?¦
Now, have noticed a small bug with service status display, but it is minor.
When will we see SQL 2005 service pack one?
MikeC|||The failure to create a package on Win03R2/SQL05 is back!
This time, SSIS package saving worked fine for a while. I copied a few
development dbs over to 2005, then went away and did some other work on
SharePoint Services setup.
The server was rebooted and I ran the SQL setup - only to the point of
looking at what was installed - oddly the complete list of installed
components in setup is un-checked. I was looking for the SQL Server Mobile
components - they are not installed and I did not install them.
Anyway the problem is back. So it it not really an install problem. More
trouble-shooting is in order - the absolutely minimal error information
provided does not make it easy.
Have you seen this problem? Help!
MikeC|||It looks like reinstalling VS05pro after installing SQL05dev on Win03R2
CORRUPTS SQL Server 2005 Integration Services (a package cannot be saved to
server).
If I do the SQL05 install last, I avoid the problem. Now the question is -
what exactly gets corrupted and will this problem reappear when the VS SP1
comes out?
I think SQL Server 2000 was one of the best products that Microsoft created.
The SQL 2000 install was so simple and well behaved. Now, M$ has made SQL
Server into a monsters mess of DCOM linkages and registry bloat. Is it too
much to ask that M$ provide a simple install routine, one that displays a
manifest of files installed, and a list of registry changes? Maybe they are
ashamed of the mass of horrific registry db linkages put in to prop-up their
database â'programmingâ' model and want to pretend its not thereâ?¦
MikeC|||SQL05 creators - you must be joking - when you choose the Copy Database
""Wizard"" by right clicking on an existing db and then connect to another
server as a *source* you automatically select the local database as a copy
source **IN-ADDITION** to the db that a user needs to select on the remote
server.
In this case the remote db gets copied AND the local db gets copied - two
copies at the same time, so wacky! Just put in a copy db button to fix your
dangerous db copy interface.
MikeC

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 Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

Hello

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.Options.Default = true;
xfr.Options.Indexes = true;
xfr.Options.DriDefaults = true;
xfr.Options.DriAllKeys = true;
xfr.Options.DriForeignKeys = true;
xfr.Options.DriIndexes = true;
xfr.Options.DriPrimaryKey = true;
xfr.Options.DriUniqueKeys = true;
xfr.CopyAllDefaults = true;
xfr.DestinationDatabase = "db2";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.CopyData = true;

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

This is a known issue with TransferData(), which is slated to be fixed in service pack 1. ScriptTransfer generates the correct T-SQL for identity columns. If you are only moving schema, then ScriptTransfer() should meet your needs.

Peter

|||Peter,

This explains why my backups aren't correct, but I still have another problem. I also need the database created with transfer to have the foreign keys copied.

What do you suggest?

Chris
|||

You can accomplish this by setting the following property:

xfr.Options.DriForeignKeys = true;

However, you might want to just specify:

xfr.Options.DriAllKeys = true;

Peter Saddow

|||Thanks!
|||

Hello

Has it really been fixed in SP1?

We have running Sql Server's on Version 9.0.2047.
I try to use Smo.Transfer to ship selected tables and data between the servers.

Beside NoIdentities I tried a lot of the option settings.
But I never get the identity property to 'Yes' on the target system.

The transfer.ScriptTransfer(); shows the correct script e.g. 'CREATE TABLE [dbo].[tableName] (colA Int IDENTITY(1,1) ...)
But logging the DataTransferEventArgs.Message shows that transfer.TransferData(); ignores the identity while creating the table.

Any advice?

|||i worked fine with me...when i installed the SP1 the TransferData() transfered tables with its identities

Copy Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

Hello

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.Options.Default = true;
xfr.Options.Indexes = true;
xfr.Options.DriDefaults = true;
xfr.Options.DriAllKeys = true;
xfr.Options.DriForeignKeys = true;
xfr.Options.DriIndexes = true;
xfr.Options.DriPrimaryKey = true;
xfr.Options.DriUniqueKeys = true;
xfr.CopyAllDefaults = true;
xfr.DestinationDatabase = "db2";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.CopyData = true;

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

This is a known issue with TransferData(), which is slated to be fixed in service pack 1. ScriptTransfer generates the correct T-SQL for identity columns. If you are only moving schema, then ScriptTransfer() should meet your needs.

Peter

|||Peter,

This explains why my backups aren't correct, but I still have another problem. I also need the database created with transfer to have the foreign keys copied.

What do you suggest?

Chris|||

You can accomplish this by setting the following property:

xfr.Options.DriForeignKeys = true;

However, you might want to just specify:

xfr.Options.DriAllKeys = true;

Peter Saddow

|||Thanks!|||

Hello

Has it really been fixed in SP1?

We have running Sql Server's on Version 9.0.2047.
I try to use Smo.Transfer to ship selected tables and data between the servers.

Beside NoIdentities I tried a lot of the option settings.
But I never get the identity property to 'Yes' on the target system.

The transfer.ScriptTransfer(); shows the correct script e.g. 'CREATE TABLE [dbo].[tableName] (colA Int IDENTITY(1,1) ...)
But logging the DataTransferEventArgs.Message shows that transfer.TransferData(); ignores the identity while creating the table.

Any advice?

|||i worked fine with me...when i installed the SP1 the TransferData() transfered tables with its identities

Copy Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

Hello

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.Options.Default = true;
xfr.Options.Indexes = true;
xfr.Options.DriDefaults = true;
xfr.Options.DriAllKeys = true;
xfr.Options.DriForeignKeys = true;
xfr.Options.DriIndexes = true;
xfr.Options.DriPrimaryKey = true;
xfr.Options.DriUniqueKeys = true;
xfr.CopyAllDefaults = true;
xfr.DestinationDatabase = "db2";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.CopyData = true;

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

This is a known issue with TransferData(), which is slated to be fixed in service pack 1. ScriptTransfer generates the correct T-SQL for identity columns. If you are only moving schema, then ScriptTransfer() should meet your needs.

Peter

|||Peter,

This explains why my backups aren't correct, but I still have another problem. I also need the database created with transfer to have the foreign keys copied.

What do you suggest?

Chris|||

You can accomplish this by setting the following property:

xfr.Options.DriForeignKeys = true;

However, you might want to just specify:

xfr.Options.DriAllKeys = true;

Peter Saddow

|||Thanks!|||

Hello

Has it really been fixed in SP1?

We have running Sql Server's on Version 9.0.2047.
I try to use Smo.Transfer to ship selected tables and data between the servers.

Beside NoIdentities I tried a lot of the option settings.
But I never get the identity property to 'Yes' on the target system.

The transfer.ScriptTransfer(); shows the correct script e.g. 'CREATE TABLE [dbo].[tableName] (colA Int IDENTITY(1,1) ...)
But logging the DataTransferEventArgs.Message shows that transfer.TransferData(); ignores the identity while creating the table.

Any advice?

|||i worked fine with me...when i installed the SP1 the TransferData() transfered tables with its identities

Copy Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.WithDependencies = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.DestinationDatabase = "Clarina_N";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.TransferData();

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

And same problem with defaults even when
xfr.Option.Default = true;

|||This has nothing to do with SSIS. You should try posting to the SMO forum.

Thanks,
Mattsqlsql

Copy Database with Microsoft.SqlServer.Management.Smo.Transfer breaks identity columns

Hello

I have a task to copy at runtime "etalon" database inside one same SQL 2005 server. Everythings ok except identity fields: identity breaks in new database.

I use such code:

Transfer xfr = new Transfer(db);
xfr.CopyAllObjects = true;
xfr.Options.ContinueScriptingOnError = true;
xfr.Options.NoIdentities = false;
xfr.Options.NoCollation = true;
xfr.Options.Default = true;
xfr.Options.Indexes = true;
xfr.Options.DriDefaults = true;
xfr.Options.DriAllKeys = true;
xfr.Options.DriForeignKeys = true;
xfr.Options.DriIndexes = true;
xfr.Options.DriPrimaryKey = true;
xfr.Options.DriUniqueKeys = true;
xfr.CopyAllDefaults = true;
xfr.DestinationDatabase = "db2";
xfr.DestinationServer = srv.Name;
xfr.DestinationLoginSecure = true;
xfr.CopySchema = true;
xfr.CopyAllUsers = true;
xfr.CopyData = true;

when I try to create just script by xfr.ScriptTransfer() I see correct sql with IDENTITY.

Thanks for help

Vladislav

This is a known issue with TransferData(), which is slated to be fixed in service pack 1. ScriptTransfer generates the correct T-SQL for identity columns. If you are only moving schema, then ScriptTransfer() should meet your needs.

Peter

|||Peter,

This explains why my backups aren't correct, but I still have another problem. I also need the database created with transfer to have the foreign keys copied.

What do you suggest?

Chris|||

You can accomplish this by setting the following property:

xfr.Options.DriForeignKeys = true;

However, you might want to just specify:

xfr.Options.DriAllKeys = true;

Peter Saddow

|||Thanks!|||

Hello

Has it really been fixed in SP1?

We have running Sql Server's on Version 9.0.2047.
I try to use Smo.Transfer to ship selected tables and data between the servers.

Beside NoIdentities I tried a lot of the option settings.
But I never get the identity property to 'Yes' on the target system.

The transfer.ScriptTransfer(); shows the correct script e.g. 'CREATE TABLE [dbo].[tableName] (colA Int IDENTITY(1,1) ...)
But logging the DataTransferEventArgs.Message shows that transfer.TransferData(); ignores the identity while creating the table.

Any advice?

|||i worked fine with me...when i installed the SP1 the TransferData() transfered tables with its identities

Copy All Tables from Server A to Server B

I haven't had any luck using the Transfer SQL Server Objects task and from what I have found in researching the issues I ran into, no else has. I have found the Roll Your Own article but being non-educated in SMO and VB.Net (I know some VB but nothing about C#), I really need an example of a server to server copy/transfer.

Any help would be greatly appreciated.

Did you take a look at this link? It has VB code for copying a database: http://blogs.msdn.com/mattm/archive/2007/04/18/roll-your-own-transfer-sql-server-objects-task.aspx. To copy server to server, you'd just need to modify it to include a source and destination server.|||I know this will sound lame, but that's my problem. I haven't figured out how to add a source and destination server. I've never worked with this before and I haven't found any examples.

copy a task error

Have you ever gotten an error when copying a task in the designer? I can't copy a task. That's going to be a pain for developing big packages. I have already uninstalled BIDS and installed Visual Studio and still get the error. Any thoughts on what could I could remove and reinstall to get a working environment? Would removing VS and SQL 2005 and all its components work? I ran some of the CTP cleanup tools (ttool and msiinv) and they reported no problems and no beta software

TITLE: Microsoft Visual Studio

An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects.

ADDITIONAL INFORMATION:

Could not copy object 'info Script Task' to the clipboard.
(Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=SerializeComponentsFailed&LinkId=20476

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

BUTTONS:

OK

I posted here the same question in the VS Installation and Setup forum http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=26&SiteID=1, but I think this forum may be more on target.

The problem was lingering beta code. Which is weird since I looked at CTPs on an older machine and can't remember installing any beta code on this new machine.

I resolved the problem by uninstalling Visual Studio, SQL components and finally SQL 2005. I ran cleanup tools, ttool.exe, vs_uninst.exe, vs_uninst_betas.exe, and vs2005_beta_cleanup_tool, and it was vs_uninst_betas.exe that did the job.

Next time, I'll only run the CTP on a virtual machine. Heck, CTPs/betas should be released in virtual PC or VMware. Then testers will just need a vm player.

Converting negative integer from string to int

I'm having an issue using the data conversion task and trying to convert from a negative integer that is bounded by brackets e.g, (1) for -1. I keep getting an error that the value cannot be converted although I've tried all signed integer types.

Yeah that doesn't surprise me. You'll be able to do this with a Derived Column component. *I think* the following should do it (assuming the column is called yourCol:

(DT_I4)SUBSTRING(yourCol, 2, 1)

-Jamie

|||

If there are positive numbers in that column as well, you may need to place a conditional split before the derived column; and then, as Jamie suggested, use an expression (in a derived column transform) to remove the brackets and to multiply the resulting number by -1 so you get the negative sign.

|||

A Derived Column Tx would be my preferred option, as this could be done in a single transform. There is no need for the split either. The following expression should do the trick. It avoids the need for any conditional processing, are we +ve or -ve type stuff. The only thing I have not catered for is NULLs.

(DT_I4)REPLACE(REPLACE([Column], "(", "-"), ")", "")

|||Thanks Darren, I'm going to give that a try. It's bizarre because I have a DTS in SQL 2000 and it handles this issue without any problem or conversion issues.|||

whatthedilly wrote:

Thanks Darren, I'm going to give that a try. It's bizarre because I have a DTS in SQL 2000 and it handles this issue without any problem or conversion issues.

The fact that DTS does this is not a good thing. It is a very bad thing and SSIS was a conscious effort to move away from DTS's technique of effectively guessing how it should treat values. With SSIS you have to explicitly tell it how to treat values. You are in control. This removes the possibility of the engine making the wrong guess - as DTS was wont to do.

-Jamie