BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Streamline and extend SAP BusinessObjects software 
Streamline and extend SAP BusinessObjects software (Opens a new window)  

General Notice: No events within the next 45 days.

Problem with Datetime2 in SQL server 2008

Goto page Previous  1, 2
 
Search this topic... | Search DI: Performance and Tuning... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Author Message
manoj_d
Forum Addict
Forum Addict



Joined: 01 Jan 2009

Posts: 2754



PostPosted: Tue Jul 20, 2010 5:16 pm 
Post subject: Re: Problem with Datetime2 in SQL server 2008

usign the ATL that is attached to the case I am getting a different error

error message for operation <SQLExecute>: <[Microsoft][SQL Server Native Client 10.0] Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding

looks like something is broken while binding DATETIME column, I am debugging this
Back to top
manoj_d
Forum Addict
Forum Addict



Joined: 01 Jan 2009

Posts: 2754



PostPosted: Wed Jul 21, 2010 12:04 pm 
Post subject: Re: Problem with Datetime2 in SQL server 2008

if you are looking for a workaround then try this instead of changing the Datatype of column in the table

the problem is with the scale value that is stored in AL_COLUMN table when the table is imported, for the columns with Datatype DATETIME, the value is 9, it should be 3, the workaround in your case would be to reimport the table and see if the value gets update to 3

I don't think changing the version to 2005 in Datastore will work since the server version is queried from the server after connecting to the database and native driver for sql server 2008 is loaded
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3456
Location: Massachusetts, USA


flag
PostPosted: Wed Jul 21, 2010 3:01 pm 
Post subject: Re: Problem with Datetime2 in SQL server 2008

I tried this query in my repo:

Code:

select a.DATASTORE,a.TABLE_NAME,a.COLUMN_NAME
from ALVW_COLUMNINFO a
where COLUMN_DATATYPE like 'date%'
and COLUMN_SCALE=9
order by 1,2,3


Sure enough, there are some columns set up as you suggested. And reimporting them does seem to reset the column_scale to a proper value.

However, the vast majority of the 350 items in my results, are columns in Data Transfer tables, or Template tables -- both of which are generally not re-importable; they have schemas defined by DS...
Back to top
manoj_d
Forum Addict
Forum Addict



Joined: 01 Jan 2009

Posts: 2754



PostPosted: Thu Jul 22, 2010 10:46 am 
Post subject: Re: Problem with Datetime2 in SQL server 2008

yes, template table will be an issue, that's a bug, let me work on that too

checked the template table for SQL Server 2008, if you are using drop and create then there shouldn't be an issue, for DATETIME datatype DI will create column in the database as DATETIME2

are you running into issues with template tables using column datatype as DATETIME ?
Back to top
chuckdiegoo
Senior Member
Senior Member



Joined: 12 Oct 2009

Posts: 37



PostPosted: Fri Jul 23, 2010 11:59 am 
Post subject: Re: Problem with Datetime2 in SQL server 2008

I've tried reimporting the tables and the error continues
Back to top
chuckdiegoo
Senior Member
Senior Member



Joined: 12 Oct 2009

Posts: 37



PostPosted: Fri Jul 23, 2010 1:53 pm 
Post subject: Re: Problem with Datetime2 in SQL server 2008

I started to try different scenarios and I ultimately was able to get this error to go away by reimporting the table, saving the job, and not touching anything else.

Perhaps when I previously tried to re-import the table I made another change which causes this bug??
Back to top
BO_Chief
Forum Fanatic
Forum Fanatic



Joined: 06 Jun 2004
ASUG Icon
Posts: 5532
Location: Somewhere on God's Land.


flag
PostPosted: Wed Oct 27, 2010 4:12 pm 
Post subject: Re: Problem with Datetime2 in SQL server 2008

I was experiencing this issue since two days, Resolved the issue by using a new Data Store pointing to SQL Server 2005 instead of SQL Server 2008.
_________________
help us help you! make sure your post has the following elements:
Does it include BO version, Database, an error, a problem, a SQL for object or Condition ?
Does it include some sample data what you have?
Does it include any code you already tried ? (working or not..)
Did you explain what you want for results?
If any of those elements are missing,chances are you didn't post enough information for us to help you!
Back to top
chuckdiegoo
Senior Member
Senior Member



Joined: 12 Oct 2009

Posts: 37



PostPosted: Wed Oct 27, 2010 4:50 pm 
Post subject: Re: Problem with Datetime2 in SQL server 2008

Thank you for the reply. Your suggestion was what SAP Support informed me to do. They noted several 2008 SQL issues and they have yet to release a fix.
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3456
Location: Massachusetts, USA


flag
PostPosted: Wed Oct 27, 2010 5:58 pm 
Post subject: Re: Problem with Datetime2 in SQL server 2008

chuckdiegoo wrote:
Thank you for the reply. Your suggestion was what SAP Support informed me to do. They noted several 2008 SQL issues and they have yet to release a fix.


Release 12.2.2.3 is out for download, and the release notes say it fixes two issues related to DateTime2 fields in SQL 2008...
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Page 2 of 2 All times are GMT - 5 Hours
Goto page Previous  1, 2
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0504 seconds using 17 queries. (SQL 0.0029 Parse 0.0358 Other 0.0116)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo