AS2005: Replacing the sole data source for existing cube?

Does BI Dev Studio support (and if so, how) replacing the sole data source for an existing cube with a new data source? As a note, this new data source is really an enhanced version of the original, but with added fields, stored procedures, etc. I imagine that, if I can do what I'm asking about above, then the DSV Refresh function will either automate, or at least document, the changes needed at DSV level to keep the cube connection valid.

Question restated: DoesBI Dev Studio support (and if so, how) replacing the sole data source for an existing AS2005 cube with a new data source?

The best way to change the source of data for an existing OLAP or Data Mining object is simply to change the connection string of the Data Source object on which that object depends.After doing this, you may want to refresh the Data Source View object to confirm that the new relational source contains equivalent tables. (Right-click on the background of the DSV and choose the Refresh menu item.)

The connection string of Data Source objects is configuration dependent.This is handy as it allows you to have a single Data Source that will use one connection string when your project is in “Test” configuration and another connection string when it is in “Production” configuration. Click on the Properties menu item in the Project menu to see the active configuration and use the “Configuration Manager” button to create new configurations.

It also is possible to change the primary Data Source of a Data Source View by setting the Data Source property in the property panel.(Edit the DSV and click the background of the diagram to ensure that the DSV itself is selected.)However, changing the primary Data Source only changes the default Data Source for new tables added to the DSV.Since Data Source Views support tables from multiple Data Sources, each table already included in the DSV is associated with a specific Data Source.To change the Data Source for tables already in the DSV, you need to is replace each of the tables with an equivalent table from the Data Source of your choice (Right click on a table and choose Replace Table->With Other Table…).

AS2005 calculations bug?

This problem appears in BI-Developer Studio SP1 on a Windows 2003 Terminal server session. It appeared when I switched from the list view to the script/code view.

The calculations tab is empty with only this error message:

"Unexpected error occurred. Length cannot be less than zero. Parmeter name:length"

I am unable to see the calculations in the script view and in the list view. It is possible to see the calculations in the xml file if I choose code view.

Anyone who have seen this before?

Regards

Thomas Ivarsson

Problem solved. I had some rolling last 12 months calculations in a cube with only a few months of data that I tried to set to null and commented out all MDX-code. The purpose was to activate these calculations later. After removing null and activating the MDX-code everything works fine.

I think however that this is a minor bug.

Regards

Thomas Ivarsson

AS Deployment Wizard: Always generates errors

None of my SSAS projects will deploy & process with the Wizard. They will always deploy & process directly from BI Studio.

As a test, I built a very simple project. Two measures from a fact table, and one very simple dimension, from the same fact table.

The target DB is hosted on a server where I am admin.

When I attempt to deploy with the Deployment Wizard, I do the following:

1. Run the Wizard locally on the target server, under my login (which has admin rights).

2. Select the .asdatabase produced by the 'Build' from BI Studio.

3. Choose the target server and database.

4. Use the default settings for Partion & Role Options (the test DB has no seperate partitions or role definitions)

5. Use the default settings for Configuration Options

6. Choose Full Processing for Processing Options

... then I confirm the deploy...

Then I will always get:

"Errors in the OLAP storage engine. An error occurred while the 'xxxx' attribute of the 'yyyy' dimension from the 'zzzz' database was being processed."

When I do the same for a more complex project, the attribute and dimension that fail are random, changing from one deploy to the next. Unlike processing from BI Studio, no additional detail is given.

As an aside, when I select 'Process in Single Transaction', I get something different:

"The following system error occurred: Logon failure: unknown user name or bad password"

I'm not sure why this happens for that option, but not when it's turned off. I (think) I've tried all the different impersonation methods in the Configurations page, but always get the same result.

Seperately, I have tried instead to process an XMLA file from a SQL job, but get even less information in the errors there.

I've also looked at using ASCMD, but I dead ended there due to it only being availabe as source code (I don't have the full dev environment at present, so I don't think i can compile the project, unless there is some of distributable make utility).

The sound of a deadline whooshing by was about a week ago... any ideas much appreciated.

Your processing errors have nothing to do with the way you send the deploy command or the way you send processing command.

Most likely you got some referential integrity problems in your domain or fact tables. Simple cases where during processing of an attribute Analysis Server cannot find matching keys.

Most simple and brutal way is: tell it to ignore processing errors- you will find such options in the processing dialog.

The "cannot login" errors are pretty straightforward. If you get it during processing, it means Analysis Server cannot access the relational database. That could be due to the security settings of the datasource object. Or due to the Analysis Server's service account not having permissions.

If your XMLA script fails with such error, that could be simply becuase it couldnt access Analysis Server.

One general suggestion is: Try searching this forum, tons of information is avaliable here to you.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

I don't think it's the design. I've been processing it for weeks now using BI Studio, deploying to the exact same server - no errors, the cube works perfectly. No tricks like ignoring key errors either implemented or required - all the data, for both fact and dimensions, is from a dumb flat table. And, just to prove it to myself, I built the equivelent of a "Hello World"... with one measure, and one single attribute dimension. Totally basic - the dimension is just a text column in the fact table. Again, it always fails to deploy in the wizard. Again, it always deploys ok from BI.

As for the login issue when I select single transaction, I've now realised I can use SQL Profiler to see how far the wizard is getting with regards to RDBMs access - so I'll take a look at that on Monday.

I also think I'll make the test project even dumber on Monday. Just one row of fact data, and two coumns. One column for a measure, with a '1' in it. And one other column for a dimension, with 'Hello' in it. I don't think I can make it dumber than that.

My gut feel is that it's something authentication related but I just can't quite pin it down at the moment.

Regards,

Paul

|||

OK I've now solved this - it was authentication related as I'd guessed. I'm not sure I understand authentication & permissions configuration in SSAS, but here's what I did:

In properties for the solution:
Configuration Properties: Deployment: Build - set Remove Passwords to False

In Data Source designer for the data source:
Impersonation Information: Selected 'Use a specific user name and password' - entered credentials for a user account. (The account has admin permissions on the box. I don't know if that's significant.)

AS Deployment Wizard: Always generates errors

None of my SSAS projects will deploy & process with the Wizard. They will always deploy & process directly from BI Studio.

As a test, I built a very simple project. Two measures from a fact table, and one very simple dimension, from the same fact table.

The target DB is hosted on a server where I am admin.

When I attempt to deploy with the Deployment Wizard, I do the following:

1. Run the Wizard locally on the target server, under my login (which has admin rights).

2. Select the .asdatabase produced by the 'Build' from BI Studio.

3. Choose the target server and database.

4. Use the default settings for Partion & Role Options (the test DB has no seperate partitions or role definitions)

5. Use the default settings for Configuration Options

6. Choose Full Processing for Processing Options

... then I confirm the deploy...

Then I will always get:

"Errors in the OLAP storage engine. An error occurred while the 'xxxx' attribute of the 'yyyy' dimension from the 'zzzz' database was being processed."

When I do the same for a more complex project, the attribute and dimension that fail are random, changing from one deploy to the next. Unlike processing from BI Studio, no additional detail is given.

As an aside, when I select 'Process in Single Transaction', I get something different:

"The following system error occurred: Logon failure: unknown user name or bad password"

I'm not sure why this happens for that option, but not when it's turned off. I (think) I've tried all the different impersonation methods in the Configurations page, but always get the same result.

Seperately, I have tried instead to process an XMLA file from a SQL job, but get even less information in the errors there.

I've also looked at using ASCMD, but I dead ended there due to it only being availabe as source code (I don't have the full dev environment at present, so I don't think i can compile the project, unless there is some of distributable make utility).

The sound of a deadline whooshing by was about a week ago... any ideas much appreciated.

Your processing errors have nothing to do with the way you send the deploy command or the way you send processing command.

Most likely you got some referential integrity problems in your domain or fact tables. Simple cases where during processing of an attribute Analysis Server cannot find matching keys.

Most simple and brutal way is: tell it to ignore processing errors- you will find such options in the processing dialog.

The "cannot login" errors are pretty straightforward. If you get it during processing, it means Analysis Server cannot access the relational database. That could be due to the security settings of the datasource object. Or due to the Analysis Server's service account not having permissions.

If your XMLA script fails with such error, that could be simply becuase it couldnt access Analysis Server.

One general suggestion is: Try searching this forum, tons of information is avaliable here to you.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

I don't think it's the design. I've been processing it for weeks now using BI Studio, deploying to the exact same server - no errors, the cube works perfectly. No tricks like ignoring key errors either implemented or required - all the data, for both fact and dimensions, is from a dumb flat table. And, just to prove it to myself, I built the equivelent of a "Hello World"... with one measure, and one single attribute dimension. Totally basic - the dimension is just a text column in the fact table. Again, it always fails to deploy in the wizard. Again, it always deploys ok from BI.

As for the login issue when I select single transaction, I've now realised I can use SQL Profiler to see how far the wizard is getting with regards to RDBMs access - so I'll take a look at that on Monday.

I also think I'll make the test project even dumber on Monday. Just one row of fact data, and two coumns. One column for a measure, with a '1' in it. And one other column for a dimension, with 'Hello' in it. I don't think I can make it dumber than that.

My gut feel is that it's something authentication related but I just can't quite pin it down at the moment.

Regards,

Paul

|||

OK I've now solved this - it was authentication related as I'd guessed. I'm not sure I understand authentication & permissions configuration in SSAS, but here's what I did:

In properties for the solution:
Configuration Properties: Deployment: Build - set Remove Passwords to False

In Data Source designer for the data source:
Impersonation Information: Selected 'Use a specific user name and password' - entered credentials for a user account. (The account has admin permissions on the box. I don't know if that's significant.)

AS 2005: Where are Parallel Period calculations in the Business Intelligence Wizard

I am using the July CTP of SQL Server 2005.

Books Online indicates that the Business Intelligence Wizard in the BI Development Studio supports parallel period calculations. I did not see this class of calculations in the list.

The available calculations were Year to Date, Quarter to Date, Month to Date, Twelve Months to Date, Twelve Month Moving Average, Six Month Moving Average, Three Month Moving Average, Year Over Year Growth, Year Over Year Growth %, Quarter Over Quarter Growth, Quarter Over Quarter Growth %, Month Over Month Growth, Month Over Month Growth %, Day Over Day Growth, Day Over Day Growth %. I would have expected calculations like "Period Prior Year" or "Prior Period"

Is there something special I need to do to make the parallel period calculations available? Or are they just not yet available in the July CTP?Kendal,

When BOL states "Parallel period comparisons", it is referring to the "... Growth" and "... Growth %" calculations that are available. For example, the "Year Over Year Growth" calculation will return the difference between measures for a period in one year compared to the same period in the prior year (whether the period selected is a quarter, month, week, or day).

Let me know if that is not the type of parallel period calculations you are wanting...

Dave Fackler
|||

You have clarified it for me.

I had previously created and used more direct period calculated measures like "Value in prior period" or "Value for same period in prior year". I expected to see those as well. But we can create those on our own based on the provided templates.

Thanks for the response!

AS 2005 Business Intelligence Wizard scripts

In Analysis Services 2005, using the Business Intelligence Wizard in the BI Development Studio to add time calculations to a cube worked fine when only base measures are included in the scope. When a calculated measure is added to the scope of the Business Intelligence Wizard time calculation, all that is returned for the Business Intelligence Wizard time calculation value is “NA”. What could be the problem/how can I make this work correctly?

The exact same time calculation was being created by the Business Intelligence Wizard - the only difference was adding one calculated measure to the scope. Could this have anything to do with pass and solve order? Do the measures that the calculated measure depends on also have to be listed in the scope for the BI Development Studio time calculation? I'm sort of reaching here.

Any help is appreciated,
Dan

Dan,

There are two separate issues, both of which have been fixed for the next CTP:

1. A redundant scope is created when both physical and calculated measures are chosen.
2. Seeing NA in the context of calculated measures.

The only *possible* workaround I know of for issue 2 is to edit the MDX script and replace the AGGREGATE function with SUM, but this should only be done if you are postive that it makes sense to sum the measures in question. Applying this change may then cause you to hit issue 1, at which point you would, if needed, want to remove the first (outer scope) and move those physical measures into the inline scopes (on the left hand side of the '=').

-rob|||Thanks for the thorough explanation. I will attempt to apply the solution(s).

Dan
|||Is a CTP planned between now and the final release?|||Yes, there will be another CTP released in the near future.

-rob

AS 2005 Business Intelligence Wizard - rerunning to add time calculations

In Analysis Services 2005, once you’ve used the Business Intelligence Wizard in the BI Development Studio to add time calculations to a cube, how do you come back later and add additional standard time calculations based on the named calculation that was already created in the cube’s data source view by the prior run of the Wizard?Kendal,

Assuming I understand you correctly, you want to add additional custom time calculations after running the Time Intelligence wizard...

The wizard creates a calculated column in your time dimension table within the DSV. It then adds a new attribute hierarchy to your time dimension based on this new attribute. This serves as an "holding point" for the calculations. It then creates the calculations you selected in the calculations script for the cube, using the following steps:

- it starts the calcuations by adding new members to the attribute hierarchy with a default value of "NA"
- the script then uses a "scope" statement to scope the calculations to the measures you selected in the wizard
- each of the calculations you selected by the wizard are then implemented by using an MDX expression to define the values of the new calculated member across the time hierarchy the calculations apply to
- finally, an "end scope" statement closes things out

If you review the calculation script for the cube, you should see this at the end of the script after running the Time Intelligence wizard.

To add new custom calculations to the same attribute hierarchy created by the wizard, you can just add new calculated members to the hierarchy and add the custom MDX expressions needed. To add a new member, simply add a new "Create Member" statement before the "scope" statement, using the same syntax and construction as the ones added by the wizard. Then, add a new calculation after the "scope" statement that specifies the MDX expression that should be used to calculate values for the new calculated member. Use a syntax similar to that used for the existing calculations.

You should note that any calculated members that you add in this manner will be restricted to the same scope as the ones added by the wizard -- meaning the calculated member will only return values for the same list of members selected in the wizard. You can use variations of this technique with your own scope statements to change this.

Hope this helps and is understandable :-)

Dave Fackler
|||It does help. I had been thinking of the wizard as more of a development tool that would allow me to iteratively refine my cube. However, at this point in time, it serves more to codify best practices and generate examples of how to implement certain features in the context of the cube.

As is, it certainly is a big benefit.

Thanks for the clarification.|||

Hi Dave

I have tried to 'add time intelligence' but when I browse the result I only get something in 'current time'. In 'year to date' I only get the value 'N/A'

On my calculations I have nothing in my 'parent hierarchies' . On the parent hierarchies i got an error

the specified hierarchy [Time].[Year - Quarter - Month - Date Time Calculations] does not exist int the cube.

Scope(

{

[Measures].[Invoice Net Sales Amt]

}

)

( [Time].[Year - Quarter - Month - Date Time Calculations].[Quarter to Date],

[Time].[Quarter].[Quarter].Members ) =

Aggregate(

{ [Time].[Year - Quarter - Month - Date Time Calculations].DefaultMember } *

PeriodsToDate(

[Time].[Year - Quarter - Month - Date].[QuarterName],

[Time].[Year - Quarter - Month - Date].CurrentMember

)

)

End Scope

|||

Some of the calculations produced by the Time Intelligence Wizard, including the Year to Date ones, don't work - see
http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!379.entry

Hopefully this will be fixed in SP1.

Chris

|||

I'm running SP1 on Standard Edition. I'm also using the code (below) that's recommended as a workaround but still get the same problem. NAs are displayed when I try to browse the data for this measure / calculation combo. All I've done is put the code into my calculations script on Adventure Works. Current Date (which I assume is a hidden calculation) works.

Is there any other issue that could be causing this or is there something I've missed? Any help on this would be much appreciated.

Create Member

CurrentCube.[Date].[Fiscal Date Calculations].[Year to Date]

As "NA" ;

Scope(

{

[Measures].[Internet Sales Amount],

[Measures].[Reseller Sales Amount]

}

);

( [Date].[Fiscal Date Calculations].[Year to Date],

[Date].[Fiscal Year].[Fiscal Year].Members ) =

Aggregate(

{ [Date].[Fiscal Date Calculations].DefaultMember } *

PeriodsToDate(

[Date].[Fiscal].[Fiscal Year],

[Date].[Fiscal].CurrentMember

)

) ;

AS 2005 Business Intelligence Wizard - rerunning to add time calculations

In Analysis Services 2005, once you’ve used the Business Intelligence Wizard in the BI Development Studio to add time calculations to a cube, how do you come back later and add additional standard time calculations based on the named calculation that was already created in the cube’s data source view by the prior run of the Wizard?Kendal,

Assuming I understand you correctly, you want to add additional custom time calculations after running the Time Intelligence wizard...

The wizard creates a calculated column in your time dimension table within the DSV. It then adds a new attribute hierarchy to your time dimension based on this new attribute. This serves as an "holding point" for the calculations. It then creates the calculations you selected in the calculations script for the cube, using the following steps:

- it starts the calcuations by adding new members to the attribute hierarchy with a default value of "NA"
- the script then uses a "scope" statement to scope the calculations to the measures you selected in the wizard
- each of the calculations you selected by the wizard are then implemented by using an MDX expression to define the values of the new calculated member across the time hierarchy the calculations apply to
- finally, an "end scope" statement closes things out

If you review the calculation script for the cube, you should see this at the end of the script after running the Time Intelligence wizard.

To add new custom calculations to the same attribute hierarchy created by the wizard, you can just add new calculated members to the hierarchy and add the custom MDX expressions needed. To add a new member, simply add a new "Create Member" statement before the "scope" statement, using the same syntax and construction as the ones added by the wizard. Then, add a new calculation after the "scope" statement that specifies the MDX expression that should be used to calculate values for the new calculated member. Use a syntax similar to that used for the existing calculations.

You should note that any calculated members that you add in this manner will be restricted to the same scope as the ones added by the wizard -- meaning the calculated member will only return values for the same list of members selected in the wizard. You can use variations of this technique with your own scope statements to change this.

Hope this helps and is understandable :-)

Dave Fackler
|||It does help. I had been thinking of the wizard as more of a development tool that would allow me to iteratively refine my cube. However, at this point in time, it serves more to codify best practices and generate examples of how to implement certain features in the context of the cube.

As is, it certainly is a big benefit.

Thanks for the clarification.|||

Hi Dave

I have tried to 'add time intelligence' but when I browse the result I only get something in 'current time'. In 'year to date' I only get the value 'N/A'

On my calculations I have nothing in my 'parent hierarchies' . On the parent hierarchies i got an error

the specified hierarchy [Time].[Year - Quarter - Month - Date Time Calculations] does not exist int the cube.

Scope(

{

[Measures].[Invoice Net Sales Amt]

}

)

( [Time].[Year - Quarter - Month - Date Time Calculations].[Quarter to Date],

[Time].[Quarter].[Quarter].Members ) =

Aggregate(

{ [Time].[Year - Quarter - Month - Date Time Calculations].DefaultMember } *

PeriodsToDate(

[Time].[Year - Quarter - Month - Date].[QuarterName],

[Time].[Year - Quarter - Month - Date].CurrentMember

)

)

End Scope

|||

Some of the calculations produced by the Time Intelligence Wizard, including the Year to Date ones, don't work - see
http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!379.entry

Hopefully this will be fixed in SP1.

Chris

|||

I'm running SP1 on Standard Edition. I'm also using the code (below) that's recommended as a workaround but still get the same problem. NAs are displayed when I try to browse the data for this measure / calculation combo. All I've done is put the code into my calculations script on Adventure Works. Current Date (which I assume is a hidden calculation) works.

Is there any other issue that could be causing this or is there something I've missed? Any help on this would be much appreciated.

Create Member

CurrentCube.[Date].[Fiscal Date Calculations].[Year to Date]

As "NA" ;

Scope(

{

[Measures].[Internet Sales Amount],

[Measures].[Reseller Sales Amount]

}

);

( [Date].[Fiscal Date Calculations].[Year to Date],

[Date].[Fiscal Year].[Fiscal Year].Members ) =

Aggregate(

{ [Date].[Fiscal Date Calculations].DefaultMember } *

PeriodsToDate(

[Date].[Fiscal].[Fiscal Year],

[Date].[Fiscal].CurrentMember

)

) ;

AS 2005 Business Intelligence Wizard - rerunning to add time calculations

In Analysis Services 2005, once you’ve used the Business Intelligence Wizard in the BI Development Studio to add time calculations to a cube, how do you come back later and add additional standard time calculations based on the named calculation that was already created in the cube’s data source view by the prior run of the Wizard?Kendal,

Assuming I understand you correctly, you want to add additional custom time calculations after running the Time Intelligence wizard...

The wizard creates a calculated column in your time dimension table within the DSV. It then adds a new attribute hierarchy to your time dimension based on this new attribute. This serves as an "holding point" for the calculations. It then creates the calculations you selected in the calculations script for the cube, using the following steps:

- it starts the calcuations by adding new members to the attribute hierarchy with a default value of "NA"
- the script then uses a "scope" statement to scope the calculations to the measures you selected in the wizard
- each of the calculations you selected by the wizard are then implemented by using an MDX expression to define the values of the new calculated member across the time hierarchy the calculations apply to
- finally, an "end scope" statement closes things out

If you review the calculation script for the cube, you should see this at the end of the script after running the Time Intelligence wizard.

To add new custom calculations to the same attribute hierarchy created by the wizard, you can just add new calculated members to the hierarchy and add the custom MDX expressions needed. To add a new member, simply add a new "Create Member" statement before the "scope" statement, using the same syntax and construction as the ones added by the wizard. Then, add a new calculation after the "scope" statement that specifies the MDX expression that should be used to calculate values for the new calculated member. Use a syntax similar to that used for the existing calculations.

You should note that any calculated members that you add in this manner will be restricted to the same scope as the ones added by the wizard -- meaning the calculated member will only return values for the same list of members selected in the wizard. You can use variations of this technique with your own scope statements to change this.

Hope this helps and is understandable :-)

Dave Fackler
|||It does help. I had been thinking of the wizard as more of a development tool that would allow me to iteratively refine my cube. However, at this point in time, it serves more to codify best practices and generate examples of how to implement certain features in the context of the cube.

As is, it certainly is a big benefit.

Thanks for the clarification.|||

Hi Dave

I have tried to 'add time intelligence' but when I browse the result I only get something in 'current time'. In 'year to date' I only get the value 'N/A'

On my calculations I have nothing in my 'parent hierarchies' . On the parent hierarchies i got an error

the specified hierarchy [Time].[Year - Quarter - Month - Date Time Calculations] does not exist int the cube.

Scope(

{

[Measures].[Invoice Net Sales Amt]

}

)

( [Time].[Year - Quarter - Month - Date Time Calculations].[Quarter to Date],

[Time].[Quarter].[Quarter].Members ) =

Aggregate(

{ [Time].[Year - Quarter - Month - Date Time Calculations].DefaultMember } *

PeriodsToDate(

[Time].[Year - Quarter - Month - Date].[QuarterName],

[Time].[Year - Quarter - Month - Date].CurrentMember

)

)

End Scope

|||

Some of the calculations produced by the Time Intelligence Wizard, including the Year to Date ones, don't work - see
http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!379.entry

Hopefully this will be fixed in SP1.

Chris

|||

I'm running SP1 on Standard Edition. I'm also using the code (below) that's recommended as a workaround but still get the same problem. NAs are displayed when I try to browse the data for this measure / calculation combo. All I've done is put the code into my calculations script on Adventure Works. Current Date (which I assume is a hidden calculation) works.

Is there any other issue that could be causing this or is there something I've missed? Any help on this would be much appreciated.

Create Member

CurrentCube.[Date].[Fiscal Date Calculations].[Year to Date]

As "NA" ;

Scope(

{

[Measures].[Internet Sales Amount],

[Measures].[Reseller Sales Amount]

}

);

( [Date].[Fiscal Date Calculations].[Year to Date],

[Date].[Fiscal Year].[Fiscal Year].Members ) =

Aggregate(

{ [Date].[Fiscal Date Calculations].DefaultMember } *

PeriodsToDate(

[Date].[Fiscal].[Fiscal Year],

[Date].[Fiscal].CurrentMember

)

) ;

Arithmetic overflow error converting expression to data type datetime

I tried this new SQL2K5 Performance Dashboard Reports using custom reports in Management Studio.

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

But running it first on any server gives me this error:

Difference of two datetime columns caused overflow at runtime.

Has anybody come across this error? How to fix it?

Thanks in advance!

- Rupesh

first check the sp2 is applied or not.

ref : http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx

Because DATEDIFF returns and int once you have connection that is more than 24 days or so old it will overflow the dattype if you modify the procedure so caluclates the differnce in minutes first converts this to milliseconds then add the number of minutes diffrence onto the start time and then calculate the remianing number of milli seconds it will work so basicalyy if you modify trhe offending line

sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

to

sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS

BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute,

DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

then it will work

hopes this helps the rest of you who have the same problem.

Madhu

|||

Hi,

I am facing an error saying ‘Arithmetic overflow error converting expression to data type datetime.’ In data base due to my following query.

Then I tried with cast and convert function too, still I got the error.

select*

from datetable

wherecast(('May 29 20076:30:00:000PM' - endtime) as int) >=2

andcast(('May 29 20076:30:00:000PM' - endtime)as int)<=3

anddatetable_id= 102

order by datetable_iddesc

I got this beacause of some bad ‘endtime’ data in datetable for datetable_id102 : 5465-08-12 12:00:00.000.

But I need to support all type of date here and the table is also huge. So I have this col as indexed.

I thought of to use datediff func here. again I am not sure what will be the performance impact on my query, coz it will diff and convert to int and compare for each of the row.

So can any body suggest how efficiently can I handle this?

Thanks

~Dhiru

Are we not able to edit stored procedures in SQL express?

Have sql 2005 express installed.

Running a database for a DotNet Nuke site

Opened Microsoft SQL Server Management Studio Express.

Navigated to CASPORTAL\Databases\DotNetNuke\Programmability\Stored Procedures\dbo.AddUser

Right clicked dbo.AddUser and selected "modify"

This allowed me to paste the additional code into the right hand window/pane, however when I try and save this it wants to save it a seperate file /query. Is there something I don't understand?

am I not able to edit the original stored procedure?

Hi,

thats a common minsunderstanding. Hitting the disc symbol will save the data, what you will have to do is to execute the stored procedure within this pane. There is a button for this reading "Execute", this will execute the current query presented in your pane (which is an ALTER PROCEDURE statement). This will apply your changes to the database.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Are SRS 2005 Reports Backwards Compatible?

I have a customer that is running SRS 2000 and unfortunately I only have SRS
2005 and Visual Studio 2005 in a test environement. Is it possible to
develop the reports in SRS 2005 and save them somehow that they can be
deployed on a 2000 instance of SRS?
If not, can I create the reports on in Visual Studio 2005 while I am hitting
their SRS 2000 instance?No on both accounts.
RS 2000 report designer only runs in VS 2003.
Theorectically you could hand edit the RDL and remove RS 2005 specific
elements but I wouldn't want to try to do that.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jack Bender" <JackBender@.discussions.microsoft.com> wrote in message
news:DAC028CC-5F46-4F00-B14E-D6EE44A43913@.microsoft.com...
>I have a customer that is running SRS 2000 and unfortunately I only have
>SRS
> 2005 and Visual Studio 2005 in a test environement. Is it possible to
> develop the reports in SRS 2005 and save them somehow that they can be
> deployed on a 2000 instance of SRS?
> If not, can I create the reports on in Visual Studio 2005 while I am
> hitting
> their SRS 2000 instance?