Tag: oracle

Problems Registering Oracle.DataAccess as SQLCLR assembly in MS SQL Server 2012

EDIT 3.5 (Meant to put Item 3 below in the last update, but overlooked. Alas…) Looking at how the assembly registration fails for my problem and looking at what limited info I am able to infer from ProcExplorer traces leads me to make some conclusions on a few things. No solutions, just a few inferences […]

What is the difference when comparing with parentheses: WHERE (a, b)=(1,2)

I stumbled across the follwoing (valid) query in MySQL (also works in Oracle/MSSQL when replacing = with IN): SELECT * from mytable WHERE (a, b)=(1,2) It’s the same as SELECT * from mytable WHERE a=1 and b=2 I think the definition in the MySQL docs is here: simple_expr: […] | (expr [, expr] …) […] […]

Multiple databases with slightly changing models. How do I allow `EF` to work with different database structures at run-time?

I am working with EF6, MSSQL, Oracle, .NET4.5 on a system that is used globally across company (many departments) to query different databases that belong to our department, that have mostly same EF model, some databases are Oracle and some are Microsoft SQL, some are development or uat, some are logs. I am using different […]

Query to return unique values when one of column value is repeating

I have a sql query like the following SELECT Name.Nameguid,Name.Name,relation.ancestorguid,relation.productguid,relation.pathLength FROM Name Name JOIN ProductRelationship relation ON Name.productGuid = relation.ancestorGuid AND relation.productGuid = ‘6075D04A-E74A-464B-94E7-25374F0B9833’ ORDER BY relation.pathLength DESC Which returns the following NameGuid Name AncestorGuid ProductGuid PathLength 6D39CB04-88D9-4125-A052-8DF608AAD29C NameParentOnly 8E07F824-763C-434F-926C-80FCC8690243 6075D04A-E74A-464B-94E7-25374F0B9833 2 186E1DF3-4D1A-4020-B845-1280CF1092EA NameParentChild 8E07F824-763C-434F-926C-80FCC8690243 6075D04A-E74A-464B-94E7-25374F0B9833 2 CA60E542-4047-4B4D-AA22-0744A1E5F2E0 childlevelName D9833FCA-93A7-42F5-AFC3-5544F7A4425D 6075D04A-E74A-464B-94E7-25374F0B9833 1 A09D01FC-D69D-4AFA-B4D0-C804F030A281 NameParentChild D9833FCA-93A7-42F5-AFC3-5544F7A4425D […]

Generation of ROWID column in triggers even when its generation is switched off in SSMA v 6.0

I used SSMA v6.0 for migrating my Oracle database to SQL Server 2014. I turned off the generation of ROWID column and as expected it did not generate any additional ROWID column in any of my tables after conversion, but surprisingly it DID generate all the triggers associated with their respective tables with ROWID column […]

Issue when Transfering Data to Oracle from SQL using SSIS

As part of a project step I need to run an SSIS package which will transfer the results of the previous step to the clients Siebel / Oracle database. There can be up to 5 million rows in the results table If we just use SQL to do an insert (using a stored procedure) it […]

How to insert into SQL Server table from Oracle table?

I wanted to insert into certain column in SQL Server from Oracle. It shows error (always the last column choose error with : invalid identifier). Can guide me on how to insert some values into a SQL Server table from Oracle? INSERT INTO acc_trx_pymt_noti_ack@mssql (ATPNA_ERN, ATPNA_Status, ATPNA_Err_Desc, ATPNA_Create_Dt) SELECT ord_key, truck_id, create_by, create_dt FROM ORD_PYMT; […]

Passing Stored Procedure from SQL Server to ORACLE linked server

I’m trying to pass the following SQL query as a linked server stored procedure, but I get an error, please advise, thanks in advance! Msg 7215, Level 17, State 1, Line 17 Could not execute statement on remote server ‘CAFUAT’. Explanations: CAFUAT = Oracle Linked server CDBWEB = User STP_CAF_PERSON = stored procedure Code: DECLARE […]

Replace REGEXP_SUBSTR in SQL Server

I’m Looking for a way of replacing the use of INSTR(…) and REPLACE(REGEXP_SUBSTR(…)) oracle functions in SQL Server. Original Oracle: SELECT Name, CASE WHEN SUBSTR (NAME, 1, 2) = ‘CG’ THEN SUBSTR (NAME,INSTR (NAME,’_’,1,2)+ 1,LENGTH (NAME)) ELSE REPLACE (REGEXP_SUBSTR (NAME,’_[^_]+’,1,2),’_’,”) END AS OPT, CASE WHEN SUBSTR (NAME, 1, 2) = ‘CG’ THEN SUBSTR (NAME,INSTR (NAME, […]

Missing nvarchar columns when reading SQL Server database table from Oracle

I have a SQL Server database with a table that has a column of nvarchar(4000) data type. When I try to read the data from Oracle through a dblink, I don’t see the nvarchar(4000) column. All the other column’s data is displayed properly. Can anyone help me to find the issue here and how to […]

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