I'm trying to make the business case to move our business databases to SQL Server 2005 from Oracle 9i. The SQL Server Migration Assistant for Oracle will be absolutely necessary for this to happen. However, I am unable to launch the assistant. It tells me I have an old version of java installed - but I don't. It is a new XP machine and I downloaded and installed j2sdk-1_4_2_12-windows-i586-p.exe which did not help (I rebooted). Why does it think I have an old version and how can I get this assistant working.
Has anyone ever tried to use this free software to migrate an old ORACLE 8.1.7 database to SQL Server 2005? I was just wondering if there were any "gotchas" I should be aware of before hand. Thanks, Robert
I selected my source Oracle database and my target SQL Server database. I click the convert SQL button and it takes about 20 minutes to convert everything. Then what? The Migrate Data button is greyed out. I configured the linked server as is outlined in the instructions. The "Migrate Data" button is always greyed out. I need to get this working soon so that I can convince the management team that moving to SQL Server is the way to go. Thanks.
i am transfering an Oracle 10g database to SQL Server 2005. i have 2 pcs one with SQL Server 2005 with SSMA For Oracle Extension 3.1 installed, also with Oracle 10g client configured to connect the Oracle database in another pc. the other pc has Oracle 10g database server installed with SSMA For Oracle 3.1. whenever i try to migrate the data there is an exception which says
"Errors: SQL Server cannot access the source table"
i dont understand why its there. i hav successfully converted the schema and loaded them in SQL Server. can anybody tell me a soltuion asap.
I can connect to my Oracle database and I can connect to my SQL Server database. But the "Convert Schema" button stays disabled. What am I doing wrong? Anyone have experience using this tool?
Hi, I am converting a schema from oracle to sql server 2005 using Sql Server Migration Assistant but for the conversion of each and every table it is asking me to connect to oracle. How can i convert this schema without again and again specifying the connection? Thanks in advance, Aru
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 downloaded the Microsoft SQL Server Migration Assistant for Access 4 weeks ago. Today, I tried this program for the first time. I added one Database with 3 Access 2003 Tables to the Access Metadata Explorer. I right clicked Databaes on the Access Metadata pane and did not see the "Convert Schema" highlighted!!!??? What is wrong with the program? How can I make it highlighted? Please help and advise.
I am trying to migrate an Access 2003 database to SQL Server 2005 and am having various problems:
1. On one table, the migration gives the following on an autonumber field: A2SS0023: SQL Server Migration Assistant for Access Warning Message: Default value '0' added to target column (1), followed later on by A2SS0028: Auto-increment attribute of the column 'MSEQ' was not converted to Identity because the column has a default value.
This table has about 80,000 records.
2. If I include another table in the migration, this one with 3.4 million records (hence our need to upgrade to SQL server 2005), I am getting a timeout error, even when I try to migrate the table by itself. I have verified that all timeout options are set to zero (unlimited time).
Any suggestions would be appreciated.
-- Larry West, MCSD, MCPD, MCTS: SQL Server 2005 [so yes, I should know the answer to this, but I don't], MOUS, FLMI, ACS
I have SQL 2000 and installed SQL 2005 Express. Had done some test migrations of Access 2003 to SQL2000 using the tool for that. Upgraded that migration assistant to SQL 2005 (my mistake) and now I have 2 instances of SQL2005 running (according to surface area configuration) and even the server mgr for SQL2000 can see those instances.. but SSMA 2005 refuses to see them.. My migration test project is dead in the water.. Can I get a copy of SSMA for SQL2000 back?..Anybody?.. Thanks..
I've installed SQL 2005 Express Addition which created a default database using Windows Authentication -- MachineName/SQLEXPRES. I want to convert some access databases to sql server in the migration assistant, however, when I try to connect the migration assistant to the default database I can't find the database in the drop down box wizard? I'm perplexed? Please shed some light or point me to some articles that will help. Thanks.
Does SQL Server has any equivalent of Oracle (PL/SQL) PRAGMA AUTONOMOUS_TRANSACTION ????
I am in a process of migrating Oracle database to SQL Server. I am facing trouble in the above mentioned thing, as I cannot compromise by not putting it.
Our clint need to migrate from already runner database on SQL SERVER 2005 to Oracle 9i, WHAT will be basic and important issue we will face in this term and HOW we will handle with our functions and procedures created in SQL SERVER 2005 database... ???
Hi,We need help on following things,1. Inputs on creating comments on the columns & Tables of a SQLDatabase & generating the sql script of that.2. Is it possible to call a .exe file in SQL server like followingcode in ORACLEcreate procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )language SQL;externalname "CERT_VERIFY"location 'HOST_IMG:TEST_CALCS.EXE'with ALL logical_name translationlanguage CGENERAL parameter style3. We are using Rules for restricting data(now), We need inputswhether to use Check constraints or Rules.Thanks & Regards,Chandra Mohan
I want to do a data transfer but i am not sure how ..from oracle to sql server.
i want to know if its required to install the oracle client to configure so as to transfer data or not.
I heard from someone that if we change the win.ini file entry for oracle.ini and place the tnsnames.ora file i spcified location it will work but it does not what is the way to deal with..
How do i perform this with the odbc configuration for oracle..and use the same in the dts.
I want to migrate data from Oracle Database to SQL Server 2005 by using SSIS Package. Client wants to migrate just data not schema. Schema is already there for SQL Server, generated through script.
I tried to migrate the data by using import-export wizard in SSIS. But, its asking for creating new tables when migrating data.
I want to migrate data in existing tables, without dropping already existed tables or creating new tables.
I have been asked to look at optimising a Data Migration Strategy for one of our clients. I am quite new to Data Migration (I'm actually a system designer!).
The migration is from a legacy Oracle 7.16 Db to a SQL Server 2000 environment.
The legacy Db has some large tables - 30 Million+ rows
Table/column Mapping is 1:1
At present we are using DTS to import into the SQL environment.
My question is: Is there any way to leverage the Oracle 7.16 export to speed up the migration process?
Has anyone had any experience with the Oracle Migration Workbench? or using DTS to migrate MS SQL to Oracle? The Migration Workbench seems to work great but it is having parsing issues with some of the syntax in our triggers and procs (roughly 1025 of them). Are there other ways of handling these errors other than manually cleaning them up?
Hi all,I am using the Oracle Workbench for migrating my DB currently on SqlServer 2000 to Oracle 9i. During the migration the following happensSELECT @tBaseTable = tBaseTable, @tDBColumnName = tDBColumnNameFROM tblColumnMain WITH (NOLOCK) left join tblCustomField WITH(NOLOCK) on aColumnID = nColumnIDWHERE aColumnID = @nPropertyIDgets converted toSELECT tBaseTable, tDBColumnNameINTOSPROC_PROPDATA.tBaseTable, SPROC_PROPDATA.tDBColumnName, FROMsa.tblColumnMain, sa.tblCustomFieldWHERE (aColumnID = nColumnID) AND (aColumnID =SPROC_PROPDATA.nPropertyID_);and gives the warning that complex outer joins maynot be reliablyconvertedOn the other hand the following codeselect @keyid = a.keyID, @pubs=b.pubs fromssd1 a left outer join ssd2 b on a.l_name = b.l_namewhere b.l_name = @namemigrates perfectly to Oracle asSELECT a.keyID, b.pubsINTO SPROC_JOINTEST8.keyid, SPROC_JOINTEST8.pubsFROM ssdash.ssd1 a, ssdash.ssd2 bWHERE (a.l_name = b.l_name(+)) AND(b.l_name = SPROC_JOINTEST8.name);with the same warning ...Could anyone enlighten me as to what exactly is the difference betweenthese 2 above cases.Also the migration bench seems to be removing nolocks and such stuff allaround. Any workaround for this is also needed.ssDash--Posted via http://dbforums.com
We are going away from a 2003 Windows Server OS with SQL Server 2008 R2 to a 2012 Windows Server with 2012 SQL Server. Both the distributor and subscriber resides on 2003 Windows Server and the focus will be to migrate those databases to the 2012 SQL Server.
We would also like to avoid sending down a new snapshot due to logic in the replication process (major headache to clean up data). what's the best approach in moving the distributor and subscriber databases without having to run a snapshot?
In theory and needs testing:
1. Work with business users to get downtime
2. Stop sql apply so no new changes are going to the Oracle publisher database
3. Remove subscription, publication and distributor from current replication
4. Oracle DBA to clear out replication related objects - fresh clean slate
5. Use log shipping to apply last t-log and restore db with Keep_Replication
6. Set up new distribution, publication (keep existing object unchanged), and subscriber (without initialization) Will this work?
I am doing migration from oracle11g to SQL server2012. Facing error while data migration from Oracle rowid dat typeto SQL uniqueidentifier using SSMA6.0
Errors: The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column.
OK I migrated a DTS package from MS SQL 2000 to MS SQL 2005 64-bit SSIS. OK so I fixed a problem with a Double Global Variable... Now I am stuck at a connection to Oracle and it returning data... Here is the error message:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "Remedy_Count": "Unsupported data type on result set binding 0.".
My SQL Statement is this:
SELECT COUNT(*) AS Expr1 FROM DB.MYTABLE
When I run it from the Query Builder it returned this:
EXPR1 = 2983
Here are some details from the General Page on the Execute SQL Task
ResultSet - Single row ConnectionType - OLE DB Connection - Oracle Provider for OLE DB SQLSourceType - Direct input BypassPrepare - True
In the Result Set I have The following:
Result Name - Variable Name 0 - Remedy_Count
So... what can I do to fix this. I have the Remedy_Count set as a:
EvaluateAsExpression - False Value - 2414 ValueType - Double
I have tried setting the ValueType to Object and other things... Same error... I read some where about how SSIS has issues when Oracle returns a Numeric type data. Can someone help me with this? Anything you all can tell me would be great.
we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
Microsoft SQL Server Management Studio 9.00.2047.00 Microsoft Analysis Services Client Tools 2005.090.2047.00 Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447) Microsoft MSXML 2.6 3.0 4.0 6.0 Microsoft Internet Explorer 6.0.3790.1830 Microsoft .NET Framework 2.0.50727.42 Operating System 5.2.3790
I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.
I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error:
(i replaced the real server name with "someServer".)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".
Hello everybody. I want to use Web Assistant with option "..data Change" so Web assitant will create trigger.
The database generated from Erwin 3.5.2 and use triggers for integrity. When I run Web assistant wizard I am getting message ".. can't create trigger becouse existing trigger created with encryption option
sp_helptext tI_prcode (my insert trigger) shows all code in the trigger where shoild i look to disable encryption option
I found way around this problem A.Save and delete existing trigger B.run Web assistant trigger will created C. Save and delete web assiatnt trigger d. Insert code from web assitant trigger into code from original erwin trigger and recreate it
I am currently with sql 2k5 and using web assistant to generate some HTML files and email the same automatically to a set of users. these html files that are generated are processed via a JOB in sql server. I am in process of replacing of the web assistant procedures. Is this possible to make this with XQuery of SQL Server 2K5 and convert it into HTML files with Stored Procedure. Is there any other possible way to do it?
I used SQL Server's web assistant wizard to create a web assistant job. In this job I chosed "When SQL Server Data Changes" for schedule options. Late, I don't want this job and by right click web assistant job I deleted this job.
However, I have a stored procedure which use the same table with the above deleted web assistant job. Normally, the stored procedure runs only 1 minute. Today it runs more than 5 minute and then failed. Here is the error messages:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++ Server: Msg 16882, Level 11, State 1, Line 0 SQL Web Assistant: Web task not found. Verify the name of the task for possible errors. ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++
Please somebody can tell me how can I solve this problem (or totally delete the above web assistant job).
