Im working on a project to convert a C program from OCI(oracle) to ODBC(MsSQL).
One of the select statements uses oracle julian time:
"Select TO_NUMBER(TO_CHAR(TO_DATE('0101'||:styear,'mmddyyyy'),'J')) STYEAR from dual"
Its not a problem converting the statement, except msSQL uses 10/01/1900 as its origin for the julain calendar, where Oracle uses somewhere around 5000BC. my questions is, because this program is made to use oracle julian and need to keep it that way, how i can make the SQL conversion using oracles origin date?
hello, I built my application using SQLClient classes and MSSQL database i have another version of the database with Oracle, is changing to OracleClient classes the only change i have to do in order to run the program and the connection string? Thanks
Hi, I have to migrate the oracle SQL statement to MSSQL. I want to know how to write a sql which can retrieve: 1. the user table name 2. the user table column name in the user table 3. the type of the user table column
I have already done a Oracle compliant sql : ------------------------------------------------------------ select table_name, column_name, decode(data_type, 'VARCHAR2', 'java.lang.String', 'VARCHAR', 'java.lang.String', 'CHAR', 'java.lang.String', 'DATE', 'java.sql.Timestamp', 'NUMBER', DECODE(nvl(data_scale,0), 0, 'java.lang.Long', 'java.lang.Double'), 'LONG RAW', 'java.lang.Byte', 'BLOB', 'java.lang.Byte','java.lang.Object') from user_tab_columns ------------------------------------------------------------
Can anyone tell me how to change it to MSSQL sql which retrieve the same value as the above sql. Thanks.
Hello.I've read many topics about this problem but i couldn't figure it out.I use form where user must insert 2 dates using texboxes.-One is required and other is optional.Sql 2000 is inserting either '20061105' or '2006.11.05' on insert update but select query returns 05.11.2006 on my report. Question 1.How do I insert or update dates from my form where date is entered dd.mm.yyyy to sql 2000 table?question 2. What to do if user left optional texbox date empty.I'm using SP and function with arguments (byval texbox1.text as date, byval texbox2.text as date)and parameters @date1, sqldbtype date =texbox1.text
i have a doubt here that would like to have some advice from u all. Can i get data from Oracle 10g database if i have an application running on MS SQL ? If can, would it be cumbersome afterall? Please advice.
hi all ms sql specialists.....i am a newbie on ms sql buti am used to oracle and toad ...and now a question (i guess this was asked xxx times befor but icouldn't find the answer easilly) :is there something powerfull like toad on oracle for mssql server? (notenterprise manager)
Hi all, Well, I have created an applicatin in asp.net and it connect to MSSQL database, and it work fine.
There is another application which was developed in Oracle and the database is oracle 8i.
Well, wat i want is whenever a form is submitted from the asp.net application, the data is inserted in the MSSQL server database and the Oracle8i database also.
Hi,I am using MSSQL 2k, and I have a linked server set up to an Oracle RDBversion 7. It goes thru an OLE DB provider for ODBC drivers on a systemDSN, which is using an Oracle RDB ODBC driver version 3.0.2.The problem occurs when I send a query that returns zero rows - queryanalyzer just does not complete nor return. This problem is not seenwhen there are rows being returned.I ran a trace and this is the error message I get-:Non-interface error: OLE DB provider MSDASQL returned an incorrectvalue for DBPROP_CONCATNULLBEHAVIOR which should be eitherDBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULLAnybody who experienced this before has a solution?Thanks,Lawrence
I am currently migrating my database from oracle to MSSQL. My problem is that i have encountered the UTL_FILE function in Oracle and i am not finding its equivalent for MSSQL.
Can anyone help me with this plz? Is there any other way of reading and writting OS files in MSSQL?
I created a link from Oracle to SQL server. There is a trigger with insert statment in Oracle side. I got error message as follow when trigger is invoked:
SQL> insert into test1 values ('zerbra','brazi'); insert into test1 values ('zerbra','brazi') * ERROR at line 1: ORA-02047: cannot join the distributed transaction in progress ORA-06512: at "GGWEB.TRIGGER2", line 4 ORA-04088: error during execution of trigger 'GGWEB.TRIGGER2'
my simple trigger is as follows: create or replace TRIGGER TESTRI AFTER INSERT ON TEST1 FOR EACH ROW BEGIN insert into test1@sqlserver (city,state) values ('what','nine');
All,International company in Australia needs an allround DBA(development&Admin) in MSSQL and Oracle.If you have 5+ experience in development and (not OR) Administration inboth MSSQL and Oracle and like challenges, please send CV toJoin Bytes!.I will send the position description to suitable candidates.Cheers,Yak
Hi,I'm doing an Orcle to SQL port.Simply all I want to do is run a script / DTS package that reads Oraclesequences value and writes the current value of it in to a MSSQL table - ofSequenceName, ValueCan this be done ? If so how ? (Please provide example)Steve
I lost few days to solve a problem regarding the connection to an Oracle database server (10) with goal to execute a distributed transaction.
The environment was:
Windows Server 2003 Standard MSSQL Server 2000 Standard Oracle 10g Production Edition
The connection was quite easy to make with the Microsoft driver, but the error was: Server: Msg 7391, Level 16, State 1, Line 3 The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction. OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
I tried to use the oracle odbc driver, but all kinds of errors were raised, regarding the TNS name and stuff like this. The real cause was that when you create the linked server you must to check the €œAllow inbound process€? in the provider options.
The Oracle ODBC driver allow distributed transactions.
Hello all, I will try to supply all of the information available to me to help generate some answers to the question I have. I am doing some benchmark testing of an application of how it deals with various database queries, for example oracle 10g, DB2 and MSSQL 2005. I have a very simple database called "student" that has various tables, with various columns, all as mentioned very simplistic. Each table has a few hundred rows of data, the most being one table with 2000 rows. The same database (structure and dataset), is replicated across all 3 DB's. In order to do the testing, I have been recording the same queries (select * from table_a, select * from table_b etc etc) for each database at an interface which monitors traffic between the client and db server, the problem starts here; The size of the tcpdumps are vastly greater for MSSQL vs oracle or db2. Some examples:
100 query or transaction dump: mssql=3.7mb oracl10g=133kb
50,000 query or transaction dump: mssql=1.8gb oracl10g=0.6gb db2=0.6gb
as mentioned each database's tables and data are identical to my knowledge and all queries are the same. The bloated dumps are making mssql performance numbers look bad.
So my question is: What could be the reason for MSSQLs client/server queries to contain so much information, has anyone else come across this? Is there any setting or something I could try to minimize it?
I have a julian date(04159) and I need to convert this to any normal date format yyyymmdd or mmddyy or basically anything that I can work with! I am just about fed up with trying to convert this. It would be simple enough if there want leap years. Does anyone have a function or something out there that can convert this for me? Code that I found on the web turned out to be bogus. Your help will be greatly appreciated. If you have something in VB that would work also. SQLServer preferably.
A table that I am querying from has the date field in julian date format "04194". I am only wanting to pull the information from this table that is => than todays date. Maybe some of you have done this before and can give a little help in finding the best way to do this?
Can anyone tell me how to convert julian date time to DateTime and Vice Versa?the function which I have only convers the date to Julian and julian to date but the time is not appended. How can i get the time into Julian format and from julian format?Any help would be appreciated.thanks.
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U')) DROP TABLE [dbo].[temp_tbl] GO CREATE TABLE [dbo].[temp_tbl]( [tbl_id] [bigint] NULL, [cs_id] [int] NOT NULL, [USERID] [int] NOT NULL,
Hi, I have julian date in the format of 2003182 and I need to convert into our regular calendar date. Anybody have any SQL statement for this. Thanks, Ravi
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
i was planning to create a database migration tool .. its a certain database of a DMS (document management system) to another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..
they have different table structures and names . . :D
i was thing of what language shall i use.. or what language is the best to work on this kind of project :)
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
