I am racking my brain on this one. I am migrating Oracle data to SQL Server and would like an SSIS package that will copy the oracle data from every table to SQL server. I already have the schema set up in SQL and a straight copy of a specified table works fine between the OLEDB source and OLEDB destination. However, when I set up a loop which sets a global variable for the table names, which are identical on both dbs, and try to set the OLEDB Source and Destination OpenRowsetVariable to the global variable, the process fails with these errors with all validation turned off:
[OLE DB Source [18]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
[OLE DB Source [18]] Error: Column "ID" cannot be found at the datasource.
[DTS.Pipeline] Error: component "OLE DB Source" (18) failed the pre-execute phase and returned error code 0xC0202005.
To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?
I have to copy a large (3000) amount of different tables from a Oracle machine into an SQLServer machine. I am able to do this using a (VB) script. I use now several methods:
1) INSERT INTO TABLE1 SELECT * FROM SID1..DB.TABLE1 (SID1 is a linked server)
2) INSERT INTO TABLE1 SELECT * FROM OPENQUERY(SID1,'SELECT * FROM DB.TABLE1')
3) Also used OPENROWSET method (similar to 2)
For small tables this is fine, however for BIG tables (15M Rows/150Cols) the methods above are too slow. If I compare the same copy action with a simple DTS, the DTS is 3 times faster. Also, the DTS seems to bulk copy the data directly into the desired database while the mentioned methods first fill the tempdb, then the transaction log of the desired database and then finally the desired table (need very much extra space on your filesystem). The total size of data is about 300GB.
Can anyone supply me with a simple example how to copy data from an Oracle table into a SQLServer table in script (or SQL) that is as fast as the DTS and not filling my logfiles?? I read the bcp (which I use for import/export files) and bulk insert commands, but I do not understand how to use them in this question.
I have a oracle database that creates a table (to store call records) everyday and places data into it. How can i use Interation services to transfer the newly created tables to an SQL server database (SQL server 2005).
Hi, I am trying to use SQL Server 2005 Enterprise Edition to copy tables from my local server to my hosts server at brinkster.com. In the old version you could use DTS, but everything seems to have got far more complicated now! Please can anybody guide me in the right direction? Thanks a lot.
Hi all, I am using Visual web developper 2005 with sql server express 2005 and i have also sql server management studio express. it's all free now . my web site is ready I didn't have problem to upload my site to my hoster. Now I want to upload all my tables and my stored procedure create locally with VWD express How can i do it ? NB: I know i can't design DB (create/modify tables and stored proc) with express edition thank's for your help
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 ?
I've tried creating a simple SSIS package but I get the error:
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
THis is a known bug and while we wait for SP1 to fix this error, is there another way to simply copy tables (with indexes and without having the identity column renumbered) ?
i want to copy some tables from 1 database to another with its data & procedures etc to another database in Microsoft SQL 2005. can any one help plz reply tnx alot
Dear All, I am working on a project to migrate a .net desktop application to web based application using ASP.NET 2.0. The present app has lots of tables with data in sql server 2000. The new app is to have Oracle 10.2 G as data store. Although the data store is being changed most of the tables in the present database will remain same along with the data they hold. My problem is: 1) how to transfer the tables to the new datastore 10.2 G in an automated way? After all it's not possible to create each table afresh in Oracle and then insert records into each table one by one. If that is done it will take atleast few months if not years. 2) can I export the tables to a xml file and then import it from Oracle.How to do that? It has become a nightmare for me. Pls suggest the easiest way and which takes the minimum time. A solution to the problem will be gratefully accepted. Thanks.
My manager told me to link SQL Server database tables to access so that he can access the tables in MSAccess to do his SQL queries. I am thinking of linking server but I am not sure about that. Is someone can tell me what to do and explain me how. Thanks in advance Sincerely. David
We are running SQL Server 2000 SP3. We have linked servers in use thatwe use to access Oracle tables.Recently the claim has been made that you can access Oracle tables fromwithin SQL Server without using a Linked Server. I searched BooksOnline using keywords: linked, remote, and Oracle and did not findanything.A search of the newsgroup archives found only entries related to usingLinked Servers.Is there any such method as claimed? I do not think there is, but Ineed to find some support for my position or else learn something new.Thank you-- Mark D Powell --
Hi I'm new in SSIS I'm trying to create SSIS that can copy tables,permission,index,constrant... to a sqlserver2000 from sqlserver2005. Can any one help stepbystep.
I have a SQL Server 2005 evaluation that has already been installed and setup on a server. I believe it originally had a 180 day eval. There have been numerous databases and users added as well as maintenance plan created...
The eval was put on the machine as an interim solution while waiting for paper work and order processiing things to happen. All the paperwork and ordering... have been completed and I now have the real SQL Server 2005 Standard Edition license key ....
I am very new to SQL Server and need to determine ...
1. Can I update the eval copy to become permanently licensed?
2. Would I want to upgrade the eval to permanent? Will I lose any capabilities by keeping the current eval setup?
If it is reasonable to keep the eval setup
3. How do I go about entering the license key to make it permanent?
If it is necessary to install the new Standard Edition...
4. How do I install it while maintaining the already defined databases, data, users, maintenance plan...
We currently have a PPTP connection set up for our developers toaccess our development SQL server through a VPN tunnel. When theyneed to copy tables up to the dev SQL from their local machine theysimply do a DTS copy.However, we are now moving to a thin client solution where they willbe working on a terminal server. They will have access to thedevelopment SQL servers and SQL tools such as EM and QA. However,they will not have access to their local SQL server and, therefore,will not be able to directly perform DTS copies. We have exploredseveral possibilities such as exporting tables to a .csv or .mdb fileand then importing them on the development SQL server but this is notideal because things are lost in that process (e.g. primary keys,field names, data types, etc.)My question is this: Is there a way to export and then import SQLtables without losing dependent objects such as primary keys and datatypes in the process? If any of you are working with a similarsituation I would really like to hear how your remote users copyobjects from their remote location to your SQL servers. Thanks!Ryan--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict211310.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=722630
I've got a table adapter that connects using an oracle data connector. In the adapter, I'm using native oracle SQL such as:
select TO_DATE(SUBSTR(TO_CHAR(weird_oracle_field),0,12),'YYYYMMDDHH24MI') as dt_added from oracle_data_table
There's also a CASE statement in there with some other data transformations.
Anyway, I want to take the results of that Oracle query and put the dataset into a SQL Server Compact Edition database - within an application that I'm creating in Visual Studio 2005.
For whatever reason, I can't seem to do anything like that in 'bulk' and there aren't any data migration tools that work with anything other than "full" SQL Server versions. My client doesn't support SQL Server, but I can deploy my app with SQL CE. I need a 'local' copy of the database (for several reasons) and just can't seem to figure out how to make this work.
I'm really going nuts. I feel like I'm soooo close when I see the data I want in the table adapter - but I can't seem to actually *move* the data over!!
I would like to export all tables from Oracle 11.2 to MS SQL Server 2012 R1.
Using the tool "Microsoft SQL Server Migration Assistant v6.0 for Oracle" did not work for me because there are too many warnings and errors regarding the schema creation (MS cannot know it because they are not the schema designer). My idea is to leave/skip the schema creation to the application designer/supplier and instead concentrate on the Oracle data export and MS SQL data import.
What is the easiest way to export all tables data from Oracle to MS SQL Server quickly?
Is it:
- the „MS SQL Import and Export Data“ Tool - the “MS SQL Integration Services” Tool - not Oracle dump *.dmp format because it is a propritery binary format - flat file *.csv (delimited format)
I would like to know if is possible copy data from a database located in my hosting to my database located in my pc,but using a store procedure,how can i do that ?.What tool i have to use?
I'm using SQL Server Management Studio Express and I'm trying to figure out how to copy a table(s) from my local database to my web hosting database. I know how to do it in 2000, but it's completely different now. Is this feature not allowed on SSMSE? If so, then how do I deploy database tables to a web host?Also, how do you add local database(s) to SSMSE? I tried to use 'attach database' in SSMSE and it wouldn't allow me to navigate to My Documents folder where the database resides. Thanks...
1)I created a linked server to a oracle database, works fine. Now I am writing queries, using "Query Desgner", when I do a "Add table" linked server tables do not appear. Is this a limitation. I can get around the problem by creating a view.
2) When I use "Microsoft OLE DB Provider for Oracle" for linked server, some times connection seems to get lost. On one occassion, the next day it was fine, it re-established itself.
I have more than 8 GB Data in oracle and Everyday we have to check some data in oracle but it takes times due to lot of data. So what i was looking that data that we need can import in SQL Server database and do checking purpose. But I was looking some command that connect to oracle brings its data and import in SQL Server. For that we will fire SQL statement to oracle through asp.net and insert into SQL Server I will fire differencial data insert covery only. is there any way or idea to do that please advice me.
Anyone know where I can find some good resources to help us choose betweenSQL and Oracle ( Progress Openedge as well ) . Any comments on what youwould choose ?? We are creating a new Warehouse Management System which wilmanage our very large inventory.Anyway comments suggestions welcomeThanksPaul
I am trying to import an Oracle .dmp database into SQL Server 2005, what would you suggest as the fastest and easiest way to do this? DTS/SSIS doesn't have an instant solution and the script I tried to use gave me an error...
Msg 3241, Level 16, State 0, Line 1
The media family on device '<DATABASE>' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I would like a GUI based way to do this if at all possible. Thanks -Kyle
I'm relatively new to DTS and I was trying to import data from the oracle database to SQL server database based on some parameter from the destination table. i.e select * from SourceTable where SourceTable.Column1 > (select max(Column1) from DestinationTable) This tries to search DestinationTable from the source database(Oracle) How shoud I go to solve this problem? Any Hint is greatly appreciated....
OK new Windows 2003 Enterprise 64-bit with MS SQL Server 2005 64-bit.. Now I am migrating the DTS packages from our olld Windows 2000 Standard with MS SQL 2000 (all 32-bit). I am not moving the packages to SSIS yet just want to move the legacy DTS jobs over... However whenever I try and connect to the Oracle DB I got ORA-12154: TNS:could not resolve the connect identifier specified.
I have the Oracle 10g 32-bit & Oracle 10g 64-bit clients installed and I can run SQL+Plus and do everything just fine. It is just in MS SQL 2005 I can not. Even in SSIS I get the ORA-12154 error. I can create an ODBC and test it and it works just fine... Has anyone here ever fixed this? Some say it is b/c of the ('s around x86 in program files and I have done their suggestion in making a ProgramFilesx86 directory and copy the visual stupid & sql directories there... still nothing. I have one of my DBA's searching Oracle for any patches but thought I would ask and see.
Does anyone have some more detailed information about how Oracle and MSimplement / allow Tuning on Oracle 10g and SQL Server 2005 and thedifferences between them?Which of them, In a deep comparison about it, allow better tuning andwhy.Regards,Marcio Evangelista
