HELLO FRIENDS I AM TRYING TO USE IMPORT EXPORT WIZARD FOR CONNECTING TO THE ORACLE DB. I AM USING ' MICROSOFT OLEDB PROVIDER FOR ORACLE' AS DATA SOURCE I AM GETTING AN ERROR: ERROR: ORA-12504: TNS: listner was not given the SERVICE_NAME in CONNECT_DATA. AM I USING CORRECT DATA SOURCE PROVIDER? SECOND THING IS WHAT IS THE POSSIBLE REASON? HERE IS THE tnsnames.ora entries: MOCTST.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 11.10.92.134) (PORT = 1521) ) ) (CONNECT_DATA = (SERVICE_NAME = MOCTST) ) ) I HAVE ONE MORE CONFUSION THAT AFTER SELECTING ' MICROSOFT OLEDB PROVIDER FOR ORACLE' IN THE CHOOSE DATA SOURCE IN IMPORT EXPORT WIZARD WHEN I GO IN PROPERTIES IT ASK ME SERVER NAME I AM PUTTING 11.10.92.134. iS THAT ALSO OK OR NOT? IT DOES NOT ASK FOR SERVICE NAME THEN WHY I AM GETTING WORD SERVICE NAME IN THE ERROR? THANKS IN ADVANCE dimrd_SQL

I would start with creating an ODBC DNS to figure out if you can connect to your data source at all. In some cases the Oracle connection is misconfigured, typically it would need file sqlnet.ora configured, too. The file is in the drive:\oracle\ora92\network\ADMIN (ora92 may be something else in your case)Arthur My Blog

thanks arthurz i will try . but one more question i am really new in this task . and tell me how should i create an ODBC DNS to figure out if i can connect to your data source at all? Thanksdimrd_SQL

How to create a DNS: http://www.truthsolutions.com/sql/odbc/creating_a_new_odbc_dsn.htm you may want a System DNS.Arthur My Blog

ARTHURZ I TRIED THE SAME WAY AS YOU SAID IN THE LINK PROVIDED BUT IT DID NOT ASK FOR ANY TEST CONNECTION LIKE IN THE LINK IT SAYS I JUST CHOSE 'MICROSOFT ODBC FOR ORACLE' IN SYSTEM DSN THEN DATA SOURCE NAME = MOCTST USER = MIC NOTE THAT THE USER NAME IS NOT MY NAME ITS THE USER WHICH EVERY BODY FROM APPLICATION SIDE USE. I M DBA BUT WORK WITH APPLICATION TEAM OF ORACLE. IT IS NOT MY NAME. IS THAT CAN ALSO CAUSE ISSUE? i AM ALSO USING TOAD TO CONNECT TO THAT DB AND LIKE OTHER APP USERS I ALSO USE THE SAME USER. I AM GIVING SERVER=11.10.92.134 SO WHY I AM NOT GETTING TEST CONNECTION SCREEN? THANKS dimrd_SQL

You should use the user name you was told to do. Once you have the DSN configured you must be able to test it. Please see the link that is more specific: http://www.osgeo.org/files/fdo/docs/FET_TheEssentialFDO/files/WS1a9193826455f5ff14f6e3f110bc356094-7879.htmArthur My Blog

thanks it solved my purpose to some extent yet test db is not reachable as its on 8i but production one is reachable its on 10g. I just downloaded oracle client 9.2. Its ok with 10g but not 8i. But i have one problem as when i am using import export its not changing the data types. So i a m thinking to use SSIS packages. But i do not have any knowledge of using transforms. Please help me or point me to the direction for using SSIS???? But i really appreciate i learnt STh from you.. dimrd_SQL

Could you elaborate a bit more on details of what you need to achieve please? Just a wild guess - your data types are different, if so, output the data first from Oracle to a flat file, then read from it into your SQL Server. Arthur My Blog

Yes Arthurz Most of the datatypes which are on oracle side are DATE, NUMERIC AND VARCHAR2 thats it. I tried using a SSIS package i was using Oledb source and oledb destination. while configuring the properties i selected OLEDB connection manager for oracle when i am selecting on OLEDB Source Editor the choice columns after connection manager i am getting a warning that says [OLEDB SOURCE 1]: cannot retrieve the column code 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 components localeID will be used. So that is the warning now please point me to the direction i follow for migrating data to sql. thanksdimrd_SQL

To resolve this, right-click your OLEDB Source, set the property "AlwaysuseDefaultcodepage" to true.Arthur My Blog

Thanks That is resolved but i am worried about how should i tranform the datatypes? second thing is i just ran this package without using the transformation and i am getting very weird name of the Table. eg: In oracle a table is A_returns but when it comes to SQL side its SOM\MY name.A_returns. So now i have two issues one how to do transformation and that name issue. In oracle tables ate under schema like mic. Thanksdimrd_SQL

Please make it a new post describing what are the issues.Arthur My Blog

ok thanks it will be under datatype tranformation issue in oracle to sql migrationdimrd_SQL

ARTHUR THIS IS THE NAME OF THE LINK HELP MAN I AM GETTING ERRORS 'datatype tranformation issue in oracle to sql 2008 migration using SSIS' THANKS dimrd_SQL