I am trying to import application tables from oracle but they do not show up in the list of tables. I suspect that this is because the tables are owned by System. Is there any way other than changing the owner to get these tables to appear in the import list?
Thanks,
Ken Nicholson
We are in the process of supporting two databases (Oracle 10g, SQL Server 2005) for our application.
I want to know what is the equivalent Tables/Views in SQL Server for the Oracle System tables dba_tab_comments, dba_tab_cols
Thanks in advance
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 am trying to import 6M records in a table from an Oracle 10g databaseinto a SQL Server 2000 database.After importing 1.5M+ records I get the following error:Ora-01555: snapshot too old: rollback segment number 129 with name"_SYSSMU129$" too smallHow can I speed up the import process between Oracle and SQL Server?The database box I'm using has 12GB of memory and 1.5 TB of space. Itshouldn't be a hardward problem. :-)Thanks in advance.
View 2 Replies View RelatedI am trying to import a variety of data from an Oracle 10g
database. When I import the data using the OLE DB it takes a long
time (100K records an hour).
How can I improve the throughput of my import?
Is there a better driver I could be using?
Any input will be appreciated.
db55
Hi all,
Please let me know if it is possible to import an Oracle .dmp binary file into SQL using DTS. DTS has ODBC and OLE DB drivers for Oracle but both require specifying the Oracle database name, user name and password. i.e., it looks like the only way to import the .dmp file into SQL would be to first load the .dmp file into Oracle and then tranfer it from Oracle to SQL using DTS. Is there any way to load the .dmp file directly into SQL?
The .dmp file is a binary file that has been generated from Oracle using the 'Export' utility.
Thanks in advance,
-Praveena
hi,
this is my current set up:
sql server 2005 express edition,
sql server management studio express,
oracle tables (original source data currently linked through odbc)
adp database file (new db where i'm trying to import the above oracle tables)
i need to import (or make copies) on a weekly/daily basis, of several oracle tables into a new adp database.
what is the fastest option? some of these tables hold over 1m records.
i have also heard of "linked servers" and "dts", but would like some experts advice before starting looking into this.
many thanks
luzippu
Hello all, I hoping to get some input on how to get around this odd problem. I use the SSIS Import Wizard to create a package that imports data from an Oracle 9 database into SQL 2005. One of the 'columns' selected from Oracle is actually two floating point columns multiplied together.
ie: SELECT one * two FROM table
In the SSIS wizard the data preview for this shows up as it should, with the column data in floating point form. However after actually importing the data, all the numbers get imported as integers even though they are going into a floating point column.
Any ideas how to get around this?
Thanks
I have SQL Server 2000 and am using DTS to simply import some data located on a remote Oracle DB.
If I have the following information:
TNS Name:
ThisApp.world
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcpxxx.world)
(PROTOCOL = TCP)
(Host = SomeHost)
(Port = 1234)
)
)
(CONNECT_DATA =
(SID = App)
(GLOBAL_NAME = ThisApp.world)
)
)
And I also have the name of the Table.
Using the DTS Wizard, on the Choose a Data Source screen, I pick the following:
Data Source: Microsoft ODBC Drive for Oracle
Server:
Username:
Password:
I don't know what I should put into the other fields, given what I have above, i.e. what to put in for Server, Username, and Password! Or should I use a different procedure to import this Oracle data? I am almost positive I was not supplied the username/password. I was told I could connect given the above TNS information. What do you all recommned?
Your assistance will be greatly appreciated.
Hi,
I created SQL Server package and schedule the job. SQL Server allows us to connected different database for e.g. for oracle using "Oracle Provide for OLD DB" to retrieve our database. A link is create between this database which move the data to SQL Server. I'm sure there is no issues in the link, as I'm using it to retrieve several oracle database which contains both Arabic and English data.
But after Importation it is showing Junk values. Please advise me what step I should take next.
Regards
Azeem
Hi,I have a few things on my databases which seem to be neither true systemobjects or user objects - notably a table called 'dtproperties' (createdby Enterprise manager as I understand, relating to relationship graphingor something) and some stored procs begining with "dt_" (some kind ofsource control stuff, possible visual studio related). These show up whenI use"exec sp_help 'databaseName'"but not in Ent. Mgr. or in Query Analyzer's object browser, and also notin a third party tool I use called AdeptSQL. I am wondering how thosetools know to differentiate between these types of quasi-system objects,and my real user data. (This is for the purpose of a customized schemagenerator I am writing). I'd prefer to determine this info with systemstored procs (ie sp_help, sp_helptex, sp_...etc) but will dip into thesystem tables if needed.Thanks,Dave
View 1 Replies View RelatedHi everybody,
View 13 Replies View RelatedI am currently trying to migrate a excel file into SQL 2012 database on an Oracle Virtual Box VM. I am new to SQL and this type of IT in general.
View 3 Replies View RelatedOK so there is some data in an Oracle DB that I have to summarize based on grouping info stored in a SqlServer DB. How can I import the Oracle data into a SqlServer temp table using SqlServer Express? Thanks.
View 2 Replies View RelatedI am trying to import tables from my access database.
1st trying simple table, In ent mgr i right clicked the db folder
all task
import data
data source : Microsoft access
file name : c:Data Management SystemDMS.mdb
User name : mike (same used for lonin in access)
password : (same used for lonin in access)
advanced button:
jet Oledb: System database c:Data Management SystemSecured1.mdw
windows authentication
server (local)
database New -> dms
copy tables
now i see the items in my access database so i am guessing every step so far was correct
hey it worked !
ha,, hope u dont mind if i leave this here for anyone else, i ansd my own ?
I need to implement a file system for an application that allows me to roll back to a point in time. I can do this with either a journaling file system (Unix based such as JFS) or with a database file system such as Oracle Internet File system (now Oracle Content Services). I would MUCH prefer to use SQL Server but cannot find anything that supports this other than a 2000 ppt referencing the then up and coming SQL Server .NET File System.
The application(s) in question are older and store data in proprietary data files and need to access a local (or mapped) drive in standard form (d:programsmyprogram).
Does SQL Server 2005 or 2008 support this type of access? I have searched but cannot find anything to support this.
Thanks.
B.W.
Hello,I work for an attorney staff, so basically the whole day they aresending themselves a lot and a lot of papers (Word docs or Excellsheets).We are going to propose them the use of a Document Exchange andDatabase System (like Lotus Notes or Oracle Managment Content,formerly known as Internet File System IFS ) with .NET interfazes.Basically the idea is that instead of sending papers the whole day,they can use a software to check and manage more quickly theirdocuments.We are looking for a system that allows us to manage:- Security- Versioning- Folders- Check-in/Check-out- Workflow- Search and Retrieval- Extensible MetadataMy question is:1) Is there any Microsoft tool who can help us to achieve that(management of codes, dates, text, names of documents- I mean like aDocument Exchange and Database System) with .NET interface?2) Or do we have to built it from scratch?Thank you very much!Jorge
View 4 Replies View RelatedHi, I've just got my brand new shiny SQLServer and would like to set some new tables set up etc. But I'm really lazy and I was wondering if there is any way of importing table layouts, coulmns, whole structure type thing into SQLServer from Microsoft Access using DTS? I know it's a bit optimistic, but just wondered....
Any help gratefully accepted.
Thanks,
Paula.
Hello,
Am very conversant with MS SQL 2K, however i just downloaded 2005 express edition and will like to know how to
1. How to import tables using DTS graphic view to Management studio
2. How to get to Query Analyzer ?
thanks
Mary
Yes O !
Hello Everybody,
Not sure if this is the correct Forum, anyway lemme me know your views. I
have SQL Server running in two remote machines (in different geographical
locations). I need to import a database from one SQL Server
installation to another installation. What is best possible solution:
1) Transfering the .mdf and .ldf file from one installation to another installation
2) Generating the records of all the tables in the database to a common
file format CSV and then loading it in destination database. If this is
ok then how to generate import and export data using CSV format
3) Any other options?
Srikanth
I have created a simple package that uses a sql command to pull data from an oracle database and inserts the data into a sql 2005 table. Some of the data fields that i am pulling from contain two digits after the decimal point, however this data is lost when it gets into sql. I have even tried putting the data into a flat file, and still the data is lost.
In the package I have a ole db source connection which is the oracle database and when i do the preview i see all the data I need. I am very confused and tried a number of things to get the data into sql, but none work. Any ideas would be very helpful.
thanks
Hi,
I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.
Please let me know.
Thanks
HiI am new to SQL and am having problems importing data from two tables to one.I have 3 tables : Franchise, Club and Reference.Franchise has FranchiseID as Primary key and FranchiseNumber as unique.Club has ClubID as Primary key.Reference has a combination of ClubID and FranchiseNumber as primary key. I am not able to insert data into table Reference from Franchise and Club. Any help or suggestions would be highly appreciated. Thanks in advance.
View 2 Replies View Relatedhello, i have a problem after importing my db tables to the aspnetdb the default database that come with visual studio i put my tables into that db but i added some columns to the aspnet_users and i made a relation between my tables and some of aspnetdb tables the problem is when i drag and drop a grid view and manage its datasource i cant find the columns that i add to the table? second problem is when i press the advanced button in the datasource the two checkboxes are not active i dont know why and i clicked on the column that has the primary key but i still have the problem i cant update or delete records and i tried on the original aspnetdb users table(without my columns or anything) also found that two check boxes not active? is it a problem in the aspnetdb or what? there also something strange when i choose the tables in the sqldatasource the aspnetdb tables names are appear like this : vw_aspnet_roles vw_aspnet_users ...etc they all has that vw word dont know why it also exist in the original aspnetdb, so hope anyone can help cos if it's no solution for that problem i will have to build my db again from the begining ,hope u reply soon.thanks
View 10 Replies View RelatedHi everyone,
I'm making an application in Delphi that use an SQL database, i want the user can import a table in DBF format (that its located in another computer) to the SQL Server (in the database that i want) by one click of a button.
I try with the import wizard but its not what i want because it requiere to do the process manually.
Its there anyway of achive this (Stored Procedure or something?)
Ernesto Cisneros Sanchez
Senior Software Engineer
Credit and Trade Bank
Havana, Cuba
I am having a problem to get all datas into one table
I must get datas from 2 other tables
datas from the first table are columns
but datas of the second table are rows
I cannot change the structure of TableA because an other application is using it as it is
TableA
num | value | key
1 | a | 1400
2 | b | 1401
3 | c | 1402
4 | d | 1403
...|... | 1403
TableB
key | name | descr | value
400 | john | ok | 451
TableC (the table where I want to insert datas from Table1 and Table2)
val1 | val2 | val3 | val4 |name | descr | value
1 | 2 | 3 |4 | john | ok | 451
TableA has thousands of rows and different numbers
I must get 8 different datas from TableA
so I get an horrible Query !
SELECT
TableB.name, TableB.descr, TableB.value,
TableA_1.value AS val1,
TableA_2.value AS val2,
TableA_3.value AS val3,
TableA_4.value AS val4,
TableA_5.value AS val5,
TableA_6.value AS val6,
TableA_7.value AS val7,
TableA_8.value AS val8
FROM dbo.TableB INNER JOIN
dbo.TableA_1 ON TableB.key = TableA_1.key INNER JOIN
dbo.TableA_2 ON TableB.key = TableA_2.key INNER JOIN
dbo.TableA_3 ON TableB.key = TableA_3.key INNER JOIN
dbo.TableA_4 ON TableB.key = TableA_4.key INNER JOIN
dbo.TableA_5 ON TableB.key = TableA_5.key INNER JOIN
dbo.TableA_6 ON TableB.key = TableA_6.key INNER JOIN
dbo.TableA_7 ON TableB.key = TableA_7.key INNER JOIN
dbo.TableA_8 ON TableB.key = TableA_8.key
WHERE TableA_1.num = 145
AND TableA_2.num = 80
AND TableA_3.num = 3160
AND TableA_4.num = 41
AND TableA_5.num = 50
AND TableA_6.num = 51
AND TableA_7.num = 53
AND TableA_8.num = 56
how can i do it in the best way ?
Thank you
i'm importing tables from another sql server -
now the tables are coming is as
esther.tablename (my username on the other server)
instead of
dbo.tablename
how can i change this?
I'm trying to input a few thousand flat files into a few thousand tables in a sql databaseim using integration services with a for each loop to read all the files in a directorythe problem is i can only insert the data from all the files into one tabledoes anyone know a way to do multiple tables? maybe using some sort of variable?
View 1 Replies View RelatedI'm trying to input a few thousand flat files into a few thousand tables in a sql database, using SQL Server Business Intelligence Development Studio.
im using a for each loop to read all the files in a directory
the problem is i can only insert the data from all the files into one table
does anyone know a way to do multiple tables? maybe using some sort of variable?
Hi,
I hope you can help me.
I've got a csv that I need to import. It's about 74 columns wide and 2500 rows long. What I need to do is select the first 3 columns from the columns from the csv and put them into one table and then put every 7 columns (plus the 1st column as the primary key) into a different table.
I've not explained that very well, basically it's like this...
A B C D E F G H I J K L M N O P
1
2
3
4
5
Table 1 Data
cs# A1
fe B1
cl C1
Table 2
cs# A1
desc D1
date E1
pay F1
amount G1
vat H1
total I1
cReq J1
I'm not really sure were to start. I want to import the data nightly into the tables. I've tried to use the import wizard but I can't seem to select the data I want and exclude the data I don't.
Steve
Hi!
View 36 Replies View Relatedhi. this is a beginner question as i am new to databases :)so, I have an access database and I want to take 2 tables from it and copy it to sql server in my ASP.NET application (.MDF file)I have visual studio 2005 installed on my computer and got no sql server 2005 installed on it. what is the way copy 2 tables to the ASP.NET database application?thanks alot guys.
View 2 Replies View RelatedHello,
I'm fairly new to MS SQL & I have a problem copying tables from one d/b to another. When I do an import or export select Copy tables, the tables & the data get transferred, but all the Identities are set to NO. My D/Bs have 188 tables & it's a pain to reset all of them manually.
Is there a setting I don't know about that will transfer the tables & maintain the Identity?
(I'm using SQL Server Management Studio 9.0)
Thanks for your help - Breck Cogdill