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.
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
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
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.
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
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
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
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?
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
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.
I want to write this query in sql server. Please help me, it is very urgent :(
Any help will be appreciated.
SELECT VALID_ITEM.PC_CODE,PC_TITLE,VALID_ITEM.DB_CONTRACT , valid_item.ITEM_NO,DESCPT,UNITS,CONTQ, CONTR_PRICE ,nz(QTD,0) as qtd,ind_ovr_und, nz(QTD,0) - NVL(QUANTITY,0) as QUANT_PREV, Nz(QUANTITY,0) as quant_rev, round(NVL(quantity,0)*nvl(CONTR_PRICE,0),2) as QTD_VAL, AMT_PAID_ITEM,AMT_RET_ITEM FROM VALID_ITEM, cqe_item,CONTRACT_PC WHERE valid_item.db_contract = contract_pc.db_contract and valid_item.pc_code = contract_pc.pc_code and valid_item.db_contract = cqe_item.db_contract (+) and cqe_item.cqe_numb (+) = :EST_NO and valid_item.item_no = cqe_item.item_no (+) and valid_item.pc_code = cqe_item.pc_code (+) order by valid_item.item_no
I need the basic information about how the query is working in Oracle & SQl Server.
Sicne I am getting different values when I run the query. I have a table called 'Client_Master' in both the DB (ie., Oracle & SQL). I have inserted a record with the below values for Client_Master table. (both Oracle & SQl).
SELECT * FROM CLIENT_MASTER WHERE ID = '200' AND ((START_CODE <= '102' AND END_CODE >= '102') OR (START_CODE <= '200' AND END_CODE >= '200') OR (START_CODE >= '102' AND END_CODE < '200') OR (START_CODE = '102' AND END_CODE is null))
Oracle result : No rows return. SQl Server : one row returned.
Data type for start_code & end_code are 'NVARCHAR2 (40)'.
I would highly appreciate if anyone can explain why is this and any suggestion like how to handle this in both the database.
The subject of this query is the values which is entered by the user should not be overlapped. But I dont how to handle this if it is alphanumeric.
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.
I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ? When I tried to import system.data.oracleclient, it was not available. Do I need to install anything for being able to use it ? Thanks a lot.
I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?Thanks a lot.
We have developed a solution that sends data from SQL Server to an Oracle server as the result of a stored procedure called by a job that runs every minute. While this development worked fine in our test environment, after moving it to production it ran successfully the first minute, but the second minute the stored procedure hung, and the process could not be killed. In order to stop the process I had to stop both the SQL Agent and the MSDTC services.
Our SQL Server box: SQL Server 2000 Standard Edition SP4 Windows 2003 Server R2 SP1
To setup the SQL box, I did the following: 1) Install Oracle Client Tools version 10.2.0.1 2) Restart Server 3) Modify the registry as follows: [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI] "OracleXaLib"="oraclient10.dll" "OracleSqlLib"="orasql10.dll" "OracleOciLib"="oci.dll" 4) Modified the PATH variable so that all references to SQL Server appear in front of Oracle path references 5) Added the linked server via sp_addlinkedserver '<tns name>','Oracle','MSDAORA','<tns name>' 6) Added linked server logins via sp_addlinkedsrvlogin '<tns name>','False','<SQL user>','<Oracle User name>','<password on oracle>' 7) Changed the registry for MSDTC to match this:
8) Stopped and restarted services in the following order: 1) MSDTC Stop 2) SQL Server Stop 3) MSDTC Start 4) SQL Server Start
The stored procedure: In a single transaction, the stored procedure compares a production table against a logging table. If a record exists in the production table that is not in the logging table, a record is inserted into logging table, and a record sent to Oracle via an INSERT INTO OPENQUERY('INSTANCE','SELECT Column1, column2, column3,... FROM SCHEMA.TABLE') SELECT column1,column2,column FROM SQLTable
This stored procedure has worked just fine for us in test, to either the test or production Oracle boxes, but it now fails, and hangs, in production to either the test or production Oracle boxes.
Additionally, I can run the following query via Query Analyzer from our test box to both the test Oracle and production Oracle and it runs successfully (this is NOT used in our stored procedure code, but is presented here as an indication that I think there is something wrong with the settings on our production SQL box):
SELECT * FROM OPENQUERY('INSTANCE','SELECT * FROM SCHEMA.TABLE')
When I run this same query via Query Analyzer on our Production SQL box, to either the test Oracle or production Oracle, it hangs, and I have to kill the process, and restart the MSDTC service. Other queries that hang are: SELECT * FROM SERVER..SCHEMA.TABLE
Additionally, I noticed that when I used this method to kill the process I would see errors like the following in the Application Event Log on the SQL box:
The XA Transaction Manager attempted to perform recovery with the XA resource manager. The XA resource manager reported that recovery was unsuccessful. DSN = MTxOCI.Dll.
Since I figured this was an aborted transaction still residing in the MSDTC log file, I would stop the MSDTC service, delete the MSDTC log file, reset the MSDTC log, and then restart the MSDTC service in order to prevent this error from occurring.
Not ALL queries from the production SQL box to production and test Oracle boxes fail. I can get results returned for this query:
SELECT COLUMN1, COLUMN2 FROM SERVER..SCHEMA.TABLE
I've been scouring the internet for about a week now, and I've run out of ideas on what to check on the production SQL box. Any suggestions would be greatly appreciated.
Hi, I have this oracle query with outer join situation. how can i convert it into sql server query.
SELECT distinct ae.dB_CONTRACT,CP.PC_CODE,BID_ITEM.ITEM_NO, 'N',BID_ITEM.PRICE_WORDS,OFF_ITEM.DESCPT, OFF_ITEM.UNITS,OFF_ITEM.TYPE_ITEM, PRES_ITEM.RET_PERC FROM BID_TOTAL,BID_ITEM,OFF_ITEM,PRES_ITEM, AE_CONTRACT AE, CONTRACT_PC CP WHERE RANK_NUMB = 1 AND BID_TOTAL.DB_CONTRACT = 37044 AND BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT AND BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO AND OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO (+) AND AE.DB_CONTRACT=BID_TOTAL.DB_cONTRACT AND CP.DB_CONTRACT = AE.DB_CONTRACT AND CP.pc_code = 1
When I perform a query on a linked Oracle server in the Query analyser Ihave noprboblem' to perform this query.However, when I create this query in a stored procedure I get a compilationerrorwhen saving this procedure. (Not when compiling; it has no errors)Server: Msg 7405, Level 16, State 1, Line 1Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS optionsto be set for the connection. This ensures consistent query semantics.Enable these options and then reissue your query.When I create a dynamic SQL statement then I can save this stored procedurewhen I run the stored procedure this same error happens.What do I have to do.Arno de Jong, The Netherlands
I finally have my server linked, figured out my date issed with the decode statement and now I get a missing Expression error from Oracle.
Here is the statement:
SELECT * FROM OPENQUERY(PROD_ORACLE,' SELECT LEFT(CUST_ORDER_STUS_NME, 25) as CUST_ORDER_STUS_NME, LEFT(SRVC_ORDER_STUS_NME, 25) as SRVC_ORDER_STUS_NME, LEFT(Ser_ORD, 20) AS Ser_ORD, LEFT(CUST_ORDER_NME, 25) as CUST_ORDER_NME, LEFT(SRVC_ORDER_NME, 25) as SRVC_ORDER_NME, LEFT(CLS_ALLOWED_NBR, 2) as CLS_ALLOWED_NBR, LEFT(NOC_TO_NOC_NME, 3) as NOC_TO_NOC_NME, LEFT(CHARS_ID, 20) as CHARS_ID, LEFT(PRI_DNS_QTY, 5) as PRI_DNS_QTY, LEFT(SCNDY_DNS_QTY, 5) as SCNDY_DNS_QTY, LEFT(ORDER_TYPE_CD, 10) as ORDER_TYPE_CD, LEFT(ACTY_NME, 25) as ACTY_NME, replace(LEFT(CUST_A_NME, 50), ''|'', ''-'') as CUST_A_NME, LEFT(RLAT_CKT_ID, 8) as RLAT_CKT_ID, DECODE (BILL_CLR_DT, GREATEST(BILL_CLR_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), BILL_CLR_DT, NULL), DECODE (CMPLT_DT, GREATEST(CMPLT_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), CMPLT_DT, NULL), REPLACE(replace(LEFT(CMNT_TXT, 1000), char(10), '' ''), ''|'', ''-'') as CMNT_TXT, LEFT(CUST_H1_ID, 9) as CUST_H1_ID, LEFT(CUST_CMS_ID, 8) as CUST_CMS_ID, LEFT(Circuit_ID, 30) as Circuit_ID, DECODE (SO_CMPLT_DT, GREATEST(SO_CMPLT_DT, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), SO_CMPLT_DT, NULL), LEFT(DNS_ADMIN_NME, 30) AS DNS_ADMIN_NME, LEFT(DNS_ADMIN_PHN_NBR, 15) AS DNS_ADMIN_PHN_NBR, LEFT(DNS_ADMIN_EMAIL_ADDR, 128) AS DNS_ADMIN_EMAIL_ADDR, LEFT(CONTACT, 30) AS CONTACT, LEFT(GOVT_CD, 10) AS GOVT_CD, LEFT(SOTS_ID, 30) AS SOTS_ID, LEFT(RAS_USER_BLK_NBR, 20) AS RAS_USER_BLK_NBR, LEFT(VSYS_QTY, 10) AS VSYS_QTY, LEFT(ZONE_QTY, 10) AS ZONE_QTY, LEFT(PLCY_QTY, 10) AS PLCY_QTY, LEFT(PIC_CODE, 6) AS PIC_CODE, LEFT(SO_Order_Entry_Nbr, 15) as SO_Order_Entry_Nbr, LEFT(SO_Rlat_Order_Entry_Nbr, 15) as SO_Rlat_Order_Entry_Nbr, '' '' as filler From PROD_ORACLE..RDBADM.CUST_SRVC_ORDER_V')
Here is Query Analyzers response:
Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE DB provider 'OraOLEDB.Oracle'. [OLE/DB provider returned message: ORA-00936: missing expression] OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandPrepare:repare returned 0x80040e14].
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 ?
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".
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.