Joining MS SqlServer Data With Oracle Data

Nov 7, 2007

OK so there is some data in an Oracle DB that I need to query and analyze.  Unfortunately, the criteria for selecting/grouping the data is stored in a MS Sql Server DB.  This cannot be changed. 

SqlServer
Group Name       ID#      Item     Condition
AAA123              1         a              1
AAA123              2         a              1
AAA123              3         a              1
AAA123              4         a              2
AAA123              5         a              2
AAA123              1         b              3
AAA123              2         b              4
AAA123              3         b              3
AAA123              4         b              4
AAA123              5         b              3
BBB123              1         a              1
BBB123              2         a              1
BBB123              3         a              2
BBB123              4         a              2
BBB123              5         a              2

Oracle
Group Name       ID#     Value
AAA123              1          50%
AAA123              2          55%
AAA123              3          60%
AAA123              4          80%
AAA123              5          70%
BBB123              1          35%
BBB123              2          45%
BBB123              3          50%
BBB123              4          50%
BBB123              5          80%

 I need to be able to get this:
Group Name  Item   Condition Value
AAA123          a           1          55%
AAA123          a           2          75%
AAA123          b           3          60%
AAA123          b           4          67.5%
BBB123          a           1          40%
BBB123          a           2          60%

 Any idea how I can get the data from these two DBs to talk to each other?  Thanks.

View 6 Replies


ADVERTISEMENT

Importing Oracle Data In SqlServer Using Express

Nov 8, 2007

OK so there is some data in an Oracle DB that I have to summarize based on grouping info stored in a SqlServer DB. How can I import the Oracle data into a SqlServer temp table using SqlServer Express? Thanks.

View 2 Replies View Related

DataFlow Problem Moving Data From SQLServer To Oracle

Feb 26, 2008

Hi pals,

I have a small problem in Datflow task.

Basically i am having two tables(similar to my current requirement). One in sql server 2005 and another in ORacle

SQL Table
create table t1
( c1 numeric(6),
c2 numeric(6),
c3 varchar(50),
year numeric(4),
district varchar(10)
)


Oracle Table
create table app_t1
( c1 number(6),
c2 number(6),
c3 varchar2(50),
year number(4),
district varchar2(10),
constraint cpk primary key (c1,year,district)
)

I am using 2 global variable for passing values to c4 and c5 columns in oracle table.
The variable names are g_year and g_district.
i am using a dataflow task to move data from sql server 2005 to oracle 9i database
I am able to load the data from sql server to oracle.
Till here it is fine.
But the requirement is if i get same data i.e same dist and same year then we need to delete the data
in oracle table as
"delete from app_t1 where year = ? and district = ?"

Immediately i need to issue a commit statement.

and thereafter i need to load data for that particular district and year.


To accomplish this requirement i am using ,

An Execute SQL Task to Delete the data from ORacle
An Execute SQL Task to COMMIT the transaction
Finally i am loading data from sqlserver to oracle using a dataflow task.

But i am getting an error as
"Value violated the integrity constraints for a column or table.".

I think the deletion operation is not getting committed.

So what i did was i connected to oracle and deleted the data using above DELETE statement
and again come back to SSIS package and ran the package once again, this time it is successfull.

Why is it so. Any thoughts on this?

Any help would be greatly appreciated.
Thanks!

View 13 Replies View Related

Joining Summary Data With Detail Data

Mar 19, 2007

I know similar questions have been asked but I wanted to try my luck that my issue is somehow different.

I am querying a database which has detail information (sales transactions) and is grouped by customer. I also have a table with one record for each customer with some historical sales information (summary information). The requirements for the report are to have the sums of the sales for each customer along with the historical data for that customer in the same row in the table. I haven't found a way to do this using one dataset and from what I've read, the current version doesn't support joining multiple datasets over a grouping field (customer).. or at all.

Any one have ideas?

View 1 Replies View Related

Integration Services :: SSIS VB Script Loading Data Into Oracle DB Missing Some Data

Nov 10, 2015

I'm using Script Component to load data into Oracle DB due to the poor performance issue. Now, I found it will missing some data during the transmission. Please see the screenshot below: 

SQL Server:
Oracle:
DDL:

create table Person
(
BusinessEntityID Integer,
FirstName nvarchar2(50),
MiddleName nvarchar2(50),
LastName nvarchar2(50)
);

Result:

I follow up this article: [URL] ....

VB Script: 
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

[Code] ..........

View 8 Replies View Related

SqlServer 2005 String Or Binary Data Would Be Truncated When Data Is OK

Feb 21, 2006

When using AquaData or JDBC (inet tds driver), when doing an insert using SqlServer 2005, I get error "String or binary data would be truncated" when the data is actually OK. There are no triggers, etc. that would confuse the situation. It works fine in SqlServer 2000.

The scenario is as follows:

Create table:
create table test3 (
name varchar (18) ,
tbname varchar (18)
)

Create and populate table:
create table maxtable (
tablename varchar (18) not null,
[...]
)

Try to insert into test3:
insert into test3 (name, tbname)
select i.name, o.name
from dbo.sysindexes i, sysobjects o, maxtable m
where i.indid > 0 and i.indid < 255
and i.id = o.id and i.indid = 1
and o.name = lower(m.tablename)

And I get the error "String or binary data would be truncated." The values being selected for i.name and o.name have maximum length of 18. There are other rows in sysindexes and sysobjects with longer values, but they are not being selected.

The error does not occur with SQL Server Management Studio, and does not occur using SqlServer 2000.

View 6 Replies View Related

Importing Data From Oracle To Sql Loosing Data After The Decimal Point

Jun 18, 2007

I have created a simple package that uses a sql command to pull data from an oracle database and inserts the data into a sql 2005 table. Some of the data fields that i am pulling from contain two digits after the decimal point, however this data is lost when it gets into sql. I have even tried putting the data into a flat file, and still the data is lost.

In the package I have a ole db source connection which is the oracle database and when i do the preview i see all the data I need. I am very confused and tried a number of things to get the data into sql, but none work. Any ideas would be very helpful.

thanks

View 6 Replies View Related

Problem Unicode Data 0x2300 In SQLServer 2000 SQLServer 2005 Express

Sep 20, 2006

Hi experts;
I have a problem with unicode character 0x2300
I created this table
create table testunicode (Bez nchar(128))

Insert Data
insert into testunicode (Bez)values('Œ€„¢')
with 2 Unicode characters
Œ€ = 0x2300
„¢ = 0x2122

Selecting the data
select Bez from testunicode
I see
"?„¢"

„¢ = 0x2122 is ok but instead of 0x2300 there is 0x3f

When I modify the insert statement like that ( 8960 = 0x2300 )
insert into testunicode (Bez)values(NCHAR(8960)+'„¢')

and select again voila i see
"Œ€„¢"
Does anyone have an idea?

Thanks

View 1 Replies View Related

&&<Data&&>Microsoft.SqlServer.Dts.Pipeline.BlobColumn&&</Data&&>

Feb 26, 2008



I have a custom (dataset) destination component from ms samples and it has an input holds DT_NTEXT value.

Whenever I try to retrieve data from this it returns "Microsoft.SqlServer.Dts.Pipeline.BlobColumn" as value.

I try this but didn't work:


String sValue = System.Text.Encoding.Default.GetString(Convert.FromBase64String(this.dataSet.Tables[0].Rows["Data"].ToString()));


It throws an execption "invalid character in.."

Please help how I can convert this?

Thanks in advance

View 6 Replies View Related

Data Access :: Accessing Oracle Tables From Server Via Oracle DBLINK?

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

Joining Data Types

Jun 6, 2008

Can anyone show me how to join 2 different data types in a SQL query. For example, I want to join two tables.

Table 1:
- Field 1 = ID (Varchar,10)
- Field 2 = Balance

Table 2:
- Field 1 = SSN (Numeric, 9)

Field ID should be = to SSN, but the data type is different. PLease help me to join them.

Thanks,

View 7 Replies View Related

Best Way Of Joining Tables Data

Oct 17, 2007



Hi,

I have multiple table with the same schema as follows
Table1
ID Date
---------------

1 D1
2 D2
3 D3
4 D4

Table2
ID Date
---------------
2 D21
3 D31
4 D41

.....

I just want to build a mater table out of these existing tables . I need a query that builds as faster as possioble as these tables might contains millions of records

The final Table Should be as follows

ID CatName Date
------------------------------
1 Name1 D1
2 Name2 D2
2 Name21 D21
3 Name3 D3
3 Name31 D31
4 Name4 D4
4 Name41 D41

Can any one provide me with the best performace query that results the above output

With Best Regards,

~Mohan




View 2 Replies View Related

Joining Same Data From Two Different Tables

Oct 8, 2007

Hello,
I am using Visual studio 2005 and have such a problem. I have a report that takes most of its date from €œordersview€? that is linked to lots of other fields. The final report has fields such as product name, number, Date, quantity and actual quantity etc. This works fine, but the program that I get the data from can€™t keep all the data in itself, because it slows the time of its work enormously. So we made it so that all the data of orders that are completed ant older that 2 days would be copied to another table €œOrdersArchive€? and deleted from €œOrders€? table.
Now I need to make a report that takes data from €œordersArchive€? if its older that two days, and from €œorders€? if it is new. The fields are the same in both cases and I would like the data to appear in the same table so it could be grouped, sorted and evaluated by the same criteria. Is it even possible and if it is how should I do it?
For now I only managed to create 2 separate tables in the same report that take date from different datasets.
Regards
Darius

View 4 Replies View Related

Joining Different Table Data

May 30, 2008



Dear all,

I have four different tables with three columns. The first two columns are equal for all tables. The third column is different:

Table I
COUNTRY | PRODUCT | SALES VALUE
AAA AAA 10
AAA AAB 10

Table II
COUNTRY | PRODUCT | SALES COST
AAA AAC 10


Table III
COUNTRY | PRODUCT | SALES MARGIN
AAB AAA 10

Table IV
COUNTRY | PRODUCT | SALES XXX
AAA AAA 5

What I would like to accomplish is to obtain a table where the records from these tables would be joined in a single table:

COUNTRY | PRODUCT | SALES VALUE | SALES COST | SALES MARGIN | SALES XXX
AAA AAA 10 0 0 5
AAA AAB 10 0 0 0
AAA AAC 0 10 0 0
AAB AAA 0 0 10 0

Can you advise me on what the best way to achieve this is? Thanks!

Kind regards,
Pedro Martins

View 4 Replies View Related

How To Migrate Oracle Applications 11.03/Oracle 8.05 To Navision 4.0/ms Sqlserver

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

Joining And Conditional Column Data Help

Dec 21, 2006

I have 2 table that I want to join and output a row on a condition that one of the records have a null in the field. Heres what I have.employee table (empid, name)tasks table (taskid, empid, taskname, resolution)If the resolution is null than I want it to be accounted for in each employee record. Heres my query so far that joins the 2 tables and accounts for each employee and counts each task they have. I need another column that counts the tasks.resolution's null values for each employee but cant figure it out. Thanks for any help!SELECT e.empid, e.name, COUNT(t.ID) as 'tcount'FROM tasks t RIGHT JOIN employee e ON c.empid = t.empidGROUP BY e.empid, e.nameorder by 'tcount' desc

View 3 Replies View Related

Problem Joining Child Data (JOIN, Subquery, Or Something Else?)

Jun 21, 2006

The problem:I'm updating a report to be "multi-language" capable. Previously,any items that had text associated with them were unconditionallypulling in the English text. The database has always been capable ofstoring multiple languages for an item, however.Desired output:Given the test data below, I'd like to get the following resultsselect * from mytestfunc(1)Item_Id, Condition, QuestionText1876, NOfKids <= 10, This many children is unlikely.select * from mytestfunc(2)CheckID, Condition, QuestionText1876, NOfKids <= 10, NULLThe current SQL for my UDF:CREATE FUNCTION Annotated_Check (@Lang_ID int) RETURNS TABLE AS RETURN (SELECT tblCheck.Item_ID, tblCheck.CheckDescr AS Condition,tblQuestionText.QuestionTextFROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =tblQuestionText.Item_ID)WHERE ((tblQuestionText.LanguageReference = @Lang_ID) OR(tblQuestionText.LanguageReference IS NULL)))Test data:CREATE TABLE [dbo].[tblCheck] ([Item_ID] [int] NOT NULL ,[CheckDescr] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[tblCheck] ADDCONSTRAINT [DF__tblCheck__Creati__0D7A0286] DEFAULT (getdate()) FOR[CreationDate],CONSTRAINT [PK_Check] PRIMARY KEY CLUSTERED([Item_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE TABLE [dbo].[tblLanguage] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Priority] [int] NULL ,[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Spoken] [bit] NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblLanguage] WITH NOCHECK ADDCONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblLanguage] ADDCONSTRAINT [DF__tblLangua__Creat__2CF2ADDF] DEFAULT (getdate()) FOR[CreationDate],UNIQUE NONCLUSTERED([Priority]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE TABLE [dbo].[tblQuestionText] ([Item_ID] [int] NOT NULL ,[LanguageReference] [int] NOT NULL ,[QuestionText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SameAs] [int] NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[tblQuestionText] ADDCONSTRAINT [DF__tblQuesti__Creat__76969D2E] DEFAULT (getdate()) FOR[CreationDate],CONSTRAINT [PK_QuestionText] PRIMARY KEY CLUSTERED([Item_ID],[LanguageReference]) WITH FILLFACTOR = 90 ON [PRIMARY]GOINSERT INTO tblCheck (Item_ID, CheckDescr)VALUES(1876, 'NOfKids <= 10')INSERT INTO tblLanguage (ID, Priority, Name, Spoken)VALUES(1,1,'English', 1)INSERT INTO tblLanguage (ID, Priority, Name, Spoken)VALUES(2,2,'Espanol', 1)INSERT INTO tblQuestionText (Item_ID, LanguageReference, QuestionText)VALUES (1876, 1, 'This many children is unlikely.')Any tips or pointers will be appreciated. Thanks.

View 2 Replies View Related

SQL Security :: Data Masking Removed After Joining With Other Tables In CTP 2.1?

Jul 6, 2015

I find that dynamic masking does not work on joining tables in SQL Server 2016 CTP2.1.

For examples, I create the following table:

CREATE TABLE [dbo].[HRM_StaffAppointment](
[StaffID] [nvarchar](11) NOT NULL,
[ApptSeqNo] [smallint] NOT NULL,
[ReportingDept] [nvarchar](10) NULL,

[Code] ...

Then I apply mask on StaffID and RankDesc.

alter [dbo].[HRM_StaffAppointment] alter column [StaffID] add masked with (function='default()')
alter [dbo].[HRM_StaffAppointment] alter column [RankDesc]
add masked with (function='default()')

When User A logged in and query on HRM_StaffAppointment, StaffID and RankDesc are perfectly masked. But User A can remove the masking using another table:

CREATE TABLE [dbo].[staffID](
[staffID] [nvarchar](255) 
) ON [PRIMARY]
select a.*
from dbo.HRM_StaffAppointment as a
left join dbo.staffID as b
on a.StaffID = b.StaffID

It looks like a security hole to me, or I'm doing anything wrong?

View 4 Replies View Related

COnverting Numeric Data Type (Oracle) To Date Data Type Using SSIS

Mar 7, 2007

We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.

I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.

On Error, If I fail the component, then the error is :

There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Regards

RH

View 3 Replies View Related

How To Transfer Data From A SqlServer Database To A SqlServer Express Database

Mar 29, 2006

Is there a way to transfer data from a SqlServer db to a SqlServer Express db. I tried to use the backup file of SqlServer, but this file is not valid for SqlServer Express. Or there any alternatives?

thanks,

Henk

View 7 Replies View Related

Asp.Net And SqlServer Data

Oct 19, 2004

Hi

can anybody tell me that:
1)How can i retrieve a binary field (image field) that stored in SqlServer2000 with Vb.Net and save
it again in my hard disk that i have again that file?
2)can SqlServer itself convert image field to file with its store procedures?
3)can sqlserver run an exe file on local computer from its storeprocedure?

Best Regards

View 1 Replies View Related

How Do I Get My Data Into SqlServer?

Mar 16, 2006

I have a development machine that I have prototyped with access.  I don't know how to get the access data into Sql Server on the Internet.  Can you help me?  I'm not sure where to start.  Thank you for any help.

View 5 Replies View Related

Saving Data Into SQLServer

Nov 6, 2007

Hi,
     
