Import Data From Oracle9i DB
Feb 21, 2007
I hope this is the correct forum to be posting this in but,
we have a database setup on a linux box that we are trying to get rid of and replace w/ sql server 2005 once we have the data off that machine. The problem that we are running into when we try and import data from that machine is, we get the following message during the DTS import / export wizard:
'Error Source: Microsoft OLE DB Provider for ODBC Drivers'
'Error Description: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist'
'Context: Error calling OpenRowset on the provider'
Hopefully this is just a silly mistake on my end but I have been searching around and have not been finding anything too helpful.
does anyone have any advice??
thank you very much in advance
View 3 Replies
ADVERTISEMENT
Jul 20, 2005
Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com
View 1 Replies
View Related
Apr 30, 2008
Sorry If I have posted this in the wrong head,
I would like to Establish connectivity to a Oracle9i DB thro Linked Server, Can any of you guide me to suitable links...
Thanks
View 3 Replies
View Related
May 5, 2004
Will anyone be willing to direct me to a specific kb article for my problem? I've read the following articles: 251248,244661,259959, 280106 regarding troubleshooting a link to an Oracle server from SQL Server.
Our query performed very well (took 1 second) until the Oracle database was upgraded to 9i. To test we have links to both production and test on our test box. We are running SQL Server 2kSP3. Both Oracle databases are at 9i.
The query running against production (which is on a bigger, faster Unix box) runs in 5+ minutes whereas the same query run against their test, takes 1 second.
Using the Oracle client tool, SQLPLUS, the data comes back at lightening speed, but when the data goes through SQL Server, using the link to the Oracle production database, it has horrible performance.
Both database links are created the same. Both links display the table list of tables in Oracle in about the same amount of time when looking through EM. I've verified the presence of the mtxoci81_win2k.reg in the correct path.
The Oracle dba assures me that, other than a larger SGA and similar init parameters (which would be expected for a production environment), the other init parameters are the same. I've supported Oracle over the years (although I prefer SQL Server), and I respect this dba. He is thorough.
I've also changed the Server Options on the database link definition, checking them all and removing them 1 at a time and testing my query at each step. The last thing I'm looking at is the Provider Options, in case those settings are affected by the production Oracle 9i environment. I'm not coming up with much information regarding the Provider Options, hence my question to you.
The only other piece that I can see might be an issue would be that we have our sqlnet.ora file renamed. When present, our links don't work, when renamed, they do.
Any ideas or suggestions would be greatly appreciated!
TIA,
View 1 Replies
View Related
Feb 25, 2008
A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server.
I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard.
However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Error)
Messages
Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
(SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
- Setting Destination Connection (Stopped)
- Validating (Stopped)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Stopped)
- Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped)
- Post-execute (Stopped)
Does anyone encounter this problem before and know what is happening?
Thanks for kindly reply.
Best regards,
Calvin Lam
View 6 Replies
View Related
Oct 16, 2006
I am attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few errors.
Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Could you please look into this and guide me
Thanks in advance
venkatesh
imtesh@gmail.com
View 4 Replies
View Related
Jan 7, 2004
Hello:
I am not sure how to implement the following, but I believe it entails using DTS, and hopefully it is fine that I post it here b/c ultimately I will need this backend data for my frontend .aspx pages:
On a weekly basis, I need to IMPORT some data located on a remote Oracle DB into SQL Server 2k. Since there is so much data to transfer, I would only like to transfer the data that is new to the table since the last IMPORT, i.e. a week ago and leave behin the OLD data.
Is DTS the correct way to go or do I have more control via DTS with STORED PROCEDURES? Does anyone have any good references for me?
On a similar note, once this Oracle data is IMPORTED into a certain table, I would like to EXPORT some of these NEWLY acquired rows matching certain criteria into another table for auditing purposes. For this scenario, should I implement a TRIGGER UPDATE event here on the first table?
Any advice will be greatly appreciated!
View 3 Replies
View Related
Oct 25, 2007
Hello,
i want to import data from an excel sheet into a database. While reading from the excel sheet OleDb automatically guesses the Datatype of each column. My Problem is the first A Column which contains ~240 Lines. 210 Lines are Numbers, the latter 30 do contain strings. When i use this code:
Code BlockDim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & conf_path_current & file_to_import & ";Extended Properties=""Excel 8.0;HDR=NO"""
Dim oConn As New OleDb.OleDbConnection(sConn)
Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [Table$]", oConn)
Dim rdr As OleDb.OleDbDataReader = cmd1.ExecuteReader
Do While rdr.Read()
Console.WriteLine(rdr.Item(0)) 'or rdr(0).ToString
Next
it will continue to read the stuff till the String-Lines are coming.
when using Item(0), it just crashes for trying to convert a DBNull to a String, when using rdr(0).ToString() it just gives me no value.
So my question is how to tell OleDB that i want that column to be completly read as String/Varchar?
Thanks for Reading
- Pierre from Berlin
[seems i got redirected into the wrong forum, please move into the correct one]
View 1 Replies
View Related
Jul 13, 2007
I'm moving data between identical tables and have to use a flat file as an intermediary. I thought: "No problem, SSIS can do a quick export to a file, then move the file to another server, then use SSIS to import the data to the new server."
Seems simple, right?
I'm hitting all sorts of surprising data conversion errors. I used the export wizard to create the export package. This works fine. However using the same flat file definition, the import package fails -- even when I have no destination. That is I have just one data flow task that contains only one control: the Flat File source. When I run the package the flat file definition fails with data type conversion and truncation errors. One of the obvious errors is for boolean types. The SQL field is a bit, SSIS defined the column as DT_BOOL, the output of the data are literal text values "TRUE" and "FALSE". So SSIS converts a sql datatype of bit to "TRUE" and "FALSE" on export, but can't make the reverse conversion on import?
Does anyone else find this surprising? I would expect that what SSIS exports, it can import given all the same table and flat file definitions. Is SSIS the wrong tool to do such simple bulk copies? I'd like to avoid using BCP because this process will need to run automatically within SQL Agent so we can leverage all the error tracking and system monitoring.
View 12 Replies
View Related
Jun 7, 2006
I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios:
Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL.
Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical.
I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN.
The result of this query would be exactly what I need to import.
How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet.
In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server.
I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do.
Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible!
Thanks in advance for your suggestions!
View 9 Replies
View Related
Oct 4, 2007
Hi all,
It looks like these options are only available in the SQL Server Management Studio? I installed SQL Server Management Express Studio and I can't even find the DTSWizard.exe on my machine.
Can you please help how I can import data from excel or where can I download the SQL Server Management Studio?
Your prompt response is greatly appreciated.
Thanks!!
Tram
View 8 Replies
View Related
Sep 10, 2007
I have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column
data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
View 4 Replies
View Related
Jul 29, 2015
I am trying to import data from an excel Sheet to SQL Database using OPENROWSET. After import I found that all the cells containing data of more than 2000 length got truncated to  255 characters only. I tried finding the solution and found that We need to have the data with length more than 255 in first 8 rows of Excel sheet. It worked for me also. But In real scenario the data that I cant do the manual work on excel. I tried out with Dot Net utility and SSIS package also but the truncation is still the issue.
INSERT into tmp_Test
SELECT
*
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel
12.0;Database=D:Book1.xlsx', [Sheet1$])
View 9 Replies
View Related
Aug 10, 2000
I have data for online catalogue in SQL 7.0. The web grogrammer asked me to add a unique key for reference. I used int datatype with identity seed of 1 and increment of 1. This works fine BUT when I try to import new data I get an error because the csv file has no column and therefore no value for the unique field which will not allow null by definition.
How can I maintain a unique field to act as primary key in my data when
I want to add (and delete) data that doesn't have this field.
I tried adding the uniqueidentifier field but this gives error message.
The only work round is to delete the unigue field altogether and then add the new data and afterwards create a new unique field. At 600000 + lines of data, this is time and memory consuming
Any help appreciated,Thanks, Keith
View 1 Replies
View Related
Jan 6, 2007
Hello
Looks like I'am the first one to pop this forum!
But I'am a forward guy so lets get to the problem.
A problem with my replication system as occured.
I have a working SQL server that can do replication trough internet, everyting works
The problem is when I try to import large amount of data (10000 rows) to my database on the SQL server
the subscriber on my client don't get the rows. That will say imported data is not being replicated.
only rows that i have manually inserted will be replicated.
I used the import wizard that came with SQL server.
Is there a solution to this problem?
View 5 Replies
View Related
Mar 4, 2008
Hello all, I am using the Import Wizard to pull in data from an Excel spreadsheet. One column in particular SQL Server sees as a float data type but it contains varchar data. So I change this in the wizard but some of these values are missing when I select * from Sheet1$ in SQL Server 2005. Any ideas why this would happen? I have formatted the particular column as text in Excel.
View 6 Replies
View Related
Jan 6, 2007
Hello
A problem with my replication system as occured.
I have a working SQL server that can do replication trough internet, everyting works
The problem is when I try to import large amount of data (10000 rows) to my database on the SQL server
the subscriber on my client don't get the rows. That will say imported data is not being replicated.
only rows that i have manually inserted will be replicated.
I used the import wizard that came with SQL server.
Is there a solution to this problem?
View 7 Replies
View Related
Dec 28, 2007
I need to import data from a SQL Server (2000) to a SQL Server (2005 Express).I need some data only, and the database structure is different between the 2 databases.How should I do that? What's the best practice?
View 3 Replies
View Related
Jul 28, 2000
Hi!
There are various ways to import data in the sql server enterprise, but i want to import data through code, if it is possible, please help me..
View 1 Replies
View Related
Aug 10, 2000
I'm trying to import data into an SQL Server (7.0) and I'm wondering which Source (Microsoft Data Link, Microsoft ODBC Driver for Oracle, Microsoft ODBC Driver for SQL Server, etc.) -- I THINK we would use the SQL Server driver but I'm not sure... to use and where to go from there? So far, I get seem to get things to work in my favor. I appreciate any help :)
View 2 Replies
View Related
Aug 9, 2000
Working with SQL 7.
I wanted to upsize an Access 97 database (serves as backend of db app so contains tables only) to SQL 7. I used Import Data from Data Transformation Services from the Tools menu. The import procedure worked well except it stashed the tables in the Master db. That's probably because I didn't create a new db to import them into.
How to recover from this is my question. It is a new install of SQL 7.
Thanks
Juan
View 1 Replies
View Related
Oct 28, 1999
Hi!I am using SQL Server 7.0.My query goes as follows
There are two tables Table1 and Table2 both of which have a column companykey
which have PK and FK relationship
I have imported data from a *.CSV file into Table2
Table2 has many to one relationship with Table1 in the sense for each company in Table1 there can be more than 1 employees in Table2
Now I want to insert only the value of column CompanyKey from Table2 to Table1 and also some other columns values like employeekey from Table2 to regiskey column in Table1.
Now how do I do this???
I need this very urgently...
View 1 Replies
View Related
Mar 17, 2004
Hi,
I do a monthly import in MSSQL 2K. The file contains 7.000.000 rows. These rows are loaded in to a special (preload) database. After the import several modifications are performed before it's uploaded to production.
The importfile is a comma-seperated file, fields are enclosed by double-quotes (seems to be an issue for BCP?)
If I'm right, there are 3 options to import:
1) data transformation
2) Bulk insert
3) BCP import
Could someone give me a more detailed explanation what the differences are between those methods? What's the fastest? disadvantages?
Thanks in advance!
View 5 Replies
View Related
Aug 24, 1999
How can i import data from Visual Fox Pro Tables into SQL Server 7.0 using Transact SQL. Thanks.
João Pedro Carvalho
View 1 Replies
View Related
Jan 7, 2007
A problem with my replication system as occured.
I have a working SQL server that can do replication trough internet, everyting works :D
The problem is when I try to import large amount of data (10000 rows) to my database on the SQL server
the subscriber on my client don't get the new inserted rows. That will say imported data is not being replicated. :o
only rows that i have manually inserted will be replicated.
I used the import wizard that came with SQL server.
Is there a solution to this problem?
View 3 Replies
View Related
Oct 6, 2005
We have a database in sql server. Now in the front end, we have two fields called “latefee and latefeeDateReceived”. When next time the people get another late fee. They just replace the old one and enter the new one. But now we want to keep all the history. Keep whatever they get the late fee. I am thing maybe need to create another table called “latefee” to contain these value. Is is good idea? As later they may want to get a report for all the late fee in one company. The thing is, I need to retrieve the current latefee and latefeeDateReceived and import to “latefee” table. But I need to create primary key for the latefee table. How can I import? Only one thing is I can import the data first, then add primary key. If I import later, how can I import. (Explain, the data retrieved have fields called “CID, Latefee, latefeeDataReceived”. But the table “latefee” has four fields called, lateID, CID, Latefee, latefeeDateReceived. When I import, it is always failed. Anybody can give me an idea? Many thanks.
View 2 Replies
View Related
May 15, 2006
Hi everybody,I want to import data from different server.According to my system. when we sell anything to our customer we upload all the information to webserver from our local server. This job we are doing it manually right now. but i want to upload the information automatically. when my agents sell something and they submit as sell i want to send a copy of information to my webserver automatically. Is there any trigger which can insert data into different server or schedule which can import data from different server. Please help me
View 2 Replies
View Related
Jul 12, 2007
I have two databases identical in structure but with different data (staging database and a production database). There is a bunch of data in the staging database that needs to be moved to the production database. The data is in two tables and a foreign key link exists between them. The ID's in the tables of both databases started with '1' and started incrementing, so if I import all the data into production, there will be duplicate ID's.
Is there an easy way to combine these tables? I'm not really a dba, but familiar enough with sql from an admin standpoint.
Thanks for any advice
View 1 Replies
View Related
Jul 19, 2007
Hi All,
I'm working on a DTS to import data from excel files in a SQL database. The thing is, the excel files never have the same structure, meaning, never the same number of columns,sheets as we have to define a destination table and a transformation, do you know a way to do that?
View 1 Replies
View Related
Dec 28, 2003
I am importing data from text files nightly and I am wondering if there is a way to have SQL recognize whether the text file has been updated before the job runs. The text files come over from our Unix box and every once in a while the transfer fails, so when this happens I don't want the SQL jobs to run because I just get duplicate data and not updated data. I don't know if this is possible, but I am just wondering. Thanks.
View 8 Replies
View Related
May 15, 2008
A little background first.
I have been taking Oracle classes to learn SQL structure and statements. I have also used that knowledge to start working with MySQL to create a pricing structure outside of our MS SQL database.
I am obviously still learning how to work with MS SQL Server 2005 and I am starting to see that there a A LOT of differences amongst the three databases.
My question begins here:
1. I want to import an excel spreadsheet to a temporary or actual table newprice (does not matter which, temp or actual). The spreadsheet contains two columns of data, A) item_id which holds all of our SKUs and B) item_prc_new which are my newly calculated and rounded prices. I am dealing with over 13000 SKUs that need to be updated and taking three days and 2 people to enter them one by one is not my cup of tea.
2. Once I have that table in the same database as our production item table (I may be forced to import it into another database named test on the same server instead of the production database as downtime is limited for that database) I want to use this statement to update those 13000 SKUs in the newprice table to the production item table using:
update item set item_prc_2 = test.newprice.item_prc_new where item.item_id in (SELECT item_id from test.newprice.item_id)
What I need to know is:
1. How to see what formatting is set on the item.item_id column so I can match it in test.newprice.item_id
2. The easiest or most efficient way to import that spreadsheet c:ewprice.xls into the test database
Brooks C. Davis
IT AdministratorLogistics Manager SFTF LLC dba Ashley Furniture Homestores
DELL POWEREDGE 2850 Dual Core Xeon x3 = 1xDB 1xSQL 1xTS | DELL POWEREDGE 2950 Quad Core Xeon = 1xTS | SERVER 2003 | MS SQL 2005 | PERVASIVE EMBEDDED V.9
View 2 Replies
View Related
Jun 10, 2008
I am new to programming.i have come up with a porject which i don't know how to solve it
i have to import data from excel file to sqlserver 2005.
The business logic for the import is as follows.
i have one excel file Applicant.xls
appliantid firstName last name etc..
1 , mathew1 , jacob1
2 , mathew2 , jacob2
3 , mathew3 , jacob3
4 , mathew4 , jacob4
5 , mathew5 , jacob5
another excel file cleed jobmatrix.xls
Applicantid jobid
1 , 100
1 , 123
2 , 100
3 , 101
3 , 103
3 , 104
4 , 101
5 , 156
now i have to enter this data in ot 5 different tables based on the following logic.
1, create an account for each applicant (userid autogenerated)
2, create a masterprofile using this userid from useraccount
3, create a application using userid(applicationid autogenerated)
4, create a personal profile for based on applicationid (profileid)
5, create a resumereceived based on the profielid
if the applicant applied for more than one job then application,personalprofile and resumereceived should be created fro each job
that is for applicant one i have to create
1 user account
1 masterprofile
2 applicatins
2 personalprofiles
2 resumereceived
View 1 Replies
View Related
Jan 25, 2007
hi,
im trying to import ther following data into a sql table.
column1column2
23ROW1
45ROW2
796ROW3
45ROW4
WEROW5
FTROW6
HUROW7
78ROW8
once the import completes rows 5-7 return a null value
can anyone help?
cheers
Hazz
View 4 Replies
View Related
