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..
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
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
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 RelatedI 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????
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?
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
How do i migrate Stored Procedures from SQL Server (7.0) to ORACLE (8.0)??
Thanks
Anand
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
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?
Is there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?
I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.
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 ?
Hi--
I am running SQL Server 2005 on Win2k3:
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".
This is how I set up my Linked server:
Provider: "Oracle Provider for OLE DB"
Product Name: SomeServer
Data Source: SomeServer
Provider String: "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"
The query I run is:
Select * from [Someserver].[schema or database]..[tbl_name]
Any help??? What am i missing?
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?
I've developed a website and it's hosted by a hosting company. Lately i've registered a new domain with a new virtual hosting account. I've transfered my web files succesfully. But migrating the sql server failed for a couple of times now. My database containes many tables with many products. My hosting provides doesn't give me the opportuniy to restore my backup which i've backuped from my old server. First I tried to use the import and export tool of sql server 2005. This was very easy and it succeeded. But this tool only migrates the tables and the views. All the common stored procedures of asp.net membership are not exported. So I can't login at the moment. I've tried to generate a sql script with all the stored procedures with sql server management studio. But If i want to run this script on my new db, it fails with various errors. I tried to use the aspnet_regsql tool to create all the membership tables and stored procedures. If this succeeds, I can import and export my old db to the new one. But i'm getting a error while trying to use the aspnet_regsql tool. The error number is 290 and the error is: How can I fix this? Er is een fout opgetreden bij het uitvoeren van het SQL-bestand InstallMembership.sql. Het SQL-foutnummer is 290 en het SqlException-bericht is: Invalid EXECUTE statement using object "Relation", method "SetUseVarDecimal".----------------------------------------Details van fout----------------------------------------SQL Server: Database: [samet]Geladen SQL-bestand:InstallMembership.sqlOpdrachten mislukt:/*************************************************************//*************************************************************//*************************************************************/DECLARE @ver intDECLARE @version nchar(100)DECLARE @dot intDECLARE @hyphen intDECLARE @SqlToExec nchar(400)SELECT @ver = 8SELECT @version = @@VersionSELECT @hyphen = CHARINDEX(N' - ', @version)IF (NOT(@hyphen IS NULL) AND @hyphen > 0)BEGIN SELECT @hyphen = @hyphen + 3 SELECT @dot = CHARINDEX(N'.', @version, @hyphen) IF (NOT(@dot IS NULL) AND @dot > @hyphen) BEGIN SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen) SELECT @ver = CONVERT(int, @version) ENDEND/*************************************************************/IF (@ver >= 8) EXEC sp_tableoption N'aspnet_Membership', 'text in row', 3000/*************************************************************//*************************************************************/IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_CreateUser') AND (type = 'P')))DROP PROCEDURE dbo.aspnet_Membership_CreateUserSQL-uitzondering:System.Data.SqlClient.SqlException: Invalid EXECUTE statement using object "Relation", method "SetUseVarDecimal". bij System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) bij System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) bij System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) bij System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) bij System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) bij System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) bij System.Data.SqlClient.SqlCommand.ExecuteNonQuery() bij System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)
View 2 Replies View RelatedHello!
We are planning migrating to different server.
I know the answers how to migrate all jobs and DTS packages.
But the only question I have is "How to migrate logins and passwords?"
What is the best way to transfer logins?
Can't you just export syslogins table into text file and then import it into the new server?
Thank you
Lena
HI all,
Could any one pls suggest me, how i have to migrate to sql server 7.0 from db2. Pls also recommend some sites where will i get the information regarding this migration.
Thank you all.
---Chowdary
We use MS Access 2000 as our database, but run into lots of proplems. So we decided to research the migration to SQL Server.
I used Access Upsizing utitlity to migrate tables and their data to SQL server very easily, and all the relationships, indecies and other information are converted correctly.
Next is to migrate the queries. I found that to be a pain. Is there any tool out there that can do it for me? Any help is greatly appreciated.
Once that's done, then it's the application itself, but I am not worrying that for now.
Thanks, guys
I have another question regarding migration process to diffrent SQL Server.
Last time when I did migration I didn't restore system databases (master in particular).
I only restored user databases and of course I've got login errors such
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
I just used sp_change_users_login to fix those orphans.
I've read articles in MS knowledge base and not quite sure is this a good idea to restore master database prior to restoring all user databases on the new server in order to keep all logins?
I'll try to use recommended article for transfering logins with password instead of restoring master database from backup
http://support.microsoft.com/support/kb/articles/Q246/1/33.ASP
Any advice is greatly appreciated.
Lena
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 RelatedI would like to know the 'best' way to migrate an existing S2K server database from the 'C' drive to another drive on the same machine.
Thank you
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.
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.
Hello, anyone knows a good way to export a data base from MSSQL Server 2000 to MySQL? Any suggestion is accepted. Thanks
View 3 Replies View RelatedHi,
I will migrate data from a legacy COBOL system to SQL server 2000 Database.
OS: Open VMS
Data storage: RMS files
Any of you has any useful information or links related to this subject?
Thanks,
What is the difference between the SQL used in MySQL and on the MS SQL 2000 server?
Can anyone recommend a good reference on translating a mySQL dump to a format that could be easily uploaded to a MS SQL server?
Thanks!!!!
Hi all,
I have a Database in SQL Server 2000 with several objects (tables, views , stored procedures , functions ).
Is there any specific procedure or tool by which I could migrate the entire database to SQL Server 2005.
Any help would be appreciated.
Thanks,
Hari Haran Arulmozhi
The Setup: I'm moving a SQL Server 7 Database from a SQL Server sp'd somewhere between 3 and 4 (7.00.1028) to a SQL Server without any service packs. The old machine is a Dell PowerEdge 2400 (about 3 years old) and the new machine is a Dell PowerEdge 2650 with dual gigabit nics (and I have them teamed). Both machines run Windows 2000 Server. Mostly default installation on both the OS and SQL Server.
The Problem: When I restore the database to the new server (and resolve the orphaned login issues, etc.) the server hangs unbelievably. Most of my users can't even connect, much less work in their vb apps. I can ping the machine, but can't run the simplest T-SQL against it.
Any ideas?
Help would be greatly appreaciated.
Hope somebody can help a novice, so I can impress my boss.
I've got to move a database to a new server and I'm trying to use Gregory A Larsen's sp_help_revlogin script http://www.databasejournal.com/features/mssql/article.php/2228611.
It works perfectly for all users, but I want to limit it to one database by using his suggestion shown on that page:
SELECT sid, name, xstatus, password FROM master..sysxlogins
Your_db..sysusers b on a.sid = b.sid
WHERE srvid IS NULL AND name <> 'sa'
However, when I make the changes, so it looks like this:
SELECT sid, name, xstatus, password FROM master..sysxlogins a join
int_test..sysusers b on a.sid = b.sid
WHERE srvid IS NULL AND name <> 'sa'
I get this error when I try to run it:
Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Ambiguous column name 'sid'.
Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Ambiguous column name 'name'.
Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Ambiguous column name 'password'.
Server: Msg 209, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Ambiguous column name 'name'.
Can anyone see what I'm doing wrong.
The database that I want to limit the script/procedure to is called int_test on the same server, and I've checked that the table sysusers does exist, and that it has a column called name.
Any help would be greatly appreciated.
Paul
Hello,I am trying to migrate data from a DB2 database to SQL Server 2005database. Does anyone know about any migration tool that does that? Ihave heard about DB2 Migration Tool kit, but I think you can onlymigrate data to a DB2 database with that. Thank you.Sincerely,Eldhose Cyriac
View 2 Replies View RelatedHello,I have got the job to migrate data from an Excel-sheet to asql-server-database. I have a lot of experience with Access butSql-Server is not in my line.So who can give me some advice about how to do this job?thanx for your help, Helmut
View 1 Replies View RelatedI am in the process of migrating from [Source] SQL Server 7.0 sp4 on windows NT to [Target] SQL Server 2005 on Windows 2003 Server.
Please suggest me what should be the migration approach it this case.
Would De-attach and attach of DB still be valid if OS changes? (both are 32 bit OS).
regards
Hello. I am using the built-in SQL Server 2005 Express edition which comes bundled with Visual C# 2005 Express and found that, apparently the separate download of SQL, i.e., 2005 Express Edition with Advanced Services SP2 offers more features.
I wanted to know the following.
Can I somehow migrate the instance of SQL Server Express bundled with Visual C# to SQL Server 2005 Express Edition with Advanced Services SP2?
Or is there a way to have my IDE tie into the other SQL Server natively?
I would like to be able to write distributeddeployable C# applications which can connect to the more full-featured SQL if possible.
Thank you for any suggestions