Hi,
I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
ran into some problems. First I tried using SSMA vith 32-bit Oracle
client got connection with both databases. I was able to create
objects (tables,index etc..) but when I tried to migrate data SSMA
stopped at 2%. I tried the same against SQL2005 32-bit everything
worked fine.
I then changed Oracle client from 32-bit to 64-bit so I was able to
create a linked server but moving data with insert into..Select * from
linked server did take some time, about 1 million rows/30-min this is
too slow!
SSMA doesnt seem to be working with 64-bit Oracle client? Can't get
connected to the Oracle database.
So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
client should I use? Is SSIS a way out of the problem. Could I speed
up moving data via linked server?
Thanks in advance
Regards /Patrik
Patrik,
Have you tried the import wizard from SQL Server 2005 Management Studio?
Right-click on the database that you want to import to, choose Tasks /
Import Data ... and go through the dialogs. One of the data sources is
"Microsoft OLE DB Provider for Oracle". Choose that, specify where you want
the data to come from, then where it will go on your server, etc.
(Disclaimer: I have never personally tried an import from Oracle.) This
import wizard will actually make an SSIS package to do the imports for you.
SSIS can use the Bulk Insert facilities so it can be about as fast as
possible, provided the mappings are simple.
RLF
<pama@.unitema.se> wrote in message
news:3d51377a-3f73-49c1-a7aa-9970106b79ad@.8g2000hse.googlegroups.com...
> Hi,
> I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
> ran into some problems. First I tried using SSMA vith 32-bit Oracle
> client got connection with both databases. I was able to create
> objects (tables,index etc..) but when I tried to migrate data SSMA
> stopped at 2%. I tried the same against SQL2005 32-bit everything
> worked fine.
> I then changed Oracle client from 32-bit to 64-bit so I was able to
> create a linked server but moving data with insert into..Select * from
> linked server did take some time, about 1 million rows/30-min this is
> too slow!
> SSMA doesnt seem to be working with 64-bit Oracle client? Can't get
> connected to the Oracle database.
> So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
> client should I use? Is SSIS a way out of the problem. Could I speed
> up moving data via linked server?
> Thanks in advance
> Regards /Patrik
|||Hi,
I also have seen many problems connecting to Oracle from SQL Server 2005
64-bit. This version of the client and this patch worked fine for me:
Oracle Client 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
Hope this helps,
Ben Nevarez
"pama@.unitema.se" wrote:
> Hi,
> I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
> ran into some problems. First I tried using SSMA vith 32-bit Oracle
> client got connection with both databases. I was able to create
> objects (tables,index etc..) but when I tried to migrate data SSMA
> stopped at 2%. I tried the same against SQL2005 32-bit everything
> worked fine.
> I then changed Oracle client from 32-bit to 64-bit so I was able to
> create a linked server but moving data with insert into..Select * from
> linked server did take some time, about 1 million rows/30-min this is
> too slow!
> SSMA doesnt seem to be working with 64-bit Oracle client? Can't get
> connected to the Oracle database.
> So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
> client should I use? Is SSIS a way out of the problem. Could I speed
> up moving data via linked server?
> Thanks in advance
> Regards /Patrik
>
|||BTW, it is the 32-bit client.
Ben Nevarez
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi,
> I also have seen many problems connecting to Oracle from SQL Server 2005
> 64-bit. This version of the client and this patch worked fine for me:
> Oracle Client 10.2.0.1.0
> Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
> Hope this helps,
> Ben Nevarez
>
>
> "pama@.unitema.se" wrote:
Showing posts with label ssma. Show all posts
Showing posts with label ssma. Show all posts
Monday, March 12, 2012
Migrate data from Oracle to SQL2005 64-bit
Hi,
I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
ran into some problems. First I tried using SSMA vith 32-bit Oracle
client got connection with both databases. I was able to create
objects (tables,index etc..) but when I tried to migrate data SSMA
stopped at 2%. I tried the same against SQL2005 32-bit everything
worked fine.
I then changed Oracle client from 32-bit to 64-bit so I was able to
create a linked server but moving data with insert into..Select * from
linked server did take some time, about 1 million rows/30-min this is
too slow!
SSMA doesnt seem to be working with 64-bit Oracle client' Can't get
connected to the Oracle database.
So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
client should I use? Is SSIS a way out of the problem. Could I speed
up moving data via linked server?
Thanks in advance
Regards /PatrikPatrik,
Have you tried the import wizard from SQL Server 2005 Management Studio?
Right-click on the database that you want to import to, choose Tasks /
Import Data ... and go through the dialogs. One of the data sources is
"Microsoft OLE DB Provider for Oracle". Choose that, specify where you want
the data to come from, then where it will go on your server, etc.
(Disclaimer: I have never personally tried an import from Oracle.) This
import wizard will actually make an SSIS package to do the imports for you.
SSIS can use the Bulk Insert facilities so it can be about as fast as
possible, provided the mappings are simple.
RLF
<pama@.unitema.se> wrote in message
news:3d51377a-3f73-49c1-a7aa-9970106b79ad@.8g2000hse.googlegroups.com...
> Hi,
> I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
> ran into some problems. First I tried using SSMA vith 32-bit Oracle
> client got connection with both databases. I was able to create
> objects (tables,index etc..) but when I tried to migrate data SSMA
> stopped at 2%. I tried the same against SQL2005 32-bit everything
> worked fine.
> I then changed Oracle client from 32-bit to 64-bit so I was able to
> create a linked server but moving data with insert into..Select * from
> linked server did take some time, about 1 million rows/30-min this is
> too slow!
> SSMA doesnt seem to be working with 64-bit Oracle client' Can't get
> connected to the Oracle database.
> So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
> client should I use? Is SSIS a way out of the problem. Could I speed
> up moving data via linked server?
> Thanks in advance
> Regards /Patrik|||Hi,
I also have seen many problems connecting to Oracle from SQL Server 2005
64-bit. This version of the client and this patch worked fine for me:
Oracle Client 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
Hope this helps,
Ben Nevarez
"pama@.unitema.se" wrote:
> Hi,
> I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
> ran into some problems. First I tried using SSMA vith 32-bit Oracle
> client got connection with both databases. I was able to create
> objects (tables,index etc..) but when I tried to migrate data SSMA
> stopped at 2%. I tried the same against SQL2005 32-bit everything
> worked fine.
> I then changed Oracle client from 32-bit to 64-bit so I was able to
> create a linked server but moving data with insert into..Select * from
> linked server did take some time, about 1 million rows/30-min this is
> too slow!
> SSMA doesnt seem to be working with 64-bit Oracle client' Can't get
> connected to the Oracle database.
> So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
> client should I use? Is SSIS a way out of the problem. Could I speed
> up moving data via linked server?
> Thanks in advance
> Regards /Patrik
>|||BTW, it is the 32-bit client.
Ben Nevarez
"Ben Nevarez" wrote:
> Hi,
> I also have seen many problems connecting to Oracle from SQL Server 2005
> 64-bit. This version of the client and this patch worked fine for me:
> Oracle Client 10.2.0.1.0
> Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
> Hope this helps,
> Ben Nevarez
>
>
> "pama@.unitema.se" wrote:
> > Hi,
> >
> > I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
> > ran into some problems. First I tried using SSMA vith 32-bit Oracle
> > client got connection with both databases. I was able to create
> > objects (tables,index etc..) but when I tried to migrate data SSMA
> > stopped at 2%. I tried the same against SQL2005 32-bit everything
> > worked fine.
> >
> > I then changed Oracle client from 32-bit to 64-bit so I was able to
> > create a linked server but moving data with insert into..Select * from
> > linked server did take some time, about 1 million rows/30-min this is
> > too slow!
> >
> > SSMA doesnt seem to be working with 64-bit Oracle client' Can't get
> > connected to the Oracle database.
> >
> > So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
> > client should I use? Is SSIS a way out of the problem. Could I speed
> > up moving data via linked server?
> >
> > Thanks in advance
> >
> > Regards /Patrik
> >
I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
ran into some problems. First I tried using SSMA vith 32-bit Oracle
client got connection with both databases. I was able to create
objects (tables,index etc..) but when I tried to migrate data SSMA
stopped at 2%. I tried the same against SQL2005 32-bit everything
worked fine.
I then changed Oracle client from 32-bit to 64-bit so I was able to
create a linked server but moving data with insert into..Select * from
linked server did take some time, about 1 million rows/30-min this is
too slow!
SSMA doesnt seem to be working with 64-bit Oracle client' Can't get
connected to the Oracle database.
So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
client should I use? Is SSIS a way out of the problem. Could I speed
up moving data via linked server?
Thanks in advance
Regards /PatrikPatrik,
Have you tried the import wizard from SQL Server 2005 Management Studio?
Right-click on the database that you want to import to, choose Tasks /
Import Data ... and go through the dialogs. One of the data sources is
"Microsoft OLE DB Provider for Oracle". Choose that, specify where you want
the data to come from, then where it will go on your server, etc.
(Disclaimer: I have never personally tried an import from Oracle.) This
import wizard will actually make an SSIS package to do the imports for you.
SSIS can use the Bulk Insert facilities so it can be about as fast as
possible, provided the mappings are simple.
RLF
<pama@.unitema.se> wrote in message
news:3d51377a-3f73-49c1-a7aa-9970106b79ad@.8g2000hse.googlegroups.com...
> Hi,
> I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
> ran into some problems. First I tried using SSMA vith 32-bit Oracle
> client got connection with both databases. I was able to create
> objects (tables,index etc..) but when I tried to migrate data SSMA
> stopped at 2%. I tried the same against SQL2005 32-bit everything
> worked fine.
> I then changed Oracle client from 32-bit to 64-bit so I was able to
> create a linked server but moving data with insert into..Select * from
> linked server did take some time, about 1 million rows/30-min this is
> too slow!
> SSMA doesnt seem to be working with 64-bit Oracle client' Can't get
> connected to the Oracle database.
> So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
> client should I use? Is SSIS a way out of the problem. Could I speed
> up moving data via linked server?
> Thanks in advance
> Regards /Patrik|||Hi,
I also have seen many problems connecting to Oracle from SQL Server 2005
64-bit. This version of the client and this patch worked fine for me:
Oracle Client 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
Hope this helps,
Ben Nevarez
"pama@.unitema.se" wrote:
> Hi,
> I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
> ran into some problems. First I tried using SSMA vith 32-bit Oracle
> client got connection with both databases. I was able to create
> objects (tables,index etc..) but when I tried to migrate data SSMA
> stopped at 2%. I tried the same against SQL2005 32-bit everything
> worked fine.
> I then changed Oracle client from 32-bit to 64-bit so I was able to
> create a linked server but moving data with insert into..Select * from
> linked server did take some time, about 1 million rows/30-min this is
> too slow!
> SSMA doesnt seem to be working with 64-bit Oracle client' Can't get
> connected to the Oracle database.
> So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
> client should I use? Is SSIS a way out of the problem. Could I speed
> up moving data via linked server?
> Thanks in advance
> Regards /Patrik
>|||BTW, it is the 32-bit client.
Ben Nevarez
"Ben Nevarez" wrote:
> Hi,
> I also have seen many problems connecting to Oracle from SQL Server 2005
> 64-bit. This version of the client and this patch worked fine for me:
> Oracle Client 10.2.0.1.0
> Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
> Hope this helps,
> Ben Nevarez
>
>
> "pama@.unitema.se" wrote:
> > Hi,
> >
> > I'm trying to migrate data from Oracle 10g to SQL2005 64-bit but I've
> > ran into some problems. First I tried using SSMA vith 32-bit Oracle
> > client got connection with both databases. I was able to create
> > objects (tables,index etc..) but when I tried to migrate data SSMA
> > stopped at 2%. I tried the same against SQL2005 32-bit everything
> > worked fine.
> >
> > I then changed Oracle client from 32-bit to 64-bit so I was able to
> > create a linked server but moving data with insert into..Select * from
> > linked server did take some time, about 1 million rows/30-min this is
> > too slow!
> >
> > SSMA doesnt seem to be working with 64-bit Oracle client' Can't get
> > connected to the Oracle database.
> >
> > So what to do, doesn't SSMA work with SQL2005 64-bit? What Oracle
> > client should I use? Is SSIS a way out of the problem. Could I speed
> > up moving data via linked server?
> >
> > Thanks in advance
> >
> > Regards /Patrik
> >
Monday, February 20, 2012
Microsoft SQL Server Migration Assistant for Access Fails to Migrate Data
I attempted to use SSMA to migrate a 2000 file format .mdb into SQL Server Express (and, as I've just purchased VS2005 Pro, into SQL Server Developer Edition).
The file is actually a backend, so nothing to migrate other than tables.
After several runs during which I received (and fixed up) some errors, the process runs smoothly with no errors or warnings, until it comes to the last step; migrating the data itself.
Data migration into 'parent' tables works fine. However, wherever I have data in a table with a foreign key relationship to any of the aforementioned 'parent' tables, it refuses to migrate it. The text of the error message will be very smiliar to the following (from the log):
[Datamigrator: Error] [464/7] [2007-09-23 14:18:54]: Exception: The INSERT statement conflicted with the FOREIGN KEY constraint "Branch$CompanyBranch". The conflict occurred in database "DPMTest", table "dbo.Company", column 'CompanyID'.
There is nothing wrong, so far as I can determine, with the relationships involved.
I can insert data into the tables using any of the following methods:
1. Directly in Access, in the source backend.
2. Using the original Access frontend application, attached to the source backend.
3. Using VB.Net forms I am developing, in the 'upsized' database.
4. Directly in the Management Studio, in the 'upsized' database.
None of these four methods complains in the least about the relationships which SSMA balks at.
I would be grateful for any ideas....thanks.
The error is self explanatory...You are inserting companyID that are not there in the master table. Just run a select some thing like this
Select *from ThisTableName where CompanyID not In (select companyID from ParentComapnyTableName)
You will get few rows. this needs to be deleted otherwise you have to correct the data.
Madhu
|||As I said, I can insert data via all other methods. I am not inserting CompanyID where it does not exist in the master table. There is only one CompanyID at this point, whose ID is '1'. If I add a Branch with CompanyID of '1' in the Access source database which, again, doesn't complain about the relationship - because there is no problem with the relationship, or the data I'm inserting - the Migration Assistant throws the abovementioned error.
I appreciate your response, but I feel you didn't comprehend my original post. Thanks, anyway.
|||I have "successfully" migrated MDB files only to find out that SQL and Visual Studio balks at them. Problems with constraints, views, and other stuff.Microsoft SQL Server Migration Assistant for Access Fails to Migrate Data
I attempted to use SSMA to migrate a 2000 file format .mdb into SQL Server Express (and, as I've just purchased VS2005 Pro, into SQL Server Developer Edition).
The file is actually a backend, so nothing to migrate other than tables.
After several runs during which I received (and fixed up) some errors, the process runs smoothly with no errors or warnings, until it comes to the last step; migrating the data itself.
Data migration into 'parent' tables works fine. However, wherever I have data in a table with a foreign key relationship to any of the aforementioned 'parent' tables, it refuses to migrate it. The text of the error message will be very smiliar to the following (from the log):
[Datamigrator: Error] [464/7] [2007-09-23 14:18:54]: Exception: The INSERT statement conflicted with the FOREIGN KEY constraint "Branch$CompanyBranch". The conflict occurred in database "DPMTest", table "dbo.Company", column 'CompanyID'.
There is nothing wrong, so far as I can determine, with the relationships involved.
I can insert data into the tables using any of the following methods:
1. Directly in Access, in the source backend.
2. Using the original Access frontend application, attached to the source backend.
3. Using VB.Net forms I am developing, in the 'upsized' database.
4. Directly in the Management Studio, in the 'upsized' database.
None of these four methods complains in the least about the relationships which SSMA balks at.
I would be grateful for any ideas....thanks.
The error is self explanatory...You are inserting companyID that are not there in the master table. Just run a select some thing like this
Select *from ThisTableName where CompanyID not In (select companyID from ParentComapnyTableName)
You will get few rows. this needs to be deleted otherwise you have to correct the data.
Madhu
|||As I said, I can insert data via all other methods. I am not inserting CompanyID where it does not exist in the master table. There is only one CompanyID at this point, whose ID is '1'. If I add a Branch with CompanyID of '1' in the Access source database which, again, doesn't complain about the relationship - because there is no problem with the relationship, or the data I'm inserting - the Migration Assistant throws the abovementioned error.
I appreciate your response, but I feel you didn't comprehend my original post. Thanks, anyway.
|||I have "successfully" migrated MDB files only to find out that SQL and Visual Studio balks at them. Problems with constraints, views, and other stuff.
Subscribe to:
Posts (Atom)