 Forum main
page
Most
active member
Login page
Register
Log out
Search page
View our event calendar
Add general event such as an upcoming
software release or conference
View all birthdays
Public image gallery
Upload your images in your profile
Profile editing page
Subscription list page
Address book page
Member list page
View the
most active member
User groups listing
Private message page
|
|
Users viewing this topic: none
|
|
Login  | |
|
Oracle date woes - 28 March 2005
|
|
|
lvogel
Posts: 31
Score: 0
Joined: 2 November 2004
Status: offline
|
I have a project in which a date looking like this (from SQL Server):
<UpdatedOn>2005-03-25T17:05:30.9030000-08:00</UpdatedOn>
must go into Oracle. The error I keep getting consistently, no matter what date format I change to, I keep getting the same.
the destination table has a date column with values looking like '6-JAN-04'. If I take the above date and convert it to look the same, I get the same error.
Could someone please tell me what I need to do in order to successfully enter dates into the system?
Thanks,
Lucas
========================================================
Event Type: Warning
Event Source: BizTalk Server 2004
Event Category: BizTalk Server 2004
Event ID: 5743
Date: 3/28/2005
Time: 3:14:39 PM
User: N/A
Computer: VOGEL2003
Description:
The adapter failed to transmit message going to send port "database://Oracle Data Provider for .NET". It will be retransmitted after the retry interval specified for this Send Port. Details:"Oracle.DataAccess.Client.OracleException ORA-01830: date format picture ends before converting entire input string at BTSUtils.Adapters.Databases.Updategram.InsertUpdate.Execute(String dbObject, XmlElement element, XmlSchemaElement request, IDbTransaction transaction, Int32 commandTimeout)
at BTSUtils.Adapters.Databases.Updategram.Table.Execute(String dbObject, XmlElement element, XmlSchemaElement request, XmlSchemaElement response, XmlTextWriter writer, String targetNamespace, IDbTransaction transaction, Int32 commandTimeout)
at BTSUtils.Adapters.Databases.Updategram.Object.Execute(XmlElement element, XmlSchemaElement request, XmlSchemaElement response, XmlTextWriter writer, String targetNamespace, IDbTransaction transaction, Int32 commandTimeout)
at BTSUtils.Adapters.Databases.Updategram.Operation.Execute(XmlElement element, XmlSchemaElement request, XmlSchemaElement response, XmlTextWriter writer, String targetNamespace, IDbTransaction transaction, Int32 commandTimeout)
at BTSUtils.Adapters.Databases.Updategram.Client.Execute(Instance provider, String Connectionstring, Boolean transactional, IsolationLevel isolationLevel, Int32 connectionTimeout, Int32 commandTimeout, XmlDocument request, XmlSchema schema)".
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
========================================================
|
|
|
|
RE: Oracle date woes - 29 March 2005
|
|
|
lvogel
Posts: 31
Score: 0
Joined: 2 November 2004
Status: offline
|
I'm still getting errors.
My DateTime property for the Oracle adapter looks like M/dd/yyyy hh:mm:ss tt as required by the database server. I took my incoming date that looked like
<UpdatedOn>2005-03-24T13:41:49.8300000-08:00</UpdatedOn>
and turned it into something like
<ns0:LAST_UPDATE_DATE>3/25/2005 4:43 PM</ns0:LAST_UPDATE_DATE>
in the database schema with a functoid in the transformation (in the InsertUpdate record).
This is the error I'm getting now. (Note that I have two datetime fields in my record; while both look the same it could be either field)
=======================================
Event Type: Warning
Event Source: BizTalk Server 2004
Event Category: BizTalk Server 2004
Event ID: 5743
Date: 3/29/2005
Time: 2:18:27 PM
User: N/A
Computer: VOGEL2003
Description:
The adapter failed to transmit message going to send port "database://Oracle Data Provider for .NET". It will be retransmitted after the retry interval specified for this Send Port. Details:"Oracle.DataAccess.Client.OracleException ORA-01843: not a valid month at BTSUtils.Adapters.Databases.Updategram.InsertUpdate.Execute(String dbObject, XmlElement element, XmlSchemaElement request, IDbTransaction transaction, Int32 commandTimeout)
at BTSUtils.Adapters.Databases.Updategram.Table.Execute(String dbObject, XmlElement element, XmlSchemaElement request, XmlSchemaElement response, XmlTextWriter writer, String targetNamespace, IDbTransaction transaction, Int32 commandTimeout)
at BTSUtils.Adapters.Databases.Updategram.Object.Execute(XmlElement element, XmlSchemaElement request, XmlSchemaElement response, XmlTextWriter writer, String targetNamespace, IDbTransaction transaction, Int32 commandTimeout)
at BTSUtils.Adapters.Databases.Updategram.Operation.Execute(XmlElement element, XmlSchemaElement request, XmlSchemaElement response, XmlTextWriter writer, String targetNamespace, IDbTransaction transaction, Int32 commandTimeout)
at BTSUtils.Adapters.Databases.Updategram.Client.Execute(Instance provider, String Connectionstring, Boolean transactional, IsolationLevel isolationLevel, Int32 connectionTimeout, Int32 commandTimeout, XmlDocument request, XmlSchema schema)".
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
=======================================
|
|
|
|
|
RE: Oracle date woes - 29 March 2005
|
|
|
Pieter
Posts: 615
Score: 2
Joined: 8 March 2004
Status: offline
|
Did you go through the document.
You don't need to change these values in a Transformation.
The DB Adapter will take care of this as long as you change the formatting within your provider definition.
What is currently happening is that the Adapter is seeing the value as being a DateTime value. It is thus converting it again to a DateTime value and then attempting to reformat it to the specified format.
There should be an Application Event Log entry before the one you just posted. Can you possibly mail this to support@topxml.com?
_____________________________
Need a BizTalk Adapter? Visit http://www.topxml.com/biztalkutilities and check out the latest BizTalk Adapters.
|
|
|
|
RE: Oracle date woes - 30 March 2005
|
|
|
lvogel
Posts: 31
Score: 0
Joined: 2 November 2004
Status: offline
|
I solved the problem!
The columns being updated were DATE columns, and the dateTime datatype generated by the schema was invalid. I changed the datatype to Date, set the format to 'dd-MMM-yy', which is how it shows up in isql and required by the to_date function. Trying to set the format to something different from what shows up was what was giving me the problems -- you HAVE to set the format to exactly what Oracle is asking for.
Out of this whole quagmire, two things that I noticed that involved the adapter:
- the adapter was not giving me trace statements because it was configured as Production, even though I clearly specified Development during install...
- the adapter specified the DATE column as a dateTime schema datatype, whis is invalid -- must be Date.
Thanks for the help Pieter!
Lucas
|
|
|
|
|
RE: Oracle date woes - 30 March 2005
|
|
|
Pieter
Posts: 615
Score: 2
Joined: 8 March 2004
Status: offline
|
Good to hear that you have resolved the issue.
With reagards to the Schema not generating the values as Date Values.
The Provider actually does not differentiate between Date and DateTime values. In the .NET world there is only a single type called DateTime. We this have to assume that all Date columns are DateTime. That said, it could be changed after generating the Schema.
With regards to the EIF side of things, we will have a look into the installers, to see if this being fone properly.
_____________________________
Need a BizTalk Adapter? Visit http://www.topxml.com/biztalkutilities and check out the latest BizTalk Adapters.
|
|
|
|
|
RE: Oracle date woes - 14 February 2006
|
|
|
Brynn
Posts: 18
Score: 0
Joined: 10 January 2006
Status: offline
|
I am also having the same problem when trying to insert a record into oracle. I think I changed all the datetime fields to date only but I am still getting the same error. I have posted the entries from the application log below.
BTSUtils.Adapters.Databases.EnterpriseInstrumentation.Schema.TraceEvents.CustomEvents.Databases.Transmit.Command
{
String Message = "ns0:Insert"
String CommandText = "INSERT INTO IN_ORDERHEAD
(
DOCNUM,
DOCDATE,
DOCTYPE,
TPID,
FLDATE,
FLTIME,
REF4,
CURRENCY,
AUDREF,
ORDERACTION,
ORDERTYPE,
TRANSTYPE,
OURID,
USER1)
VALUES
(
'M101~76349',
'14-Feb-2006 12:00:00',
'ORDERS',
'nonsuch Limited',
'14-Feb-2006 12:00:00',
'15:28:30',
'CTPO157187',
'GBP',
'Nonsuch',
'9',
'105',
'ORDERS',
'15',
'nonsuch Limited')"
Int32 CommandTimeout = 0
String CommandType = "Text"
Int32 Counter = 1
Parameter[] Parameters = {
}
Int32 ProcessID = 3924
String ProcessName = "C:\PROGRA~1\MICROS~4\BTSNTSvc.exe"
String ThreadName = ""
ComPlusInfo ComPlus = <null>
WindowsSecurityInfo WindowsSecurity = <null>
ManagedSecurityInfo ManagedSecurity = <null>
String StackTrace = ""
String EventSourceInstance = "38721103-19c7-4918-a3df-fb8cc1592ee0"
String EventSourceName = "BizTalk Utilities Adapter for Databases Transmitter"
Int64 EventSequenceNumber = 3076
String EventSchemaVersion = "2.0.3.0"
DateTime TimeStamp = 14/02/2006 15:28:34
String AppDomainName = "DefaultDomain"
String MachineName = "WAR08S-BT202"
String RootRequestName = ""
String RootRequestInstance = ""
String RequestInstance = ""
String RequestName = ""
Int64 RequestSequenceNumber = 0
Int32 EventLogEntryTypeID = 4
}
DOCDATE and FLDATE are both date only fields but it appears that I am still getting these as dateTime fields and therefore I get the following error as the next entry
BTSUtils.Core.EnterpriseInstrumentation.Schema.ErrorMessageEvent
{
String Message = "An Exception occurred attempting to submit a Message to the Database Transport.
System.Data.OracleClient.OracleException
ORA-01830: date format picture ends before converting entire input string
"
Exception InnerException = {
String HelpLink = ""
String Message = ""
String Source = ""
String StackTrace = ""
String TargetSite = ""
}
String ErrorCode = ""
Int32 Severity = 0
Int32 ProcessID = 3924
String ProcessName = "C:\PROGRA~1\MICROS~4\BTSNTSvc.exe"
String ThreadName = ""
ComPlusInfo ComPlus = {
String ActivityID = ""
String ApplicationID = ""
String TransactionID = ""
String DirectCaller = ""
String OriginalCaller = ""
}
WindowsSecurityInfo WindowsSecurity = {
String CurrentAccountName = "xxxxxxxxx\Administrator"
String ProcessAccountName = "BUILTIN\Administrators"
}
ManagedSecurityInfo ManagedSecurity = {
String IdentityName = ""
String AuthenticationType = ""
Boolean IsAuthenticated = False
}
String StackTrace = " at BTSUtils.Core.Adapters.EnterpriseInstrumentation.Helper.Error(EventSource EventSource, Exception Exception, String Message)
at BTSUtils.Adapters.Databases.Updategram.Client.Execute(Group group)
at BTSUtils.Adapters.Databases.Transmit.Batch.SendMessages(IBaseMessage[] messages)
at BTSUtils.Core.Adapters.Transmit.Batch.Asynchronous.Batch.Base.Done(IBTDTCCommitConfirm commitConfirm)"
String EventSourceInstance = "38721103-19c7-4918-a3df-fb8cc1592ee0"
String EventSourceName = "BizTalk Utilities Adapter for Databases Transmitter"
Int64 EventSequenceNumber = 3078
String EventSchemaVersion = "2.0.170.0"
DateTime TimeStamp = 14/02/2006 15:28:34
String AppDomainName = "DefaultDomain"
String MachineName = "xxxxxxxxxxxx"
String RootRequestName = ""
String RootRequestInstance = ""
String RequestInstance = ""
String RequestName = ""
Int64 RequestSequenceNumber = 0
Int32 EventLogEntryTypeID = 1
}
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Can you help please
Thanks
|
|
|
|
|
RE: Oracle date woes - 15 February 2006
|
|
|
Pieter
Posts: 615
Score: 2
Joined: 8 March 2004
Status: offline
|
Have a look at the DateFormats document inclded within the Adapter Installation. This will take you through the steps involved in resolving the issue.
_____________________________
Need a BizTalk Adapter? Visit http://www.topxml.com/biztalkutilities and check out the latest BizTalk Adapters.
|
|
|
|
|
RE: Oracle date woes - 15 February 2006
|
|
|
Brynn
Posts: 18
Score: 0
Joined: 10 January 2006
Status: offline
|
Pieter, I have already read this (and just about ever other hit on this website to do with this subject) and the format of the date is consistant with our oracle system. The problem is not the date format being wrong, it is the fact it is trying to stuff a date/time value into a date field even though I have altered the genereated schema to a date only.I have tried altering the schema in both the following ways.
<element minOccurs="0" maxOccurs="1" name="FLDATE" type="date" />
or
- <element minOccurs="0" maxOccurs="1" name="FLDATE">
- <simpleType>
<restriction base="date" />
</simpleType>
either way it still tries to insert date and time.
|
|
|
|
|
RE: Oracle date woes - 15 February 2006
|
|
|
Pieter
Posts: 615
Score: 2
Joined: 8 March 2004
Status: offline
|
The Database provider that you are using will look for the XSD Type within the generated Schema.
If this is a Date Value ,the date format property on the Provider definition will be used.
This definitely works. So the only thing I can think of is that this value is set incorrectly.
Can you attach a screenshot of the the provider definition show the date formats within BizTalk Utilities Adapter for Databases Administration Snap-In?
_____________________________
Need a BizTalk Adapter? Visit http://www.topxml.com/biztalkutilities and check out the latest BizTalk Adapters.
|
|
|
|
|
RE: Oracle date woes - 15 February 2006
|
|
|
Pieter
Posts: 615
Score: 2
Joined: 8 March 2004
Status: offline
|
Okay. I think I know what the problem is. From the stack trace we can see that you are using the Oracle .NET Data Provider (ODP). From the image you are changing the values for the Microsoft .NET Provider for Oracle.
Can you please change the date property for the Oracle .NET Providers and try it again?
_____________________________
Need a BizTalk Adapter? Visit http://www.topxml.com/biztalkutilities and check out the latest BizTalk Adapters.
|
|
|
|
|
RE: Oracle date woes - 15 February 2006
|
|
|
Brynn
Posts: 18
Score: 0
Joined: 10 January 2006
Status: offline
|
Thats misleading because from one of the other events I see
String Name = "Microsoft .NET Data Provider for Oracle"
Anyway my Virtual Machine crashed out this afternoon and forced a complete restart of the machine. Now it is formatting the message correctly and it is all working. I don't understand why it is working now as I had stopped and restarted Biztalk several times prior to this and I had seen the format of the message change when I changed the dateTime format. Maybe a complete restart was all that was required?
Thanks for all your help Pieter.
|
|
|
|
|
RE: Oracle date woes - 20 February 2006
|
|
|
Pieter
Posts: 615
Score: 2
Joined: 8 March 2004
Status: offline
|
From what we've seen is that BizTalk sometimes does not pick up small changes to Maps and Schemas.
We usually bump the version numbers of the assemblies and this usually causes BizTalk to accept the changes.
_____________________________
Need a BizTalk Adapter? Visit http://www.topxml.com/biztalkutilities and check out the latest BizTalk Adapters.
|
|
|
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version
Email TopXML