SQL 2012 :: Migrating Server Database Over To Oracle

May 29, 2015

I just wanted to know what the easiest way would be to migrate a SQL Server 2012 database to Oracle?

Apart from the tables and views, the database also uses a number of stored procedures and functions which will also need to be migrated over onto Oracle.

I guess the problem with generating scripts for the database (right clicking database > tasks > generate scripts) is that the TSQL scripts will not run in Oracle which uses PSQL? Is this correct?

I have also considered using the import/export wizard. Do I need to install a feature to use this to export to Oracle?

View 2 Replies


ADVERTISEMENT

Migrating Database From Oracle To Sql Server

Sep 17, 2001

i am migrating data and schema from oracle server to sql server., when i try to do the dts it gives me an error saying could not resolve tnsnames.ora. iam not sure what is the problem.

My oracle server host is dbserver.hari.com.edu
and its name is Prod (name of the machine)

i tried both it still gives me the same error., but when i install an oracle client on the machine and configure the same it is working great.

My question is, is it necessary to install oracle client to migrate database from oracle to sql server. If not how do i do the same...what is the process to do it. since this is a prodction machine we don't want more of applications running on the same machine.


Your help is appreciated, thanks in advance.

Kavitha

View 1 Replies View Related

SQL 2012 :: Migrating Database To New Server - Map Login To Users

Mar 10, 2014

I am migrating a database TESTDB from SQL 2008R2 to a new server running SQL Server 2012.

Management has decided the current sql users should have "better" user names. So the login and username "BadUsername" on the old server should be called "GoodUserName". Goodusername should have the same permissions as Badusername.

I have now restored a backup from the old server to the new server.

I used the following script for creating the login:

CREATE LOGIN [GoodUserName] WITH PASSWORD=N'difficultpassword', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Then I run the following script:

ALTER USER Badusername WITH LOGIN = GoodUserName, NAME = GoodUserName

But the results are not what I wanted. I now have two database users: Badusername and GoodUserName. I would have preferred if BadUserName was "replaced" by GoodUserName, but it won't be a problem if I have to delete badusername manually. Worser is that GoodUserName have NOT "inherited" any permissions from Badusername.

Is there an easy way to transfer permissions or do I need to loop through the permissions of badusername and apply those to badusername?

View 4 Replies View Related

Migrating From Oracle To SQL Server

Feb 6, 2003

I have a program that uses ADO to read/write to/from an Oracle back-end database. I'm hoping that given the underlying schema is the same, I can simply change the database driver I'm using and use a SQL Server database instead. Has anyone migrated an application using ADO from Oracle to SQL ? Are there any issues/problems/tips that I should be aware of ?

Thanks in advance..

Scott..

View 4 Replies View Related

Migrating From Oracle 8i To SQL Server 2000

Jan 31, 2004

Hi
I am trying to migrate my database in Oracle 8i to Sql Server 2000 but am unsure of how to carry out the migration. How do I do it and what do I have to consider. My OS is Windows 2003. Thanks in advance

View 2 Replies View Related

Migrating From Oracle To SQL Server 2005

May 30, 2006

I am migrating a database from Oracle to SQL Server 2005. I have a problem when migrating code from PL-SQL (oracle labguage) to T-SQL (sql server language). My problem is: there is pseudo-column in oracle called level that return the level from a register in a tree view of a select (a parent-child relationship). I would want to know how to translate that level column to T-SQL. Thanks!

View 3 Replies View Related

Migrating CLOBS And BLOBS To SQL SERVER From ORACLE 9i

Feb 5, 2004

I am taking over a Database set up where we have 2 databases. One is a SQL SERVER 2000 db and the other is an Oracle 9i db.

THe previous DBA had/has a problem with transferring CLOB and Blob datatypes From the ORACLE db to SQL SERVER. He says that the OLEDB driver SQL SERVER provides has limitations particularly on the CLOB data because it truncates data over 4000 characters in length.


For the Blob issue:
We basically want to store/move attachments (word documents etc)
into image datatypes (SS) from the blob datatype (9i).

I figured I could do this using DTS or even using the Linked server method. I figured worst case scenario I would just store the documents on the server and then use SQL SERVER's textcopy utility to upload the documents into the image datatype column of the SQL SERVER table.

For the Clob Issue I have no idea what to do, because I cant find anything online that mentions SQL SERVER having issues with importing CLOB data over 4000 charcters in length, afterall the text datatype accomdates more than 4000 characters.

Does anyone have any suggestions for moving the Blob and most importantly the Clob datatypes from 9i to SQL SERVER without any truncation in the data????

View 5 Replies View Related

Migrating Oracle PL/SQL Objects And Collection Types Into SQL Server 2005

Mar 7, 2008



Hi

Can anyone help me out in migrating Oracle stored procedures & Functions which contains collection types (user-defined datatypes like VARRAYS) into SQL Server 2005. How to achieve this taks? This is very important for me and it's very urgent. Please someone help me.

Thanks & Regards
K P Kumar

View 1 Replies View Related

Migrating SQL Stored Procedures To ORACLE 8.0

Jun 2, 2000

How do i migrate Stored Procedures from SQL Server (7.0) to ORACLE (8.0)??


Thanks
Anand

View 1 Replies View Related

Migrating To ORACLE (Uniqueidentifier Issue)

Jan 26, 2008

If i want to migrate from SQL SERVER 2005 to Oracle, How Oracle will deal with my primary key columns in almost all tables having datatype of UNIQUEIDENTIFIER ???

is there any concept of uniqueidentifier in Oracle same as in SQL SERVER ????


Regards

View 2 Replies View Related

SSMA For Oracle And Migrating Views With Outer Joins

May 23, 2008



I am useing SQL Server Migration Tools for Oracle to move some data for archive purposes.

The data is moving great, however the SSMA crashes or just plain stops when it tries to convert a view that has an outer join.

Is ther any sort of fix for this?

View 1 Replies View Related

Migrating 6.5 Database To A New Server

Mar 19, 2002

I am migrating MS SQL Server 6.5 databases to a new server and wanted to create the data & log devices differently than they are on the old box. My dbcc checks are giving me 2558 errors because my devices weren't built the same size in the same order. I thought if I didn't copy the master, I didn't have to rebuild the database exactly the same. Can anyone tell me if there is a way to rebuild different device sizes and do a successful restore ? Thanks in advance !

View 1 Replies View Related

Migrating All Database To A New Server

Apr 10, 2003

I need to move all existing dbs to a new server. One of the database is over 90GB in size. If I plan to use to restore database option, should I restore all user database first, then the system databases(master, msdb) or should I restore the system database first and the user databases second??
Thanks.

View 11 Replies View Related

Migrating SQL Server Database

Sep 18, 1998

Hi!

I got a project of moving a web site of one a firm from one ISP machine to another ISP
machine. They are using Cold Fusion with SQL Server as the database. I am new to SQL Server.
I want some tips on how to migrate the database from one machine to another.

Any little help will be appreciated.

I will be waiting for suggestions.

Mark.

View 1 Replies View Related

Migrating From Access Database To SQL Server 7.0

Mar 28, 2001

Hi,
We are planning to migrate data from access to sqlserver. I have good knowledge on sql server...but never used access to migrate. Can anyone help me with the basics to be kept in mind and methods and errors you get in migrateing data.If possible explain in detail and what is to be done while migrating data.....thank you in advance.And its urgent please.

View 3 Replies View Related

Need Help On Migrating A SQL 7 Database To SQL 2005 Server Express

Mar 25, 2008



Hi all,
I'm not sure if this is the right place to seek help. I'm trying to migrate a small sql 7 database to a new server runing sql 2005 server express. is that possible? if anyone has any instructions or point me to the information that i need to get this done. i'm not a dba so any details instructions would be really appreciated. Thanks in advance.

Brandon

View 1 Replies View Related

Problem Occurs When Migrating A SQL Server 2000 Database To A SQL Server 2005 Server

Sep 20, 2006

When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;

Performing operation...

- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)

When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;

InnerException-->An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005,
this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).

How do I solve this problem?





View 9 Replies View Related

SQL 2012 :: Oracle Linked Server

Jun 8, 2015

I have a SQL Server 2012 instance set up with a linked server to an Oracle Database. I need to build a stored procedure (single NVARCHAR(6) parameter in: @accountID) which executes a select statement against the Oracle DB.

Everything's working fine when testing with 'EXEC (''<oraclecodeblock>'') at LINKEDSERVERNAME' if I hard code the value of @accountID in <oraclecodeblock> but when I encapsulate it all within sp_executesql and attempt to parameterize @accountID it's throwing ORA-01722 'invalid number' errors on the Oracle DB. The parameterized value is NVARCHAR as are the account numbers.

Working:
DECLARE @accountID VARCHAR(6);
SET @accountID = '10842';
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'EXEC (''SELECT * from A.Accounts WHERE ID = ''''10842'''';'') AT LINKEDSERVER';

[code]...

View 0 Replies View Related

Migrating Access XP Database To SQL Server 2005 Express

Sep 5, 2006

I want to migrate my Access XP database to SQL Server 2005 Express Edition. The Start Menu does not display any migration assistant in SQL Server 2005 Express menu list.

How to migrate it?

View 4 Replies View Related

DB Engine :: Migrating Logins Related To Specific Database Not Whole Server Level?

Aug 25, 2015

<g class="gr_ gr_78 gr-alert gr_gramm Punctuation replaceWithoutSep" data-gr-id="78" id="78">Friends,</g>
<g class="gr_ gr_78 gr-alert gr_gramm Punctuation replaceWithoutSep" data-gr-id="78" id="78">I</g>

i am migrating one database from SQL Server 2008 where I have around 20 databases to SQLServer 2012. To migrate the login I was thinking about to use SP_Help <g class="gr_ gr_114 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="114" id="114">revlogin</g> store procedure but now what I believe is this SP scripts out whole server level login and I don't need all the logins in the server except related to the database that I am migrating to.

<g class="gr_ gr_418 gr-alert gr_gramm Grammar multiReplace" data-gr-id="418" id="418">please</g>

best way to migrate logins only related to the specific database to the new server. 

View 4 Replies View Related

Transact SQL :: Replace Special Characters In ORACLE Or SERVER 2012?

Jun 8, 2015

I'm trying to replace special characters in SQL SERVER and all the solutions for this RDBMS that I found, it uses loops and the source of my data it's in Oracle. in ORACLE and they use REGULAR EXPRESIONS to solve it..Do you know what its the better option to replace special characters? Using loops in SQL SERVER or REGULAR EXPRESSIONS in ORACLE ?

View 5 Replies View Related

Problem Migrating Application Database From SQL Server 2000 To Express Edition 2005

Jan 21, 2008

Hi there

I am new to SQL Server, but the current project that I am working on has the following requirement:-

1) Migrate the application (a servlet based web application on Apache Tomcat) from Solaris to Wintel
2) Migrate the supporting database from SQL Server 2000 to Sql Server 2005
3) Get IIS to communicate with Tomcat for serving servlet/jsp etc to the client

Though I successfully completed item 1 above, am stuck at item 2. Details are below

Actions taken for item 2

A. Installed MS 2005
B. Created new database in MS 2005 (logged in as user 'sa')
C. Generated SQL scripts (such as create table table_A etc) from existing MS 2000
D. Genearted SQL scripts (such as insert into table_A etc) from existing MS 2000
E. Created new schema in MS 2005
F. Ran scripts C & D in the new schema. All tables are records populated.
G. Obtained new JDBC driver and test run to see if connection is working fine, and it worked. Even ran an sql statment


Code Block[select count(*) from sa.table_A]

and got appropriate response.


H. When I made the application to talk to this new database (which is a copy of Production from step C, D above), it's behaving as though it cannot find the record.
I. When I further debugged, I realised that the web application is excuting queries without mentioning the schema. For eg.



Code Block[Select firstName, lastName from table_A]

Or rather it assumes that the user connecting to database is same as the schema name.


J. To further ascertain my point, I ran the query


Code Block[Select firstName, lastName from sa.table_A]

and it worked!



Now the real problem is that I cannot modify the existing code to append a schema name and this approach is rather not recommended best practise.


I tried to match the user name with the schema name, even made this schema as default to the user. But still not finding any luck.

I request all you experts out there to help me out with this problem.

Regards,

prad.nair

View 3 Replies View Related

SQL 2012 :: Migrating VLDB To Availability Group

Feb 4, 2015

I have been using AlwaysON AG for a long time now and currently have about 10TB of data across 120 databases and 3 AG groups for any application that is on SQL 2012 with great success. Each AG group is running on patch level 11.0.5058.0 with 2 synchronous replica(on different SANS) in Primary Data center and 1 ASYNC replica in DR. Migration has been a non-issue because none of the databases weren't substantial enough that I could not fit into my maintenance window which is 12-4AM on SAT morning.

My issue is that my last application to migrate to 2012 includes a 4TB TDE encrypted databases database which is about 10x larger than any of the previous ones I have migrated. The database takes 4 hours to backup after tuning extensively(I hate TDE!!)

The restore to the primary replica is instant because of seeding incremental but the issue comes from having to backup the database before adding to the availability group. 4 hours is my exact outage window and I can't get any more. My plan to migrate application is to -

First Outage Window

1) Restore Database from 2008 to 2012 Primary Replica
2) Change application ARECORD(or cname not sure which) to Primary replica
3) Run database on single node until next outage window

Week Later
1) Add database to availability group
2) Change ARECORD/CNAME to listener

What I don't like about this is I am going an entire week with 1 node instead of 3 which is worrisome. How to accomplish this I would love to hear from you or any type of comment from people who have worked with VLDB in availability groups and what you like/hate/loved about doing it. I am trying to go all in on this software and have loved it so far but getting worried when it comes to the VLDB migration.

View 4 Replies View Related

Migrating Sql Server 2005 Express Database To Sql Server 2005 Database

Mar 16, 2007

Hi, I have an application developed using VWD and sqlserver express database. The express database is turning out to be small in size and we need to migrate to larger sqlserver 2005 database. What are the steps for this migration, please list in detail.
Regards, Sandy

View 1 Replies View Related

How To Convert Oracle 8 Database Into SQL Server 7.0 Database

