Oracle To Sql Server Conversion
Jan 31, 2006
Something went a bit wrong. Converted Oracle 10g to Sql Server 2000.
Now, in the schema there is a column that allows nulls.
In Oracle if you look at the column, you see NULL.
However if I look at the same created in Sql Server it actually does not have a <NULL> value there.
When the conversion was done however, all of these NULLs from Oracle came across into Sql Server with the actual value of <NULL>.
This causes the problem that these objects are now no longer displayed within my application. They show up fine using Oracle, and if I use Sql Server from the start it is fine -- the column is just blank, not acutally NULL. I can force the <NULL> value by doing the ctrl+0 on the field, and that breaks it as well.
The column has to allow nulls, but the actual value cannot be <NULL> (in Sql Server). Any suggestions on getting rid of the NULL - I could do an update, but it actually just has to be blank rather than having a value. I tried an update to set it to ' ' but that didn't really work - here was my statement:
update [table] set [columnname] = '' where [columnname] = '<NULL>'
Any other suggestions besides try again? But if it has to be 'try the conversion again' then that's the answer.
Thanks much
View 1 Replies
ADVERTISEMENT
May 9, 2001
I am in the process of trying to create a mirror image of our Oracle database in SQL Server. I have all of the code that was used to create Stored Procedures on the Oracle side. I am having a lot of trouble trying to figure out what the corresponding syntax to create these procedures in SQL Server would be. Has anybody been able to locate any sources of information that would help me or had any experience with this problem? I would appreciate any help anyone can give me.
Specifically I am looking for help on the following items:
Oracle Sql Server
RETURN NUMBER ?
IS ?
BEGIN ?
SELECT FROM DUAL ?
EXCEPTION HANDLING ?
Here's some of what I have so far:
Oracle SQL Server
CREATE OR REPLACE PROCEDURE CREATE PROC AS
Variable_name @variable_name
number numeric
dual #temptable
Thanks!
View 2 Replies
View Related
May 18, 2005
Hi,
I've got a couple of snippets of code that I am not sure how to convert. Any thoughts?
Thanks,
Steve
====================================
Select
to_char(AM_VEST_YEAR0, 'FM999,999,999,999,990') ||'|'||
to_char(AM_VEST_YEAR1, 'FM999,999,999,999,990') ||'|'||
to_char(AM_VEST_YEAR2, 'FM999,999,999,999,990') ||'|'||
to_char(AM_VEST_YEAR3, 'FM999,999,999,999,990') ||'|'||
to_char(AM_VEST_YEAR4, 'FM999,999,999,999,990') ||'|'||
to_char(AM_VEST_YEAR5, 'FM999,999,999,999,990') as data_row
=====================================
from
VEST_SCHED,
(select to_date('31-DEC-'||to_char(to_number(to_char(sysdate, 'yyyy'))+1), 'dd-mon-yyyy') as max_date
from dual) b,
(select to_date('1-JAN-'||to_char(to_number(to_char(sysdate, 'yyyy'))+1), 'dd-mon-yyyy') as min_date
from dual) c
where
OPTS_CANC < '1' and
VEST_DT BETWEEN c.min_date AND b.max_date and
emplid = '[CURRENT_EMPLID]'
group by
emplid, GRANT_NUM
View 1 Replies
View Related
Nov 9, 2004
Hi Guys, I have this statement that I am converting from Oracle to SQL. Help pls:-) PP_PRICEPOINT_ID is a decimal. What is the appropriate usage..
Oracle
-------------
update pricepoint set pp_type = decode(substr(pp_pricepoint_id,1,1),7,0,2),
pp_qtybreakindex =substr(pp_pricepoint_id,3,1) where pp_type is null and pp_qtybreakIndex is null;
Here is its SQL
-----------------
UPDATE pricepoint
SETpp_type =
CASE SUBSTRING(pp_pricepoint_id, 1, 1)
WHEN 7 THEN 0
ELSE 2
END,
pp_qtybreakindex = SUBSTRING(pp_pricepoint_id, 3, 1)
WHERE pp_type is null
ANDpp_qtybreakIndex is null
----------------
I am getting the error
The data type decimal is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.
View 3 Replies
View Related
Sep 12, 2007
Hi Friends,
Need ur help desperately. I am stuck with one of the queries which i had written in Oracle and need the same in SQL Server.Please have a look at the following query :
select * from r_tin_1099_info where instr(translate( nm_ctrl_cd , '~!@#$%^&*()_+}{":?><`-=]['''';/., ', '*******************************' ),'*') > 0;
Basically my purpose is to replace the values in column NM_CTRL_CD having wild card characters with '*' and then select this rows to display.
However i am not able to run the same query in SQL Server since TRANSLATE is not a built in func. I have tried a lot to replace it but could only one func : REPLACE . But the same will not replace any one of the above wild characters but will replace the entire pattern.Please note that it should be able replace even if one of the wild card characters are present in the string and not necessarily the entire pattern shown above.
please reply ASAP since i am working and need this query to fix a defect.
Thanks in advance.
View 9 Replies
View Related
Mar 12, 2008
Hi Gurus,
I need to convert this statement to sql server.
SELECT to_date('24-08-2007 13:11:12','dd-mm-yyyy hh24:mi:ss'),to_date('24-aug-2007 13:11:12','dd-mon-yyyy hh24:mi:ss') from dual
can anyone help please.
View 1 Replies
View Related
Feb 15, 2000
Hello Everyone,
First problem :
I have an application which uses Power Builder and Oracle and my job is to convert it so that it can run on a SQL server database.
Oracle allows empty strings - '' - to mean NULL. If the application inserts an empty string in a column of a table, Oracle takes it to mean NULL.
How can i have this functionality in SQL Server?
Second Problem:
SQL Server does not allow stored procedures like sp_addlogin and sp_droplogin etc., to be executed from within a transaction. So everytime i need to execute these stored procedures thru my application, i disconnect and set autocommit to true and connect again. after executing these stored procedures, i disconnect again , set autocommit to false and then connect again. is this how it should be done or is there some better way of doing it.
All help will be highly appreciated. I really need help on these problems - fast.
Thanks
View 2 Replies
View Related
Mar 1, 2004
How do I convert Oracle's LTRIM(char, set) to SQL Server?
Thanks,
Jake
View 8 Replies
View Related
Dec 21, 2000
I have been working with SQLServer since 1996, and now our company is being forced to consider a switch to Oracle. Does anyone know of a good Oracle/SQL Server comparison or an Oracle discussion board where I can find out the Oracle half of the equation to make a case for switching or not?
Thanks.
View 3 Replies
View Related
Jul 20, 2005
Hi... Have a customer who's running version 1 of oracle on a DECmachine.. is there a driver out there for that stuff? How might oneget the data from the DEC machine to Sql Server 2000???? All I havenow are fdl/sfl files.thanks in advance.Steve
View 2 Replies
View Related
Jan 31, 2008
I am using SSIS to extract data from one oracle server to another. When i use this SSIS package in another Server, it gives me Unicode conversion error to non unicode for some Columns which are VARCHAR2 type. I have to then used drived column and use conversion, but my question is why this error from i migrate my SSIS package to another server.
View 1 Replies
View Related
Nov 6, 2004
Hi,
i am trying to convert the following oracle sql,
SELECT TO_CHAR(SYSDATE,'DDMMYYYYHHMMSS')
(oraclequery)
into a MSSQLServer sql query.
Could you help me to get the equivalent of the above query in sqlserver.
PS: i tried using the following query, but i cannot get the month equivalent as 01/02/03/04 instead i get january/febrauary/march, etc
SELECT
(DATENAME(d, GETDATE())+
DATENAME(m, GETDATE())+
DATENAME(yyyy, GETDATE())+
DATENAME(hh, GETDATE())+
DATENAME(mi, GETDATE())+
DATENAME(ss, GETDATE()))
AS "Month Name"
(ms sqlserver)
The above query gives output as "6November2004174837"
what i need as result is "06112004174837"
Thanks,
Gopi.
Follow your DREAMS...
View 3 Replies
View Related
Jun 30, 2007
This is driving me nuts..
I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.
I'm struggling with this for a quite a while and I'm not able to get it working.
I tried the oracle query something like this,
SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE
FROM TBLA
this gives me an output of 20070511 23:06:30:000
the space in MM : SS is intentional here, since without that space it appread as smiley
I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error
The value could not be converted because of a potential loss of data
I'm struck with error for hours now. Any pointers would be helpful.
Thanks
View 3 Replies
View Related
Oct 26, 2006
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.
View 1 Replies
View Related
May 8, 2015
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 ?
View 2 Replies
View Related
Jan 11, 2007
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?
View 3 Replies
View Related
Sep 12, 2007
I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?
Thanks.
View 4 Replies
View Related
Apr 2, 2007
Hi!
I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:
1) Union
I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"
Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.
2) SQL command from variable
The select basically looks like this:
"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"
Again, even if I cast all columns (like in the union), I still get the same error.
Any help would be greatly appreciated. Thanks!
View 10 Replies
View Related
Feb 22, 2006
Hello,
On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.
I use one or the other according to my needs.
In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.
Almost everything works except Microsoft OLEDB provider for Oracle.
ssis packages on the test machine will return an error
Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.
Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".
I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.
Does anyone could point me to the right direction to solve this issue?
Thanks,
Philippe
View 17 Replies
View Related
Jan 12, 2006
Hi,
I am trying to make an oracle publiching from sql server 2005 enterprise final release, i installed the oracle client 10.2 (10g) on the same server where sql server already installed, i made different connection to oracle database instance and it was ok.
from sql server : right click on publication -New oracle publication-Next-Add Oracle Publisher-Add button-Add Oracle Publisher-i entered server insttance test1 and their users and passwords--connect --->
the oracle publisher is displayed in the list of publisher but when press ok i got the following error :
TITLE: Distributor Properties
------------------------------
An error occurred applying the changes to the Distributor.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
SQL Server could not enable 'test1' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The permissions associated with the administrator login for Oracle publisher 'test1' are not sufficient.
Changed database context to 'master'. (Microsoft SQL Server, Error: 21684)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21684&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Any idea about this error ?
Thanks
Tarek Ghazali
SQL Server MVP.
View 2 Replies
View Related
May 11, 2007
Hi Everyone,
I've been searching for a solution for this for a week-ish, so I thought I would post my quesiton directly. Here is my scenario..
Source: MS SQL Server
Destination: Oracle 10g
The destination table has a partition set on a column called "DATE_HIGH". How do I populate this date high column in my package? Currently I just have a source object, and a destination object, but I'm unclear how to populate this field in the destination. I've read one blog that states "use OLE DB Command" - but that isn't enough information for me to implement - Can someone be more specific in these steps? Here is an example of what my newb-ness needs to understand
OLE DB Source (Select * from Table) ---> OLE DB Command (What query goes here?) --> OLE DB Destination.
Second part of my question: There is a second column called "ROW_NUM" and there is an Oracle Sequence provided to me... What objects do I need (Source, Destination, OLE DB Command etc...) and how do I call this sequence to populate on the fly as I'm loading data from my source?
If these are simple questions - my appologies, I am new to the product.
Best Regards,
Steve Collins
View 1 Replies
View Related
Aug 23, 2007
Hi everybody,
I have designed a DTS package which will migrate a view from Sqlserver 2000 to Oracle.My package is using Microsoft OLEDB provider for Oracle driver for connecting to oracle.Im able to execute this package on the my system ie on the system where sqlclient is installed(Oracle client is also installed on my machine) .But when im doing it on the server im not able to do it.The Connection to Oracle Fails.
I wanted to know in order to connect to Oracle from the server,is it necessary that Oracle has to be installed on the server?.If yes, is it enough if i install oracle client on the server or Oracle Server version has to be installed on Server.
Please suggest me wht should i do know?
Thanks in advance
Regards
Arvind L
View 3 Replies
View Related
Apr 23, 2007
Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.
Anyone knows how to update an Oracle table through SSIS?
Thanks!
Wenbiao
View 5 Replies
View Related
Oct 29, 2005
need a clue about how to migrate the data from an Oracle applications 11.03 and underlying Oracle 8.05 database to navision 4.0 running sql server 2000
tia
View 1 Replies
View Related
Nov 7, 2007
Hi does anybody know how to programmatically convert an SQL Server table into a dbf or an excel spreadsheet? I'm using C#. Also, i need to put the new table into a different folder. Thanks.
View 3 Replies
View Related
Jul 13, 2006
If I develop my app in SQL Server 2005 Express, and then want to use a hosting service that only offers full SQL Server 2005, do I have to do some kind of conversion to my DB file, and if so, what might that be? (I notice that there are hosting services that provide SQL Server Express...I'd like to know how much work it would be to be able to use other services.)
Any guidance on this would be appreciated.
Thanks!
View 10 Replies
View Related
Mar 14, 2008
My application which ran perfectly well yesterday suddenly stopped working in this morning with following error message. I didn't change anything, at least I believe. Interesting thing is that I don't use any Oracle connection but connects to MS SQL Server 2005 Express version locally. Error messages are different according to the connection string though I don't think it makes difference:
ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;SSPI=true" />ErrorMessage from VS2008 ="Unable to get records. Object reference not set to an instance of an object."
ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;User Id=handtool_DB;Password=mypassword" />ErrorMessage from VS2008 = "Unable to get records. Unable to connect to database. ORA-12154: TNS:could not resolve the connect identifier specified." By the way, the application was generated from IronSpeed 5.1 Enterprise. My development environment is : VS2008, SQL2005 EXPRESS, Windows XP SP2, ASP.NET 2.0
We have actually an Oracle server in the network though. My gut feeling is that something might have changed in my network server during the night but how come it can possibly affect my application running on my local PC. I checked the connection of local SQL Express Server from SQL Server Management Studio and it connected well with UserID=handtoo_DB and showed all tables and stored procedures required for the application. So there is no problem in SQL Server database side.
I would welcome and appreciate any input.
fudata
View 1 Replies
View Related
May 2, 2007
Hi,
This might seems a little 'out there', but has anyone tried doing ETL from an Oracle 10g ODS into an Oracle 10g DW, and from there into SSAS2005 cubes?
Any caveats houghtscomments on doing this?
Thanks,
JGP
View 1 Replies
View Related
Mar 28, 2001
We are Planning to convert from SQL 7 to 2000.Can any one give the related issues and how it can be done?
Sujas
View 2 Replies
View Related
Jan 21, 2000
Hi Folks,
I am converting a 6.5 database to 7.0. I have heard that some areas(for e.g. Nulls) are treated differently in SQL Server 7.0. So even if the conversion goes through smoothly there are poteential problems with the way Nulls are treated in 7.0. Is there any whitepaper or some other source which lists all the problems faced by developers when converting from 6.5 to 7.0?
View 2 Replies
View Related
Apr 9, 2001
Why am I getting the following error during a SQL Server 6.5 conversion?
##### Monday, April 09, 2001 - 21:06:36 #####
LOGINSID: Unable to open SQL Server registry key
View 1 Replies
View Related
Sep 30, 1998
Any ideas on converting integer to a proper date format in SQL server.
View 2 Replies
View Related
May 17, 2007
Hi folks,
Here are the fields I have
eventDate = smallDateTime 2005-12-12 00:00:00
eventTime = varchar(20) 1:00:00
newEventDate = dateTime [desired result: 2005-12-12 1:00:00]
When I run the following script:
update healthEvent
set newEventDate = cast(substring(convert(varchar,eventDate,120),1,10 )+' '+eventTime as DateTime)
I get "Syntax error converting datetime from character string." error.
Any ideas why?
Thanks!
-Parul
View 4 Replies
View Related
