Looking For A Function Similat To InitCap In Oracle For SQL SERVER 7.0
Hi Guys,
Does somebody know if a function similar to INITCAP in Oracle is also available in SQL SERVER 7.0 ?
Just FYI: INITCAP function takes a character string, say 'sql', and returns the string 'Sql' which has first letter 'S' in caps.
TIA,
anuj
View Complete Forum Thread with Replies
Related Forum Messages:
Oracle Translate Function Equivalent In SQL Server
Hi I want to know the equivalent of the Oracle translate function in SQL Server. eg : select translate('entertain', 'et', 'ab') from dual. I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'. Please let me know if there is some other equivalent function in SQL Server thanks.
View Replies !
Oracle Translate Function Equivalent In SQL Server
Hi I want to know the equivalent of the Oracle translate function in SQL Server. eg : select translate('entertain', 'et', 'ab') from dual. I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'. Please let me know if there is some other equivalent function in SQL Server thanks.
View Replies !
Oracle NVL Function
Hi, I want to know if there is a function in SQL Server that do the same thing than the Oracle function NVL() In Oracle we can do the following thing : select nvl(last_name,first_name) from table_test; This have the effect that if the last_name field is NULL, it will display the first_name Is there a function like this in SQL 7 Thank Martin
View Replies !
How To Use The OLEDB Command To Call A Oracle Function?
HI, I want to use the OLEDB command to call a oracle function, but i havnt found any materials about how to do that, my oracle function is as below: CREATE OR REPLACE function GET_ZONEID_FROM_SYFZ(ycz varchar2,xc varchar2,strat_id varchar2) return varchar2 IS zone_id_result varchar2(10) ; begin PKG_DM_DQ.GET_ZONEID_FROM_SYFZ(ycz,xc,strat_id,zone_id_result); return zone_id_result; end; In OLEDB command transformation component, i fill the sql command with "select GET_ZONEID_FROM_SYFZ(?,?,?) from dual", but i dont have it worked. The error message is :provider can not derive parameter information and setparameterinfo has not been called. Who have any idea about how to make it work? Thanks ~~
View Replies !
Timeout Error While Executing Oracle Function
Hi, I have developed reports for Oracle database. I wanted to avoid use of OUT REF cursor hence am using function & calling it from reports. The logic in SP/function is so complex that it takes 5 mins to execute n get the result. I see the results on the report server, But when I call that report from web application, I get System.Net.WebException - Time out error. My questions: 1. What setting I make on report server to avoid timeout? --Are these settings applied on report server restart? 2. Is there any setting needed for Datasource also? TIA Tanmaya
View Replies !
Trouble With: Linked Server To Oracle Using OraOLEDB.ORacle Provider
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 Replies !
Oracle Connection Fail With Microsoft OLEDB Provider For Oracle MSDAORA.1
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 Replies !
Output Column Has A Precision That Is Not Valid (loading From Oracle Using OraOLEDB.Oracle.1)
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 Replies !
Oracle Publication Error:The Permissions Associated With The Administrator Login For Oracle Publisher 'test1' Are Not Sufficient
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 Replies !
Inserting Into Oracle Table That Has DATE_HIGH As A Partition And Need Oracle Sequence Used
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 Replies !
Oracle Connectivity Patch For Windows 64 Bit SSIS, Oracle Client 10.0.2
Hi, I see many people had problems with connecting to Oracle while creating package on SSIS 2005 on the machine running Windows 2003 Server 64 bit. I have the same problem, but can anyone tell me the exact patch number from Oracle to fix this issue for Oracle Client v10.0.2 We need to connect to Oracle 10g when Oracle Client 10.0.2 installed on Windows 2003 64 bit machine. I know about copying Visual Studio to another folder than default for 32 bit programs which is Program Files(x86), but has anyone got "cleaner" solution with official patch from Oracle for 10g. Regards Bakhodir Makhamadov
View Replies !
Unable To Connect To Oracle Using Microsoft OLEDB Provider For Oracle
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 Replies !
Failed To Process Cub With Oracle Datasource Via Oracle OLE DB Provider
Hi everyone, Could you please help me take a look at the error below? OLE DB or ODBC error: Class not registered. Now I can connect to the Oracle datasource successfully via Oracle OLEDB provider, but failed to process it. I just installed the Oracle Client on my machine, should I installed Oracle OLEDB provider separatly? According to the error above, seems that I should register something.... could you tell me which dll I should re-register..... Thanks^_^ Winnie
View Replies !
New SSRS Install, Works Great Until I Try To Access Oracle Datasource - System.Data.OracleClient Requires Oracle Client Software
re: new SSRS install, works great until I try to access Oracle datasource - System.Data.OracleClient requires Oracle client software From my desktop the report accessing an Oracle 9i db with no problem. I've deployed other reports accesing sql server with no problem. Now I deploy the report and try to access my Oracle source and get: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater On the server in question, I know they've installed the oracle client. I can access the db from sql plus with no problem. As the user that I access ssrs credentials, i remote login and am able to write to oracle home.. C:oracleora10gNETWORKADMIN i've confirmed my database is in tnsnames and it works from sqlplus.
View Replies !
Oracle/Unix Environments --&&> SQL Server 2005/Windows 2003 Server
I was a Oracle Developer / DBA on Unix Environments all along my career, Very recently iam starting to manage a SQL Server 2005/Windows 2003 Server setup. Part of my new job is to automate to load Huge Data files/Flat Files (3/4 GB in size) into SQL Server 2005 DB. Have these initial questions.. Since the files are too large to open at once... What sort of Command Line Interfaces people use on the Windows Boxes.. like doing a "wc" (Word Count) / GREP 'ng Files / Massaging Data Files one line at a time (Like using SED / AWK Commands).. Etc Any Input / Direction is appreciated...
View Replies !
Importing Data From Oracle 10g To Sql Server 2005 Using Linked Server
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
View Replies !
How To Link/connect To Oracle Server From SQL Server 2000?
Greetings, I want to connet to Oracle databse from SQL server 2000 since I am more comfortable with SQL 2000 client package. The oracle server is 8i or above. I am running a SQL Server 2000 on a PC with Windows XP OS. Please help what I need to do and configuration information. Thank you all in advance. BPG
View Replies !
Sql Server 2000 - Trouble Linking To Oracle 7.3 Server
SQL Server 2000 Standard Edition running on Windows 2000 Server with sql*net installed and configured correctly, trying to create a linked server to an Oracle 7.3 server. Added linked server with sql*net alias and valid user name and pwd for remote server. Returns error message: "Error 7399: OLE DB provider 'MSDAORA' reported an error." Have successfully linked to this same Oracle server from Sql Server 2000 running on Windows 2000 Professional and Windows NT. Any help would be appreciated.
View Replies !
Execute Script To Linked Oracle Server From Sql Server
Ok, so these are the facts: My company wants to implement a BI, the warehousing company we used sold us panorama on sql server. Our database is on oracle. They were proposing this method: a) Link oracle server, copy oracle tables to sql database and then build cubes from sql database. The operation will be made only once a day at night. I don't like it so make it like this: b) Build cubes directly from linked oracle server. As it is not good to build directly from tables and views (because they could change between processing dimensions), I used materialized views (or snapshots) and some views on slow modifying tables. Problem: Oracle snapshots must be refreshed and that is done in oracle with a procedure. My question is: How can I run an oracle procedure directly from sql server? I want to create a job that will refresh the oracle snapshots and process analysis database after that. I know I can use OPENQUERY to return queries from oracle but I want to execute a script. Solutions? Another way was by doing it in a command line. I asume there is an special job feature that can run programs. PS. posted in another section too
View Replies !
Linked Server To Oracle Causes SQL Server Instance To Crash
When running a linked server to Oracle using the MSDAORA driver using in-process option checked, I'm getting the following error: "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)" This then causes the instance to crash. In the Event Log, it shows: SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart). When using the out-of-process, it shows the column names in the query results window, but doesn't show any data then immediately in the message window it shows: Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "MSDAORA" for linked server "smtest" reported an error. Access denied. Msg 7350, Level 16, State 2, Line 2 Cannot get the column information from OLE DB provider "MSDAORA" for linked server "smtest". We're using: SQL Server 2005 SP1 with hot fixes (9.0.2153) 32-bit on 64-bit o/s Windows 2003 R2 Standard x64 Edition Sevice Pack 1 We use lots of linked servers to Oracle on other servers, and can't find anything different on this one. Appreciate any thoughts, insights! thanks, Steve
View Replies !
Oracle Pl/sql -&> Sql Server
Hello,I am an SQL server newbie. Our company has a massive applicationwritten in PL/SQL. I need to port parts of it to SQL Server.- Which SQL server version should I choose, to have a reasonable chanceof porting?- Could you recommend any best practices, tools? I use Toad for SQLdevelopment on Oracle, I am looking for something similar for SQLserver.Regards,Hegyvari Krisztian
View Replies !
SQL Server To Oracle
Hello,Well unfortunately, I have to get an Oracle back end working with myproduct and am having a hard time doing it. I was wondering if anyonehas had the same experience and could give me a little insight intowhat I am doing wrong (if anything). I installed Oracle 10g on amachine which ended up not being able to run it fast enough, though Idid manage to import the database that I wanted to use. After failingat some syntax problems, I was finally able to return records, but itwas so slow, that we decided to install Oracle on a faster machine.After that was done, I thought I was connecting to the newer machine,but alas, I was actually connecting to the old machine when I foundthat some structure changes were not being recognized. I shut off theold machine, and now the name I was using to connect to it "ORCL",could not be recognized. I uninstalled and reinstalled it to a newname "GE", but it still doesn't recognize the name. Just wondering ifwithout trying to get into an Oracle/SQL Server contest, if there is asimple stupid thing that I am missing. I don't want to learn Oracle,I just want the thing running as a back end to do some simple SQLstuff. I almost had this working, it is just the naming issue. Anyhelp would be great.Thanks-Jevon Thurlow
View Replies !
Sql Server And Oracle
Hi everybody I have 500 sql servers with 2 or more systems connected to each. THe data from each of these SQL Servers has to be updatedon a daily/weekily/monthly basis to a main oracle server. What are the problems that can be forseen in this transfer of data. How do i maintain data integrity in oracle ,if a particular table has identity column and each sql server will have the same identity column but different data, How do i receive data (format)from sql server and insert it into oracle. I ll be very thankfull
View Replies !
DB2, SQL Server, And Oracle
Hello all, I am curious to know if anyone has done a trade study on DB2, SQL Server, and Oracle databases (all Enterprise editions). Basically I am trying to identify which one to chose based on "Ease of implementation", Ease of maintenance", "Pricing" and "Ease of technical support". The criteria I am looking at can be obtained by interviewing people who have worked with these products. I appreciate any input I can get. Thanks, Shaun
View Replies !
Oracle Or SQL Server
Hi All, I am new in the DB World. We have SQL Server in our company. Our website gets about 3 million hits every month. I have been asked to tell the management why is SQL Server 2k a better option than using Oracle. Right now, I know little about SQL Server and almost nothing about Oracle. Can someone help me by telling me what are the main differences between the two? Thanks
View Replies !
SQL Server & Oracle
Are there any tools out there that provides a bridge/gateway between an ORACLE db & a SQL Server db?? What we need is to update tables in a SQL server database when a user updates a table in the ORACLE db. Any help/assistance would be much appreciated!! Thanking you in advance.
View Replies !
SQL Server Vs Oracle
Does anyone know where I could find a good head to head comparison btween MS SQL Server and Oracle? I'm looking to quantify some questions such as how much overhead does adding another database to a server add if there are already 20 databases on that server? If there are 100? Pros and cons of keeping a number of small databases with some redundant data compared to one big database. Any insight appreciated, -Ringo
View Replies !
Oracle Vs SQL Server 7.0
Does anyone have any comparisons on Oracle Vs Sql Server, not just costs but the overall performance. The company I am working for is considering moving from SQL Server to Oracle. Thanks for your assistance.
View Replies !
SQL Server And Oracle 8i On The Same Box
Has anyone ever tried running both products on the same box? Yes I know why it shouldnt be done, but there are reasons, which I wont go into. Possible problem areas are SQLNet / TCPIP/NamedPipes/Multiprotocol, Conflicting service priorites, file system conflicts. Any others? Regards BJ
View Replies !
SQL Server To Oracle
we will like to migrate our datawarehouse from sql server 7.0 to oracle (don't know the version) using DTS. I need advice from people that have done it b4. What should i look out for? (issues and how it was resolved) also do we need to make changes to our DTS ETL packages as we will want to continue using it as our ETL Tool. list of oracle suite. which is the lates version. good oracle administration books. good oracle websites. Thanks
View Replies !
SQL Server Vs. Oracle
what application are better suited for Oracle vs. SQL or SQL vs. Oracle. What factors to look at when installing SQL vs. Oracle. Thank You, John
View Replies !
SQL Server Vs. Oracle
I'm not a tech guy, but a Product Manager, trying to make a decision on what database to go with for an application I'm building and am looking for analysis on it, specifically advantages/ disadvantages of SQL Server vs. Oracle db. ANy suggestions appreciated. Outline of application: - must handle high transaction volume. (avg is 2,000/day, high end is 500,000/day, less than 10% of customers). - application is sold to mid market (avg. sell is $50k) so selection must be cost efffective. - Probably building in Java so it will run quote anywhere unqoute.
View Replies !
Oracle DBA New To SQL Server Needs Help
Hi all! I am an Oracle DBA who is brand new to SQL Server and I need some help. If anyone is familar with Oracle there are a set of views starting with DBA.. that will return loads of useful information about your instance. One in particular is DBA_TAB_COLUMNS which will return all information on all of a table's columns. What I am looking for now is a table or view in SQL Server that will return information on DATA TYPE and DATA LENGTH of columns in individual tables. Is there such a view or table in SQL Server? Please, please for any help on this issue. I am stuck right now and after two days of research I can't seem to find this on my own. Thank you in advance; Trea Johnson Oracle DBA Vulcan Materials
View Replies !
Sql Server Vs Oracle....
why oracle server is still considered as the best server in many fields rather than sql server and why is it that when the same amount of load is given to both sql server and oracle sql server performance reduces compared to oracle .. y ????
View Replies !
SQL Server Or ORACLE DB - Where To Go?
Friends, I am working on a project to build a Web Based Education System. There are different modules like Online Admissions, Attendance, Assessment, Discussion Communities, Job Portals, and some of them are supposed to work offline (as desktop apps). I am planning to use ASP.NET (VB.NET) and SQL Server 2005. As in my view, it can be easily be hosted on some good hosting facility. There is another colleague of mine, who works in Oracle / Developer. He and my Director IT are convinced that we should be using ORACLE for this whole Web Based Education System, and they provide no solid reason for doing so. However, They also believe that ultimately we have to shift all this stuff towards ASP.NET / SQL. Can we have a discussion over here, as whats the good platform to go for such an application, what can be the disadvantages of using SQL or ORACLE, what problems can be faced while integration, and shifting towards ASP.NET after four/five months. And how it can be integrated if some desktop apps are developed in ORACLE/Developer, while web apps are in other platform. This dialogue can help me in generating a report for the policy makers in this regard. Thanks & Best Regards,
View Replies !
|
TRACKER
