Excel 2007 Issue (KB 929766) For Cube Reports; Doesn't Make Any Sense
Mar 12, 2007
In short, we have started deploying Office 2007 to our users and Excel is currently the only client we use to interact with our AS2005 cubes.
A few users have reported issues (which I've verified), but the explanation in the KB article doesn't make any sense to me. These reports were originally developed in Excel 2003 and when opening them up in Excel 2007, we'll see a message saying that Excel found unreadable content in the .xls file and after clicking 'Yes' to recover contents of the workbook, we then receive a message that a PivotTable report was discarded due to integrity problems. If I opened up this report in Excel 2003, I don't receive these errors or messages.
Per the KB's explanation (http://support.microsoft.com/default.aspx/kb/929766):
This issue occurs if the following conditions are true: €¢The workbook contains a PivotTable that uses key performance indicators (KPIs).€¢The KPIs are created in the Analysis Services Business Intelligence Development Studio.€¢One or more of the KPIs have an expression in the Current Time Member property.
Now, we are running 2005 Standard Edition with no SP, but will be deploying SP2 in a few days. Our cubes do not have any KPIs defined. Can I even define KPIs if we are only running Standard Edition?
I need any one's advice/imput on this...PLEASE!My computer will now begin the process of taking all the MS Access (NativeJet Engines - x30 total departmetns) and put the tbles/BE on SQL Server 2005and the Ms Access FE on MS Sharepoint.This is the kicker, say 20 out of the 30 (ball park) was created by oneperson and that is their whole job function was to create/maintain a QAtracking system and more.The person who created the 20 out 30 only knows intermediate ms access andsome vba, but NOT sql or net conversions (visual studio - all the differentlanguages), so the IT director asks me (I develop in MS Access andintermediate in VBA and can create web sites using publisher, front page andHTML) he asks me and this other person if we want to take on the challenge ofhelping him and the other IT guy in the conversion process of all of thesedb's.What does this do the developers who developed and still maintain thesecurrent 30 ms access db's, well you guessed it, it now takes all that hardwork that those developers did and still do (they still add more forms,updates) and it NOW takes the databases owners away from them and grant itnow to the person (s) who will maintain SQL Server 2005 ( I hope will be aDBA)???Is this true, once all the databases are converted, the owners will no longerbe able to go behind the scenes in tables, queries,etc.... It will now be inthe hands of a DBA?You know the funny thing is the IT Director wasn't even sure if he was goingto hire a DBA, who in the heck will maintain all of those db's on the server?There is only one other guy and he certainly does not have the training orskills or TIME.MY POINT QUESTION IS:when these conversion take place like this at a company, most of the time thems access dbs that have now be put into sql will now take the ownership awayfrom the owner (they cannot develop no more, unless they are sql friendly/dba)and put all of that into one persons hand (DBA) to maintain and development??????--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200606/1
I'm troubleshooting a stored procedure that suddenly decided to stop working. I narrowed down the problem to the last part of the stored procedure where it selects data from a temp table and inserts it into a physical table in the SQL2000 database.
I keep receiving the following error:
Server: Msg 8115, Level 16, State 8, Line 140 Arithmetic overflow error converting numeric to data type numeric.
The data values all appear to be correct with none of them seeming to be out of precision, but I keep getting the error. I've tried casting all the values and it didn't work. It executes w/o error when I comment out that particular insert. I just don't get it.
-- Input: @SPVId - SPV we are running process for -- @Yes - value of enum CCPEnum::eYesNoYes (get by lookup).
-- Output: Recordset (temp table) of Collaterals that are eligible for MV Test (#MVTriggerInvestments).
DECLARE @Yes INTEGER EXEC @RC = [dbo].CPLookupVal 'YesNo', 'Yes', @Yes OUTPUT IF (@RC<>0)BEGIN RAISERROR ('SP_OCCalculationMVTriggerTest: Failed to find Yes enum', 16, 1) WITH SETERROR END drop table #MVTriggerInvestments BEGIN
SELECT dbal.SPVId, dbal.CusipId, dbal.GroupId, @dtAsOfDate AS AsOfDate, dbal.NormalOCRate, dbal.SteppedUpOCRate, dbal.AllocMarketValue AS MarketValue, dbal.NbrDays, dbal.PriceChangeRatio
INTO #MVTriggerInvestments
FROM DailyCollateralBalance dbal
JOIN CollateralGroupIncludeInOC gin ON dbal.SPVId = 2 AND gin.SPVId = 2 AND dbal.AsOfDate = '2006-04-16' AND @dtAsOfDate BETWEEN gin.EffectiveFrom AND gin.EffectiveTo AND dbal.GroupId = gin.GroupId AND gin.IncludeInOC = @Yes
END select * from #MVTriggerInvestments print 'end #1' --select * from #MVTriggerInvestments --looks ok
-------------------------------------------------------------- -- 2) Calculate Weighted Average Price change ratio Market Value (by Group): -- PCRMV - Price Change Ratio Market Value --------------------------------------------------------------
-- Input : Recordset of collaterals (having New/Old prices, MarketValue defined) -- Output: Recordset Aggregated by Group (#GroupOCRate) drop table #MVTriggerGroup BEGIN
cast([dbo].fn_divide_or_number (B.PriceChangeRatioMarketValue, B.MarketValueForPeriod, 0.00) as numeric(12,9)) as PriceChangeRatio,
CAST (0 AS NUMERIC(12,9)) AS OCRate, CAST ('' AS VARCHAR(6)) AS OCRateType, CAST (0 AS NUMERIC(18,2)) AS DiscMarketValue, CAST (0 AS NUMERIC(18,2)) AS InterestAccrued
INTO #MVTriggerGroup
FROM ( SELECT SPVId, AsOfDate, GroupId, NormalOCRate, SteppedUpOCRate, cast(SUM(MarketValue) as numeric(18,2)) AS MarketValue
FROM #MVTriggerInvestments GROUP BY SPVId, AsOfDate, GroupId, NormalOCRate, SteppedUpOCRate ) A --works up to here
JOIN (SELECT SPVId, cast(SUM(AllocMarketValue) as numeric(18,2)) AS MarketValueForPeriod , cast(SUM(AllocMarketValue * PriceChangeRatio) as numeric(18,2)) as PriceChangeRatioMarketValue, GroupId
FROM T_DailyCollateralBalance WHERE SPVId = 2 AND AsOfDate between '2006-03-17' and '2006-04-15' AND IsBusinessDay = 1 GROUP BY SPVId, GroupId ) B
ON A.SPVId = B.SPVId AND A.GroupId = B.GroupId
END print 'end #2' --------------------------------------------- -- Calculate OCRate to apply for each group. --------------------------------------------- BEGIN UPDATE #MVTriggerGroup SET OCRate = (CASE WHEN ((PriceChangeRatio < 0) AND ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN SteppedUpOCRate ELSE NormalOCRate END), OCRateType = (CASE WHEN ((PriceChangeRatio < 0) AND ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN 'stepup' ELSE 'normal' END) END print 'end #3' ------------------------------------- -- Calculate discounted Market Value ------------------------------------- UPDATE #MVTriggerGroup SET DiscMarketValue = MarketValue / (1.0 + OCRate * 0.01) print 'end #4' --------------------------------- -- Insert data from temp tables --------------------------------- -- 1) select * from #MVTriggerInvestments
When I try to create an Offline cube from Excel 2007 I get the following error message. This used to work but I cannot figure out what to loo for.
Code Snippet Microsoft OLE DB Provider for Analysis Services 2005 : OLE DB error: OLE DB or ODBC error: XML for Analysis parser: The 'CreatedTimestamp' read-only element at line 1, column 38747 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Cubes/Cube/Scripts/MdxScript was ignored.; XML for Analysis parser: The 'LastSchemaUpdate' read-only element at line 1, column 38803 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Cubes/Cube/Scripts/MdxScript was ignored.; XML for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 4554 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 17325 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 57387 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 60047 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 62847 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 65497 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 72718 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 75425 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; Errors in the metadata manager. The attribute hierarchy for the Month attribute cannot be created because a hierarchy with the same ID or name already exists..
Can somebody advice my on what to look for? Thx!
The attribute hierarchy for the Month attribute cannot be created because a hierarchy with the same ID or name already exists. ----- There is no other Month?
I have inherited a SQL 2005 server with a few small databases on it. There's a maintenance plan here that doesn't seem to make a lot of sense to me. Can anyone comment:
Every Sunday at 4:00 AM
1. Reorganize index on All user database Tables and Views - compact large objects. 2. Rebuild index on local server connection, All user databases, Tables and view, Original amount of free space. 3. Shrink database. All user databases. Limit 100MB.
I'm confused a little about item 3. Won't a shrink be kind of useless after all of the work that goes on in steps 1 and 2. When I ran this manually, the transaction logs jumped significantly.
l've written a cursor to koop through a table and then insert the last 100 records into a table.Reason why l want the last 100 records is to monitor and log the last 100 trans avery hr or so.
-- Declare the variables to store the values returned by FETCH. SET ROWCOUNT 100 DECLARE @customer_No char(15), @loan_No char(12), @date_Issued datetime , @maturity_Date datetime , @status int
DECLARE loan_cursor CURSOR FOR SELECT customer_No, loan_No, date_Issued, maturity_Date, status
FROM loan
OPEN loan_cursor
-- Perform the first fetch and store the values in variables.
FETCH NEXT FROM loan_cursor INTO @customer_No, @loan_No, @date_Issued , @maturity_Date, @status,
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0 BEGIN
-- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM loan_cursor INTO @customer_No , @loan_No , @date_Issued , @maturity_Date , @status END
CLOSE loan_cursor
DEALLOCATE loan_cursor;
insert into Loan1 (customer_No, loan_No, date_Issued , maturity_Date , status )
select @customer_No, @loan_No, @date_Issued, @maturity_Date, @status FROM loan ORDER BY date_Issued desc;
Thanks to several guys here, I now understand how SQL Server configuration option works... Pretty nifty stuff.
Now, I'm trying to see if I can configure the Server property of the Connection Manager that holds the information for where my configuration table is. I thought about this and tried it, but it doesn't work. Then it occurred to me, this may not make sense to try to do because it is like the question, "what came first? the egg or the chicken?"
I am new to SSIS. i am trying to port database from SQL SERVER 2000 to 2005. i am using "Transfer SQL Server Objects" for this. i am just trying to move one object for testing wether it works or not. and it is not working. i am getting this error.
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E37 Description: "Invalid object name 'dbo.consta_AE'.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Both DBs are on seprate machines. of you need any more info please let me know. that would be great help.
We started to develop a datawarehouse solution for client back in December 2008 based on SQL 2008. We are convinced that we can use some of the new feature included in the new version which is the reason we we chossed to go down this path.
Due to the delay of the next version we have some question that I would like to hear you opinion on.
The estimatet "go live" date is during spring (march/april)
Is it wrong to contiue the development on the 2008 version?
We would need to run Release candidate at the customer site until the product is released. Is there any major changes coming up that are already known?
So far in the development we have had no big problems with releasecandidate.
We are importing a very small subset of a big desktop database into a CE database on a mobile device for an occasionally connected application. The idea is that the mobile device can use this CE database as a fall back database in case we are not connected.
The database is a very simple list of barcodes.
Basically a single field as primary key
EAN13 bigint
When we import 200K rows (yes we have quite a lot of them). The database is 7MB!!!! A bit big I would say since 8 bytes times 200.000 is only 1.5 MB. Where does the extra space come from?
I've looked at the other threads on this topic, and don't see an answer to the following question:
Why should an error destination time out waiting for error rows?
I'm using SQL Server Destinations both for my staging tables and for my "Error Staging" tables. Yet it seems that these are timing out if the package runs a long time without any error rows. This leads to two questiosn:
Why should an error destination time out waiting for error rows?
I can solve this by setting the timeouts to some very large number. But, is there a better way to do this? Right now, if the package takes five minutes, I need to set the timeouts to longer than five minutes. That does not sound like a good idea.
I am wondering if there is any sense to create indexed views on single table? I simple want to improve the report query performance as most of the reports data are from a single table. As views most of the time are created as for joined across tables.
Thank you very much for your advices and I am looking forward to hearing from you shortly.
I wonder if anyone can help me save the few remaining hairs on my head? I am trying to deploy reports created in SQL Server Business Intelligence Development Studio to SharePoint running SQL Server 2005 Reporting Services in SharePoint integrated mode. Whenever I try to deploy a report, the Reporting Services Login window pops up no matter which user/password I provide it won't accept any. I have the Reporting Services database set up to use Windows credentials and my Domain adminstrator account so I presume that this is user that I should be providing when prompted when deploying.
hello when i try to view reports in reports manage i get the following error
The type Microsoft.SharePoint.Portal.Analytics.UI.ReportViewerMessages, Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c does not implement IReportViewerMessages or could not be found Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Microsoft.Reporting.WebForms.InvalidConfigFileTypeException: The type Microsoft.SharePoint.Portal.Analytics.UI.ReportViewerMessages, Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c does not implement IReportViewerMessages or could not be found
Source Error:
The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:
1. Add a "Debug=true" directive at the top of the file that generated the error. Example:
<%@ Page Language="C#" Debug="true" %>
or:
2) Add the following section to the configuration file of your application:
Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.
Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario. Stack Trace:
I am having multiple URL in multiple zones for my site.I am not able to view SSRS reports on Internet which is in Internet zone where as i can view the same reports on Intranet site having URL in default zone. I m getting the following Error while accessing reports over internet:
" Unable to connect to the remote server.No connection to be made because target machine actively refused."
It would be great help if any one can solve this problem.
Haven't been able to find much information on using openrowset with excel 2007 xlsx files. I've tried the following with no success so far.
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=\serverfolderfile.xlsx;HDR=No;IMEX=1', 'SELECT f1 FROM [Raw Keywords$] WHERE f1 IS NOT NULL')
If anyone knows the correct way to do this please tell me.
now, how do i import excel 2007 files in SSIS? i don't have office 2007 installed on my computer, but i've got some .xslx that i would like to try ro import. i tried to create an OLEDB connection, but i don't know which provider to choose.
anyway, can i do it without the sp2 or office 2007 installed on my server?
Is there any way in RS2005 to export to Excel 2007 format ?
The reason I'm asking is that we have a report that can potentially have more than 256 columns but for now, there is no way of directly importing to excel 2003 (.xls).
Is microsoft going to come out with an improvement to RS2005 or is there already a way to export to Excel 2007 to bypass the 256 column limit?
i unpacked starter site and dw.pup file and i got 34 reports provided by commerce server 2007. i want to edit these reports. i followed these steps 1)created a "Report server project" in Visual studio 2005 2)In that project in "Shared data sources" folders i added "Startersite_Datawarehouse" of type "Microsoft SQL Server" 3)i added another shared data source name "Startersite_Datawarehouse" of the type "Microsoft SQL services analysis services" 4)later i added one of the reports to "Reports" folder by selecting "Add existing item". But in design mode of that report when i clicked "Preview" tab i am getting the error "an error occured during the local report processing. The item /CSDW_olap cant be found"
when i clicked "data" tab i am getting this below error "Connection cant be made to database"
I am trying to export data from database to excel 2007 file on my machine. I have downloaded the provider for office 2007 and I have XP with sp2 and SQL server 2005 with SP2. I havn't installed office 2007 on my machine,but I have a excel file which is created in office 2007.To get acess of read and write i have downloaded office compatibilty pack too,So I can read and write into the file. Now I am creating one SSIS package to export data into excel file.But I m not able to coause I am getting some errors like
"test connection failed because of an error in intializing provider. Invalid UDl file"
"Test connection is failed because of an error in intializing provider.Not a valid file name".
Please help or suggest how to export data from database to 2007 excel file.
I am wondering if there is a solution for our current issue i.e. we can't export the query report from SQL report services into 2007 Excel. We have no problem with Excel 2003.
I'm creating a small test package that copies a value from an Excel 2007 worksheet into a SQL 2005 database (SP2). When I do an Execute Task, I get the following error:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager \loripMandEScorecardsSQLQueryExample.xlsx failed with error code 0xC0202009.
However, if I do a "Preview" in the Editor for this same Excel Source task that fails, the data comes up as I would expect. What am I missing?
Has anybody seen this? Is there a configuration setting or something that needs to be changed? Working with the development version of SQL 2005 that comes with Visual Studio
Problem: I can't seem to get IS to work with Excel 2007 files. I've tried both BIDS and Import/Export Wizard
I've got the connection set to use ACE The datasource is: c:BranchList - 20080331.xlsx the extended properties property is: Excel 12.0;HDR=Yes Test connection succeeds Preview succeeds If I save the Excel file as tab-delimited text, BULK INSERT succeeds
But trying to run the package against Excel I get this error, over & over. I have not yet been able to get SQL2005 to import Excel 2007 data
TITLE: SQL Server Import and Export Wizard ------------------------------ Could not connect source component. Error 0xc0202009: Source - 'Branches $' [1]: An OLE DB error has occurred. Error code: 0x80004005.
Error 0xc02020e8: Source - 'Branches $' [1]: Opening a rowset for "`'Branches $'`" failed. Check that the object exists in the database.
------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
Since I would like to use an Excel 2007 File (*.xlsx) as Data Source, I created an ODBC Connection. It worked fine so far: the connection is established, I get the data as expected in the "Query Designer tab" and I could insert the fields in my report. However, when I want to see the result on the "Preview tab", I get an error saying that the "Report Definition '/myReport' is not valid...".
I don't know what could be wrong in my Report... Am I missing something in the ODBC configuration?
My Connection String is as follows: Dsn=pl_excel;dbq=C:PublicPL_DataSource.xlsx;defaultdir=C:Public;driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5
I also tried to create an OLE DB connection but I got the same error...
Thanks for your help, I'm waiting for your suggestions...
I am currently developing a report for users who insist on exporting to Excel 2007. When I export to Excel on my PC, the report formatting is fine (I am using Excel 2003), however, font sizing as applied in the report is lost when exported to Excel 2007 (i.e. font size 9 in the report is actually 10 in excel 2007). In Excel 2007, the data which runs onto two lines does not show up correctly - the second line is squashed below the first.
I have created a crystal report connecting an OLAP cube .But once itry to view a report it took around 15 minutes and finaly gave anerror like "cannot retrieve data from cube".I want to know why it isand how can i create a crystal report for OLAP CUB.
I have an Excel 2007 file which contains values in specific cells like A23, D30 etc. I want to populate the values in these cells using SSIS packages into individual rows of an SQL table. How can this be achieved ?
