Error loading Oracle DATE data

Hello.

I'm trying to put in an SQL server database some data extracted from Oracle Server 9i.

During the load process, the "OLE DB Destination" in the task chokes up when it finds a record containing the date '0197-01-01 00:00:00' (i got this by putting the error output to a column of type varchar(50)). I can't use the Condicional Split's date functions to filter this out because they also choke on the strange date.

Can anyone give a sugestion?

Thanks in advance,

Hugo Oliveira

SQL will not recognize that as a date. Valid ranges for a datetime column are January 1, 1753, through December 31, 9999. If you want to filter it out use the error output like you're doing.|||

Hello Brent.

I gess the error output is the only solution. Just wondering if there was another one.

Thanks,

Hugo Oliveira

|||

You could treat the value as a string, test it to see if its a valid date and if it is is, cast it as a date.

Those that are not a valid date - its up to you what you do with them. You could discard them or replace the value with a default.

-Jamie

Error in stored procedure

I have a stored procedure to which I pass the following parameters
@.Date smalldatetime,
@.Amount decimal(15,3)
@.Exg_Rate decimal(5,3)
Inside this stored procedure I call another one passing to it those parameters like that
EXECUTE dbo.[Opening_Balance_AfterInsert] @.Date, @.Amount*@.Exg_Rate
I receive an error at the above line saying: Incorrect syntax near '*'
if I try to put the expression between rounded brackets I receive the error Incorrect syntax near '('
How can I pass that expression?create procedure myproc
@.Date smalldatetime,
@.Amount decimal(15,3)
@.Exg_Rate decimal(5,3)
as
declare @.combo float
set @.combo = @.Amount*@.Exg_Rate
execute do.opening_balance_afterInsert @.date, @.combo
Nicksql

error in installing hot fix

i installed an service pack 2

Quick Details

Version:

9.00.3042

Knowledge Base (KB) Articles:

KB921896

Date Published:

3/6/2007

Language:

English

Download Size:

282.4 MB - 392.4 MB*

*Download size depends on selected download components.

the above installation , i was success. but the version is 9.00.3042. so what i did was i stalled the hot fix , http://support.microsoft.com/kb/933097

but this hox fis installation is success for all, but it failed for database services with below error message :

product Installation Status
Product : SQL Server Database Services 2005 (MSSQLSERVER)
Product Version (Previous): 3152
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB933097_sqlrun_sql.msp.log
Error Number : 29537
Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Cannot find the object 'dm_exec_query_resource_semaphores', because it does not exist or you do not have permission.. To continue, correct the problem, and then run SQL Server Setup again.

now i don't know what to do...

i don't want to uninstall sqlserver 2005 and redo the whole thing...

because thisd is my first assinment in the new job and if i redo the whole thing then it won't be good since it'll take 1 week..

is there any way i can solve this issue?

A Knowledge Based article has been published explaining the problem and workaround:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;918695

Please let us know if this article does not address your problem.

Thanks,
Sam Lester (MSFT)

|||but i installed sp2.
the above kb is the issue with sp1

error in installing hot fix

i installed an service pack 2

Quick Details

Version:

9.00.3042

Knowledge Base (KB) Articles:

KB921896

Date Published:

3/6/2007

Language:

English

Download Size:

282.4 MB - 392.4 MB*

*Download size depends on selected download components.

the above installation , i was success. but the version is 9.00.3042. so what i did was i stalled the hot fix , http://support.microsoft.com/kb/933097

but this hox fis installation is success for all, but it failed for database services with below error message :

product Installation Status
Product : SQL Server Database Services 2005 (MSSQLSERVER)
Product Version (Previous): 3152
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB933097_sqlrun_sql.msp.log
Error Number : 29537
Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Cannot find the object 'dm_exec_query_resource_semaphores', because it does not exist or you do not have permission.. To continue, correct the problem, and then run SQL Server Setup again.

now i don't know what to do...

i don't want to uninstall sqlserver 2005 and redo the whole thing...

because thisd is my first assinment in the new job and if i redo the whole thing then it won't be good since it'll take 1 week..

is there any way i can solve this issue?

A Knowledge Based article has been published explaining the problem and workaround:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;918695

Please let us know if this article does not address your problem.

Thanks,
Sam Lester (MSFT)

|||but i installed sp2.
the above kb is the issue with sp1

Error in calculating Twelve Months to date using Time Intelligence

Hi,

I have used Time intelligence of AS2005 to calculate YTD and Twelve Months to date. YTD works pretty well, but there is an error in parsing the code that AS2005 generated when it created time intelligence for calculating Twelve Months to date.

The time dimension contains 2 hierarchies, Full Year (QTR- Month) and YTD hierarchy(Year - Qtr - Month) . Here is the code it generated for 12 months to date.

Scope( { [Measures].[Quote Count] } )

( [Total Year FY].[YTD Hierarchy Total Year FY Calculations 2].[Twelve Months to Date],

[Total Year FY].[YTD Hierarchy].[YTD Hierarchy].Members) =

Aggregate(

{ [Total Year FY].[YTD Hierarchy Total Year FY Calculations 2].DefaultMember } *

{ ParallelPeriod(

[Total Year FY].[YTD Hierarchy].[?MonthLevelUniqueName], 11,

[Total Year FY].[YTD Hierarchy].CurrentMember

) : [Total Year FY].[YTD Hierarchy].CurrentMember } )

End Scope

This is the error message

MdxScript(ApplicantHORT) (130, 8) The level '[YTD Hierarchy]' object was not found in the cube when the string, [Total Year FY].[YTD Hierarchy].[YTD Hierarchy], was parsed.

Any ideas how to fix this problem?

Thanks

Srinivas

Hi Srinivas,

If you refer to this SQL Server Magazine article, the MDX Script generated for Time Intelligence conforms to certain patterns; so I suspect that your [YTD Hierarchy] doesn't quite fit:

http://www.sqlmag.com/Articles/Print.cfm?ArticleID=46157

>>
Analysis Services 2005 Brings You Automated Time Intelligence
The Business Intelligence Wizard makes time analysis a snap
Mosha Pasumansky,
Robert Zare
InstantDoc #46157
June 2005

...

As Listing 3 shows, best practice uses attribute hierarchies on the left side of the assignment. In Analysis Services 2005, the cube space is defined entirely by attributes, so the space to which calculations apply is best described by the attribute hierarchies contained therein. Conversely, specifying the scope for user-defined hierarchies can result in inadvertently over-restricting the calculation scope. The Year to Date calculation illustrates this best practice through the use of the Fiscal Year attribute hierarchy, which excludes the All member (where the calculation doesn't apply). This approach includes all other attributes in the hierarchy, regardless of whether or not they are All members, which is precisely what we want.

Next, note that the right side of the assignment uses multilevel user hierarchies (rather than attribute hierarchies), which lets you use hierarchy-friendly MDX functions such as ParallelPeriod and PeriodsToDate.

...

( [Date].[Fiscal Date Calculations].[Twelve Month Moving Average],
[Date].[Month Name].[Month Name].Members ) =
Avg(
{
ParallelPeriod(
[Date].[Fiscal].[Month],
11,
[Date].[Fiscal].CurrentMember
) : [Date].[Fiscal].CurrentMember
},
[Date].[Fiscal Date Calculations].DefaultMember
) ;
>>

If you compare the above listing with your MDX Script, then [Total Year FY].[YTD Hierarchy].[YTD Hierarchy].Members should be members of the Month level of a Month attribute hierarchy, which should be incorporated in your user-defined [YTD Hierarchy]. Similarly, [Total Year FY].[YTD Hierarchy].[?MonthLevelUniqueName] should be the Month level of [YTD Hierarchy].

|||

Thanks Deepak,

It worked when i changed

[Total Year FY].[YTD Hierarchy].[YTD Hierarchy].Members to

[Total Year FY].[Months].[Months].Members and

[Total Year FY].[YTD Hierarchy].[?MonthLevelUniqueName] to

[Total Year FY].[YTD Hierarchy].[Months]

your post was really helpful.

error in calcualting week nr over DATEPART

hello,
i have the problem that the w nr calcualted from the actual date in not
localized...
in germany for example - from 21.11.2009 till 27.11.2005 there is the w
nr:47
if i execute in the queryanalyser
Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
Print DATEPART(wk, CONVERT(datetime,GetDate(),112))
i get the value 48 - instead of 47
any suggestion how to correct this?
thanksMaybe this will solve your problem:
set datefirst 6
Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
> hello,
> i have the problem that the w nr calcualted from the actual date in not
> localized...
> in germany for example - from 21.11.2009 till 27.11.2005 there is the w
> nr:47
> if i execute in the queryanalyser
> Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
> Print DATEPART(wk, CONVERT(datetime,GetDate(),112))
> i get the value 48 - instead of 47
> any suggestion how to correct this?
> thanks
>
>|||Don't use DATEPART to calculate w numbers, SQL Server doesn't follow the
ISO standard for this.
Use the ISOW function found in Books Online, of use a calendar table (you
can find an article on
this on www.aspfaq.com).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
> hello,
> i have the problem that the w nr calcualted from the actual date in not
> localized...
> in germany for example - from 21.11.2009 till 27.11.2005 there is the w
> nr:47
> if i execute in the queryanalyser
> Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
> Print DATEPART(wk, CONVERT(datetime,GetDate(),112))
> i get the value 48 - instead of 47
> any suggestion how to correct this?
> thanks
>
>|||yes, this solved my problem.
thanks Raymond
"Raymond D'Anjou" wrote:

> Maybe this will solve your problem:
> set datefirst 6
> Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
>
>|||hello Raymond,
sorry, but i have still problems
set datefirst 6
go
Print DATEPART(wk, CONVERT(datetime,'21.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'22.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'23.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'24.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'25.11.2005',104)) ->47
Print DATEPART(wk, CONVERT(datetime,'26.11.2005',104)) ->48 wrong !
Print DATEPART(wk, CONVERT(datetime,'27.11.2005',104)) ->48 wrong !
also the problem exist for
Print DATEPART(dw, CONVERT(datetime,'21.11.2005',104)) -> 3 wrong (must be 1
)
any ideas?
thanks
"Raymond D'Anjou" wrote:

> Maybe this will solve your problem:
> set datefirst 6
> Print DATEPART(wk, CONVERT(datetime,GetDate(),104))
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
>
>|||I had a feeling that it may not solve all your problems. :-(
Look at the Tibor's answer.
Here is the direct link to the article he suggested.
http://www.aspfaq.com/show.asp?id=2519
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:49D92FAA-54BE-4791-8C3B-13F3AA8D37C8@.microsoft.com...
> hello Raymond,
> sorry, but i have still problems
> set datefirst 6
> go
> Print DATEPART(wk, CONVERT(datetime,'21.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'22.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'23.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'24.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'25.11.2005',104)) ->47
> Print DATEPART(wk, CONVERT(datetime,'26.11.2005',104)) ->48 wrong !
> Print DATEPART(wk, CONVERT(datetime,'27.11.2005',104)) ->48 wrong !
> also the problem exist for
> Print DATEPART(dw, CONVERT(datetime,'21.11.2005',104)) -> 3 wrong (must be
> 1)
> any ideas?
> thanks
>
>
> "Raymond D'Anjou" wrote:
>|||hello Tibor,
yes the ISOW function solved my problem with calculating the w nr.
Any idea how to solve my problem with dw...
Print DATEPART(dw, CONVERT(datetime,'21.11.2005',104)) -> 3 wrong (must be 1
)
thanks
"Tibor Karaszi" wrote:

> Don't use DATEPART to calculate w numbers, SQL Server doesn't follow th
e ISO standard for this.
> Use the ISOW function found in Books Online, of use a calendar table (y
ou can find an article on
> this on www.aspfaq.com).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:DF192E14-BCB2-4A62-8E4E-3B158AB0A032@.microsoft.com...
>
>|||Always use a language neutral datetime format: http://www.karaszi.com/SQLServer/in...o_datetime.asp.
Anyhow, make sure you have proper SET DATEFIRST:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:9EB31742-EA64-4148-994E-531C3B730DA8@.microsoft.com...
> hello Tibor,
> yes the ISOW function solved my problem with calculating the w nr.
> Any idea how to solve my problem with dw...
> Print DATEPART(dw, CONVERT(datetime,'21.11.2005',104)) -> 3 wrong (must be
1)
> thanks
> "Tibor Karaszi" wrote:
>