Report against oracle with multiple statements

hello developers!!!!!

How I have to do a Report against oracle with multiple statements?

I need execute multiple SQL statements in the generic query designer with an oracle connection.

thanks

Maybe I'm missing something, but couldn't you just define multiple datasets in the report with one query per dataset?

If the queries have interdependencies, you could look into moving them into a stored procedure and calling the stored procedure from SSRS. You should find several threads on the regular SSRS forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1) discussing how to call Oracle stored procedures from SSRS.

-- Robert

|||

Robert -- slightly off topic but do you have any idea why the Query Designer would be really slow loading tables from Oracle?

There are two threads on this subject:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324176&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019689&SiteID=1

...maybe this deserved looking at for a fix in Katmai if it's not already done... is there an issue with multiple connections for each table that is being added or something?

Thanks in advance,

>L<

|||The query designer is actually a VS component, not an RS component. It might use the OLE DB provider but I'm not 100% certain.

|||

Thanks Brian. I might have known that the query designer was a shared component, but I haven't heard of anybody complaining about its speed except in this (report design) context.

As far as whether the query designer uses OLE DB or not... it can, and does, and when it does it is apparently fast -- against the same data.

It's just when these guys are accessing Oracle and their specified connection to this data source is *not* an OLE DB connection that they see the problem. (10-15 minutes to add a table in the designer ?!? Yuck...) I was thinking maybe the designer was trying to make a separate connection every time they pressed that "Add" button.

>L<

|||

Well, I know this won't really resolve the issue, but the shared VS query designer component is actually only supported for a particular set of OLEDB providers, as described in the following KB article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;811241

? Microsoft OLE DB Provider for SQL Server
? Microsoft Jet 4.0 OLE DB Provider
? Microsoft OLE DB Provider for Oracle

It is not supported for any other providers.

-- Robert

|||

Thanks for going the extra mile, Robert <s>. That totally makes sense. I will refer them to this post and stop worrying that the query designer has a bug <g>.

>L<

Report against oracle with multiple statements

hello developers!!!!!

How I have to do a Report against oracle with multiple statements?

I need execute multiple SQL statements in the generic query designer with an oracle connection.

thanks

Maybe I'm missing something, but couldn't you just define multiple datasets in the report with one query per dataset?

If the queries have interdependencies, you could look into moving them into a stored procedure and calling the stored procedure from SSRS. You should find several threads on the regular SSRS forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1) discussing how to call Oracle stored procedures from SSRS.

-- Robert

|||

Robert -- slightly off topic but do you have any idea why the Query Designer would be really slow loading tables from Oracle?

There are two threads on this subject:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324176&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019689&SiteID=1

...maybe this deserved looking at for a fix in Katmai if it's not already done... is there an issue with multiple connections for each table that is being added or something?

Thanks in advance,

>L<

|||The query designer is actually a VS component, not an RS component. It might use the OLE DB provider but I'm not 100% certain.

|||

Thanks Brian. I might have known that the query designer was a shared component, but I haven't heard of anybody complaining about its speed except in this (report design) context.

As far as whether the query designer uses OLE DB or not... it can, and does, and when it does it is apparently fast -- against the same data.

It's just when these guys are accessing Oracle and their specified connection to this data source is *not* an OLE DB connection that they see the problem. (10-15 minutes to add a table in the designer ?!? Yuck...) I was thinking maybe the designer was trying to make a separate connection every time they pressed that "Add" button.

>L<

|||

Well, I know this won't really resolve the issue, but the shared VS query designer component is actually only supported for a particular set of OLEDB providers, as described in the following KB article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;811241

? Microsoft OLE DB Provider for SQL Server
? Microsoft Jet 4.0 OLE DB Provider
? Microsoft OLE DB Provider for Oracle

It is not supported for any other providers.

-- Robert

|||

Thanks for going the extra mile, Robert <s>. That totally makes sense. I will refer them to this post and stop worrying that the query designer has a bug <g>.

>L<

Report against oracle with multiple statements

hello developers!!!!!

How I have to do a Report against oracle with multiple statements?

I need execute multiple SQL statements in the generic query designer with an oracle connection.

thanks

Maybe I'm missing something, but couldn't you just define multiple datasets in the report with one query per dataset?

If the queries have interdependencies, you could look into moving them into a stored procedure and calling the stored procedure from SSRS. You should find several threads on the regular SSRS forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1) discussing how to call Oracle stored procedures from SSRS.

-- Robert

|||

Robert -- slightly off topic but do you have any idea why the Query Designer would be really slow loading tables from Oracle?

There are two threads on this subject:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324176&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019689&SiteID=1

...maybe this deserved looking at for a fix in Katmai if it's not already done... is there an issue with multiple connections for each table that is being added or something?

Thanks in advance,

>L<

|||The query designer is actually a VS component, not an RS component. It might use the OLE DB provider but I'm not 100% certain.

|||

Thanks Brian. I might have known that the query designer was a shared component, but I haven't heard of anybody complaining about its speed except in this (report design) context.

As far as whether the query designer uses OLE DB or not... it can, and does, and when it does it is apparently fast -- against the same data.

It's just when these guys are accessing Oracle and their specified connection to this data source is *not* an OLE DB connection that they see the problem. (10-15 minutes to add a table in the designer ?!? Yuck...) I was thinking maybe the designer was trying to make a separate connection every time they pressed that "Add" button.

>L<

|||

Well, I know this won't really resolve the issue, but the shared VS query designer component is actually only supported for a particular set of OLEDB providers, as described in the following KB article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;811241

? Microsoft OLE DB Provider for SQL Server
? Microsoft Jet 4.0 OLE DB Provider
? Microsoft OLE DB Provider for Oracle

It is not supported for any other providers.

-- Robert

|||

Thanks for going the extra mile, Robert <s>. That totally makes sense. I will refer them to this post and stop worrying that the query designer has a bug <g>.

>L<

Report against oracle with multiple statements

hello developers!!!!!

How I have to do a Report against oracle with multiple statements?

I need execute multiple SQL statements in the generic query designer with an oracle connection.

thanks

Maybe I'm missing something, but couldn't you just define multiple datasets in the report with one query per dataset?

If the queries have interdependencies, you could look into moving them into a stored procedure and calling the stored procedure from SSRS. You should find several threads on the regular SSRS forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1) discussing how to call Oracle stored procedures from SSRS.

-- Robert

|||

Robert -- slightly off topic but do you have any idea why the Query Designer would be really slow loading tables from Oracle?

There are two threads on this subject:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324176&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019689&SiteID=1

...maybe this deserved looking at for a fix in Katmai if it's not already done... is there an issue with multiple connections for each table that is being added or something?

Thanks in advance,

>L<

|||The query designer is actually a VS component, not an RS component. It might use the OLE DB provider but I'm not 100% certain.

|||

Thanks Brian. I might have known that the query designer was a shared component, but I haven't heard of anybody complaining about its speed except in this (report design) context.

As far as whether the query designer uses OLE DB or not... it can, and does, and when it does it is apparently fast -- against the same data.

It's just when these guys are accessing Oracle and their specified connection to this data source is *not* an OLE DB connection that they see the problem. (10-15 minutes to add a table in the designer ?!? Yuck...) I was thinking maybe the designer was trying to make a separate connection every time they pressed that "Add" button.

>L<

|||

Well, I know this won't really resolve the issue, but the shared VS query designer component is actually only supported for a particular set of OLEDB providers, as described in the following KB article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;811241

? Microsoft OLE DB Provider for SQL Server
? Microsoft Jet 4.0 OLE DB Provider
? Microsoft OLE DB Provider for Oracle

It is not supported for any other providers.

-- Robert

|||

Thanks for going the extra mile, Robert <s>. That totally makes sense. I will refer them to this post and stop worrying that the query designer has a bug <g>.

>L<

Replication: Adding a column

Hi,
I have a table that is being replicated but one the the developers added a
column to the table WITHOUT using sp_repladdcolumn. Now, they are wondering
why this column is not on the subscriber. What is the easiest way to solve
this problem? (I am thinking of changing the subscription and adding the
column to the subscription and doing a snapshot but I hate to do this as it
takes a while on a 15 GB database).
Any fast way of solving this WITHOUT removing the column on the publisher?
--
I saw it work in a cartoon once so I am pretty sure I can do it.I would sp_dropScubscription/ sp_droparticle/
sp_addarticle/ start the snapshot agent.
Make sure that the sp_dropsubscription specifies that
article only. Do this first in Dev.
>--Original Message--
>Hi,
>I have a table that is being replicated but one the the
developers added a
>column to the table WITHOUT using sp_repladdcolumn. Now,
they are wondering
>why this column is not on the subscriber. What is the
easiest way to solve
>this problem? (I am thinking of changing the
subscription and adding the
>column to the subscription and doing a snapshot but I
hate to do this as it
>takes a while on a 15 GB database).
>Any fast way of solving this WITHOUT removing the
column on the publisher?
>--
>I saw it work in a cartoon once so I am pretty sure I
can do it.
>.
>