Oracle LONG to SQL TEXT - Help?
-
I have an Oracle Database I have to migrate over to SQL Server. I have approximately 450 tables total. Using DTS, I have moved all but 3 of these tables. The three I have not moved include LONG columns, which are over 8000 bytes in length. I have read a bit on the subject and found that I am required by SQL Server to use the TEXT datatype for these columns. From what I have found, TEXT is only a 16 bit field that will hold a pointer. I am assuming that this pointer will be something to the effect of:
C:\blah\datafile (please correct me if I am wrong)
The overall problem that I am having is how to get my Oracle table, with the LONG columns over to SQL Server in this format. I haven't any idea where to start. Oracle stores the data within the field itself, while SQL Server doesn't. I do not understand what the data file should look like. Or really anything at all about how to get my end result. Can anybody out there help?
Thanks!!
Chrissy
-
this data is stored in pages of data in the SQL data file.
max size for data is 2GB and can be accessed as if it were a normal varchar/nvarchar field.
no need to worry - they aren't stored as seperate pages - it's just an internal method to avoid storing large data fields in your rowset data, which would kill your sql server if you had a clustered index.
MVDBA
-
So how do I go about creating this?
Everytime I try to use DTS to do it, it gives me an error saying the the field (the LONG one) is too large.
Thanks!
Chrissy
-
what are you using for your migration ?
are you dragging the data from oracle using the SQL import, or are you pushing it from oracle somehow?
if you have created the table structure and you have made these fields a TEXT or nTEXT type then there shouldn't be a problem.
if your "text in row" option is set off(the default i beleive) then you won't have anything to worry about performance wise.
MVDBA
-
Let's see....
I right click on TABLES under my database, and select import. This brings up the DTS. I have an ODBC connection set up for my Oracle Database. After selecting that, I go to the next screen and select my destination, in this case, a SQL SERVER database. Then I go to the next slide and select all the tables I want to move over, and check the transformation. For the tables that contain the LONG fields, it says it is moving them to TEXT. I finish that up and select FINISH. After that, it runs. During the execution, when it gets to my LONG/TEXT tables, I get this error:
CANNOT INSTANTIATE STORAGE. OBJECT FOR SOURCE COLUMN 27('COMPARESTRING') BLOB DATA MULTIPLE STEP OLE DB OPERATION GENERATED ERRORS. CHECK EACH OLE DB.
Does this help?
-
what kind of data are you storing in your LONG? i take it it's just text data ?
MVDBA
-
Good Morning
Yes, just text data. No images or anything of that nature. Just plain ole boring text.
This morning, after deciding to give it one more shot. I tried my migration again. I am not getting that error and the data seems to be there. I have not done anything differently except the way I connected. I used the "Microsoft OLE DB Provider for Oracle" rather than my ODBC connection. Would that make a difference? I am wee confused on that point.
I am going to have to do this in production in a few weeks and I want to make sure I will be able to get the data moved over properly. So if this is just a weird, unexpected result, then please, let me know. I don't want to be surprised when the time comes to do it for real and it doesn't work......again.
Thanks!!!
Chrissy
-
yes, it could be the data driver you were using. it may have been "misinterpreting" the data.
stick with what works.
if you are interested microsoft have a dedicated page for migrations from oracle to SQL
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlorcle.mspx
MVDBA
-
Thanks for all your help! It has been very good.
One last question.....After doing this migration, I have to provide management with a spreadsheet showing row counts from both the original Oracle and the new SQL databases.
I am able to get this rather easily from Oracle. I just run a select/count with tablenames and commas from the database and pop it into an .csv file which I then just open up in Excel. Five minutes, tops.
When I try to do something similiar in SQL, I have issues. I cannot seem to get a rowcount concatanated<sp> with commas and the table names. I must be having a brain cramp or something. I suppose I can do it manually, but with over 400 tables, it just seems like a huge waste of time. I feel I should be able to get this in a simple script from the database.
Thanks again!
Chrissy
-
Hi Chrissy:
Try:
EXEC sp_MSforeachtable "SELECT '?', COUNT(*) as 'number of rows' FROM ?"
Francis
-
run this query in Query Analyser with the output set to File
declare @tbname nvarchar(1000)
declare @strsql nvarchar(1000)
declare curs1 cursor for select name from sysobjects where xtype='u'
open curs1
fetch next from curs1 into @tbname
while @@fetch_status=0
begin
set @strsql='select '''+@tbname+''',count (*) from '+@tbname
exec sp_executesql @strsql
fetch next from curs1 into @tbname
end
close curs1
deallocate curs1
MVDBA
-
PERFECT!!!!
Thank you so much. You have been such a great great help!! Everything seems to be working like a dream now.
I am grateful.
Chrissy
-
no problem,
but just out of interest, why are you migrating from oracle??
not that i'm biased or anything, but most oracle DBA's see SQL as a toy database (i know my coleagues here take the piss quite regularly)
MVDBA
-
Being an Oracle DBA, I understand completely and, I must admit, often have the same view as your collegues. But, I must also admit......SQL does have it's place at times.
The application that we are migrating does not need the power of Oracle and the cost of SQL made the management drool (almost literally). The decision was made long before I became involved in the project because of these reasons.
Chrissy
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply