one matrix above another doesn't export properly to Excel

A newb question: I've got a report with a table leftmost in the body,
with a matrix immediately to the right of the table. Immediately
below the table are 2 textboxes displaying totals from the table.
Immediately below the first matrix is another matrix displaying totals
from the first matrix:
Table Matrix1
Textboxes Matrix2
All is well in preview, HTML view, and PDF export.
After Excel export, the sheet is arranged thus:
Table Matrix1
Matrix2 Textboxes
I've tried putting the 2 matrices in a rectangle, and even inside
nested rectangles, with no effect on the incorrect Excel layout.
The report is designed this way because the users want empty space
between the detail data (in the Table and Matrix1) and the totals.
The number of columns in Matrix 1 is dynamic and so I need Matrix2 to
expand in step with Matrix1.
I think. Help? RDL available if anybody wants it.
Thanks
MatrixLoverI am running into similar problem. I have one matrix over another and report
logic hide / unhide matrix. Every work like charm in reports, export to pdf,
but having trouble in excel export. When export to excel, the hidden matrix
also appears just below the active matrix. Any help appreciated.
"MatrixLover" wrote:
> A newb question: I've got a report with a table leftmost in the body,
> with a matrix immediately to the right of the table. Immediately
> below the table are 2 textboxes displaying totals from the table.
> Immediately below the first matrix is another matrix displaying totals
> from the first matrix:
> Table Matrix1
> Textboxes Matrix2
> All is well in preview, HTML view, and PDF export.
> After Excel export, the sheet is arranged thus:
> Table Matrix1
> Matrix2 Textboxes
> I've tried putting the 2 matrices in a rectangle, and even inside
> nested rectangles, with no effect on the incorrect Excel layout.
> The report is designed this way because the users want empty space
> between the detail data (in the Table and Matrix1) and the totals.
> The number of columns in Matrix 1 is dynamic and so I need Matrix2 to
> expand in step with Matrix1.
> I think. Help? RDL available if anybody wants it.
> Thanks
> MatrixLover
>

One failed subscription prevents running all others

Hello,
I was trying to invertigate why a certain report does not run properly in
some cases. I scheduled it to run daily. So far so good, i got logfiles to
study. However, since this try RS no longer executes ANY scheduled
subscription. i made no changes to the system, just scheduled a defective
report. (which BTW was in Excel and the row-count exceded 65536). Does
anybode know how to edit the (hypothetical) queue used by RS to clear the way
for my other subscriptions.
extra info: RSExecutionlog DB has no entries of executed subscriptions. The
SQL job manager says all jobs have executed succesfully as planned.(including
the subscriptions)Are there any rows in either the event table or notifications table?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
news:175AE411-3994-4C8E-993F-8D4D258219B8@.microsoft.com...
> Hello,
> I was trying to invertigate why a certain report does not run properly in
> some cases. I scheduled it to run daily. So far so good, i got logfiles
> to
> study. However, since this try RS no longer executes ANY scheduled
> subscription. i made no changes to the system, just scheduled a defective
> report. (which BTW was in Excel and the row-count exceded 65536). Does
> anybode know how to edit the (hypothetical) queue used by RS to clear the
> way
> for my other subscriptions.
> extra info: RSExecutionlog DB has no entries of executed subscriptions.
> The
> SQL job manager says all jobs have executed succesfully as
> planned.(including
> the subscriptions)
>

OLE DB & SQL

Does anyone know why SQL doesn't work properly in my OLE DB component?

Underlying DB: DB2

Provider: IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider

If I insert some SQL I get get a preview of the data but it errors when I run it.

[OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E00.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

I can set the data access mode to 'table or view/OpenRowset' and that works fine.

:(

Please help....

I also faced the same problem,

Then instead of Oledb Src adapter I configured Data Reader Source.

For the connection manager I created a ADO.Net Connection manager

Create a DSN, used a ADO.Net Provider for ODBC this will create a ADO.Net Connection Manager.

Now you can Configure Data Reader Source...Workaround ...:)

Thanks

Dharmbir

|||

Awesome! That works. Many thanks Dharmbir.

|||

I also used your solution! but you dont have to forget to change the value of the Connect Time out wich standard value ist 0.

|||HI,

I used this option ( ADO.NET - ODBC combination ) but i guess ODBC does not support substring. I need to have substring of a particular column as criteria to extract data from AS400(source) and insert that into the DB2/SQL server(destination) and that's where i'm stuck.

my query looks like this:

"SELECT * FROM itcga.xxxxx where Substr(itgdta.xxxxx.GENLCDE,9,2) = '61'"

Contrast to this if i use the OLEDB source, i get the following error message:

"Error: 0xC0047021 at XXXXXX, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039."

Please Help on getting data form as400 with the where substring condition.

Thanks in advance.
Amit S|||

Another possibility is to setup the DB2 database as a linked database in SQL Server 2005, create views on that linked database within a SQL Server 2005 database, and finally create an OLE DB source in Integration Services using the SQL Server 2005 database and linked views.

|||

Amitshah,

ODBC is just a set of API's that connect to the underlying Database.

SISS does not do explicit conversions, so most often "varchar" columns need to be converted.

In the case above, it appears that two explict conversions are being done on the data before it lands in the source DB.

once to ADO.NET and again to ODBC?

I cannot tell what your destination DB is... however, SQL Server DB does not recognize the substr() function. Try Substring()

The Microsoft Whitepaper on the front of the Forum does a great job of explaining these concepts.

|||

This works for me too. But it doesn't solve my problem. If you use Data Reader Source it is not possible to set the select-statement dynamically from a local variable. This is possible with the Oledb Src.

What should I do. Any idea ?

OLE DB & SQL

Does anyone know why SQL doesn't work properly in my OLE DB component?

Underlying DB: DB2

Provider: IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider

If I insert some SQL I get get a preview of the data but it errors when I run it.

[OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E00.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

I can set the data access mode to 'table or view/OpenRowset' and that works fine.

:(

Please help....

I also faced the same problem,

Then instead of Oledb Src adapter I configured Data Reader Source.

For the connection manager I created a ADO.Net Connection manager

Create a DSN, used a ADO.Net Provider for ODBC this will create a ADO.Net Connection Manager.

Now you can Configure Data Reader Source...Workaround ...:)

Thanks

Dharmbir

|||

Awesome! That works. Many thanks Dharmbir.

|||

I also used your solution! but you dont have to forget to change the value of the Connect Time out wich standard value ist 0.

|||HI,

I used this option ( ADO.NET - ODBC combination ) but i guess ODBC does not support substring. I need to have substring of a particular column as criteria to extract data from AS400(source) and insert that into the DB2/SQL server(destination) and that's where i'm stuck.

my query looks like this:

"SELECT * FROM itcga.xxxxx where Substr(itgdta.xxxxx.GENLCDE,9,2) = '61'"

Contrast to this if i use the OLEDB source, i get the following error message:

"Error: 0xC0047021 at XXXXXX, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039."

Please Help on getting data form as400 with the where substring condition.

Thanks in advance.
Amit S|||

Another possibility is to setup the DB2 database as a linked database in SQL Server 2005, create views on that linked database within a SQL Server 2005 database, and finally create an OLE DB source in Integration Services using the SQL Server 2005 database and linked views.

|||

Amitshah,

ODBC is just a set of API's that connect to the underlying Database.

SISS does not do explicit conversions, so most often "varchar" columns need to be converted.

In the case above, it appears that two explict conversions are being done on the data before it lands in the source DB.

once to ADO.NET and again to ODBC?

I cannot tell what your destination DB is... however, SQL Server DB does not recognize the substr() function. Try Substring()

The Microsoft Whitepaper on the front of the Forum does a great job of explaining these concepts.

|||

This works for me too. But it doesn't solve my problem. If you use Data Reader Source it is not possible to set the select-statement dynamically from a local variable. This is possible with the Oledb Src.

What should I do. Any idea ?

OLE DB & SQL

Does anyone know why SQL doesn't work properly in my OLE DB component?

Underlying DB: DB2

Provider: IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider

If I insert some SQL I get get a preview of the data but it errors when I run it.

[OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E00.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

I can set the data access mode to 'table or view/OpenRowset' and that works fine.

:(

Please help....

I also faced the same problem,

Then instead of Oledb Src adapter I configured Data Reader Source.

For the connection manager I created a ADO.Net Connection manager

Create a DSN, used a ADO.Net Provider for ODBC this will create a ADO.Net Connection Manager.

Now you can Configure Data Reader Source...Workaround ...:)

Thanks

Dharmbir

|||

Awesome! That works. Many thanks Dharmbir.

|||

I also used your solution! but you dont have to forget to change the value of the Connect Time out wich standard value ist 0.

|||HI,

I used this option ( ADO.NET - ODBC combination ) but i guess ODBC does not support substring. I need to have substring of a particular column as criteria to extract data from AS400(source) and insert that into the DB2/SQL server(destination) and that's where i'm stuck.

my query looks like this:

"SELECT * FROM itcga.xxxxx where Substr(itgdta.xxxxx.GENLCDE,9,2) = '61'"

Contrast to this if i use the OLEDB source, i get the following error message:

"Error: 0xC0047021 at XXXXXX, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039."

Please Help on getting data form as400 with the where substring condition.

Thanks in advance.
Amit S|||

Another possibility is to setup the DB2 database as a linked database in SQL Server 2005, create views on that linked database within a SQL Server 2005 database, and finally create an OLE DB source in Integration Services using the SQL Server 2005 database and linked views.

|||

Amitshah,

ODBC is just a set of API's that connect to the underlying Database.

SISS does not do explicit conversions, so most often "varchar" columns need to be converted.

In the case above, it appears that two explict conversions are being done on the data before it lands in the source DB.

once to ADO.NET and again to ODBC?

I cannot tell what your destination DB is... however, SQL Server DB does not recognize the substr() function. Try Substring()

The Microsoft Whitepaper on the front of the Forum does a great job of explaining these concepts.

|||

This works for me too. But it doesn't solve my problem. If you use Data Reader Source it is not possible to set the select-statement dynamically from a local variable. This is possible with the Oledb Src.

What should I do. Any idea ?

OLE DB & SQL

Does anyone know why SQL doesn't work properly in my OLE DB component?

Underlying DB: DB2

Provider: IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider

If I insert some SQL I get get a preview of the data but it errors when I run it.

[OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E00.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

I can set the data access mode to 'table or view/OpenRowset' and that works fine.

:(

Please help....

I also faced the same problem,

Then instead of Oledb Src adapter I configured Data Reader Source.

For the connection manager I created a ADO.Net Connection manager

Create a DSN, used a ADO.Net Provider for ODBC this will create a ADO.Net Connection Manager.

Now you can Configure Data Reader Source...Workaround ...:)

Thanks

Dharmbir

|||

Awesome! That works. Many thanks Dharmbir.

|||

I also used your solution! but you dont have to forget to change the value of the Connect Time out wich standard value ist 0.

|||HI,

I used this option ( ADO.NET - ODBC combination ) but i guess ODBC does not support substring. I need to have substring of a particular column as criteria to extract data from AS400(source) and insert that into the DB2/SQL server(destination) and that's where i'm stuck.

my query looks like this:

"SELECT * FROM itcga.xxxxx where Substr(itgdta.xxxxx.GENLCDE,9,2) = '61'"

Contrast to this if i use the OLEDB source, i get the following error message:

"Error: 0xC0047021 at XXXXXX, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039."

Please Help on getting data form as400 with the where substring condition.

Thanks in advance.
Amit S|||

Another possibility is to setup the DB2 database as a linked database in SQL Server 2005, create views on that linked database within a SQL Server 2005 database, and finally create an OLE DB source in Integration Services using the SQL Server 2005 database and linked views.

|||

Amitshah,

ODBC is just a set of API's that connect to the underlying Database.

SISS does not do explicit conversions, so most often "varchar" columns need to be converted.

In the case above, it appears that two explict conversions are being done on the data before it lands in the source DB.

once to ADO.NET and again to ODBC?

I cannot tell what your destination DB is... however, SQL Server DB does not recognize the substr() function. Try Substring()

The Microsoft Whitepaper on the front of the Forum does a great job of explaining these concepts.

|||

This works for me too. But it doesn't solve my problem. If you use Data Reader Source it is not possible to set the select-statement dynamically from a local variable. This is possible with the Oledb Src.

What should I do. Any idea ?

OLE DB & SQL

Does anyone know why SQL doesn't work properly in my OLE DB component?

Underlying DB: DB2

Provider: IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider

If I insert some SQL I get get a preview of the data but it errors when I run it.

[OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E00.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

I can set the data access mode to 'table or view/OpenRowset' and that works fine.

:(

Please help....

I also faced the same problem,

Then instead of Oledb Src adapter I configured Data Reader Source.

For the connection manager I created a ADO.Net Connection manager

Create a DSN, used a ADO.Net Provider for ODBC this will create a ADO.Net Connection Manager.

Now you can Configure Data Reader Source...Workaround ...:)

Thanks

Dharmbir

|||

Awesome! That works. Many thanks Dharmbir.

|||

I also used your solution! but you dont have to forget to change the value of the Connect Time out wich standard value ist 0.

|||HI,

I used this option ( ADO.NET - ODBC combination ) but i guess ODBC does not support substring. I need to have substring of a particular column as criteria to extract data from AS400(source) and insert that into the DB2/SQL server(destination) and that's where i'm stuck.

my query looks like this:

"SELECT * FROM itcga.xxxxx where Substr(itgdta.xxxxx.GENLCDE,9,2) = '61'"

Contrast to this if i use the OLEDB source, i get the following error message:

"Error: 0xC0047021 at XXXXXX, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039."

Please Help on getting data form as400 with the where substring condition.

Thanks in advance.
Amit S|||

Another possibility is to setup the DB2 database as a linked database in SQL Server 2005, create views on that linked database within a SQL Server 2005 database, and finally create an OLE DB source in Integration Services using the SQL Server 2005 database and linked views.

|||

Amitshah,

ODBC is just a set of API's that connect to the underlying Database.

SISS does not do explicit conversions, so most often "varchar" columns need to be converted.

In the case above, it appears that two explict conversions are being done on the data before it lands in the source DB.

once to ADO.NET and again to ODBC?

I cannot tell what your destination DB is... however, SQL Server DB does not recognize the substr() function. Try Substring()

The Microsoft Whitepaper on the front of the Forum does a great job of explaining these concepts.

|||

This works for me too. But it doesn't solve my problem. If you use Data Reader Source it is not possible to set the select-statement dynamically from a local variable. This is possible with the Oledb Src.

What should I do. Any idea ?