Here is a structure of my table 
create table events( event_id integer primary key identity(1,1), event_name varchar(200), event_date datetime,)
Note: The event_id is a automacally and we don't need to insert record in this field.
for example
i have inserted 10 records in a table
then i deleted all records manually from sql server
but when again I save the record the event_id field shows started from 11 even the table was empty,
although it should be start from 1?
 

View 6 Replies View Related

Fatch The Data With Two Different Sqlserver.

Aug 11, 2005

Can i fatch the data with to different sqlserver.i want a query i.e  select query fatch the data this different database and diff.table.i.e. sqlserver to sqlserver  and oracle to sqlserver

View 2 Replies View Related

About Accessing Data From Sqlserver

Sep 15, 2005

Hello sir,I have installed .net1.1version on windows2003 operating system.I have also installed sqlserver2000  on the same system.My problem is that when i am trying to get data from sqlserver from asp.net program i am getting error as 'access denied to user NT AUTHORITY/NETWORK USER'.I request you to kindly suggest me with an appropriate solution.Thanking you.Please email me on:-aanandkumar786@yahoo.com

View 1 Replies View Related

Restore Data From .Mdf And .Ldf For SQLserver 7.0

Dec 23, 1999

Hi all,

Since my daily backup failed, I need to restore a database
from .mdf and .ldf files so that i can get all the transactions.


Thanks for your help

Sanjeev Kumar

View 1 Replies View Related

Import Data To Sqlserver

Feb 5, 2008



Hi,
How to expor data from Filemaker to Sqlserver? From the Filemaker, I am exporting data into Tab File(values are separated by tab). How to put data from that file into Sqlserver tables?

Thanks

View 3 Replies View Related

Oracle Accessing Data

Oct 22, 2001

There is any tools or products that allow Oracle Users to access SQL 2000 data in realtime? I know how to go in the opposite direction using linked servers, but am not sure on this direction,

Thanks
David

View 1 Replies View Related

Data Source From ORACLE

Sep 7, 2006

Currently, I am working SSIS project that its data source from ORACLE 9i. I installed full SSIS in server and also ORACLE. So, I have three connections available in server. They are the following below.

Oracle Client
Microsoft OLE DB Provider for Oracle
Oracle Provider for OLE DB

The problem is the three component can not recognize parameterization and ORACLE store procedures.

Finally, I executed the store procedures by ORACLE package from command prompt (bat files). So, the command execute from SSIS package.

Ashari Imamddin

View 1 Replies View Related

Import Data From Oracle

May 2, 2007

I am using Import and Export wizard and seeing a very weird behaviour.



I am able to choose 'Microsoft OLE DB Provider for Oralce' and provide server name, username/ password. The test connection results in a success. However when I try to move to next window/page by clicking next I get invalid oracle error ora-01017 - invalid username/ password.



what is going on here?

View 1 Replies View Related

ADO.NET Data Provider For Oracle

Jan 9, 2008

Hi,
how do you pass parameters into a SQL statement to run in an Oracle database when you use the OracleClient Data Provider?

thanks in advance
Peter

View 3 Replies View Related

Oracle BLOB Data

Dec 18, 2007

Hi, I have to migrate an oracle database over to SQL Server 2005. One of the tables has PDF files stored as BLOB. All the ORACLE providers in SSIS do not support BLOB column. What can I use or how can I get the PDF files into my SQL table? The SQL column can be either IMAGE or VARBINARY(MAX).

Thanks,

Les

View 8 Replies View Related

Cannot Import Oracle Data With OLE DB

Mar 6, 2006

I have a column in an Oracle source system with data type NUMBER(38,2). The value "-0.01" is causing problems when trying to import into a SSIS data-flow.

The only way I can import this into my data-flow is by using a Datareader connection manager using the ODBC Data Provider. My DSN is using the Oracle ODBC driver.

If I try and use the "Native OLE DBMicrosoft OLE DB Provider for Oracle" I get an error: "The data value cannot be converted for reasons other than sign mismatch or data overflow"

Judging by this post: http://microsoftdw.blogspot.com/2005/11/final-storyhow-to-get-data-out-of.html there aren't really any other combinations to try that will bring my data in as I want it.

I don't want to use ODBC though as:

Its an old technology
Its slow
I have to deploy an additional DSN.

Can anyone tell me why the other options don't work? Why does Microsoft OLE DB Provider for Oracle have a problem with "-0.01"?

-Jamie

 

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved