September 14, 2012 at 4:15 am
Hi,
I have setup Linked Server with Oracle and Sql Server.
When UI run Query on SQL Server as
SELECT a.*
FROM OPENQUERY(PRODERP,'SELECT
CUSTOMER_NUMBER,
RECEIPT_ENTRY_ID,
RECEIPT_DATE,
CHECK_NUMBER,
CHECK_DATE,
CUSTOMER_BANK,
RECEIPT_STATUS,
RECEIPT_HEAD,
RECEIPT_AMOUNT,
SERVICE_TAX,
REVERSED_DETAIL,
ACCOUNT_HEAD,
PRINT_DATE,
PRINTED,
RCVD_FROM
from Apps.xxcom_cust_receipt_details') a
All is well but Print_Date shown as
09-DEC-0029
10-MAR-0009
10-MAR-0012
11-MAY-0011
while it should be
29-DEC-2009
09-MAR-2010
12-MAR-2010
11-MAY-2011
What is the problem?
datatype for all is varchar in SQL Server and varchar2 in Oracle VIEWS.
please help...........
Thanks.......
-----------------------------------
My Blog[/url] | Articles
September 14, 2012 at 4:28 am
could you provide the sample data ?
i.e. what is existing and what is required , ddl would be a great help to under the problem more clearly.
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
September 14, 2012 at 5:27 am
pls try below code.
SELECT CUSTOMER_NUMBER,
RECEIPT_ENTRY_ID,
RECEIPT_DATE,
CHECK_NUMBER,
CHECK_DATE,
CUSTOMER_BANK,
RECEIPT_STATUS,
RECEIPT_HEAD,
RECEIPT_AMOUNT,
SERVICE_TAX,
REVERSED_DETAIL,
ACCOUNT_HEAD,
convert(nvarchar(20),PRINT_DATE) PRINT_DATE,
PRINTED,
RCVD_FROM
FROM OPENQUERY(PRODERP,'SELECT
CUSTOMER_NUMBER,
RECEIPT_ENTRY_ID,
RECEIPT_DATE,
CHECK_NUMBER,
CHECK_DATE,
CUSTOMER_BANK,
RECEIPT_STATUS,
RECEIPT_HEAD,
RECEIPT_AMOUNT,
SERVICE_TAX,
REVERSED_DETAIL,
ACCOUNT_HEAD,
PRINT_DATE,
PRINTED,
RCVD_FROM
from Apps.xxcom_cust_receipt_details') a
September 14, 2012 at 6:39 am
Not knowing anything about Oracle, I can tell you that there is a problem with the way the data is being translated between the two (numbers are getting cut off). Unfortunately, without more details, I can't tell you where the translation problem is. Because you've left out so much information, we can't assist you with tracing the problem.
First of all, the query you posted... Is it the Oracle query or the SQL Server query?
If the former, how is that data getting over to SQL Server anyway? SSIS, ODBC, Flat File?
Is there a staging table in the mix?
As demonfox asked, please provide us some sample data and DDL.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply