?
Solved

replacing truncated data, how?

Posted on 2010-01-12
19
Medium Priority
?
347 Views
Last Modified: 2013-11-30
Greetings all,

We just migrated data from Oracle database into SQL Server 2008.

However, since we were having problem using SQL Server dts for this migration, due to date, timestamp and other Oracle-SQL Server compatibility issues, we decided to use Oracle's SQL Developer tool to extract data from Oracle to .csv file and then to SQL Server.

Problem is that user just found out 3 weeks after conversion that some data was truncated.

There are a few comment fields with large character lenght that lost some data from Oracle to .csv file.

I am trying to fix this problem by just importing only those comment fields.

Is there a way, perhaps easier way to just add truncated data without having to delete entire table and reload data?

The user doesn't want to go this route because they have added so many new records since we went live.

Any ideas how to fix this?

Suddenly, my blood pressure has gone up due to this sudden discovery.

Thanks a lot in advance
0
Comment
Question by:nigerman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 3
19 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26297744
If you use sql server 2005 or higher, then try this with SSIS instead of dts, because ssis is much more powerful than dts.
for this you can create a ssis package
and add a dataflow task in it
in this dataflow add oracle as datasource and sql server as data destination. and you must only select data from oracle table that is not in sql server table.

you must have installed SQL server Integration service for working with SSIS packages
0
 

Author Comment

by:nigerman
ID: 26297875
Thanks very much for your prompt response.

This is where the issue is:

in this dataflow add oracle as datasource and sql server as data destination. and you must only select data from oracle table that is not in sql server table.

All data that is in Oracle is also in SQL Server. Only problem here is that the one in SQL Server is truncated. As a result, you see something like (this is just an example), My name is James Turi and I am...

The rest is truncated. Again that was an example.

Usually, the truncation starts after 255 character length is used up.

So, my desire here is to delete records in only the comment fields and replace them with complete data from the Oracle db but I am not sure the best approach.
0
 
LVL 30

Accepted Solution

by:
Reza Rad earned 150 total points
ID: 26298059
try this:
firstly , set length of this field in sql server as much as you want
then create a SSIS package to handle a update on this field for you.
just you need to read all data from oracle, and insert them in recordset destination
and after this , you can use a foreach loop ,
inside this use Execute SQL Task to update each row of sql server by equivalent value in recordset

does it make sense to you or not?
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

Author Comment

by:nigerman
ID: 26298143
yes, some sense. I will attempt your suggestion as I am about to be kicked out of office for the rest of the day.

Maybe in an hour, I will try and let you know.

Thanks for your assistance
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 150 total points
ID: 26299746
>>Usually, the truncation starts after 255 character length is used up.<<
Here is an explanation of the cause of your problem and a solution for it:
DataPump truncates delimited fields to 255 characters
http://www.sqldts.com/297.aspx
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26300497
Glad to help,
let me know if you have problem on this issue
0
 

Author Comment

by:nigerman
ID: 26303155
acperkins,

This is a nice piece but it addresses sql server 2000 or earlier.

I suppose it doesn't matter whether it is 2000 or 2008 and I am using sql server 2008 and still having the same issue.

Second, the article references a package.

I don't have a package. I am just trying to do a one time import with dts.

reza_rad,

I will bet you have some pretty good experience with SSIS package.

I spent half of last night trying to use a package to dump the data just like you had suggested.

However, I got stuck on the data transfer section.

Recall, that you have 2 options, one to copy tables from source to destination.

The other to use query to transfer.  Based on the article on how to use SSIS package to import data, I was instructed to use the Query option to transfer data.

When I got the box to formulate my query, I used the following:

SELECT * from [fstatus$]. The fstatus is the name of the excel file I was trying to import data from and I kept getting the following error message:

Sql statement is not a query.

I can use a text file if that is possible. I am just frustrated right now.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26303258
don't select use query to transfer.
try this way ( this is simpler):
select copy data from one or more table and then in next window select the sheet you want to import data from
you can then click on edit mapping and set mapping column to what column you want in sql server table as below image

let me know if you have problem

1.JPG
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26303453
>>This is a nice piece but it addresses sql server 2000 or earlier.<<
No, it addresses DTS, regardless of whether you are using 2000, 2005 or 2008.

>>I don't have a package. I am just trying to do a one time import with dts.<<
That does not make any difference.  It is still a DTS package.

But let me put it this way, either use:
1. DTS and implement the changes recommended in the link or
2. SSIS.

Pick your poison and we can help you from there.  I was merely addressing your original question as to why it was failing and providing a solution.
0
 

Author Comment

by:nigerman
ID: 26304009
Thanks a lot reza_rad.

I don't think the mapping of columns is the issue. The issue is that the string length is smaller than the data from the excel file.

I know you suggested yesterday that I set the size as I want on the DB and I have done that but somehow, when I go into the mapping screen, I don't see the size I set on the db.

acperkins, thanks also for your continued assistance.

See sql 2008 no longer supports DTS and as you can see from comments above with reza_rad, i am struggling to get a handle on this SSIS thing.
0
 

Author Comment

by:nigerman
ID: 26304070
Ok, some progress report:

using the SSIS package and your instructions reza_rad, I was able to transfer data.

However, I ran into one warning. Please see screenshot and let me know if you know what that means and how to resolve it. I can see the changes now in length increase. That doesn't appear to be an issue anymore.
warning.JPG
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26305284
>>See sql 2008 no longer supports DTS and as you can see from comments above with reza_rad, <<
Actually it does. But that is fine SSIS is a better option.
0
 

Author Comment

by:nigerman
ID: 26305822
Thanks again but do you have any idea why I am having the issue that I attached and how to resolve it?
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26306207
don't worry about that warning,
as it said clearly:
you have two columns that is not used in transfer data, you can remove them from transfer process if you don't need them at all.

if this doesn't make sense to you , provide a screen shot of your mapping columns window here
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26306261
>>I know you suggested yesterday that I set the size as I want on the DB and I have done that but somehow, when I go into the mapping screen, I don't see the size I set on the db

I suggest to you this:
after generating your ssis package with import/export wizard , save this package ( you can do this in last steps)
then open it in BIDS
in BIDS you can go through data flow task
select data source with advanced editor
and then select appropriate length for your columns in transfer operation
0
 

Author Comment

by:nigerman
ID: 26307065
Ok, thanks a lot for your help and patience.

I am going through this process. It is a bit slow for me but I am making some progress and will update you again soon.

Again, many thanks.
0
 

Author Comment

by:nigerman
ID: 26314488
I found this article that addressed the character issue I have been having:

http://support.microsoft.com/kb/q281517/

As soon as I followed it and changed the value for TypeGuessRows to 0, I am now able to import data from Excel into SQL Server successfully.

I basically imported the data into a temp table called statusTemp.

My hope now is to be able to replace the 4 comment fieldnames that have their data truncated with data from these newly imported 4 comment fieldnames.

Is there a way to do this?

I tried insert into status (field1,field2,field3,field4) select field1,field2,field3,field4 from statustemp.

Of course i am getting an error that some fieldnames cannot have null values.

Will update work? If yes, any ideas???

Thanks a lot
0
 

Author Comment

by:nigerman
ID: 26356337
I have figured this out.

Thanks
0
 

Author Closing Comment

by:nigerman
ID: 31676362
They provided good assistance but they didn't help me.

However, I am truly grateful for their assistance.

B is really not a bad grade and doesn't mean that I didn't appreciate their assistance. I did and I do.

Thanks a lot
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question