Enterprise Manager: defaults lost in import/export

Hi,
When I copy tables in a database from one server to another using
enterprise manager, everything copies ok, except for field defaults.

Has anyone seen this, and what is the solution?

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Dis...pict174830.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=586973steve (UseLinkToEmail@.dbForumz.com) writes:
> When I copy tables in a database from one server to another using
> enterprise manager, everything copies ok, except for field defaults.
> Has anyone seen this, and what is the solution?

No, I have not seen it. Then again, I never copy tables with Enterprise
Manager.

The way that metadata should be installed in my opinion are from scripts
kept under source control. Relying on tools that you don't know what they
do under the cover is not a reliable process.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog2" wrote:
> steve (UseLinkToEmail@.dbForumz.com) writes:
> > When I copy tables in a database from one server to another
> using
> > enterprise manager, everything copies ok, except for field
> defaults.
> > Has anyone seen this, and what is the solution?
> No, I have not seen it. Then again, I never copy tables with
> Enterprise
> Manager.
> The way that metadata should be installed in my opinion are
> from scripts
> kept under source control. Relying on tools that you don't
> know what they
> do under the cover is not a reliable process.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thanks, Erland

I am used to mysql where you just backup table defs and data, and
easily copy the stuff to a new server.

Since I have a ton of tables, I dont want to set up anything by hand.
Is there an easy and bulletproof way to transfer data and contents
(and views and stored procedures) from one sever to another?

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Dis...pict174830.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=588260|||steve (UseLinkToEmail@.dbForumz.com) writes:
> I am used to mysql where you just backup table def's and data, and
> easily copy the stuff to a new server.
> Since I have a ton of tables, I don't want to set up anything by hand.
> Is there an easy and bulletproof way to transfer data and contents
> (and views and stored procedures) from one sever to another?

If you want to transfer the entire database, just use BACKUP/RESTORE.
That's far more robust than scripting the lot. An alternative is
to use sp_detach_db/sp_attach_db.

The only time this is not possible is when you for some reason need to
change the collation. (Or the database is corrupt.) In this, if you don't
have scripts for your database under version control, you can script the
database from Enterprise Manager to det the definitions, and use BCP
to bulk data in and out. But relying on scripting is deceivable as you
have noticed. (I don't know what the Export/Import wizard does, but it
probably packages scripting and BCP:ing. I have never used it.)

If you want to move the metadata and data for some other reason, for
instance shipping from a development environment to a production
environment, then a version-control system is essential.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog2" wrote:
> steve (UseLinkToEmail@.dbForumz.com) writes:
> > I am used to mysql where you just backup table defs and data,
> and
> > easily copy the stuff to a new server.
> > Since I have a ton of tables, I dont want to set up anything
> by hand.
> > Is there an easy and bulletproof way to transfer data and
contents
> > (and views and stored procedures) from one sever to another?
> If you want to transfer the entire database, just use
BACKUP/RESTORE.
> Thats far more robust than scripting the lot. An alternative is
> to use sp_detach_db/sp_attach_db.
> The only time this is not possible is when you for some reason need
to
> change the collation. (Or the database is corrupt.) In this, if you
> dont
> have scripts for your database under version control, you can
script
> the
> database from Enterprise Manager to det the definitions, and use
BCP
> to bulk data in and out. But relying on scripting is deceivable as
you
> have noticed. (I dont know what the Export/Import wizard does,
> but it
> probably packages scripting and BCP:ing. I have never used it.)
> If you want to move the metadata and data for some other reason,
for
> instance shipping from a development environment to a production
> environment, then a version-control system is essential.

Hi Erland,