Oct 15, 2001

Ho do I convert Oracle 8 database into SQL server 7.0 database ? Is it possible ? Please let me know at skbhaduri@rediffmail.com

View 1 Replies View Related

Migrating From Sql2000 MSDE Database To Sql2005 Express - Attach Database Errors

Apr 23, 2006

I have a medical records system, SoapWare v4.90, that uses MSDE (SQL2000) databases. Due to the 2gb limitation, I am trying to migrate over to SQL 2005 (Standard or Express) which I have heard works fine. The SoapWare has a datamanager that allows me to log in to the MSDE instance, detach the SoapWare databases from msde (as well as do backups, etc) which I can confirm are detached.

Then I log back into a SQL2005 database instance using the datamanager and try to attach the database. This is what their pictured instructions demonstrate. However, I get the following error:


Database 'sw_charts' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.


Of course, some of the entries will be read only, since doctors have to sign off the charts and are not allowed to subsequently change them.  But I should still be able to switch over to sql 2005?!?!?!?


Or... is there a way to attach the databases to SQLExpress manually?

Help pls?

View 1 Replies View Related

Move Database From Oracle To Sql Server.

Jul 30, 2004

Anybody can give me a reference about how to move database from Oracle to Sql Server.

View 2 Replies View Related

Export SQL Server Database To Oracle

Feb 27, 2004

Hi there,
I have a problem of exporting the SQL Server 2000 database to Oracle 8i. Can anyone help regarding this??
Please help....
Thanking you in anticipation
RGDS
Amit

View 3 Replies View Related

How To Connect To SQL Server Database From Oracle 9ias Application Server (9.0.3)

May 20, 2004

How to connect to SQL server database from Oracle 9ias Application Server (9.0.3) & Test & Verify the Connection. Is it posible.

View 1 Replies View Related

Sql Server Linked To Oracle Database...very Slow...why?

Dec 26, 2003

Hi i have a sql server instance on my system and it is linking into an oracle database on another server. When i run queries against this other server...it takes forever...

However, when i use access, and link the table and run the same query against the oracle database...it runs immediatly.

I am very confused as to why there would be such a performance difference and why sql server would run so slow.

I am wondering if it has something to do with the way i configured the linked server. there are several options that I didn't know what they meant.

collation compatible (not selected)
Data access (selected)
RPC (not selected)
RPC Out (not selected)
collation name
connection timeout
query timeout

View 1 Replies View Related

Creating A Linked Server To An Oracle Database

Aug 29, 2000

Hi all,

I hope someone can help me with linked servers.

In one of my applications running on a MSSQL database, I need to issue a select call to an Oracle database to pull information into my application.

To accomplish this,

1. the ORacle Admin has created a view of the Oracle database for me.
2. I have installed the Oracle client on the SQl server box.
3. A friend has told me that one way to accomplish this is create a linked server from SQL to use OLE/DB for Oracle to connect to the Oracle database
4. So I create the linked server successfully; create the server login sp_addlinkedsrvlogin successfully
5. When I go to query the database, I get the error below:


Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 (or greater) client software installation.

You will be unable to use this provider until these components have been installed.]


Anybody understand this?


Thanks in advance,
Faustina

View 4 Replies View Related

Cannot Get Linked Server Working To Oracle 10.1 Database

May 16, 2007

Hi -

I am having an issue trying to get a linked server to work from SQL Server 2000 SP4 to an Oracle 10.1 database. This server runs Windows 2003 Server.

I had first installed the Oracle client 10.2 on the server and was able to access the Oracle db using the client.

However - when I set up the linked server on SQL Server - and click the "Tables" underneath the linked server - I get the following error.

OLE DB Provider reported an error.

OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned]

I uninstalled the 10.2 client and put the Oracle 9i client on the server (on a suggestion from a SQL Server DBA to put an earlier client on the server) - still able to connect using the Oracle client - but not via SQL Server.

I have checked to make sure the MSDAORA.dll is registered. I have also checked that the account that SQL Server starts under has proper permissions to the Oracle folders (that login has admin permissions on the server).

I have named the Linked Server the same name as the service name in tsanames.ora.

I'm at a loss - any ideas? Help - i'm tearing my hair out here.

View 1 Replies View Related

Database Wars: Oracle Vs SQL Server Lock Escalation

Jul 20, 2005

I was at a conference and had it asserted to me by an Oracleafficiando that Oracle and DB2 handled low-level locks "better" thanSQL Server, and that this was likely the cause of SQL Server'srelatively slower and more deadlock-prone performance when running thesame application. (SQL does seem to perform more poorly for this app,a PeopleSoft customer service and billing app.)Is there any significant difference in lock escalation strategiesbetween major databases, and if so, what are the implications?Jeff Roughgarden, MSCD, MCDBA

View 1 Replies View Related







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