Why Does Moving Data Between Partitions Take So Long ?

Oct 17, 2007

I have a table with millions of rows and about 70 columns that move through a number of states (11 possible states in all) from "New" via various states to "Processed" and eventually to "Archive" (there's a complicated state diagram that I won't bore you with)


Movement between states is based on a heap of business logic including the move to Archive (not just dates).


Different sorts of processing (querying and update both by users and overnight processing) are carried out on the data according to its state.


Maintaining the indexes for optimum performance across the board is a headache.


We have two problems in that we want better query performance and want to be able to easily switch out objects that are in the Archive state.


I had in mind partitioning the table (and its indexes) on state so that :
(a) Queries would be directed only at the appropriate partition (that is always use "where state=" as part of the query)
(b) The Archive partition could be swapped out of the table periodically


In my test setup 10 of 11 partitions are in [PRIMARY] but Archive is in a different filegroup.


Query performance is OK - execution plans look good.


However my update performance is now appalling when moving between any two states (10 times as long as on the unpartitioned table).


I understand that when you update a column which is used as a partition key it will cause the row to "move from one partition to another" as it says in another post.


Fine - because that's exactly what I want - logically.


I can also understand that moving from one filegroup (and hence the underlying file) to another must mean that the data has to physically move.


However is the data physically moving whenever you move between partitions or what's going on to cause such a degradation in performance ?

View 6 Replies


ADVERTISEMENT

Data Moving Between Partitions

Mar 9, 2008

I have a requirement that I need to reload the last seven days worth of data each night to ensure that we pick up late arriving and updated records. To avoid having to do updates we delete the last seven days data and reload.

I was wondering if it is possible to set up the table as a partition, paritioned on a value (OLD, NEW) or similar.

The job would set the last day in the NEW partition to be old, the theory being that this would cause the rows to move to the OLD parition, and then truncate the new partion rather than deleting. The last seven days data could then be inserted into the empty new partition.

My questions is 1. Is my theory about the data moving from one partition to another correct. 2. Can I actually truncate and individual parition, 3. Do you think it will perform any quicker. We would expect data in the range of 100K to 500K rows in the seven days and will store up to 4 years of historical data.

Thanks for your thoughts

Stapsey

View 1 Replies View Related

Moving Partitions From One DB To Another

Jun 19, 2008

assuming that you have two databases, the OLTP db and the OLAP db (take not that both have the same structure -- archiving purposes)... using table partitioning, is there a way where we can move 1 partition from the OLTP db to the OLAP db???

i'm actually trying to use this example with both tables in the DB.. I tried to modify to use two databases but sql server is unable to move the partition...

ALTER TABLE [Production].[TransactionHistory]
SWITCH PARTITION 1
TO [Production].[TransactionHistoryArchive] PARTITION 2;

SlayerS_`BoxeR` + [ReD]NaDa

View 9 Replies View Related

Moving Indexes To Seperate Partitions

Jul 8, 1999

I have been asked to move the indexes on our membership database tables to seperate partitions on the server. This is a new concept to me and thought I could use some advice on how to go about doing it.

Thanks in advance.

Brad Keck

View 2 Replies View Related

Summarize Data Over Partitions

Oct 17, 2007

Hi champs!
The data i have is like this:

nr date value

------- -------------- --------
1 2007-10-03 45
1 2007-10-05 5
1 2007-10-11 -1
1 2007-10-30 23
2 2007-03-03 3
2 2007-03-13 -5
2 2007-03-03 6
3 2007-10-03 42
3 2007-10-03 11

.....

I want to summerize the value in each group and set the date to the 1'st og that month
i.e.
nr date value

------- -------------- --------
1 2007-10-01 72
2 2007-03-01 4
3 2007-10-01 53


any help is much appreciated.
thanks

View 3 Replies View Related

Can Data Partitions Be Used With Associative Tables?

Aug 1, 2006

First of all, we are using SQL Server 2005 with a SQL Mobile subscriber and we are attempting to use Data Partitions on our current database
schema which contains associative tables for many-to-many relationships.

We have two tables, a User table
and an Audit table. A user can be
assigned more than one Audit. An Audit
can be assigned to more than one User.
So an AuditUser associative table exists. If data partitions are used based on User,
then any Audits that are assigned to one or more users should be copied to the
proper partition for each User (the msmerge_current_partition_mappings table
with the proper partition_id values).



In order to insert records with such a schema, the following
steps occur in order:

Insert
new row into Audit table with new rowguidInsert
entry into AuditUser table associating the auditguid with every userguid that
is assigned this audit.



Merge replication triggers are fired on insert of the Audit
row and another one for the insert of the AuditUser row.



When the Audit row is inserted, the replication trigger follows
the following logic:

Inserts
a copy of that row into the msmerge_contents table. Evaluates
the row to determine which partition(s) this row should be copied to as
well (msmerge_current_partition_mappings table). To do this, it checks to see if the
AuditGuid is referenced in one or more AuditUser rows. Since we haven€™t inserted the AuditUser
row at this point, the trigger€™s logic doesn€™t find a partition to copy
this row to.



When the AuditUser row is inserted, the replication trigger performs
the same logic as with the Audit row, it:

Inserts
a copy of that row into the msmerge_contents table.Evaluates
the row to determine which partition(s) this row should be copied to as
well (msmerge_current_partition_mappings table). Since the row meets the criteria for one
or more partitions, it is copied to the msmerge_current_partition_mappings
table for each partition that exists.



When replication occurs, we see only the AuditUser rows
copied down to our device, and not the corresponding Audit rows. Now that we understand the triggers, it is
plain to see why. If the AuditUser row
could be inserted first, then the trigger on the Audit row would copy that row
into the proper partitions and all would work well. However, the Audit row must be inserted
first, so that foreign key relationship constraints are preserved.



It seems that the Update trigger on the AuditUser row
actually walks the relationships and copies any related child rows to the
msmerge_current_partition_mappings table.

View 3 Replies View Related

Master Data Services :: Table Partitions In MDS?

Apr 13, 2015

Perhaps this task is not for MDS.... But another tool for rapid development & startUp - we don't have. And nevertheless....

We created table managers_plan in MDS :

year
month
id_manager (domain attr)
POS (domain attr)
plan_sum_USD
plan_unit
----------------------------
Entities:
Managers ~ 800 records
POS  ~ 100 000 records

managers_plan Total records for
1 year = 100K x 12 = 1 200 000

managers_plan  - table partitions  
- Will bemade ?

View 3 Replies View Related

T-SQL (SS2K8) :: Calculating Running Totals For Partitions Of Data

Sep 23, 2015

I have table named #t1 and three columns. CODE, Column1, and Column2.

create table #t1 (
CODE NVARCHAR(20),
COLUMN1 NUMERIC(18,2),
COLUMN2 NUMERIC(18,2)
)

And i have some data:

INSERT INTO #t1 (CODE,COLUMN1,COLUMN2)
VALUES ('432', 0,100),
('TOTAL FOR 432',0,100),
('4320001',0,250),
('4320001',50,0),
('4320001',0,140),
('4320001',300,0),
('TOTAL FOR 4320001',350,390),
('432002',200,0),
('432002',0,100),
('TOTAL FOR 432002',200,100)
drop table #t1

I want to have 4 column (named BALANCE). Balance must be column that represent running totals between two columns (Column1 - Column2) for each group of data. For each group total must start from zero.after total 432 it starts to count again for total 4320001 and again for total 432002. I'm using MS SQL SERVER 2014.

View 9 Replies View Related

SQL Server 2008 :: Restoring Database - Delete Data From Partitions

Feb 20, 2015

I am restoring a database with 10yrs worth of data which have monthly partitions but i would like to keep only 5yrs of data after the restore is done, what is the best/faster approach to delete the 5yrs data without deleting the partitions as that may cause the db in accessible.

View 9 Replies View Related

Data Warehousing :: Will Creating Partitions On Table Increase Insert Speed

Oct 8, 2015

I have table having around 100 million rows.Everyday we have an ETL process in which table will be trucnated and relaoded. Will creating a partition on the table increase the inserting speed?

View 4 Replies View Related

Long Data Type

Apr 24, 2007

What is the equivalent to a long data type in SQL Server 2005?

View 3 Replies View Related

Data Type For MP3's And Long Text

Jul 11, 2001

Hello,
I would like to know as to what data type is the best if I want to store MP3's and large amount of text in a SQL server.
Please let me know about the data type for both the tasks.
The table for MP3 is different than the table for large text (eg . saving somebody's resume)
Please do let me know.

Thank you,

John

View 2 Replies View Related

ASP, SQL, Long Text Data Problems...

Jul 20, 2005

I have an ASP page that will take form info that a user has entered,then save it into SQL server, and retrive and display the info onanother page. My problem is with long text data (10,000 bytes ormore). It appears to save the long text data, as in it gives noerrors... but it does not save it. In the SQL table, the field isdefined as ntext... So why won't it save?Thanks in advance,adam

View 3 Replies View Related

How Long It Take To Finish Replicate Data

Nov 1, 2006

I have a database A include five tables, and have more than 1,500,000 rows. There is a replica database of A. First of all, there is no data in the two dbs. When I finish inserting data into A, the replica db seems still work, the log file size still changes. How can I know the replication finished or not? How long it will take to finish replicate 1,500,000 rows of data?

View 1 Replies View Related

Long Data Type In Sqlserver 2000

Jun 3, 2005

Dear friends,
I want to know that is the alternative data type of long in sqlserver
 
regards,
asad

View 1 Replies View Related

How To Edit Long Text Data In SQL Server

Jul 20, 2005

Hi Guys,I have editing a SQL Server table field that have long text data. I amupdating some text in this field. How can I update this field insteadof re-write all text. With the Select command its gives me completetext in one line and it hard to read it. Any idea. Thanks in AdvanceAdnan

View 1 Replies View Related

Check Null Value Of Long Data Type

Jul 20, 2005

Hi, I have a record set that is bound to a table in MS SQL Server. Onefiled in the table is bound to a "long" type member variable in theRecordSet. What will happen to the "long" variable when the field isNULL in the table?Thanks!-Yi

View 1 Replies View Related

Data Import / Truncation - Very Long Field

Dec 7, 2007

Hi,

I am having issues importing data from a text file into a SQL database table using the import wizard in SQL Server 2005.

The text file contains polygon coordinates and a polygon name and looks like this:

"42.2342342,-121.1351398|42.3467984752,-122.2349234, ... ..., 42.1897498174,-122.131983","Polygon Name 1"

"42.2342342,-121.1351398|42.3467984752,-122.2349234, ... ..., 42.1897498174,-122.131983","Polygon Name 2"

and the SQL table looks like this:

polycoordinates varbinary(MAX)
polygonname varchar(50)

The length of the longest polygon coordinates record is about 115,000 characters. I believe the varbinary(MAX) type should hold that data, but SQL throws a truncation error every time I try to import the data.

Any suggestions?

Thanks,
Jay

View 4 Replies View Related

Data Flow Integration Long To Start

May 24, 2007

Hello all,



I have an ETL process like that:

- Step 1: extract data from database DB2/AS400 to stage database SQL Server (with provider "ADO.NET ODBC")

- Step 2: extract data to stage database SQL Server to SQL Server

(With provider "Native OLE DBSQL Native Client")



During the step 2, if I have a table with a lot of lines (1 million or more), the data-flow takes a lot of time (some minutes) before to start extracting the data. That's quite frustrating and it affects the global time to integrate the data...



I don't know if SSIS scan the table before to integrate it. Moreover I'm almost sure that it was not like that some time ago (and I'm wondering what could have changed...)...

I just specify that the 2 SQL Server databases are on the same server.



Any idea is welcome.



Thanks,



Guillaume

View 1 Replies View Related

DTS Wizard Fails To Retrieve Long Data

May 13, 2007

We are trying to import data into SQL Server 2005 from MySQL 5.0.x using DTS Wizard via MySQLOLE DB Provider developed by Cherry City Software .

We can read BLOB data from MySQL via ADO 2.7 and ADO.NET 2.0. We can also use DTS Wizard to read the data on Preview. For the test, we defined a table as tBlob (lText VARCHAR(9000)) and inserted one record with a few characters. However, DTS Wizard always fails at the executing step with the following error messages:

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
(SQL Server Import and Export Wizard)

Error 0xc0208265: Data Flow Task: Failed to retrieve long data for column "lText".
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with output column "lText" (17) on output "OLE DB Source Output" (11). The column status returned was: "DBSTATUS_UNAVAILABLE".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: The "output column "lText" (17)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "lText" (17)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - tblob" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)


This could be a defect in the DTS Wizard. How do we workaround this problem? Any help will be greatly appreciated.

View 3 Replies View Related

Failed To Retrieve Long Data For Column

Mar 23, 2007

Hi,

We have a SSIS project where we load lot of image files using an OLE DB Source component and put it across the database as varbinary into OLEDB Destination component.

Things were fine until now where am getting an error like this. alongside the log also reads like,

There is not enough space on the disk. Long data was retrieved and can't be added to the data flow task buffer.

SSIS Error Code: DTS_E_InducedTransformFailureOnError.

Is this method of loading the files using an OleDb Souce not the efficient one ? If not is there a way I can get this done. Comments are highly appreciated!!

Thanks in Advance



View 8 Replies View Related

Any Way To View &&<Long Text&&> In Data Viewer

Feb 1, 2007

I was wondering if there is any way to view <Long Text> in the data viewer? I have a text data type, and when using the data viewer it just says "<Long Text>". That contains the data I actually want to watch... is there any way to get this?

View 1 Replies View Related

In SQL 2000 In TEXT Data Type How Much Long Is The Limit?

Apr 18, 2005

Hai Every one
      i am facing a werid problem it is related to storing a long text data in SQL 2000 the text data is some thing like the following
"dshjfsjlksdjakdjlksadjfeidkadflkdsajfieawirfjalkdfjsakdfjaiekdvnmckaumnmmmmmmmmmmmmmmmoadifdjsakdjfauiereoweiiiiiiiiiiiiiiiiiiiiiiiiiiidalfkjdsa,mlfdsdflvmsaldifsdjfskladfakdfjakladkalfkfadkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkadlkfjaidfajfkamcmmmmmmmmmmmmmmmmmmmmmmmmmmmmmiadlmfalierfmaerjeaiaelelllllllllllllllllllll..."
in short it is really long so i opted to go with TEXT data type as it states that it can store more than 8 kb but when i try to insert this data it gives me error stating that Text data type cannot be of length more than 128...........?
What  am i doing wrong........if Text is not the proper datatype to store such a data then can anyone suggest some thing better...............
Thanks in advance
Austin

View 1 Replies View Related

Moving Data

Oct 23, 2007

 I have 2 databases on one server that I want to consolidate into one database. I'm just learning SQL Server 2005. What is the easiest way to move my 3 tables from one database to a new one on the same server? Do I have use SSIS to do it, or can a simple query be written? I'm new so please be a little detailed in your answer. Thanks in advance for any comments. 

View 2 Replies View Related

Moving Data Between 6.5 And 7

Aug 3, 2000

I have a scenario where I need to refresh a database that is in 7.0 (converted from 6.5 database) from the original database. Is there an easy way to do this. I have tried creating a DTS package but the data never seems to make it accross.

View 1 Replies View Related

Moving Data From 6.5 To 7.0

Feb 16, 1999

Hello,

I'm having problems using the update wizard to move data from 6.5 sql server
(on another machine) to a 7.0 server sitting out a PDC. The wizard dies (and
passes me over to Dr Watson) when login fails for the 6.5 machine.

I am sure I have the right pasword (I've tried variations as well) and have updated the
hosts file so that the machine is known by it's name (I think one of the FAQ answers
suggested that)....

What other possibilities are there for moving the data ? I looked at bcp, but that seems
a rather long winded route (the data contains time stamps so I suppose there will be
a problem reading them in on the 7.0 side) ...

Many Thanks,

Paul.

View 1 Replies View Related

Moving Data From One DB To Another

Sep 4, 1998

Hi all!
What is the best way to move selected data from one database to another using SQL 6.5?

View 1 Replies View Related

Moving Data

Sep 6, 2001

what would be the best way to move 59 million rows from one table to another. The table has no constraint, but has has three indexes. The table has only four columns. It will be going from SQL 2000 to SQL 2000.

Thank You

View 2 Replies View Related

Long Running Transactions W/ Other Users Needing To Read Data

Jul 23, 2005

I have a very long transaction that runs on the same database thatother users need to use for existing data. I don't care if they seedata from the transaction before it is done and am only using thetransaction because I need a way to roll it back if any errors happenduring the transaction. Unfortunately all tables affected in the longrunning transaction are completely locked and nobody else can accessany of the affected tables while it is running. I am using thetransaction isolation level of read uncommitted, which from my limitedunderstanding of isolation levels is the least strict. What can I do toprevent this from happening?Below is the output from sp_who2 and sp_lock while the process isrunning and another process is being blocked by it.SPID Status LoginHostName BlkBy DBName Command CPUTimeDiskIO LastBatch ProgramName SPID----- ------------------------------------------------------------------------------ ---------- ----------------- ---------------- ------- ------ ------------------------------------------ -----1 BACKGROUND sa. . NULL LAZY WRITER 0 006/09 15:42:52 12 sleeping sa. . NULL LOG WRITER 10 006/09 15:42:52 23 BACKGROUND sa. . master SIGNAL HANDLER 0 006/09 15:42:52 34 BACKGROUND sa. . NULL LOCK MONITOR 0 006/09 15:42:52 45 BACKGROUND sa. . master TASK MANAGER 0 506/09 15:42:52 56 BACKGROUND sa. . master TASK MANAGER 0 006/09 15:42:52 67 sleeping sa. . NULL CHECKPOINT SLEEP 0 1206/09 15:42:52 78 BACKGROUND sa. . master TASK MANAGER 0 206/09 15:42:52 89 BACKGROUND sa. . master TASK MANAGER 0 006/09 15:42:52 910 BACKGROUND sa. . master TASK MANAGER 0 006/09 15:42:52 1011 BACKGROUND sa. . master TASK MANAGER 0 106/09 15:42:52 1112 BACKGROUND sa. . master TASK MANAGER 0 006/09 15:42:52 1251 sleeping SUPERPABLOAdministratorSUPERPABLO . PM AWAITING COMMAND 1813307 06/09 16:10:34 .Net SqlClient Data Provider 5152 sleeping SUPERPABLOAdministratorSUPERPABLO 54 PM SELECT 30 506/09 16:10:16 .Net SqlClient Data Provider 5253 RUNNABLE SUPERPABLOAdministratorSUPERPABLO . master SELECT 0 306/09 16:09:44 SQL Profiler 5354 RUNNABLE SUPERPABLOAdministratorSUPERPABLO . PM UPDATE 10095206 06/09 16:10:02 .Net SqlClient Data Provider 5456 RUNNABLE SUPERPABLOAdministratorSUPERPABLO . PM SELECT INTO 151 2706/09 16:10:33 SQL Query Analyzer 56(17 row(s) affected)spid dbid ObjId IndId Type Resource Mode Status------ ------ ----------- ------ ---- ---------------- -------- ------51 5 0 0 DB S GRANT52 5 0 0 DB S GRANT52 5 1117963059 4 PAG 1:7401 IS GRANT52 5 1117963059 4 KEY (5301214e6d62) S WAIT52 5 1117963059 0 TAB IS GRANT54 5 1117963059 0 TAB IX GRANT54 5 1852025829 0 TAB IX GRANT54 5 1181963287 3 PAG 1:9017 IX GRANT54 5 1117963059 4 KEY (5301934930a4) X GRANT54 5 1117963059 3 KEY (530187fc93f3) X GRANT54 5 1117963059 4 KEY (530154df71eb) X GRANT54 5 0 0 DB [BULK-OP-LOG] NULL GRANT54 5 0 0 FIL 2:0:d U GRANT54 5 1117963059 2 KEY (1d0096c50a7d) X GRANT54 5 1117963059 2 KEY (1b004a9a6158) X GRANT54 5 1117963059 2 KEY (1800a435d44a) X GRANT54 5 1181963287 6 PAG 1:8745 IX GRANT54 5 1181963287 4 PAG 1:8923 IX GRANT54 5 1181963287 2 PAG 1:8937 IX GRANT54 5 1117963059 4 KEY (5301112b0696) X GRANT54 5 0 0 PAG 1:10889 IX GRANT54 5 1181963287 5 PAG 1:8859 IX GRANT54 5 1181963287 6 PAG 1:10888 IX GRANT54 5 0 0 PAG 1:10891 IX GRANT54 5 0 0 PAG 1:10893 IX GRANT54 5 0 0 PAG 1:10892 IX GRANT54 5 0 0 PAG 1:10894 IX GRANT54 5 0 0 PAG 1:10882 IX GRANT54 5 1117963059 3 KEY (530135fbce35) X GRANT54 5 1117963059 0 RID 1:7387:57 X GRANT54 5 1117963059 0 RID 1:7387:59 X GRANT54 5 1117963059 0 RID 1:7387:61 X GRANT54 5 1117963059 3 KEY (5301406ad2bc) X GRANT54 5 1117963059 4 PAG 1:7401 IX GRANT54 5 0 0 PAG 1:7387 IX GRANT54 5 1117963059 2 PAG 1:7389 IX GRANT54 5 1117963059 3 PAG 1:7391 IX GRANT54 5 1117963059 0 RID 1:7387:10 X GRANT54 5 1117963059 0 RID 1:7387:56 X GRANT54 5 1117963059 0 RID 1:7387:58 X GRANT54 5 1117963059 0 RID 1:7387:60 X GRANT54 5 1117963059 3 KEY (530144afbed8) X GRANT54 5 1117963059 4 KEY (530115ee6af2) X GRANT54 5 1117963059 3 KEY (5301c6cd88ea) X GRANT54 5 1149963173 0 TAB IX GRANT54 5 1181963287 0 TAB X GRANT54 5 1117963059 4 KEY (5301d2782bbd) X GRANT54 5 1117963059 3 KEY (5301015bc9a5) X GRANT54 5 0 0 DB S GRANT54 5 0 0 DB [BULK-OP-DB] NULL GRANT54 5 1117963059 4 KEY (5301501a1d8f) X GRANT54 5 1117963059 2 KEY (1c00f3a2b6c5) X GRANT54 5 1117963059 2 KEY (1a002ffddde0) X GRANT54 5 0 0 PAG 1:7411 IX GRANT54 5 1117963059 2 KEY (1900c15268f2) X GRANT54 5 0 0 PAG 1:10840 IX GRANT54 5 1181963287 4 PAG 1:10841 IX GRANT54 5 0 0 PAG 1:10842 IX GRANT54 5 1117963059 3 KEY (5301059ea5c1) X GRANT54 5 0 0 PAG 1:10820 IX GRANT54 5 1181963287 4 PAG 1:10821 IX GRANT54 5 1181963287 5 PAG 1:10874 IX GRANT54 5 1181963287 5 PAG 1:10876 IX GRANT54 5 0 0 PAG 1:10877 IX GRANT54 5 1181963287 5 PAG 1:10878 IX GRANT54 5 0 0 PAG 1:10849 IX GRANT54 5 0 0 PAG 1:10850 IX GRANT54 5 1117963059 2 KEY (1700f225b712) X GRANT54 5 1117963059 4 KEY (5301214e6d62) X GRANT56 5 0 0 DB S GRANT56 1 85575343 0 TAB IS GRANT

View 29 Replies View Related

Bulk Insert Fails. Column Is Too Long In The Data File

Jun 27, 2006

Hi,

for testing purposes I'm inserting a flat file into a sql-server table using BULK INSERT unsig the following code:

BULK INSERT rsk_staging
FROM 'c: empulk
sk.txt'
  WITH (
    FIELDTERMINATOR = '',
    ROWTERMINATOR = '
',
    CODEPAGE = 'RAW',
    DATAFILETYPE  = 'char',
    BATCHSIZE = 100000,
    ROWS_PER_BATCH = 1925604,
    TABLOCK
  )

I have two versions of "rsk.txt" one with 1.9mill rows and one with the first 2000 rows only. The files have one column only with 115 characters that I'll split in to several columns later using SUBSTRING. The one with 2000 rows fires in to the database with no problems whatsoever using this exact code, the other one throws the following error:

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.

How can I resolve this problem?

EDIT: I tried several different row- and fieldterminators but this exact one works for the small data-file so I assume it should also work for the large one...the large one is however copyed directly using binary ftp from a unix-filesystem and the small one is manually copied into a new txt-file using UltraEdit.

View 1 Replies View Related

Moving Data From One Database To Another

Dec 5, 2006

Hi all,
I want to transfer all the data from one of my Ms Access tables to SQL Server table, using C#.
How can i do it?
Thanks in advance,
J.Jasmeeta.

View 6 Replies View Related

Moving Data From One Database To Another

Feb 2, 2007

Hi all,
                        I have an Ms Access table and a MsSql table. I am running a windows service in my localhost where the data from Ms access table will be copied to Ms sql table for every one minute. Before copying the data, the Ms sql table will be flushed inorder to avoid replicates.
                       Now i want to copy only the latest records updated within 1 min in Ms access table, to Ms sql table.
My Ms access table
Name        Id
jas            100
meena       101
viji             102
 
My Ms sql table
Name        Id
jas            100
meena       101
viji             102
 
After 1 min, say  2 records are added to my Ms access table like,
Name        Id
jas            100
meena       101
viji             102
bhuvana     103
pinky         104
Now i want to insert only the latest records from Ms access to Ms sql like,
Name        Id
jas            100
meena       101
viji             102
bhuvana     103
pinky         104
how to do this? thanx in advance.
Jasmeeta. 

View 4 Replies View Related

Moving Data Across Networks

Oct 10, 2007

I am wondering the best way to go about a task I have been assigned.  We have two similar websites but each is located on a different network.  One network is secure so it cannot be accessed on the normal WWW.  The secure network will contain the master database.  I need to write a program or do something with SQL server to retrieve all records from the WWW site and get them onto the secure database.  I also in the future will need to update records from the WWW site if they have been updated.  What is the easiest way to move data from one network to the other when I cannot connect to both databases simultaneously?
 Thanks,
Matt

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved