Avatar of Support_38
Support_38

asked on 

Query in Oracle for Linked Server SQL Server

Good Morning,

I would like your help to two questions:

1 - I am running a query in Oracle through Linked Server SQL Server, when I run the query I am getting the error below to column that has the case:

Msg 7347, Level 16, State 1, Line 2
OLE DB provider 'OraOLEDB.Oracle' for linked server 'LNK-SERVER01' returned data that does not match expected data length for column '[OraOLEDB.Oracle].CASE'PRIORITY'WHEN'0'THEN'CRIT'. The (maximum) expected data length is 64, while the returned data length is 6.


2 - I need to schedule a job in SQL Server to bring the previous day's (D-1), but do not know how to do this automatically.

I'm putting the query attached

I appreciate the help
consulta_2.txt
Microsoft SQL ServerOracle DatabaseMicrosoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Support_38
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Remove the quotes from around Priority:
SELECT * FROM OPENQUERY([LNK-SERVER01], 'SELECT
Company,
Incident_Number,
CASE Priority
WHEN ''0'' THEN ''Critical''  
WHEN ''1'' THEN ''High'' 
WHEN ''2'' THEN ''Medium''
ELSE ''Low''
END,  
Assigned_Group, 
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS Submit_Date,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((last_resolved_date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS last_resolved_date
FROM HPD_TAB_INF
WHERE ASSIGNED_SUPPORT_COMPANY = ''Company - OPT''
AND ASSIGNED_SUPPORT_ORGANIZATION  in (''OPER'', ''INF'',''SEC MANAG'')      
AND STATUS <>6 
AND SERVICE_TYPE=0
AND to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''YYYY-MM-DD HH24:MI'') >=''2016-05-23'' 
AND to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''YYYY-MM-DD HH24:MI'') < ''2016-05-24''
ORDER BY SUBMIT_DATE 
')

Open in new window

Avatar of Support_38
Support_38

ASKER

The error has now changed:

OLE DB provider "OraOLEDB.Oracle" for linked server "LNK-SERVER01" returned message "ORA-00932: inconsistent datatypes: expected NUMBER got CHAR".
Avatar of Zberteoc
Zberteoc
Flag of Canada image

I think the Priority column is number not CHAR, in which case you don't have to enclose the values in quotes:
SELECT * FROM OPENQUERY([LNK-SERVER01], 'SELECT
Company,
Incident_Number,
CASE Priority
WHEN 0 THEN ''Critical''  
WHEN 1 THEN ''High'' 
WHEN 2 THEN ''Medium''
ELSE ''Low''
END,  
Assigned_Group, 
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS Submit_Date,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((last_resolved_date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS last_resolved_date
FROM HPD_TAB_INF
WHERE ASSIGNED_SUPPORT_COMPANY = ''Company - OPT''
AND ASSIGNED_SUPPORT_ORGANIZATION  in (''OPER'', ''INF'',''SEC MANAG'')      
AND STATUS <>6 
AND SERVICE_TYPE=0
AND to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''YYYY-MM-DD HH24:MI'') >=''2016-05-23'' 
AND to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''YYYY-MM-DD HH24:MI'') < ''2016-05-24''
ORDER BY SUBMIT_DATE 
')

Open in new window

Avatar of Support_38
Support_38

ASKER

hi

This error occurs executing the query the linked server, direct running on oracle not the error occurs, managed to solve the problem first creating a temporary table in SQL and inserting the result.

The other problem is that I need the query is always executed taking into account the previous day, 00:00:00 to 23:59:00 automatically
Avatar of Zberteoc
Zberteoc
Flag of Canada image

I don't understand why upi added those complicated expression for submit date, what you need is a simple filter:

AND Submit_Date BETWEEN SYSDATE-1 and SYSDATE

SELECT * FROM OPENQUERY([LNK-SERVER01], 'SELECT
Company,
Incident_Number,
CASE Priority
WHEN 0 THEN ''Critical''  
WHEN 1 THEN ''High'' 
WHEN 2 THEN ''Medium''
ELSE ''Low''
END,  
Assigned_Group, 
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS Submit_Date,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((last_resolved_date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS last_resolved_date
FROM HPD_TAB_INF
WHERE ASSIGNED_SUPPORT_COMPANY = ''Company - OPT''
AND ASSIGNED_SUPPORT_ORGANIZATION  in (''OPER'', ''INF'',''SEC MANAG'')      
AND STATUS <>6 
AND SERVICE_TYPE=0
AND Submit_Date BETWEEN SYSDATE-1 and SYSDATE
ORDER BY SUBMIT_DATE 
')

Open in new window

Avatar of Support_38
Support_38

ASKER

The period to be fixed from the previous day, regardless of the time I run the value must be always the previous day period

with sysdate -1  and  sysdate

every time I run the result will change
Avatar of Support_38
Support_38

ASKER

this expression was placed because the data type to  submit date was defined as numeric
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Then use this:
SELECT * FROM OPENQUERY([LNK-SERVER01], 'SELECT
Company,
Incident_Number,
CASE Priority
WHEN 0 THEN ''Critical''  
WHEN 1 THEN ''High'' 
WHEN 2 THEN ''Medium''
ELSE ''Low''
END,  
Assigned_Group, 
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS Submit_Date,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((last_resolved_date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS last_resolved_date
FROM HPD_TAB_INF
WHERE ASSIGNED_SUPPORT_COMPANY = ''Company - OPT''
AND ASSIGNED_SUPPORT_ORGANIZATION  in (''OPER'', ''INF'',''SEC MANAG'')      
AND STATUS <>6 
AND SERVICE_TYPE=0
AND Submit_Date BETWEEN TO_DATE(TO_CHAR(SYSDATE-1, ''YYYY-MM-DD'')) and TO_DATE(TO_CHAR(SYSDATE, ''YYYY-MM-DD''))
ORDER BY SUBMIT_DATE 
')

Open in new window

Never apply function to the table column in the where clause, you should do it the the values that you compare it to otherwise the possible existing indexes will not be used, which will make your query slower. Also use 'YYYY-MM-DD' format in strings when comparing, it is universally recognized.
Avatar of Zberteoc
Zberteoc
Flag of Canada image

I made a correction above as I forgot the AND before Submit_Date column:

AND Submit_Date BETWEEN...
Avatar of Support_38
Support_38

ASKER

This error occurs executing the query :

OLE DB provider "OraOLEDB.Oracle" for linked server "LNK_SERVER01" returned message "ORA-00932: inconsistent datatypes: expected NUMBER got DATE".
Avatar of Zberteoc
Zberteoc
Flag of Canada image

What is the type for Submit_Date column? Does that query run directly on Oracle?
Avatar of Support_38
Support_38

ASKER

submit_date is number(15)

It is occurring the same error in oracle

Attached error
Capturar.PNG
Avatar of Zberteoc
Zberteoc
Flag of Canada image

That's the problem. We are trying to compare Submit_Date, which is of type NUMBER(13) with a date TO_DATE(TO_CHAR(SYSDATE-1, ''YYYY-MM-DD'')). That doesn't work. Why is Submit_Date of type number and what is stored in it for today's date?
Avatar of Support_38
Support_38

ASKER

I have no control over it, the software was not developed internally

All date columns was created with data type numeric
Avatar of Zberteoc
Zberteoc
Flag of Canada image

So you have to find out how to turn a date into a number that matches the values in that column. Does your initial query work?
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Actually try this:
SELECT * FROM OPENQUERY([LNK-SERVER01], 'SELECT
Company,
Incident_Number,
CASE Priority
WHEN 0 THEN ''Critical''  
WHEN 1 THEN ''High'' 
WHEN 2 THEN ''Medium''
ELSE ''Low''
END,  
Assigned_Group, 
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS Submit_Date,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((last_resolved_date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS last_resolved_date
FROM HPD_TAB_INF
WHERE ASSIGNED_SUPPORT_COMPANY = ''Company - OPT''
AND ASSIGNED_SUPPORT_ORGANIZATION  in (''OPER'', ''INF'',''SEC MANAG'')      
AND STATUS <>6 
AND SERVICE_TYPE=0
AND to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''YYYY-MM-DD HH24:MI'') BETWEEN TO_CHAR(SYSDATE-1, ''YYYY-MM-DD'') and TO_CHAR(SYSDATE, ''YYYY-MM-DD'')
ORDER BY SUBMIT_DATE 
')

Open in new window

Avatar of Support_38
Support_38

ASKER

Good Morning,

As it is the query, every time I run will bring a different result, the result needs to be fixed independent fo the day time running.

The date range should always be from 0:00 to 23:59 Fixed
Avatar of Support_38
Support_38

ASKER

If I run the query now, the result is bringing records of 27 and can not, the records need to be just the previous day.

Attached results
Capturar.PNG
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Support_38
Support_38

ASKER

Hello,

Perfect, that's what I needed.

I appreciate the help.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo