Excel Export error in RS200SP2 when nesting tables within matirx

I was hoping someone could help out with this issue....I've got a report I created in Reporting Services 2000 SP2 where I am nesting table elements within the row and detail sections of the matrix. This allows me to have multiple items within my "row" data with column headings, and works perfectly fine within the designer, and viewer, and whenever I export it to any format other than Excel. Exporting to excel results in a "Specified cast is not valid" error.

To me this seems to be a bug in the Excel rendering extension, since it seems to be supported in RDL. Has anyone else run into this? Is there a fix to the renderer or a workaround that anyone is aware of?

I'd be happy to post the RDL if anyone is interested.

Thanks,

Casey

The Excel rendering extension does not support exporting nested tables within tables/matrices, so this won't work for you. The behavior is documented in books online somewhere.

However, normally the "nested" region should simply be exported as a blank space without throwing an error. A bug was reported on this problem, and it is fixed in 2005 (by "fixed", you still won't be able to export nested data regions, but you won't get an error).

|||

That's cool....I figured it wouldn't work, but thought it worth a try. I using nested tables mostly to give me the ability to add headers to rowgroup items, which I was able to reproduce visually by dropping a rectangle object on the matrix and arranging textboxes to simulate the table headers.

Thanks for the response.

|||

Were you able to find a workaround for exporting the nested tables to Excel?|||Not really, it looks like the limitation is in the Excel renderer. What I was able to do in my case which helped was to embed rectangles within the matrix column headers and add multiple text boxes within them. For the report I was working on that gave me the ability to approximate the format I was wanting. Not perfect, but good enough.

Excel Export error in RS200SP2 when nesting tables within matirx

I was hoping someone could help out with this issue....I've got a report I created in Reporting Services 2000 SP2 where I am nesting table elements within the row and detail sections of the matrix. This allows me to have multiple items within my "row" data with column headings, and works perfectly fine within the designer, and viewer, and whenever I export it to any format other than Excel. Exporting to excel results in a "Specified cast is not valid" error.

To me this seems to be a bug in the Excel rendering extension, since it seems to be supported in RDL. Has anyone else run into this? Is there a fix to the renderer or a workaround that anyone is aware of?

I'd be happy to post the RDL if anyone is interested.

Thanks,

Casey

The Excel rendering extension does not support exporting nested tables within tables/matrices, so this won't work for you. The behavior is documented in books online somewhere.

However, normally the "nested" region should simply be exported as a blank space without throwing an error. A bug was reported on this problem, and it is fixed in 2005 (by "fixed", you still won't be able to export nested data regions, but you won't get an error).

|||

That's cool....I figured it wouldn't work, but thought it worth a try. I using nested tables mostly to give me the ability to add headers to rowgroup items, which I was able to reproduce visually by dropping a rectangle object on the matrix and arranging textboxes to simulate the table headers.

Thanks for the response.

|||

Were you able to find a workaround for exporting the nested tables to Excel?|||Not really, it looks like the limitation is in the Excel renderer. What I was able to do in my case which helped was to embed rectangles within the matrix column headers and add multiple text boxes within them. For the report I was working on that gave me the ability to approximate the format I was wanting. Not perfect, but good enough.

sql

Excel Export error in RS200SP2 when nesting tables within matirx

I was hoping someone could help out with this issue....I've got a report I created in Reporting Services 2000 SP2 where I am nesting table elements within the row and detail sections of the matrix. This allows me to have multiple items within my "row" data with column headings, and works perfectly fine within the designer, and viewer, and whenever I export it to any format other than Excel. Exporting to excel results in a "Specified cast is not valid" error.

To me this seems to be a bug in the Excel rendering extension, since it seems to be supported in RDL. Has anyone else run into this? Is there a fix to the renderer or a workaround that anyone is aware of?

I'd be happy to post the RDL if anyone is interested.

Thanks,

Casey

The Excel rendering extension does not support exporting nested tables within tables/matrices, so this won't work for you. The behavior is documented in books online somewhere.

However, normally the "nested" region should simply be exported as a blank space without throwing an error. A bug was reported on this problem, and it is fixed in 2005 (by "fixed", you still won't be able to export nested data regions, but you won't get an error).

|||

That's cool....I figured it wouldn't work, but thought it worth a try. I using nested tables mostly to give me the ability to add headers to rowgroup items, which I was able to reproduce visually by dropping a rectangle object on the matrix and arranging textboxes to simulate the table headers.

Thanks for the response.

|||

Were you able to find a workaround for exporting the nested tables to Excel?|||Not really, it looks like the limitation is in the Excel renderer. What I was able to do in my case which helped was to embed rectangles within the matrix column headers and add multiple text boxes within them. For the report I was working on that gave me the ability to approximate the format I was wanting. Not perfect, but good enough.

Excel Export Error - Excel Can't Open File, CPU 100% Utilization

I've got an issue I cannot seem to address. We've upgraded to SP1, and I
still am not able to open Excel files exported from Reporting Services if
they contain more than a few hundred rows.
For example, I have a report which contains 1453 records. Exporting to an
Excel takes a few minutes, then generates a 7 MB file. Attempting to open
that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel steadily
consumes 8K of RAM per second, yet nothing happens. Excel will either
display as "Not Responding" in Task Manager, or will display "Running" and I
cannot switch back to Excel.
I can export to CSV and Excel will open it with no issues. However,
exporting to Excel is critical to our business' use of Reporting Services.
Please advise if anyone has a workaround or is encountering this issue.
--
Aaron Berquist
Business Systems Analyst, Information Services
Open Text CorporationCould you send me the excel file generated for a smaller number rows,
please?
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron Berquist" <aberquis@.opentext.com> wrote in message
news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
> I've got an issue I cannot seem to address. We've upgraded to SP1, and I
> still am not able to open Excel files exported from Reporting Services if
> they contain more than a few hundred rows.
> For example, I have a report which contains 1453 records. Exporting to an
> Excel takes a few minutes, then generates a 7 MB file. Attempting to open
> that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
steadily
> consumes 8K of RAM per second, yet nothing happens. Excel will either
> display as "Not Responding" in Task Manager, or will display "Running" and
I
> cannot switch back to Excel.
> I can export to CSV and Excel will open it with no issues. However,
> exporting to Excel is critical to our business' use of Reporting Services.
> Please advise if anyone has a workaround or is encountering this issue.
> --
> Aaron Berquist
> Business Systems Analyst, Information Services
> Open Text Corporation
>|||Any progress on this issue?
"Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
news:eU3AjxCaEHA.4052@.TK2MSFTNGP10.phx.gbl...
> Could you send me the excel file generated for a smaller number rows,
> please?
> --
> Nico Cristache [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Aaron Berquist" <aberquis@.opentext.com> wrote in message
> news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
> > I've got an issue I cannot seem to address. We've upgraded to SP1, and I
> > still am not able to open Excel files exported from Reporting Services
if
> > they contain more than a few hundred rows.
> >
> > For example, I have a report which contains 1453 records. Exporting to
an
> > Excel takes a few minutes, then generates a 7 MB file. Attempting to
open
> > that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
> steadily
> > consumes 8K of RAM per second, yet nothing happens. Excel will either
> > display as "Not Responding" in Task Manager, or will display "Running"
and
> I
> > cannot switch back to Excel.
> >
> > I can export to CSV and Excel will open it with no issues. However,
> > exporting to Excel is critical to our business' use of Reporting
Services.
> >
> > Please advise if anyone has a workaround or is encountering this issue.
> > --
> > Aaron Berquist
> > Business Systems Analyst, Information Services
> > Open Text Corporation
> >
> >
>|||Well, I solved the problem!
The header on the report was causing the issue, as the fields were not lined
up with the data columns on my report, so I think Excel was goign nuts
trying to parse out all the merged columns. By making sure that the
descriptions in the header matched eneded at the same point as a column, the
file now takes about 50 secs to open on a Pentium 3 1Ghz machine.
So, when designing a header, make sure your test boxes line up with your
columns!
Thanks,
Aaron
"Aaron Berquist" <aberquis@.opentext.com> wrote in message
news:erwtoi$bEHA.2408@.tk2msftngp13.phx.gbl...
> Any progress on this issue?
> "Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
> news:eU3AjxCaEHA.4052@.TK2MSFTNGP10.phx.gbl...
> > Could you send me the excel file generated for a smaller number rows,
> > please?
> >
> > --
> > Nico Cristache [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Aaron Berquist" <aberquis@.opentext.com> wrote in message
> > news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
> > > I've got an issue I cannot seem to address. We've upgraded to SP1, and
I
> > > still am not able to open Excel files exported from Reporting Services
> if
> > > they contain more than a few hundred rows.
> > >
> > > For example, I have a report which contains 1453 records. Exporting to
> an
> > > Excel takes a few minutes, then generates a 7 MB file. Attempting to
> open
> > > that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
> > steadily
> > > consumes 8K of RAM per second, yet nothing happens. Excel will either
> > > display as "Not Responding" in Task Manager, or will display "Running"
> and
> > I
> > > cannot switch back to Excel.
> > >
> > > I can export to CSV and Excel will open it with no issues. However,
> > > exporting to Excel is critical to our business' use of Reporting
> Services.
> > >
> > > Please advise if anyone has a workaround or is encountering this
issue.
> > > --
> > > Aaron Berquist
> > > Business Systems Analyst, Information Services
> > > Open Text Corporation
> > >
> > >
> >
> >
>|||There is also a deviceinfo setting to put the page header into the Excel
header, rather than the sheets.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron Berquist" <aberquis@.opentext.com> wrote in message
news:%23TxUDQLcEHA.3012@.tk2msftngp13.phx.gbl...
> Well, I solved the problem!
> The header on the report was causing the issue, as the fields were not
> lined
> up with the data columns on my report, so I think Excel was goign nuts
> trying to parse out all the merged columns. By making sure that the
> descriptions in the header matched eneded at the same point as a column,
> the
> file now takes about 50 secs to open on a Pentium 3 1Ghz machine.
> So, when designing a header, make sure your test boxes line up with your
> columns!
> Thanks,
> Aaron
> "Aaron Berquist" <aberquis@.opentext.com> wrote in message
> news:erwtoi$bEHA.2408@.tk2msftngp13.phx.gbl...
>> Any progress on this issue?
>> "Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
>> news:eU3AjxCaEHA.4052@.TK2MSFTNGP10.phx.gbl...
>> > Could you send me the excel file generated for a smaller number rows,
>> > please?
>> >
>> > --
>> > Nico Cristache [MSFT]
>> > Microsoft SQL Server Reporting Services
>> >
>> > This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> >
>> >
>> > "Aaron Berquist" <aberquis@.opentext.com> wrote in message
>> > news:e0DFZ2BaEHA.3404@.TK2MSFTNGP11.phx.gbl...
>> > > I've got an issue I cannot seem to address. We've upgraded to SP1,
>> > > and
> I
>> > > still am not able to open Excel files exported from Reporting
>> > > Services
>> if
>> > > they contain more than a few hundred rows.
>> > >
>> > > For example, I have a report which contains 1453 records. Exporting
>> > > to
>> an
>> > > Excel takes a few minutes, then generates a 7 MB file. Attempting to
>> open
>> > > that file in Excel 2003 pegs my CPU utlilzation at 100%, and Excel
>> > steadily
>> > > consumes 8K of RAM per second, yet nothing happens. Excel will either
>> > > display as "Not Responding" in Task Manager, or will display
>> > > "Running"
>> and
>> > I
>> > > cannot switch back to Excel.
>> > >
>> > > I can export to CSV and Excel will open it with no issues. However,
>> > > exporting to Excel is critical to our business' use of Reporting
>> Services.
>> > >
>> > > Please advise if anyone has a workaround or is encountering this
> issue.
>> > > --
>> > > Aaron Berquist
>> > > Business Systems Analyst, Information Services
>> > > Open Text Corporation
>> > >
>> > >
>> >
>> >
>>
>

Excel drillthrough to OLAP error

I am using the MSDN workaround Drillthrough VBA code to enable a drillthough from an Excel pivot table to my OLAP cube. It works fine on 2 PCs in my area. However on a 3rd PC,I get the following error:

"The OLAP provider does not support custom grouping. Custom groups were previously defined for this PivotTable Report.
Do you wish to retain the custom group definitions for the future?....."

All 3 PCs are using Excel 2003. All have data access components v2.8 installed.
We are using Analysis Services on SQL Server 2000 Enterprise Edition.

I have been tearing my hair out trying to find out the differences between the PCs....

Any help would be appreciated.

Data access components probably has nothing to do with the situation. One idea is to check service pack version of the Office installed. Make sure it is the same version.
Also try and run Office setup through add/remove programs and repair Office installation.

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

sql

Excel drillthrough to OLAP error

I am using the MSDN workaround Drillthrough VBA code to enable a drillthough from an Excel pivot table to my OLAP cube. It works fine on 2 PCs in my area. However on a 3rd PC,I get the following error:

"The OLAP provider does not support custom grouping. Custom groups were previously defined for this PivotTable Report.
Do you wish to retain the custom group definitions for the future?....."

All 3 PCs are using Excel 2003. All have data access components v2.8 installed.
We are using Analysis Services on SQL Server 2000 Enterprise Edition.

I have been tearing my hair out trying to find out the differences between the PCs....

Any help would be appreciated.

Data access components probably has nothing to do with the situation. One idea is to check service pack version of the Office installed. Make sure it is the same version.
Also try and run Office setup through add/remove programs and repair Office installation.

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

Excel Destination Number Conversion Error

In my SSIS project I am populating an Excel spreadsheet with several worksheets. When I define a worksheet in Excel Destination Editor, I use Create Table SQL statement to create a worksheet. Most of the fields are defined as numbers, however when job runs my numeric fields appear as text in Excel with yellow warning sign - 'The number in this cell is formatted as text or preceeded by an apostrophe'. I need my numbers to appear as numbers. Is there a workaround for this?

Are you specifying Import Mode by using IMEX=1 in your connection string? This forces all data to text.

The Excel driver does store what it believes to be text data preceded by the apostrophe, to force the Excel application in turn to recognize it without question as text values. This implies to me that the columns in question contain at least some values that the driver is interpreting as text.

The BOL topic on the Excel Source contains some information that explains some of the behaviors of the Jet Excel driver.

-Doug

|||

Doug,

Thanks a lot for advice. I tried to put IMEX=0 (2) into a connection string, but it did not solve the problem. I wonder what the default is if IMEX parameter is not specified?

Sergey

|||

Sergey,

I was not encouraging the use of Import Mode...on the contrary, this forces everything to text and would presumably add the opening apostrophe everywhere.

You may want to try allowing the Import and Export Wizard to build your "base" package and add necessary Data Conversion tasks etc. for you. Then you can customize the package afterward.

Be careful to check that the driver is recognizing your numeric columns as numeric, by examining the data types of columns in the various components.

-Doug

Excel destination fails

Hi All!

I am trying to create a table destination to excel which fails with the following error that " Table cannot be created " System Error in Field definition" where as the same works in the Falt File Destination. What could be the problem?

Thanks

jas

It maybe due to the fact that excel has in built data types and column sizes attached to the columns whereas a flat file does not have that.

Try creating a table first and then map the excel columns to it to extract the records from the excel spreadsheet. When you import into a staging table, it is best to just keep the columns as varchar(255). If you have fields like remarks field that could be even bigger, you might want to customize your staging table.sql

Excel DBCC Error

Forgive me for cross-posting but this involves ODBC as well.
I'm getting "User 'Test1' does not have permission to run DBCC TRACEON."
when trying to connect Excel to SQL Server 2005 using an ODBC System
DSN. I've discovered that this is due to Excel or, more appropriately
MSQuery, attempting to set traceflag 208 having to do with quoted names.
What I haven't found out is how to correct this without granting
SysAdmin privs to users needing to run those queries.
Is there a way to do this without those privs?
Thanx,
GarthI have the exact same problem. Did you find how to correct this without
granting sysadmin privs?
--
Thanks in advance.
Ade
"Garth Keesler" wrote:

> Forgive me for cross-posting but this involves ODBC as well.
> I'm getting "User 'Test1' does not have permission to run DBCC TRACEON."
> when trying to connect Excel to SQL Server 2005 using an ODBC System
> DSN. I've discovered that this is due to Excel or, more appropriately
> MSQuery, attempting to set traceflag 208 having to do with quoted names.
> What I haven't found out is how to correct this without granting
> SysAdmin privs to users needing to run those queries.
> Is there a way to do this without those privs?
> Thanx,
> Garth
>
>

Excel DBCC Error

Forgive me for cross-posting but this involves ODBC as well.
I'm getting "User 'Test1' does not have permission to run DBCC TRACEON."
when trying to connect Excel to SQL Server 2005 using an ODBC System
DSN. I've discovered that this is due to Excel or, more appropriately
MSQuery, attempting to set traceflag 208 having to do with quoted names.
What I haven't found out is how to correct this without granting
SysAdmin privs to users needing to run those queries.
Is there a way to do this without those privs?
Thanx,
Garth
I have the exact same problem. Did you find how to correct this without
granting sysadmin privs?
Thanks in advance.
Ade
"Garth Keesler" wrote:

> Forgive me for cross-posting but this involves ODBC as well.
> I'm getting "User 'Test1' does not have permission to run DBCC TRACEON."
> when trying to connect Excel to SQL Server 2005 using an ODBC System
> DSN. I've discovered that this is due to Excel or, more appropriately
> MSQuery, attempting to set traceflag 208 having to do with quoted names.
> What I haven't found out is how to correct this without granting
> SysAdmin privs to users needing to run those queries.
> Is there a way to do this without those privs?
> Thanx,
> Garth
>
>

Excel Data Mining Add-In Query Wizard Error Message

Hello Experts,

I have an error message I was wondering if anyone else has seen and resolved. When I open excel and click on the query wizard on the Data Mining Ribbon, I get an error message that says "Input String not in Correct Format"

After I close this dialog box the 'Welcome to Query .... ' wizard will open, but when I select the Advance button my process aborts due to the input string error.

I have only ran association models on the computer.

The Manage Models and Browse buttons work fine.

The error came up on Friday, but it wasn't there on Thursday. There were no changes done to the machine (no installation of new software).

I have tried reinstalling the add-in.

Thank you all for your help,

Davy

I have pasted the SQL syntax below that I get from the error dialog box after the query wizard closes.

See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.SqlServer.DataMining.Office.Excel.QueryBuilder.QueryBuilderParameters.UpdateNestedColumnName()
at Microsoft.SqlServer.DataMining.Office.Excel.XLClientUIManager.DisplayAdvancedQueryBuilder(Object sender, WizardPageEventArgs e)
at Microsoft.SqlServer.DataMining.Office.Excel.Wizard.WizardPageBase.OnWizardPageAdvanced(WizardPageEventArgs e)
at Microsoft.SqlServer.DataMining.Office.Excel.Wizard.WizardForm.btnAdvanced_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************
mscorlib
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
-
Microsoft.SqlServer.DataMining.Office.Excel.DMClient
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3154.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.DataMining.Office.Excel.DMClient/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.DataMining.Office.Excel.DMClient.dll
-
Extensibility
Assembly Version: 7.0.3300.0
Win32 Version: 7.00.9466
CodeBase: file:///C:/WINDOWS/assembly/GAC/Extensibility/7.0.3300.0__b03f5f7f11d50a3a/Extensibility.dll
-
office
Assembly Version: 12.0.0.0
Win32 Version: 12.0.4518.1014
CodeBase: file:///C:/WINDOWS/assembly/GAC/office/12.0.0.0__71e9bce111e9429c/office.dll
-
Microsoft.SqlServer.DataMining.Office.Common
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3154.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.DataMining.Office.Common/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.DataMining.Office.Common.dll
-
System.Windows.Forms
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
-
System
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
-
System.Drawing
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
-
Microsoft.SqlServer.DataMining.Office.Excel.DMClientControls
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3154.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.DataMining.Office.Excel.DMClientControls/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.DataMining.Office.Excel.DMClientControls.dll
-
System.Xml
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
-
System.Configuration
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
-
zd8aixcw
Assembly Version: 9.0.242.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
-
stdole
Assembly Version: 7.0.3300.0
Win32 Version: 7.00.9466
CodeBase: file:///C:/WINDOWS/assembly/GAC/stdole/7.0.3300.0__b03f5f7f11d50a3a/stdole.dll
-
Microsoft.Office.Interop.Excel
Assembly Version: 12.0.0.0
Win32 Version: 12.0.4518.1014
CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.Office.Interop.Excel/12.0.0.0__71e9bce111e9429c/Microsoft.Office.Interop.Excel.dll
-
Microsoft.SqlServer.DataMining.Office.Excel.TableAnalytics
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3154.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.DataMining.Office.Excel.TableAnalytics/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.DataMining.Office.Excel.TableAnalytics.dll
-
Microsoft.SqlServer.DataMining.Office.Excel.Wizard
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3154.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.DataMining.Office.Excel.Wizard/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.DataMining.Office.Excel.Wizard.dll
-
Microsoft.AnalysisServices.AdomdClient
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.AnalysisServices.AdomdClient/9.0.242.0__89845dcd8080cc91/Microsoft.AnalysisServices.AdomdClient.dll
-
System.Data
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
-
System.Web
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Web/2.0.0.0__b03f5f7f11d50a3a/System.Web.dll
-
Microsoft.mshtml
Assembly Version: 7.0.3300.0
Win32 Version: 7.0.3300.0
CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.mshtml/7.0.3300.0__b03f5f7f11d50a3a/Microsoft.mshtml.dll
-
Microsoft.DataWarehouse.Interfaces
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.DataWarehouse.Interfaces/9.0.242.0__89845dcd8080cc91/Microsoft.DataWarehouse.Interfaces.dll
-
Microsoft.AnalysisServices.Viewers
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20DM%20Add-Ins/Microsoft.AnalysisServices.Viewers.dll
-
Microsoft.DataWarehouse
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20DM%20Add-Ins/Microsoft.DataWarehouse.DLL
-
Microsoft.AnalysisServices.Graphing
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20DM%20Add-Ins/Microsoft.AnalysisServices.Graphing.DLL
-
Microsoft.AnalysisServices.Controls
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20DM%20Add-Ins/Microsoft.AnalysisServices.Controls.DLL
-
Microsoft.SqlServer.CustomControls
Assembly Version: 9.0.242.0
Win32 Version: 9.00.2047.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.CustomControls/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.CustomControls.dll
-
Microsoft.NetEnterpriseServers.ExceptionMessageBox
Assembly Version: 9.0.242.0
Win32 Version: 9.00.1399.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.NetEnterpriseServers.ExceptionMessageBox/9.0.242.0__89845dcd8080cc91/Microsoft.NetEnterpriseServers.ExceptionMessageBox.dll
-
Microsoft.AnalysisServices.OleDbDM
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20DM%20Add-Ins/Microsoft.AnalysisServices.OleDbDM.DLL
-
CustomMarshalers
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/CustomMarshalers/2.0.0.0__b03f5f7f11d50a3a/CustomMarshalers.dll
-

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
<system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

Can you try creating another non-association model (e.g. classification) and see if this problem goes away? It won't solve the real problem, but it will give us more insight.

Thanks

-Jamie

|||

Hello, Davy

You said:"I have pasted the SQL syntax below that I get from the error dialog box after the query wizard closes.", but I cannot see the SQL statement in the post. Sorry if I just missed it, do you mind posting it again?

On another note:

- is there any model visible in your current connection?

- what kind of model is selected by default when launching the query editor?

|||

Sorry about the confusion, but there is no SQL syntax...only what i pasted above.

I tried creating a model with another algorithm, but that does not change anything.

There are models visible with the current connection.

The model selected by default depends on which model i clicked on last in the manage wizard.

I believe this problem stems from the .Net Framework since the error dialog says:

Microsoft .NET Framework

Unhandled exception has occurred in a component in you application. If you click Continue, the applicaiton will ignore this error and attempt to continue.

Object reference not set to an instance of an object.

Details Continue

I currently have .NET 2.0 and .NET 1.1 installed on the computer. According to Add/Remove Program in the Control Panel, the last time .NET 2.0 was used was sometime last year. The Add/Remove Program has an option to repair .NET 2.0 . Should I use this?

Excel Browser error

Hi

I'm trying to browse a cube using excel. I get through the pivot table wizrd ok but when I click finish I am getting the following error:

XML for Analysis parser: The localeidentifier property is not overwritable and cannot be assigned a new value

I get the message regardless of whether I am trying to connect to my own cubes or the Adventure Works sample. Any idea of cause & remedy please

Thanks

Wayne

A couple of things you may want to try.

This error seems to occur if local language is anything other than English-US

a) Refer to post http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=961463&SiteID=17. This relates to CTP2, but there are a couple of tips that may be of use.

b) Had a similar issue, & narrowed down to a conflict between AS_OLEDB Version 8 & 9 installed on same PC. Seems the upgrade to 9 doesn't clean up the old version. To resolve this, run uninstall of the existing oledb driver, go to C:\Program Files\Common Files\System\Ole DB & delete all files in this directory if they exist, re-install AS_OLEDB 9.0.

P.S. Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider (OLEDB 9.0) available at www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc

c) Check analysis server default language.

i.e. SQL Management studio, connect to Analysis Server, Properties (right click on server), Language/Collation, set to US English.

Excel Browser error

Hi

I'm trying to browse a cube using excel. I get through the pivot table wizrd ok but when I click finish I am getting the following error:

XML for Analysis parser: The localeidentifier property is not overwritable and cannot be assigned a new value

I get the message regardless of whether I am trying to connect to my own cubes or the Adventure Works sample. Any idea of cause & remedy please

Thanks

Wayne

A couple of things you may want to try.

This error seems to occur if local language is anything other than English-US

a) Refer to post http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=961463&SiteID=17. This relates to CTP2, but there are a couple of tips that may be of use.

b) Had a similar issue, & narrowed down to a conflict between AS_OLEDB Version 8 & 9 installed on same PC. Seems the upgrade to 9 doesn't clean up the old version. To resolve this, run uninstall of the existing oledb driver, go to C:\Program Files\Common Files\System\Ole DB & delete all files in this directory if they exist, re-install AS_OLEDB 9.0.

P.S. Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider (OLEDB 9.0) available at www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc

c) Check analysis server default language.

i.e. SQL Management studio, connect to Analysis Server, Properties (right click on server), Language/Collation, set to US English.

Excel automation

Hi,

I tried to add column headers and delete all rows to excel database
which is a linked server to my sql server db.

I got an error message, "delete data through linked server is not
supported by Isam".

I tried to use FSO to write to the excel file, but seems like FSO
support write to only text file.

the last time I tried to create an excel applicaiton object and temper
with its cell, I hang my Excel application and had to reinstall it.

Have any ideas,

Thank you very much"dlakme" <dthuyuyen@.yahoo.com> wrote in message
news:7bdc161c.0408030437.3ce04385@.posting.google.c om...
> Hi,
> I tried to add column headers and delete all rows to excel database
> which is a linked server to my sql server db.
> I got an error message, "delete data through linked server is not
> supported by Isam".
> I tried to use FSO to write to the excel file, but seems like FSO
> support write to only text file.
> the last time I tried to create an excel applicaiton object and temper
> with its cell, I hang my Excel application and had to reinstall it.
> Have any ideas,
> Thank you very much

I have no idea about your specific error, but it may be easier to use a
client-side tool to pull the data from MSSQL, then use the Excel COM
interface to put that into Excel as you want.

Simonsql

Excel 2007 import error

I've been working in Excel 2007, importing data from SSAS and I started getting this error when I try to connect to SSAS:

Data could not be retrieved from the database.

Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again.

