Query in Oracle for Linked Server SQL Server

Support_38
Support_38 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Zberteoc

Commented:
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

Support_38

Author

Commented:
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".
Zberteoc

Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Support_38

Author

Commented:
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
Zberteoc

Commented:
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

Support_38

Author

Commented:
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
Support_38

Author

Commented:
this expression was placed because the data type to  submit date was defined as numeric
Zberteoc

Commented:
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.
Zberteoc

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

AND Submit_Date BETWEEN...
Support_38

Author

Commented:
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".
Zberteoc

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

Author

Commented:
submit_date is number(15)

It is occurring the same error in oracle

Attached error
Capturar.PNG
Zberteoc

Commented:
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?
Support_38

Author

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

All date columns was created with data type numeric
Zberteoc

Commented:
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?
Zberteoc

Commented:
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

Support_38

Author

Commented:
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
Support_38

Author

Commented:
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
Commented:
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'')||'' 00:00'' and TO_CHAR(SYSDATE-1, ''YYYY-MM-DD'')||'' 23:59''
ORDER BY SUBMIT_DATE 
')

Open in new window

Support_38

Author

Commented:
Hello,

Perfect, that's what I needed.

I appreciate the help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial