How To Do Pair-wise Comparision Like Oracle In MS Sql Server 2000?
Apr 18, 2008
How can we do pair wise comaprission using Sub query as generally can be done in Oracle?
Thanx
ADVERTISEMENT
Comparision Of Sql Server && Mysql
May 19, 2006whats the difference betwwen mysql and sql server2000 in terms of usingstoredprocedures,stored functions and views.
View 3 Replies View RelatedDTS Between SQL Server 2000 And Oracle 8.1.7
Feb 11, 2004Hi,
I used DTS Export Wizard to copy tables from SQL server 2000 to Oracle 8.1.7. After transfering completed successfully, I loged into oracle and tried to select data from the tables. I got 'table does not exist' error. But when I select table_name from oracle data dictionary, I saw tables exist in oracle. Then I opened SQL Query Analyzer, I can select data from the tables just transfered through linked oracle server.
Does anyone have same experience? Could someone please give me suggestion?
Thanks in advance!
Oracle .dmp To SQL Server 2000
Apr 7, 2004Hi guys,
I was given a task to get an Oracle .dmp file into SQL Server.
Is it even possible to do? Or do I have to script the Oracle DB and import the DB that way?
But then how would I get the data?
I need to get the Oracle structure and the Data over to SQL Server.
PLease help.
Replication From Oracle To Sql Server 2000
Mar 3, 2003Is it possible to replicate from Oracle to SQL Server.
Thanks,
Bianca
Migrating From Oracle 8i To SQL Server 2000
Jan 31, 2004Hi
I am trying to migrate my database in Oracle 8i to Sql Server 2000 but am unsure of how to carry out the migration. How do I do it and what do I have to consider. My OS is Windows 2003. Thanks in advance
Exporting Sql Server 2000 Tables To Oracle 10.2G
Jan 10, 2008Dear All,
I am working on a project to migrate a .net desktop application to web based application using ASP.NET 2.0.
The present app has lots of tables with data in sql server 2000. The new app is to have Oracle 10.2 G as data store.
Although the data store is being changed most of the tables in the present database will remain same along with the data they hold.
My problem is:
1) how to transfer the tables to the new datastore 10.2 G in an automated way?
After all it's not possible to create each table afresh in Oracle and then insert records into each table one by one. If that is done it will take atleast few months if not years.
2) can I export the tables to a xml file and then import it from Oracle.How to do that?
It has become a nightmare for me.
Pls suggest the easiest way and which takes the minimum time.
A solution to the problem will be gratefully accepted.
Thanks.
Migrade Oracle Data To SQL Server 2000
Aug 1, 2005I am doing a test on migrating an Oracle 8i database toSQL 2000. I've never done this before and I would liketo find out if there are any complications or side effectsif I try doing the transfer of data using DTS?If not DTS then what would you suggest I transfer datawith?Thank you
View 4 Replies View RelatedAccessing Oracle Tables From Within SQL Server 2000
Mar 24, 2006We are running SQL Server 2000 SP3. We have linked servers in use thatwe use to access Oracle tables.Recently the claim has been made that you can access Oracle tables fromwithin SQL Server without using a Linked Server. I searched BooksOnline using keywords: linked, remote, and Oracle and did not findanything.A search of the newsgroup archives found only entries related to usingLinked Servers.Is there any such method as claimed? I do not think there is, but Ineed to find some support for my position or else learn something new.Thank you-- Mark D Powell --
View 4 Replies View RelatedScalar Query In SQL Server 2000 Like In Oracle
Mar 28, 2008Hi Gurus,I have table (CallByCall) with following Data.Input :CallID Event DNIS UserID Time SeqID MobNo-----------------------------------------------------------------------------1 | 43 | 100 | |09:00:05 | 1 |98332107701 | 40 | | |09:01:05 | 2|1 | 41 | | |09:02:05 | 3|1 | 42 | | 25 |09:03:05 | 4|2 | 43 | 200| |09:10:05 | 1 |98339755362 | 40 | | |09:11:05 | 2|2 | 41 | | |09:12:05 | 3|2 | 42 | | 26 |09:13:05 | 4|3 | 43 | 300| |09:15:05 | 1 |98139785363 | 40 | | |09:15:55 | 2|3 | 41 | | |09:16:05 | 3|3 | 42 | | 28 |09:18:05 | 4|4 | 43 | 100| |09:45:05 | 1 |92239788964 | 40 | | |09:46:05 | 2|4 | 41 | | |09:47:05 | 3|4 | 42 | | 26 |09:48:05 | 4|Output:CallID DNIS UserID MinTime MaxTime MobNo--------------------------------------------------------------------------1 | 100 | 25 |09:00:05 |09:03:05 |98332107702 | 200 | 26 |09:10:05 |09:13:05 |98339755363 | 300 | 28 |09:15:05 |09:18:05 |98139785364 | 100 | 26 |09:45:05 |09:48:05 |9223978896In short I am getting DNIS and MobNo for Event 43 andUserId for Event 42.How can I achieve the above result?Is it possible with scalar query?Could any one help me for the same?Thanking in AdvanceSanjeev
View 1 Replies View RelatedData Migration Oracle 7.16 To SQL Server 2000
Aug 22, 2006Hi,
I have been asked to look at optimising a Data Migration Strategy for one of our clients. I am quite new to Data Migration (I'm actually a system designer!).
The migration is from a legacy Oracle 7.16 Db to a SQL Server 2000 environment.
The legacy Db has some large tables - 30 Million+ rows
Table/column Mapping is 1:1
At present we are using DTS to import into the SQL environment.
My question is: Is there any way to leverage the Oracle 7.16 export to speed up the migration process?
Any advice would be appreciated.
Many thanks
Mike
Automating The Importation Of An Oracle Table Into Sql Server 2000
Aug 25, 2004Hi guys,
I figure this should not be a complex one. I know how to manually pull in data from Oracle 9i into SQL Server 2000 using DTS. However this is my issue.....
I simply want to automate the pulling in of data from 1 table in my ORACLE 9i database into another table in my Sql Server 200. I was hoping I could simple write a stored procedure that would sort of utilize a dblink like in ORACLE and then schedule that procedure. Is this feasible in Sql Server, and how would one go about setting this automated import up????
Thanks in Advance all............
'Wale
How To Import An Oracle 8.1.7 Dump File Into SQL Server 2000 ?
Mar 2, 2005Hi,
I do not know if it can be done:
how to import an Oracle dump file into SQL server ?
That dump file contains the content of a table, excported from Oracle.
Is there a way or another to import it to SQLServer 2000 ?
Thanks
How To Link/connect To Oracle Server From SQL Server 2000?
Oct 6, 2005Greetings,
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
Accessing SQL Server Data From Oracle Developer 2000 Apps
Feb 17, 1999Hello there
I have a customer that wants to start using SQL Server 7.0 instead of Oracle, but they have a lot of Oracle applications (made in Developer 2000 and Forms). Due to the big effort needed to convert those applications (to Visual Basic), they want to be able somehow to migrate only data from Oracle to SQL Server, and then use some tool that will allow them to use the same applications with the SQL Server 7.0 engine.
I found on the "SQL Server Developer's Kit" a document describing the process of migrating data and applications from Oracle to SQL Server. There is a mention to an Oracle tool called "Oracle Gateway to SQL Server" that will translate calls received by the Oracle engine to the SQL Server engine, but this approach will still require the existence of Oracle servers (which is the thing the customer no longer wants).
The second option mentioned on that document, and the one that seems more attractive, is the use of Oracle "Open Client Adapter" (OCA) that will allow Developer 2000 applications to communicate directly to SQL Server via ODBC. However, I have been unable to find (on the Oracle site) any documentation regarding this tool.
Has anynoe heard or used such tool (OCA) ? Any comments, tips, drawbacks, experience you could share ? Any other idea on how a thing like that could be accomplished ? Perhaps a third-party application that can act as a gateway between the Forms app and ODBC/SQL Server, or that will be able to generate VB code from a Developer 2000/Forms app...
Any help will be appreciated.
Please respond directly to me, as I am not a member of the distribution list.
Luis Francisco Silva
Executing (from Sql Server 2000) Procedures From Oracle Package Through Linkserver
Feb 16, 2004Hello,
I deaply need to know how to execute procedures from package in oracle, from sqlserver 2000 using linkserver.
Thank you very much,
Victor
DBA
Linked Server From SQL-Server 2000 To Oracle 8.1.7
Jul 20, 2005We have a linked server, which worked fine, but we have just changedthe collation sequence, in SQL-Server, fromSQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS.Now trying to use the Linked server, we get the following message:"Error 7399: OLE DB Provider 'MSDAORA' reported an error. The providerdid not give any information about the error. OLE DB error trace [OLEDB provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:The provider did not give any information about the error]."Ha anyone seen this before, because I am not sure why changing thecollation sequence, would affect the Linked Server
View 3 Replies View RelatedRow Wise Operation In MSSQL Server
Jun 14, 2004How can I create a row wise trigger in MSSQL Server .
CREATE TRIGGER trigName ON tableName for
INSERT , UPDATE , DELETE
AS ...
For a multiple delete , I got only one trigger invocation .
But I need individual trigger calls for each row ...
How can I do this in t-sql ?
Is there any usage like FOR EACH ROW in Oracle ?
Is it possible through INSTEAD OF TRIGGER ?
Please help !!!!!!!!
Is It Wise To Decrypt Stored Procedures In SQL Server?
May 5, 2008Hi All,
I'm new to SQL Server 2005 encryption security. I developed a simple login form using .NET 2 framework. I'm encrypting the user's password on the execution of the INSERT statement using SQL Server EncryptByCert(Cert_ID('abc'),'password').
My only concern is that of decrypting the password on the execution of the SELECT dtatement in a stored procedure using the DecryptByCert(Cert_ID('abc'),val,N' certificate password'). Anyone who has rights on executing that particular stored procedure in SQL Server can easily return the user's password.
Can I still use the SQL encryption, whilst the login application handles the decryption (meaning I embed the DecryptByCert in the application ).
If anyone has other ways of implementing this please forward them? (I preferable would like to store all the certificates, private keys, etc on the database side )
Thanks,
Matt
Name Value Pair
Mar 12, 2008Hi,I need to display a dataset where everything is dynamic.e.g. I have a table with columns "Code", "Description" and "Inspected" andanother table with columns "UserCode", "Name", "PostCode" and "Town" etcAnd I need to dislay data like this from a single db proc with parameters:-(TableName, ColumnName, ColumnValue)Procedure called with these parameters (CodeTable, Code, TD001) would returna dataset like this:-----------------------------------|Code | TD001|Description| Printer|Inspected | Y---------------------------------Not----------------------------------|TD001|Printer|Y---------------------------------Procedure called with these parameters (UserTable, UserCode, CP1) wouldreturn a dataset like:----------------------------------|UserCode | CP1|Name | Charles|PostCode | 2000|Town | Sydney---------------------------------Not---------------------------------|CP1| Charles| 2000| Sydney---------------------------------Any ideas how I would code the database proc, I did consider using XML butnot sure.ThanksAJP
View 3 Replies View RelatedSQL Server 2012 :: Query To Get The Month Wise Data?
Sep 13, 2015I have the data in the below format.
Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201406 xxxxxx 27 1
In the above data, no record exist for 201403,201404,201405, query I wrote will give only the data for which there LeftCount exists, but I am looking for a query which get the data in the below format.
Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201403 xxxxxx 28 0
201404 xxxxxx 28 0
201405 xxxxxx 28 0
201406 xxxxxx 27 1
Pair Of Records
Sep 17, 2004Hi folks,
I am trying to write a query to get data in pairs, for example, i have data like this:
sr_no week_no
1 24-A
2 24-B
3 24-C
4 25-A
5 25-B
6 26-A
7 26-B
I want to get data in pairs i.e. data for week_no 24-A and 24-B will come togather? is it possible?
Any urgent help will be highly appreicated.
Thanks
Name Value Pair Design
Jul 20, 2005I want to store many different types of objects in a single table. Iwas thinking of using the name value pair approach to achieve this.Does anybody have any experience with a such a design?The table might look like thisCREATE TABLE NV (pk int, type int, [name] varchar(100), valuevarchar(100))--Insert a manager - type = 1INSERT INTO NV (pk, type, [name], val)VALUES (11, 1, 'FirstName', 'John')INSERT INTO NV (pk, type, [name], val)VALUES (11, 1, 'LastName', 'Smith')INSERT INTO NV (pk, type, [name], val)VALUES (11, 1, 'Position', 'CEO')--Insert an employee - type = 2INSERT INTO NV (pk, type, [name], val)VALUES (21, 2, 'FirstName', 'Joe')INSERT INTO NV (pk, type, [name], val)VALUES (21, 2, 'LastName', 'Blog')INSERT INTO NV (pk, type, [name], val)VALUES (21, 2, 'Position', 'Developer')--Insert an inventory item - type = 3INSERT INTO NV (type, [name], val)VALUES (13, 3, 'Name', 'Chair')INSERT INTO NV (type, [name], val)VALUES (13, 3, 'Color', 'White')INSERT INTO NV (type, [name], val)VALUES (3, 3, 'Price', '$150')
View 3 Replies View RelatedKey-Value Pair Design
Aug 8, 2007Hi,
I have to log the Details of the incoming xml message into databse.
But the values logged will vary with the message.So I cant fix the mumber of columns.
I thought of using table in which the fields are logged as Key-Value Pairs. The table looks as below.
TransactionID ColumnKey ColumnValue
1111 PONumber 123
1111 Sender xxx
1111 Recever yyy
using dynomic query i was able to get the results as follows
TransactionID PONumber Sender Receiver
1111 123 xxx yyy
Till now every thing was fine. but now i got new requirement where i have to identify each column with its parent. For example if we consider the line items of the PO, table may look like below.
TransactionID ChildKey ChildValue ParantKey ParantValue
1111 PONumber 123 null null
1111 Sender xxx null null
1111 Recever yyy null null
1111 ItemName soap ItemID 123
1111 Quantity 4 ItemID 123
1111 UnitPrice 2.2 ItemID 123
1111 ItemName Brush ItemID 222
1111 Quantity 5 ItemID 222
1111 unitPrice 4.4 ItemID 222
I am unable to design the database which satisfy the requirement of the reporting.
I not even know how to query the data which is logged like this.
Help me by giving the inputs to design databse for the above problem and to query the data .
advance thanks
Srinivasa Mahendrakar
SQL Server 2008 :: Rows Holding Group-wise Maximum Of Certain Column?
May 13, 2015is there any more efficient way for example to implement the next query?
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;
WHERE dealer = 'dealer sample'
What indexes I should create for this query?
Transact SQL :: How To Display Data Party Name And Time Interval Wise In Server
Sep 9, 2015i want to show data Party Name and Time interval wise. here is my table from where i will fetch data. so pasting table data here.
Call start Call duration Ring duration Direction Is_Internal Continuation Party1Name Park_Time
------------------------- ---------------- ------------- --------- ----------- ------------ --------------- -----------
2015/06/08 08:06:08 00:02:28 2 I 0 0 Emily 0
2015/06/08 08:16:38 00:00:21 0 I 0 1 Line 2.0 0
2015/06/08 08:16:38 00:04:13 5 I 0 0 Jen 0
[code]...
now i am not being able to cross join this CTE with my table to get data party name wise and time interval wise. say for if no data exist for a specific time interval then it will show 0 but each party name should repeat for time interval 9:00:00 - 9:30:00 upto 17:30:00. i like to add what filter need to apply to get data for incoming, outgoing, call transfer and miss call.
For Incoming data calculation
where direction='I' and
Is_Internal=0 and continuation=0 and
RIGHT(convert(varchar,[call duration]),8)<> '00:00:00'
For outgoing data calculation
[code]...
How Do I Delete One Of A Pair Of Records.
Sep 8, 2007I have a table, gdbdoc, that contains record-key pairs, linking records in another table. There is no significance in the order of the link: if records A and B are linked, then I don't care whether the link is A -> B or B -> A, and my normal query logic is SELECT ... Where DCIindiid = A ... union SELECT ... Where DCILinkid = A(DCIindiid = key1, DCILinkid = Key2)
The link-creation process normally checks whether there is already a link in either direction. Thus before creating a link A->B the logic checks to see whether either the A->B or B->A link record exists, and a new link is not created if the link already exists in either direction. However recently one of my processes bypassed the reverse-link check, and I've ended up with a few hundred cases where there is both an A->B link and a B->A link.
If I run a query: - select gd1.* from gdbdoc as gd1 join gdbdoc as gd2 on gd1.dciindiid = gd2.dcilinkid and gd1.dcilinkid = gd2.dciindiid
this displays all the records where one record links A -> B and there is also another record that links B -> A.
How do I write a query to delete ONE of the pair of duplicate records? I have two problems: -
Problem 1: Table gdbdoc is keyed on (DCIindiid, DCILinkid). Both guids are needed to create a unique key, and the table does not have a single key field. You can't write DELETE gdbdoc where DCIIndiid, DCILinkid IN select gd1.dciindiid, gd1.linkid from gdbdoc as gd1 join gdbdoc as gd2 on gd1.dciindiid = gd2.dcilinkid and gd1.dcilinkid = gd2.dciindiid
as the DELETE ... SELECT ... syntax only seems to support a single returned value.
Problem 2. If we solved problem 1, we would (I think) delete BOTH the A->B link and the B->A link , whereas I only want to delete one of these links.
Afterthought: Problem 2 seems easily solvable: add "Where gd1.DCIindiid < gd1.DCILinkid" to the DELETE ... statement. Although the concept of "<" doesn't really mean anything with a guid, this is accepted by SQL, and halves the number of records returned by the select. Obviously I don't care which of the two links (A->B or B->A) is deleted.
Regards, Robert Barnes
Need Another Pair Of Eyes Sql 'Order By'
Feb 9, 2006my page suddenly stopped working when I wasn't working on it and it seems to be down to the 'ORDER BY' part of my SQL. I'm here alone as usual and I need someone to glance at the sql strings below. (yes, I do need the select *)
If I run this in SQL Manager it works fine:
SELECT * from dest_search WHERE trip_type like 'Trekking' ORDER BY start_date
if I do the same from my asp page it fails but if I leave out 'ORDER BY start_date' it works.
the error I get is:
Microsoft OLE DB Provider for SQL Server error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/Newindex/trip_types.asp, line 53
line 53 is the 'desc = oRS...' bizarrely
oRS.Open strSQL, oConn, 2, 3
oRS.moveFirst
Do while not oRS.eof
country = oRS("country")
53---> desc = oRS("description")
url_link = oRS("url_link")
startDate = oRS("start_date")
endDate = oRS("end_date")
trip = oRS("trip_type")
difficulty = oRS("difficulty")
not all the descriptions are filled in (some are null) but that doesn't stop SQL manager from working or unordered results coming up fine in my web page.
any comments gratefully received thanks.
Primary Key Pair Constraints
Apr 29, 2008Hi Folks,
I would like to create a table with primary key pair:
Key1 : nchar(10)
Key2: nchar(10)
Value: money
That is, Key1 and Key2 are the primary key columns for the table. I would like to think of (Key1='Foo', Key2='Bar') to be the "same" as (Key1='Bar', Key2='Foo'). Is there a way to enforce this as a table constraint, or do I have to enforce this manually in all procedures that modify and read the table?
Thanks!
Adam Cataldo
SQL Server 2012 :: Day Wise And Date Range Calculation With Looping Or Dynamic Data?
May 14, 2015I am using Sql Server 2012.
This is how I calculate the ratio of failures in an order:
31 Days Table 1 query
sum(CASE
WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31
WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0
ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days1 .
How do i loop and pass dates dynamically in the Datediff?
31 Failures Table 2 query
SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102))
AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,31 Day Cal(Formula) combining both Table 1 and Table 2
((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]This works fine when done for a specific order.
I want a similar kind of calculation done for day wise and month wise.
2. what approach should I be using to achieve day wise and month wise calculation?
I do also have a table called Calender with the list of dates that i can use.
Key/value Pair Table Update Query
Jan 18, 2005I'm working with a table that I've created called Config which contains key/value pairs used to get and set site-wide settings. I'm now trying to create a web form which updates the table but I'm not sure how to create the most effective UPDATE query.
Table of course takes this form key | value
---------------------------------
config_setting1 | value1
config_setting2 | value2
I'm working with a System.Collections.Specialized.StringDictionary Class object which contains all of the pairs from my webform... anybody have a creative way to build an UPDATE string using this object???
Thanks for any help and suggestions,
ecolner@yahoo.com
Extended Events With Pair Matching
Oct 12, 2015I can only do one match at a time -- Like can only do either the sql_statement_(start and end), or sp_statement_(start or end). Is there any way to capture both in the same session? Or since I am adding both the events in the ADD EVENT section, can I query it somehow to get unmatched SP or SQL?
Code:
USE master;
GO
-- Create the Event Session
IF EXISTS(SELECT *
FROM sys.server_event_sessions
WHERE name='TimedOutSQL')
[code]...
Transact SQL :: Select Data In Pair?
Sep 22, 2015my business user want all record where glcode must start with 2 and 4
<sample data
Tran_No
GLCode
abcd123
2123
abcd123
21235
abcd123
4289
[code]....