Backup/Restore is really good BUT cannot be used with a remotely
hosted server (if one does not have access to the file system, which I
dont). I am still at a loss for an easy solution.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Dis...pict174830.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=589880|||"steve" wrote:
> [quote:22bdd234c4="Erland Sommarskog2"]steve
> (UseLinkToEmail@.dbForumz.com) writes:
> > I am used to mysql where you just backup table def's and
> data, and
> > easily copy the stuff to a new server.
> > Since I have a ton of tables, I don't want to set up
> anything by hand.
> > Is there an easy and bulletproof way to transfer data and
> contents
> > (and views and stored procedures) from one sever to another?
> If you want to transfer the entire database, just use
> BACKUP/RESTORE.
> That's far more robust than scripting the lot. An alternative
> is
> to use sp_detach_db/sp_attach_db.
> The only time this is not possible is when you for some reason
> need to
> change the collation. (Or the database is corrupt.) In this,
> if you don't
> have scripts for your database under version control, you can
> script the
> database from Enterprise Manager to det the definitions, and
> use BCP
> to bulk data in and out. But relying on scripting is
> deceivable as you
> have noticed. (I don't know what the Export/Import wizard
> does, but it
> probably packages scripting and BCP:ing. I have never used
> it.)
> If you want to move the metadata and data for some other
> reason, for
> instance shipping from a development environment to a
> production
> environment, then a version-control system is essential.
> [/quote:22bdd234c4]
> Hi Erland,
> Backup/Restore is really good BUT cannot be used with a
> remotely hosted server (if one does not have access to the
> file system, which I don't). I am still at a loss for an easy
> solution.

I think I have figured out a strategy with Enter. Manager (EM) that
works.

First, I have EM create me a file which includes all the sql create
statements. Then I execute that on the server where I like to migrate
the db to. I can do that using Query Analyzer.

Now I have all the "metadata" properly set up. Next step is to copy
the records over using EM, which is easy.

So the key is to use the above process to create the tables FIRST, and
dont just copy tables over using EM.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Dis...pict174830.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=590002sql

Enterprise Manager (Import Data) - DTS Error

You must verify that SQL service is starting with a domain
ID on both SQL Servers and that whatever that Id is, it is
in the local administrators group of both server you are
getting and retriving data from.
Let me know how it goes...
Jay
>--Original Message--
>I created a new database in Enterprise Manager and then
attempted to import data into this new database from an
existing database via the "import data" option. This
launches the Data Transformation Services Import/Export
Wizard. I select the data source. I select the
destination. I select 'Copy table(s) and view(s) from
source database." I select all tables. I select the 'run
immediatedly' option. When I click the 'Finish' button, I
receive the following message in a message box
entitled 'Initialize Com:"
>Could not create an instance of the DTS Package.
>Can anyone tell me what may be wrong? This is SQL Server
2000, SP3.
>.
>
Jay,
The two databases are on the same server. One is named TablesSQL and the other is named TablesSQL_empty. Everything works fine except for the import or export data functions. I always get the "Cannot create an instance of the DTS Package" error. It ha
s worked in the past, but I have not used it in quite awhile and now every attempt produces this error.
"Jay" wrote:

> You must verify that SQL service is starting with a domain
> ID on both SQL Servers and that whatever that Id is, it is
> in the local administrators group of both server you are
> getting and retriving data from.
> Let me know how it goes...
> Jay
> attempted to import data into this new database from an
> existing database via the "import data" option. This
> launches the Data Transformation Services Import/Export
> Wizard. I select the data source. I select the
> destination. I select 'Copy table(s) and view(s) from
> source database." I select all tables. I select the 'run
> immediatedly' option. When I click the 'Finish' button, I
> receive the following message in a message box
> entitled 'Initialize Com:"
> 2000, SP3.
>

Enterprise Manager (Import Data) - DTS Error

I created a new database in Enterprise Manager and then attempted to import data into this new database from an existing database via the "import data" option. This launches the Data Transformation Services Import/Export Wizard. I select the data source
. I select the destination. I select 'Copy table(s) and view(s) from source database." I select all tables. I select the 'run immediatedly' option. When I click the 'Finish' button, I receive the following message in a message box entitled 'Initializ
e Com:"
Could not create an instance of the DTS Package.
Can anyone tell me what may be wrong? This is SQL Server 2000, SP3.
Problem solved. I installed SP3a and import/export works fine now.
"Ross Russell" wrote:

> I created a new database in Enterprise Manager and then attempted to import data into this new database from an existing database via the "import data" option. This launches the Data Transformation Services Import/Export Wizard. I select the data sour
ce. I select the destination. I select 'Copy table(s) and view(s) from source database." I select all tables. I select the 'run immediatedly' option. When I click the 'Finish' button, I receive the following message in a message box entitled 'Initial
ize Com:"
> Could not create an instance of the DTS Package.
> Can anyone tell me what may be wrong? This is SQL Server 2000, SP3.

Enterprise Manager (Import Data) - DTS Error

You must verify that SQL service is starting with a domain
ID on both SQL Servers and that whatever that Id is, it is
in the local administrators group of both server you are
getting and retriving data from.
Let me know how it goes...
Jay
>--Original Message--
>I created a new database in Enterprise Manager and then
attempted to import data into this new database from an
existing database via the "import data" option. This
launches the Data Transformation Services Import/Export
Wizard. I select the data source. I select the
destination. I select 'Copy table(s) and view(s) from
source database." I select all tables. I select the 'run
immediatedly' option. When I click the 'Finish' button, I
receive the following message in a message box
entitled 'Initialize Com:"
>Could not create an instance of the DTS Package.
>Can anyone tell me what may be wrong? This is SQL Server
2000, SP3.
>.
>Jay,
The two databases are on the same server. One is named TablesSQL and the ot
her is named TablesSQL_empty. Everything works fine except for the import o
r export data functions. I always get the "Cannot create an instance of the
DTS Package" error. It ha
s worked in the past, but I have not used it in quite awhile and now every a
ttempt produces this error.
"Jay" wrote:

> You must verify that SQL service is starting with a domain
> ID on both SQL Servers and that whatever that Id is, it is
> in the local administrators group of both server you are
> getting and retriving data from.
> Let me know how it goes...
> Jay
> attempted to import data into this new database from an
> existing database via the "import data" option. This
> launches the Data Transformation Services Import/Export
> Wizard. I select the data source. I select the
> destination. I select 'Copy table(s) and view(s) from
> source database." I select all tables. I select the 'run
> immediatedly' option. When I click the 'Finish' button, I
> receive the following message in a message box
> entitled 'Initialize Com:"
> 2000, SP3.
>

Enterprise Manager (Import Data) - DTS Error

I created a new database in Enterprise Manager and then attempted to import
data into this new database from an existing database via the "import data"
option. This launches the Data Transformation Services Import/Export Wizard
. I select the data source
. I select the destination. I select 'Copy table(s) and view(s) from sourc
e database." I select all tables. I select the 'run immediatedly' option.
When I click the 'Finish' button, I receive the following message in a mess
age box entitled 'Initializ
e Com:"
Could not create an instance of the DTS Package.
Can anyone tell me what may be wrong? This is SQL Server 2000, SP3.Problem solved. I installed SP3a and import/export works fine now.
"Ross Russell" wrote:

> I created a new database in Enterprise Manager and then attempted to import data i
nto this new database from an existing database via the "import data" option. This
launches the Data Transformation Services Import/Export Wizard. I select the data s
our
ce. I select the destination. I select 'Copy table(s) and view(s) from sou
rce database." I select all tables. I select the 'run immediatedly' option
. When I click the 'Finish' button, I receive the following message in a me
ssage box entitled 'Initial
ize Com:"
> Could not create an instance of the DTS Package.
> Can anyone tell me what may be wrong? This is SQL Server 2000, SP3.