I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.
Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
Showing posts with label import. Show all posts
Showing posts with label import. Show all posts
Friday, March 30, 2012
help importing large flat file into relational tables
I am trying to find the best (fastest) way to import large text files into sql server 2000 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3 column unique index (alternate key) and a detail table that has a DETAIL_ID (IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.
Using SPs calls is not the fastest way. The quickest way is to generate files including the identity values and then bcp/.bulk insert the data into SQL. You could use SSIS to do this as well.
The reason you should generate you ID values outside of the database is so that when spliting your input file into your tables you can just insert the data straight into the tables. You can let SQL generate the PK identity values of the detail tables.
help importing large flat file into relational tables
I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp
1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a
3
> column unique index (alternate key) and a detail table that has a DETAIL_I
D
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition
Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp
1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a
3
> column unique index (alternate key) and a detail table that has a DETAIL_I
D
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition
Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
help importing large flat file into relational tables
I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Help Import Export Wizard SQL Server 2005
Hi! I have XP sp2, with SQL server 2005 Developer Edition 9.00.3054.00 and also have Microsoft SQL Server Express Edition 9.00.3042.00, well my problem alway I want import data into any server from any data source I recive this message:
TITLE: SQL Server Import and Export Wizard
The operation could not be completed.
ADDITIONAL INFORMATION:
Could not load file or assembly 'System.EnterpriseServices.Wrapper.dll' or one of its dependencies. The system cannot find the file specified. (System.EnterpriseServices)
Could not load file or assembly 'System.EnterpriseServices.Wrapper.dll' or one of its dependencies. The system cannot find the file specified.
The system cannot find the file specified. (Exception from HRESULT: 0x80070002)
BUTTONS:
OK
I have this name space but, I try for many ways and I can't import data in my server, What can I do?
Thanks a lot.
Do you have your Dev edition and Express edition of SQL Server installed on the same machine?
This could be some sort of side by side problem. Could you try to install only the dev edition and then attempt the data import?
Thanks,
Bob
|||Hi! I find the problem... Well I have install frameWork 1.1 2.0 and 3.0 but in Assemblie cache have System.EnterpriseServices.dll version 1.0 so... There are 2 solutions..
1.- Uninstall FrameWork 1.1 and reinstall 2.0
2.- Use gacutil and register System.EnterpriseServices.dll from version 2.0 <- this work for me.
Thans a lot for every one.
Friday, March 23, 2012
help copying databases
I am trying to import all of my production databases onto my development
laptop which is running the same version of SQL SQL 2000. I can not get the
imports to complete. I have all kinds of login already has an account, or
invalid object, or foreign constraint error, or...,
There has got to be a better way of creating a duplicate of my production
databases on my development desktop. Can someone tell me what I am doing
wrong. Ideally, I would "dump" my production server to my develpment server
on a regular basis to keep my .net apps using real data and current
procedures.
Any help would be appreciated.
Currently I am trying to just "import" the databases. I have tried with
database logins, without database logins, with sql user accounts, without
sql user accounts and it is not working.
Thanks,
FredDid u try the DTS Imports wizard?
You can also try to backup the production database and "restore as" in your
laptop. Did you try that option?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
> I am trying to import all of my production databases onto my development
> laptop which is running the same version of SQL SQL 2000. I can not get
the
> imports to complete. I have all kinds of login already has an account, or
> invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
server
> on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>|||I don't recommend using the wizard. Simply restore a full backup and
address the logins and you should be all set.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
>I am trying to import all of my production databases onto my development
>laptop which is running the same version of SQL SQL 2000. I can not get
>the imports to complete. I have all kinds of login already has an account,
>or invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
> server on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>sql
laptop which is running the same version of SQL SQL 2000. I can not get the
imports to complete. I have all kinds of login already has an account, or
invalid object, or foreign constraint error, or...,
There has got to be a better way of creating a duplicate of my production
databases on my development desktop. Can someone tell me what I am doing
wrong. Ideally, I would "dump" my production server to my develpment server
on a regular basis to keep my .net apps using real data and current
procedures.
Any help would be appreciated.
Currently I am trying to just "import" the databases. I have tried with
database logins, without database logins, with sql user accounts, without
sql user accounts and it is not working.
Thanks,
FredDid u try the DTS Imports wizard?
You can also try to backup the production database and "restore as" in your
laptop. Did you try that option?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
> I am trying to import all of my production databases onto my development
> laptop which is running the same version of SQL SQL 2000. I can not get
the
> imports to complete. I have all kinds of login already has an account, or
> invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
server
> on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>|||I don't recommend using the wizard. Simply restore a full backup and
address the logins and you should be all set.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
>I am trying to import all of my production databases onto my development
>laptop which is running the same version of SQL SQL 2000. I can not get
>the imports to complete. I have all kinds of login already has an account,
>or invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
> server on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>sql
help copying databases
I am trying to import all of my production databases onto my development
laptop which is running the same version of SQL SQL 2000. I can not get the
imports to complete. I have all kinds of login already has an account, or
invalid object, or foreign constraint error, or...,
There has got to be a better way of creating a duplicate of my production
databases on my development desktop. Can someone tell me what I am doing
wrong. Ideally, I would "dump" my production server to my develpment server
on a regular basis to keep my .net apps using real data and current
procedures.
Any help would be appreciated.
Currently I am trying to just "import" the databases. I have tried with
database logins, without database logins, with sql user accounts, without
sql user accounts and it is not working.
Thanks,
Fred
Did u try the DTS Imports wizard?
You can also try to backup the production database and "restore as" in your
laptop. Did you try that option?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
> I am trying to import all of my production databases onto my development
> laptop which is running the same version of SQL SQL 2000. I can not get
the
> imports to complete. I have all kinds of login already has an account, or
> invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
server
> on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>
|||I don't recommend using the wizard. Simply restore a full backup and
address the logins and you should be all set.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
>I am trying to import all of my production databases onto my development
>laptop which is running the same version of SQL SQL 2000. I can not get
>the imports to complete. I have all kinds of login already has an account,
>or invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
> server on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>
laptop which is running the same version of SQL SQL 2000. I can not get the
imports to complete. I have all kinds of login already has an account, or
invalid object, or foreign constraint error, or...,
There has got to be a better way of creating a duplicate of my production
databases on my development desktop. Can someone tell me what I am doing
wrong. Ideally, I would "dump" my production server to my develpment server
on a regular basis to keep my .net apps using real data and current
procedures.
Any help would be appreciated.
Currently I am trying to just "import" the databases. I have tried with
database logins, without database logins, with sql user accounts, without
sql user accounts and it is not working.
Thanks,
Fred
Did u try the DTS Imports wizard?
You can also try to backup the production database and "restore as" in your
laptop. Did you try that option?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
> I am trying to import all of my production databases onto my development
> laptop which is running the same version of SQL SQL 2000. I can not get
the
> imports to complete. I have all kinds of login already has an account, or
> invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
server
> on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>
|||I don't recommend using the wizard. Simply restore a full backup and
address the logins and you should be all set.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
>I am trying to import all of my production databases onto my development
>laptop which is running the same version of SQL SQL 2000. I can not get
>the imports to complete. I have all kinds of login already has an account,
>or invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
> server on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>
help copying databases
I am trying to import all of my production databases onto my development
laptop which is running the same version of SQL SQL 2000. I can not get the
imports to complete. I have all kinds of login already has an account, or
invalid object, or foreign constraint error, or...,
There has got to be a better way of creating a duplicate of my production
databases on my development desktop. Can someone tell me what I am doing
wrong. Ideally, I would "dump" my production server to my develpment server
on a regular basis to keep my .net apps using real data and current
procedures.
Any help would be appreciated.
Currently I am trying to just "import" the databases. I have tried with
database logins, without database logins, with sql user accounts, without
sql user accounts and it is not working.
Thanks,
FredDid u try the DTS Imports wizard?
You can also try to backup the production database and "restore as" in your
laptop. Did you try that option?
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
> I am trying to import all of my production databases onto my development
> laptop which is running the same version of SQL SQL 2000. I can not get
the
> imports to complete. I have all kinds of login already has an account, or
> invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
server
> on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>|||I don't recommend using the wizard. Simply restore a full backup and
address the logins and you should be all set.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
--
Andrew J. Kelly SQL MVP
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
>I am trying to import all of my production databases onto my development
>laptop which is running the same version of SQL SQL 2000. I can not get
>the imports to complete. I have all kinds of login already has an account,
>or invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
> server on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>
laptop which is running the same version of SQL SQL 2000. I can not get the
imports to complete. I have all kinds of login already has an account, or
invalid object, or foreign constraint error, or...,
There has got to be a better way of creating a duplicate of my production
databases on my development desktop. Can someone tell me what I am doing
wrong. Ideally, I would "dump" my production server to my develpment server
on a regular basis to keep my .net apps using real data and current
procedures.
Any help would be appreciated.
Currently I am trying to just "import" the databases. I have tried with
database logins, without database logins, with sql user accounts, without
sql user accounts and it is not working.
Thanks,
FredDid u try the DTS Imports wizard?
You can also try to backup the production database and "restore as" in your
laptop. Did you try that option?
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
> I am trying to import all of my production databases onto my development
> laptop which is running the same version of SQL SQL 2000. I can not get
the
> imports to complete. I have all kinds of login already has an account, or
> invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
server
> on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>|||I don't recommend using the wizard. Simply restore a full backup and
address the logins and you should be all set.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
--
Andrew J. Kelly SQL MVP
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uOMXO4tNFHA.3492@.TK2MSFTNGP09.phx.gbl...
>I am trying to import all of my production databases onto my development
>laptop which is running the same version of SQL SQL 2000. I can not get
>the imports to complete. I have all kinds of login already has an account,
>or invalid object, or foreign constraint error, or...,
> There has got to be a better way of creating a duplicate of my production
> databases on my development desktop. Can someone tell me what I am doing
> wrong. Ideally, I would "dump" my production server to my develpment
> server on a regular basis to keep my .net apps using real data and current
> procedures.
> Any help would be appreciated.
> Currently I am trying to just "import" the databases. I have tried with
> database logins, without database logins, with sql user accounts, without
> sql user accounts and it is not working.
> Thanks,
> Fred
>
Wednesday, March 21, 2012
Help bcp import reports row size too large error
Hi
I want to use bcp tool to export a table from one machine to another
machine. And now the export works fine. But when I want to import the
text file generated by the export process to the target database, bcp
reports an error, and the operation fails. The error is
"Cannot sort a row of size 22416, which is greater than the allowable
maximum of 8094."
I think this error happens because my target table row size is too
big. Because I can not change the schema of target table, I do not how
to resolve it. Anybody can help me? Thanks.Hi
Your row size of 22416 is far more than 8094, therefore I would expect that
the row terminator is not being specified correctly. What command did you use
to BCP out the file and how are you trying to load it?
Are the versions of SQL Server on each machine the same or different?
John
"Hailin.Cai@.gmail.com" wrote:
> Hi
> I want to use bcp tool to export a table from one machine to another
> machine. And now the export works fine. But when I want to import the
> text file generated by the export process to the target database, bcp
> reports an error, and the operation fails. The error is
> "Cannot sort a row of size 22416, which is greater than the allowable
> maximum of 8094."
> I think this error happens because my target table row size is too
> big. Because I can not change the schema of target table, I do not how
> to resolve it. Anybody can help me? Thanks.
>|||If you are using Microsoft's BCP utility to extract the data from the
source database, then I assume the source is SQL Server 2005 and is
using something like VARCHAR(MAX) to allow row size greater than the
normal limit.
If the target table can not be changed you have to decide what is the
proper thing to do with the rows that have the problem. Should they
be discarded? Should the problem column(s) be truncated?
Once you know how you want to deal with the problem, I suggest writing
a VIEW on the source system. The view should truncate the output (if
that is the choice), or skip the rows, whatever you have to do so the
output from the view matches the table on the target system. Then BCP
out from the view, rather than the table.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Sep 2007 16:29:51 -0700, Hailin.Cai@.gmail.com wrote:
>Hi
>I want to use bcp tool to export a table from one machine to another
>machine. And now the export works fine. But when I want to import the
>text file generated by the export process to the target database, bcp
>reports an error, and the operation fails. The error is
>"Cannot sort a row of size 22416, which is greater than the allowable
>maximum of 8094."
>I think this error happens because my target table row size is too
>big. Because I can not change the schema of target table, I do not how
>to resolve it. Anybody can help me? Thanks.|||On Sep 28, 9:42 am, "Roy Harvey (SQL Server MVP)"
<roy_har...@.snet.net> wrote:
> If you are using Microsoft's BCP utility to extract the data from the
> source database, then I assume the source is SQL Server 2005 and is
> using something like VARCHAR(MAX) to allow row size greater than the
> normal limit.
> If the target table can not be changed you have to decide what is the
> proper thing to do with the rows that have the problem. Should they
> be discarded? Should the problem column(s) be truncated?
> Once you know how you want to deal with the problem, I suggest writing
> a VIEW on the source system. The view should truncate the output (if
> that is the choice), or skip the rows, whatever you have to do so the
> output from the view matches the table on the target system. Then BCP
> out from the view, rather than the table.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Sep 2007 16:29:51 -0700, Hailin...@.gmail.com wrote:
> >Hi
> >I want to use bcp tool to export a table from one machine to another
> >machine. And now the export works fine. But when I want to import the
> >text file generated by the export process to the target database, bcp
> >reports an error, and the operation fails. The error is
> >"Cannot sort a row of size 22416, which is greater than the allowable
> >maximum of 8094."
> >I think this error happens because my target table row size is too
> >big. Because I can not change the schema of target table, I do not how
> >to resolve it. Anybody can help me? Thanks.- Hide quoted text -
> - Show quoted text -
Thanks for your suggestion. I will try.
I want to use bcp tool to export a table from one machine to another
machine. And now the export works fine. But when I want to import the
text file generated by the export process to the target database, bcp
reports an error, and the operation fails. The error is
"Cannot sort a row of size 22416, which is greater than the allowable
maximum of 8094."
I think this error happens because my target table row size is too
big. Because I can not change the schema of target table, I do not how
to resolve it. Anybody can help me? Thanks.Hi
Your row size of 22416 is far more than 8094, therefore I would expect that
the row terminator is not being specified correctly. What command did you use
to BCP out the file and how are you trying to load it?
Are the versions of SQL Server on each machine the same or different?
John
"Hailin.Cai@.gmail.com" wrote:
> Hi
> I want to use bcp tool to export a table from one machine to another
> machine. And now the export works fine. But when I want to import the
> text file generated by the export process to the target database, bcp
> reports an error, and the operation fails. The error is
> "Cannot sort a row of size 22416, which is greater than the allowable
> maximum of 8094."
> I think this error happens because my target table row size is too
> big. Because I can not change the schema of target table, I do not how
> to resolve it. Anybody can help me? Thanks.
>|||If you are using Microsoft's BCP utility to extract the data from the
source database, then I assume the source is SQL Server 2005 and is
using something like VARCHAR(MAX) to allow row size greater than the
normal limit.
If the target table can not be changed you have to decide what is the
proper thing to do with the rows that have the problem. Should they
be discarded? Should the problem column(s) be truncated?
Once you know how you want to deal with the problem, I suggest writing
a VIEW on the source system. The view should truncate the output (if
that is the choice), or skip the rows, whatever you have to do so the
output from the view matches the table on the target system. Then BCP
out from the view, rather than the table.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Sep 2007 16:29:51 -0700, Hailin.Cai@.gmail.com wrote:
>Hi
>I want to use bcp tool to export a table from one machine to another
>machine. And now the export works fine. But when I want to import the
>text file generated by the export process to the target database, bcp
>reports an error, and the operation fails. The error is
>"Cannot sort a row of size 22416, which is greater than the allowable
>maximum of 8094."
>I think this error happens because my target table row size is too
>big. Because I can not change the schema of target table, I do not how
>to resolve it. Anybody can help me? Thanks.|||On Sep 28, 9:42 am, "Roy Harvey (SQL Server MVP)"
<roy_har...@.snet.net> wrote:
> If you are using Microsoft's BCP utility to extract the data from the
> source database, then I assume the source is SQL Server 2005 and is
> using something like VARCHAR(MAX) to allow row size greater than the
> normal limit.
> If the target table can not be changed you have to decide what is the
> proper thing to do with the rows that have the problem. Should they
> be discarded? Should the problem column(s) be truncated?
> Once you know how you want to deal with the problem, I suggest writing
> a VIEW on the source system. The view should truncate the output (if
> that is the choice), or skip the rows, whatever you have to do so the
> output from the view matches the table on the target system. Then BCP
> out from the view, rather than the table.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Sep 2007 16:29:51 -0700, Hailin...@.gmail.com wrote:
> >Hi
> >I want to use bcp tool to export a table from one machine to another
> >machine. And now the export works fine. But when I want to import the
> >text file generated by the export process to the target database, bcp
> >reports an error, and the operation fails. The error is
> >"Cannot sort a row of size 22416, which is greater than the allowable
> >maximum of 8094."
> >I think this error happens because my target table row size is too
> >big. Because I can not change the schema of target table, I do not how
> >to resolve it. Anybody can help me? Thanks.- Hide quoted text -
> - Show quoted text -
Thanks for your suggestion. I will try.
Subscribe to:
Posts (Atom)