Tag: oracle

Oracle and SQL Server – different results when comparing date to null

I have following query in JPQL: select * from Record rec where rec.validFrom <= :param and (rec.validTo is null or rec.validTo >= :param) I know, that there exists only one record in database. If I run it with param = null, on Oracle I get the record back. However if I run it on a […]

Setup of DG4SQL Gateway from Oracle 11.2 to SQL 2005

First, I am new to using Oracle and need to make a connection from Oracle 11.2 to SQL Server 2005. I have read many articles, but I still can’t setup a heterogeneous connection. I am asking for any help, please?? Thanks in Advance The error displayed is ORA-12154 I am using one pc running Windows […]

Error while trying to run Oracle stored procedure using Linked Server in SQL

I am trying to run an Oracle stored procedure using linked server on SQL Server. Stored procedure is crs.uf and it accepts a parameter p_date. From SQL Server, I am executing it like this: EXECUTE (‘begin crs.uf(“2016-02-26″); end;’) AT LServer I’m getting this error: OLE DB provider “OraOLEDB.Oracle” for linked server “LServer” returned message “ORA-06550: […]

MS SQL Linked Oracle Server join condition

I am trying to create a view using both SQL tables and tables from linked Oracle database & the SQL part as below SELECT empv.FULL_NAME, CAST(C_Date + ‘ ‘ + SUBSTRING(C_Time, 1, 2) + ‘:’ + SUBSTRING(C_Time, 3, 2) + ‘:’ + SUBSTRING(C_Time, 5, 2) AS DATETIME) AS “PUNCH_TIME”, tT.C_Name “MACHINE_NAME”, CASE WHEN LEN(L_UID) < […]

SSIS failing to get column value and setting it to NULL

I have an SSIS package that reads data from an Oracle table and puts it into a Netezza table. For some reason, SSIS cannot read a specific column value (2% of the records) as shown in the screenshot here. The column type in Oracle is NUMBER(3) and in my Netezza table it’s SMALLINT. Any idea […]

SQL error when querying from a linked server (SQL Server linked to Oracle)

I am getting an error on some queries in SQL Server that select from a linked Oracle database. The error occurs only for some SELECT statements but not others. I am trying to compare data on the two servers and get records that exist on one but not the other. I know the Oracle database […]

Select all rows for the first N distinct child table rows

It seems like this should be a common problem with a simple solution, but I haven’t found it. I would like to compute child_order, which is the order of appearance of distinct child table rows as is shown below in the following data: child_order PK1 PK2 ACCESS ACCESS_ID 1 99 Al NULL NULL 2 55 […]

Migrate Oracle partitioned tables to SQL Server

I need to migrate about 700 Oracle partitioned tables (RANGE and LIST partitioning) to SQL Server. Turns out the SSMA (SQL Server Migration Assistant) does not handle Oracle partitioned tables (this is the official answer I got from Microsoft). Any tool / script / other suggestion to automate this process? Thanks!

SQL query with multiple outer joins

I have three table with relationship as GradParent ( Country ) > Parent ( State ) > Child ( People ). I am trying to achieve a query to get distinct information on below scenario and order by Country Name. 1. All People Data with their State and Country. 2. If State doesn’t have People, […]

Simple SSIS package for Delta change – SSIS 2008

We have a client requirement to copy data/rows from Oracle Database(Live feed) to SQL Database on daily basis. Source database tables contains around 11 million rows at average on each table and some tables contain Modified date field which has trigger on it. How do I create a package to copy all the rows to […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.