I realize this isn't the Excel help board but was wondering if anyone else is seeing this? In regards to the message, I know that the SSAS is online and available - I can connect and browse the cube via Managaement Studio. This problem started happening after I processed the cube. I've stopped/started the SSAS service, closed/re-opened Excel and rebooted the SSAS machine and Excel still keeps giving that error.

Any help would be much appreciated.

When do you get the error? When creating a new connection in Excel? Or when opening an existing workbook?

It looks like the SSAS OLEDB provider might not be installed correctly. Try installing it (ASOLEDB9.msi) from http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

|||That download didn't fix the problem. I could use Excel 2007 to connect to SSAS just fine. I made some changes to the cube and re-processed it, THEN Excel 2007 stopped working. Previous connections in the workbook (to that re-processed cube) stopped working and I could not create new connections (to that re-processed cube). Any ideas welcome...|||Still having the problem, however I tried something on a whim and wanted to post it here. I had a hunch that the KPIs in the cube may be causing the problems so I removed them and re-processed the cube. Tried to connect with Excel 2007 and it worked. Added the KPIs back and I get the "cannot connect" error message I originally posted. Seems like an issue to me.sql

Excel 2007 import error

I've been working in Excel 2007, importing data from SSAS and I started getting this error when I try to connect to SSAS:

Data could not be retrieved from the database.

Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again.

I realize this isn't the Excel help board but was wondering if anyone else is seeing this? In regards to the message, I know that the SSAS is online and available - I can connect and browse the cube via Managaement Studio. This problem started happening after I processed the cube. I've stopped/started the SSAS service, closed/re-opened Excel and rebooted the SSAS machine and Excel still keeps giving that error.

Any help would be much appreciated.

When do you get the error? When creating a new connection in Excel? Or when opening an existing workbook?

It looks like the SSAS OLEDB provider might not be installed correctly. Try installing it (ASOLEDB9.msi) from http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

|||That download didn't fix the problem. I could use Excel 2007 to connect to SSAS just fine. I made some changes to the cube and re-processed it, THEN Excel 2007 stopped working. Previous connections in the workbook (to that re-processed cube) stopped working and I could not create new connections (to that re-processed cube). Any ideas welcome...|||Still having the problem, however I tried something on a whim and wanted to post it here. I had a hunch that the KPIs in the cube may be causing the problems so I removed them and re-processed the cube. Tried to connect with Excel 2007 and it worked. Added the KPIs back and I get the "cannot connect" error message I originally posted. Seems like an issue to me.

Excel 2007 AcquireConnection error

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 \\lorip\MandEScorecards\SQLQueryExample.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?

Thanks in advance!

This error code is sometimes related to security issues. How are you running the package?|||

Is this a 64-Bit Server?

If is, there is an Article that show that SQL Server 2005 in 64-Bits don't give support to any OLE DB Transactions especially Excel.

sql

Excel 2007 AcquireConnection error

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 \\lorip\MandEScorecards\SQLQueryExample.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?

Thanks in advance!

This error code is sometimes related to security issues. How are you running the package?|||

Is this a 64-Bit Server?

If is, there is an Article that show that SQL Server 2005 in 64-Bits don't give support to any OLE DB Transactions especially Excel.

EXCEL / SQL SERVER HELP - URGENT

I got a stored procedure with 1 parameter and I want to call this stored
procedure inside an Excel Query ,
I tried
.dbo.MySP ?
It return with error message
"Parameters are not allowed in queries that can't be displayed graphically"
The purpose for this approach -
This would allow me to anchor my parameter to certain cells on the
spreadsheet.
My report users could then change the values in those cells and click a
button to refresh the query data.
Thanks
MikeAs long as you are going to use a button, you might try using code like ADO
(using a visual basic control button that is - get from View/Toolbars):
Sub GetData()
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
Dim DateVar As Date
DateVar = Sheets("Sheet1").Range("A1")
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=yourServer;" _
& "Initial Catalog=yourDB;UID=SA;PWD=Tiger;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_yourSP"
cmd.Parameters("@.bDate").Value = DateVar
Set RS = cmd.Execute
Sheets("Sheet1").Range("A2").CopyFromRecordset RS
End Sub
The user enters a date value Cell("A1") and then clicks the button. Boom!
starting at Cell("A2") you have your resultset.
HTH
Rich
"MS User" wrote:

> I got a stored procedure with 1 parameter and I want to call this stored
> procedure inside an Excel Query ,
> I tried
> ..dbo.MySP ?
> It return with error message
> "Parameters are not allowed in queries that can't be displayed graphically
"
> The purpose for this approach -
> This would allow me to anchor my parameter to certain cells on the
> spreadsheet.
> My report users could then change the values in those cells and click a
> button to refresh the query data.
> Thanks
> Mike
>
>
>

EXCEL / SQL SERVER HELP - URGENT

I got a stored procedure with 1 parameter and I want to call this stored
procedure inside an Excel Query ,
I tried
..dbo.MySP ?
It return with error message
"Parameters are not allowed in queries that can't be displayed graphically"
The purpose for this approach -
This would allow me to anchor my parameter to certain cells on the
spreadsheet.
My report users could then change the values in those cells and click a
button to refresh the query data.
Thanks
Mike
As long as you are going to use a button, you might try using code like ADO
(using a visual basic control button that is - get from View/Toolbars):
Sub GetData()
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
Dim DateVar As Date
DateVar = Sheets("Sheet1").Range("A1")
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=yourServer;" _
& "Initial Catalog=yourDB;UID=SA;PWD=Tiger;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_yourSP"
cmd.Parameters("@.bDate").Value = DateVar
Set RS = cmd.Execute
Sheets("Sheet1").Range("A2").CopyFromRecordset RS
End Sub
The user enters a date value Cell("A1") and then clicks the button. Boom!
starting at Cell("A2") you have your resultset.
HTH
Rich
"MS User" wrote:

> I got a stored procedure with 1 parameter and I want to call this stored
> procedure inside an Excel Query ,
> I tried
> ..dbo.MySP ?
> It return with error message
> "Parameters are not allowed in queries that can't be displayed graphically"
> The purpose for this approach -
> This would allow me to anchor my parameter to certain cells on the
> spreadsheet.
> My report users could then change the values in those cells and click a
> button to refresh the query data.
> Thanks
> Mike
>
>
>