Tag: oracle

How to use extractvalue in SQL Server

I am trying to convert my Oracle query to SQL Server. The query shown below is causing some difficulty when trying to convert EXTRACTVALUE, XMLSEQUENCE, XMLTYPE functions. Can anyone help me learn how to convert these to SQL Server? EXTRACTVALUE(COLUMN_VALUE, ‘pipeline/@name’) NAME FROM TABLE (SELECT XMLSEQUENCE(XMLTYPE(MESSAGE).EXTRACT(‘processEngine’)) FROM NMS_MESSAGES WHERE OBJECT_CODE=’pe_monitor’ AND ID=@WHERE:PARAM:USER_DEF:INTEGER:PARAM_PROCESS_ENGINE@)) INSTANCENAME ,(SELECT EXTRACTVALUE(COLUMN_VALUE, […]

Primary Key – Foreign Key relationship between two different database

This question already has an answer here: Foreign keys in alternate schemas with Oracle? 2 answers

Converting Sql Server If not exists to Oracle

I have a snippet of a SQL Server procedure that looks like this: IF NOT EXISTS(SELECT ID FROM IDTABLE WHERE ID=@ID) BEGIN DECLARE @MSG VARCHAR(100) SET @MSG = ‘The ID (ID: ‘ + CONVERT(VARCHAR(50), @ID) + ‘) does not exist’ EXEC SP_LOGAUDITMESSAGE ‘Warning’, ‘Bill report’, @MSG SET @RETVAL = 0 RETURN END This runs the […]

Hierarchical Query in MySQl vs SQLServer vs Oracle

I am trying to implement the Connect By query in oracle/mysql/sqlserver to understand the difference in working. Trying to understand how it works. So I have a simple table which looks likes: empno ename mno 1 KS null 2 AB 2 3 BC 1 4 TR 3 5 QE 2 6 PL 3 7 LK […]

Error: The module depends on the missing object 'sysdb.ssma_oracle.db_check_init_package'

i migrated an entire oracle Database to SQL Server 2014 Database using SSMA. (SQL Server Migration assistant) The migration resulted in introduction of multiple Execution calls in my stored procedures (like below): The module ‘INFRA_IP_WEBLOGGER$spCheckIPStatus’ depends on the missing object ‘sysdb.ssma_oracle.db_check_init_package’. The module will still be created; however, it cannot run successfully until the object […]

Joining oracle with mssql table

When i execute following line i get the result: select * from my_table@link; But when i try to join linked table with other tables like in select i get error that says “z.id – invalid identifier” SELECT e.USER_NAME,e.FULL_NAME,r.RFC_NUMBER,r.TYPE, TO_CHAR(TRUNC(a.TOTAL_TIME/3600),’FM9900′) || ‘:’ || TO_CHAR(TRUNC(MOD(a.TOTAL_TIME,3600)/60),’FM00′) || ‘:’ || TO_CHAR(MOD(a.TOTAL_TIME,60),’FM00′) as TOTAL_TIME, a.ASSIGN_DATE,a.TIME_START,a.TIME_STOP, r.SUBMITTED_BY,r.REGISTER_DATE,r.DESCRIPTION FROM table_a a JOIN […]

SSIS and sending query with date to Oracle

I am trying to create a flow to pull data from an Oracle table into a SQL Server table. I am sending the following query to Oracle to get the data: select distinct CHLD.id, nvl(chld_c_spl, ‘N’), to_char(chld_d_start, ‘YYYY-MM-DD’) chld_d_start, to_char(chld_d_end, ‘YYYY-MM-DD’) chld_d_end from child chld, picture ptct where CHLD.id = PTCT.chld_id and nvl(chld_d_end, sysdate) >= […]

Update from aggregate in same table if aggregate value wrong – SQL Server/Oracle/Firebird

I have a table with grouped tasks: tt_plan_task_id is the id records with tt_plantype=1 represent ‘groups’ tasks in/under a group have a tt_group_id pointing to the tt_plan_task_id there are tasks that don’t belong to a group (tt_group_id is null) groups nest multiple levels I need to fix (update) the tt_fromdate field values for the group […]

Oracle to SQL Server Migration issue – On delete cascade

Folks, Iam working on Oracle to MS Sql Server migraiton. One of the issue I came across is that “ON DELETE CASCADE” works fine in Oracle, but not in Sql Server. Is there any alternative to the syntax/command or is it something we need to manually remove/fix it. Could not get a valid link/reference on […]

Is there any common function for formatting date in SQL Server and Oracle?

I am building an application and need to format the datetime field to dd/mm. I was using TO_CHAR in Oracle and which is not supported in SQL Server and got error when it is deployed with SQL Server database. Is the any common function which can be used across different databases for formatting the datetime […]

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