PDF Export taking too long

I am using RS SP2 with SQL 2k SP4.
I have a fairly large report, in the region of a few hundred pages. It has a
couple of parameters that filters the data. I have it linked from a web
application and it goes directly to PDF. If the parameters are set the
report renders to PDF pretty quickly, the size runs from 1 page to 70 pages.
If we try to render the whole thing it just sits and sits and sits. I let it
run for an hour at one point at the report never appeared. The CPU is spiked
to 25% continually and eventually I have to do an iisreset to. Now I am
pretty sure, say 95% sure that this report worked fine with RS SP1 and SQL
2k SP3a.
So two questions.
Is there a known problem with large PDF exports?
Does anyone know if any third parties are selling PDF rendering extensions
for sql reporting services? I know there is at least one for Excel.So 1 - 70 pages is fast. How many pages is it if it is the whole thing? PDF
rendering and Excel rendering are very resource intensive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alex" <atelford6@.hotmail.nospam.com> wrote in message
news:O7cFPTVYFHA.2768@.tk2msftngp13.phx.gbl...
>I am using RS SP2 with SQL 2k SP4.
> I have a fairly large report, in the region of a few hundred pages. It has
> a couple of parameters that filters the data. I have it linked from a web
> application and it goes directly to PDF. If the parameters are set the
> report renders to PDF pretty quickly, the size runs from 1 page to 70
> pages. If we try to render the whole thing it just sits and sits and sits.
> I let it run for an hour at one point at the report never appeared. The
> CPU is spiked to 25% continually and eventually I have to do an iisreset
> to. Now I am pretty sure, say 95% sure that this report worked fine with
> RS SP1 and SQL 2k SP3a.
> So two questions.
> Is there a known problem with large PDF exports?
> Does anyone know if any third parties are selling PDF rendering extensions
> for sql reporting services? I know there is at least one for Excel.
>|||Good news and bad news
Good news is I have figured out the problem. Bad news is it does appear to
come from a PDF rendering bug.
We mad a minor change to the report and uploaded it, which is when we saw
the problem. I rolled back the report, redeployed and all is well, renders
290 pages in around 20 seconds.
The report has a table with 4 groups. On the third group we added a single
blank row above the group row. The group row contains some text. So for
groups #3 we have two header rows, one blank the other has a datafield. We
basically wanted to add some whitespace. With that extra blank row the
report with null parameters will not render and just pegs the CPU. If we
remove the row it works fine. If we supply a parameter to filter the report
it will render in both cases. I don't know at what point it breaks and I
don't really have the time to mess with it.
I will leave it without the whitespace for now but its very annoying to have
such a simple report change break the rendering.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%230WlwdVYFHA.3864@.TK2MSFTNGP10.phx.gbl...
> So 1 - 70 pages is fast. How many pages is it if it is the whole thing?
> PDF rendering and Excel rendering are very resource intensive.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Alex" <atelford6@.hotmail.nospam.com> wrote in message
> news:O7cFPTVYFHA.2768@.tk2msftngp13.phx.gbl...
>>I am using RS SP2 with SQL 2k SP4.
>> I have a fairly large report, in the region of a few hundred pages. It
>> has a couple of parameters that filters the data. I have it linked from a
>> web application and it goes directly to PDF. If the parameters are set
>> the report renders to PDF pretty quickly, the size runs from 1 page to 70
>> pages. If we try to render the whole thing it just sits and sits and
>> sits. I let it run for an hour at one point at the report never appeared.
>> The CPU is spiked to 25% continually and eventually I have to do an
>> iisreset to. Now I am pretty sure, say 95% sure that this report worked
>> fine with RS SP1 and SQL 2k SP3a.
>> So two questions.
>> Is there a known problem with large PDF exports?
>> Does anyone know if any third parties are selling PDF rendering
>> extensions for sql reporting services? I know there is at least one for
>> Excel.
>

Password not Added after Installation

Hi,
I recently installed MSDE 2000, by giving the following to
setup parameters in the Command Prompt:
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\> D:\MSDE2000\Setup.Exe SAPWD="apple" SECURITYMODE=SQL
DISABLENETWORKPROTOCOLS=0 /L*v C:\MSDELog.txt
C:\>
In log file, it did mentioned that the installation is
successfull at it's end of the file. However the SA
password is blank eventhough I gave the password
as "apple". Why did this happen. Is there anything wrong
in my setup parameter mentioned in the Command Prompt?
Help required on this matter, cos there are several
computers left to install MSDE 2000 and hoping to use the
above setup parameters.
Thanx in advance,
shahzard.
hi shahzard,
"Shahzard" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:574901c47465$e8574990$a301280a@.phx.gbl...
> Hi,
> I recently installed MSDE 2000, by giving the following to
> setup parameters in the Command Prompt:
> Microsoft Windows XP [Version 5.1.2600]
> (C) Copyright 1985-2001 Microsoft Corp.
>
> C:\> D:\MSDE2000\Setup.Exe SAPWD="apple" SECURITYMODE=SQL
> DISABLENETWORKPROTOCOLS=0 /L*v C:\MSDELog.txt
> C:\>
> In log file, it did mentioned that the installation is
> successfull at it's end of the file. However the SA
> password is blank eventhough I gave the password
> as "apple". Why did this happen. Is there anything wrong
> in my setup parameter mentioned in the Command Prompt?
> Help required on this matter, cos there are several
> computers left to install MSDE 2000 and hoping to use the
> above setup parameters.
the command line is ok, and i reproduced the problem on a Win2k box, where
SECURITYMODE=SQL has not been honored...
will investigate a little further... for workaround, run Regedit, locate
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer
and set LoginMode key = 2
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Passing text parameters

Hello Guys,

create procedure spsCheckMsg(
@.sTyp as varchar(100),
@.tMsg as text) as
select @.tMsg = md_body from tbl_msgdef where msgname = @.sTyp

Parameters of the type text can be used in stored procedures. So I wanted to get the text data (md_body) by a select statement and pass it to the parameter @.tMsg. This code doesn't work. I always get the message "The assignment operation can't have a text datatype as argument" which refers to the select statement.

Does anyone have a solution for this?Howdy

If I have understood correctly, try :

-------------------
Create Procedure spsCheckMsg
(
@.sTyp as varchar(1000),
@.tMsg as varchar(1000)
)

AS

set @.tMsg =
( select convert(varchar(1000),md_body) from tbl_msgdef where msgname = @.sTyp )
-------------------

Cheers,

SG

Passing SQL Clauses as Parameters to a Stored Procedure

Hi,

I have a complex SQL query against multiple tables that ideally would be best used as a stored procedure.

I know of and have read about creating named parameters and setting the parameters' values in ASP just before issuing an execute command via the Command object. What I'd like to do is assign the text of the filter and sort clauses to the parameters. Not the values, but the entire string, such as

"WHERE lastname LIKE 'A%' "

"ORDER BY lastname "

I need to assign the entire clause because the query may or may not use a particular clause.

I have experimented. However SQL treats the parameter as a literal string rather than part of the SQL query itself. Is there another technique that I may use to accomplish my goal?

Thanks in advance for your opinion, suggestion, criticism, etc.

--Tom.Dynamic SQL|||and look up SQL Injection

DECLARE @.SQL varchar(8000)

SELECT @.SQL = 'SELECT * FROM INFORMATION_SCHEMA'
EXEC(@.SQL)

You should use dynamic as a last reort...

What prob you trying to solve?

Passing results from select into exec

Hi
I'm trying to call a stored proc from within another of my stored
procs, passing the results of a select statement as the parameters -
but it's not working. Here's what I'm trying to do:
CREATE PROCEDURE [MoveRecordsToArchive]
AS
EXEC [ARCHIVEInsert]
(
SELECT
*
FROM
[CURRENT_DATA]
)
where [ARCHIVEInsert] is a stored proc which takes parameters which are
the same as the columns in [CURRENT_DATA].
But I'm getting the following error when trying to run
MoveRecordsToArchive:
"Procedure 'MoveRecordsToArchive' expects parameter '@.ID', which was
not supplied.", where @.ID is the first parameter to [ARCHIVEInsert] and
[ID] is the first column in [CURRENT_DATA].
Am I trying to do something impossible again? If so, how else could I
go about this?You will have to do it like this
declare @.ID' int @.name varchar(50)
select @.id =id,@.name =name
from [CURRENT_DATA] where id = Somevalue
exec ARCHIVEInsert @.id ,@.name
keep in mind you can only use 1 row at a time
You can do this if you need to move more rows
INSERT INTO DATA_ARCHIVE
SELECT * FROM [CURRENT_DATA]
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Unfortunately, there may well be many rows to move, and I can't just
use your second method (i.e. a straight INSERT) since I need to insert
if the row doesn't exist in the archive table, and update if it does,
which is the process that the ARCHIVEInsert sp follows.
Is there then an easy way to iterate through all the rows in the table
so that I can use your first method?|||The insert
INSERT INTO DATA_ARCHIVE
SELECT * FROM [CURRENT_DATA] c
left join DATA_ARCHIVE d on c.id =d.id
where d.id is null
The update
update d set d.field1 = c.field1,d.field2 = c.field2,etc,etc,etc
FROM [CURRENT_DATA] c
join DATA_ARCHIVE d on c.id =d.id
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Yeah, that's not quite what I need. As I say, I have the
insert-or-update functionality working, in my ARCHIVEInsert storedproc.
I guess I could duplicate in the MoveRowsToArchive proc, but it seems
a bit silly when I have a perfectly good stored proc to call that
already does what I need.
Thanks anyway.
SQL wrote:
> The insert
> INSERT INTO DATA_ARCHIVE
> SELECT * FROM [CURRENT_DATA] c
> left join DATA_ARCHIVE d on c.id =d.id
> where d.id is null
> The update
> update d set d.field1 = c.field1,d.field2 = c.field2,etc,etc,etc
> FROM [CURRENT_DATA] c
> join DATA_ARCHIVE d on c.id =d.id
> ----
--
> "I sense many useless updates in you... Useless updates lead to
> fragmentation... Fragmentation leads to downtime...Downtime leads to
> suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
> and DBCC DBREINDEX are the force...May the force be with you" --
> http://sqlservercode.blogspot.com/|||"Cathryn Johns" <cjohns@.gmail.com> wrote in message
news:1130510060.880509.220720@.f14g2000cwb.googlegroups.com...
> Yeah, that's not quite what I need. As I say, I have the
> insert-or-update functionality working, in my ARCHIVEInsert storedproc.
> I guess I could duplicate in the MoveRowsToArchive proc, but it seems
> a bit silly when I have a perfectly good stored proc to call that
> already does what I need.
> Thanks anyway.
> SQL wrote:
>
Well, if you want to do it the hard way..
Create a CURSOR in the first stored procedure and pull one row at a time and
call your insert/update spoc in a loop.
Something like:
declare @.ID' int @.name varchar(50)
DECLARE Cur CURSOR FOR
select id, name
from [CURRENT_DATA] where id = Somevalue
OPEN Cur
FETCH NEXT FROM Cur
INTO @.ID, @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec ARCHIVEInsert @.id ,@.name
FETCH NEXT FROM Cur
INTO @.ID, @.name
END
CLOSE Cur
DEALLOCATE Cur|||Cathryn Johns (cjohns@.gmail.com) writes:
> Yeah, that's not quite what I need. As I say, I have the
> insert-or-update functionality working, in my ARCHIVEInsert storedproc.
> I guess I could duplicate in the MoveRowsToArchive proc, but it seems
> a bit silly when I have a perfectly good stored proc to call that
> already does what I need.
That's not really right. You have a stored procedure which has the
logic to do this for one single row. Now you need something that performs
the same thing for multiple rows.
You can of coruse iterate over the source table, and move one row at a
time. But, frankly, for simple logic like this, that would be about
criminal. OK, that choice of words may stun you, but if I tell you
that to move 10000 rows, it would take 50 seconds with calling the
stored procedure for each row, and five seconds with the solution
that Denis posted, you may agree. Of course, I just made those numbers
up, but the difference is really that drastic - or even worse, if
proper indexing is not in place.
RDBMS are designed to work with sets of data, and you should try to
this as much as possible.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Cathryn Johns (cjohns@.gmail.com) writes:
> That's not really right. You have a stored procedure which has the
> logic to do this for one single row. Now you need something that performs
> the same thing for multiple rows.
> You can of coruse iterate over the source table, and move one row at a
> time. But, frankly, for simple logic like this, that would be about
> criminal. OK, that choice of words may stun you, but if I tell you
> that to move 10000 rows, it would take 50 seconds with calling the
> stored procedure for each row, and five seconds with the solution
> that Denis posted, you may agree. Of course, I just made those numbers
> up, but the difference is really that drastic - or even worse, if
> proper indexing is not in place.
> RDBMS are designed to work with sets of data, and you should try to
> this as much as possible.
Okay, I see what you're saying, but please bear with me here because I
know I don't have the right sql mindset :-) - I'm more used to regular
functional programming.
I understand the update & insert as posted by SQL, but what I don't get
is how to determine which rows need to be inserted and which need to be
updated *without* going through each row, one by one. Currently my
table has a uniqueness constraint consisting of several columns, and
what my stored proc does is try to insert (since this will happen
successfully 99% of the time, I try the insert first), then checks
@.@.error and if the code indicates a uniqueness constraint violation, it
updates instead. Maybe this isn't the best way to implement what I'm
trying to accomplish, but either way I still don't see how to do this
using a set-based approach. The cursor approach makes more sense to
me, but I can see that it could be really slow.|||Cathryn,
"I understand the update & insert as posted by SQL, but what I don't
get
is how to determine which rows need to be inserted and which need to be
updated *without* going through each row, one by one"
when you do a join only rows that exist in both tables are returned so
you can do an update
when you do a left join with where d.id is null only the rows that
don't exist in the other table are returned so you can do an update
When you work with SQL you have to think in terms of sets, basically
you have to unlearn what you were taught for VB, Java C# etc etc
A very good book on T-SQL is
The Guru's Guide to Transact-SQL by Ken Henderson
http://www.amazon.com/exec/obidos/t...=glance&s=books
And of course Books On Line
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Cathryn Johns (cjohns@.gmail.com) writes:
> Okay, I see what you're saying, but please bear with me here because I
> know I don't have the right sql mindset :-) - I'm more used to regular
> functional programming.
Then you have a bit to unlearn. :-)

> I understand the update & insert as posted by SQL, but what I don't get
> is how to determine which rows need to be inserted and which need to be
> updated *without* going through each row, one by one.
UPDATE target
SET col = s.col
FROM target t
JOIN sources s ON t.keycol = s.keycol
INSERT target (...)
SELECT ...
FROM source s
WHERE NOT EXISTS (SELECT *
FROM target t
WHERE t.keycol = s.keycol)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

passing report parameters to an Oracle proc

Hi,
I received an error message after running a report by pushing the Preview
button:
ORA-00972:identifier is too long
ORA-06512:at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1
Here is the Oracle proc:
CREATE OR REPLACE PACKAGE BODY MPC IS
PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
ip_launch_list IN VARCHAR2,
results_cur
OUT T_RESULT_CURSOR)
Here is the Query string:
"MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
At the same time, in the Parameters tab of the Dataset screen, I also create
two parameters:
Name= :ip_response_interval
Value= =Parameters!ip_response_interval.Value
Name= :ip_launch_list
Vaue= =Parameters!ip_launch_list.Value
However, if I click the generic query design button and just type
"MPC.Report_Performance_Main_5" and click the Run button, it pops out the
"Define Query Parameters" screen. After typing the necessary data and hit the
"OK" button, I am able to see the query result in the buttom pane.
I am confused what's wrong when I hit the Preview button.
Any clues will be very appreciated!
JamesNot sure but I think in the generic query window you can do this:
MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
It should create the report parameters for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <James@.discussions.microsoft.com> wrote in message
news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> Hi,
> I received an error message after running a report by pushing the Preview
> button:
> ORA-00972:identifier is too long
> ORA-06512:at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Here is the Oracle proc:
> CREATE OR REPLACE PACKAGE BODY MPC IS
> PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> ip_launch_list IN VARCHAR2,
> results_cur
> OUT T_RESULT_CURSOR)
> Here is the Query string:
> "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> At the same time, in the Parameters tab of the Dataset screen, I also
> create
> two parameters:
> Name= :ip_response_interval
> Value= =Parameters!ip_response_interval.Value
> Name= :ip_launch_list
> Vaue= =Parameters!ip_launch_list.Value
> However, if I click the generic query design button and just type
> "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> "Define Query Parameters" screen. After typing the necessary data and hit
> the
> "OK" button, I am able to see the query result in the buttom pane.
> I am confused what's wrong when I hit the Preview button.
> Any clues will be very appreciated!
> James
>|||I did this in the generic query and got the following error:
An error occurred while retrieving the parameters in the query.
ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1
Is there any document about call a Oracle proc?
Thanks,
James
"Bruce L-C [MVP]" wrote:
> Not sure but I think in the generic query window you can do this:
> MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> It should create the report parameters for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "James" <James@.discussions.microsoft.com> wrote in message
> news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > Hi,
> >
> > I received an error message after running a report by pushing the Preview
> > button:
> >
> > ORA-00972:identifier is too long
> > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > ORA-06512: at line 1
> >
> > Here is the Oracle proc:
> >
> > CREATE OR REPLACE PACKAGE BODY MPC IS
> > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> >
> > ip_launch_list IN VARCHAR2,
> > results_cur
> > OUT T_RESULT_CURSOR)
> >
> > Here is the Query string:
> > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> >
> > At the same time, in the Parameters tab of the Dataset screen, I also
> > create
> > two parameters:
> > Name= :ip_response_interval
> > Value= =Parameters!ip_response_interval.Value
> >
> > Name= :ip_launch_list
> > Vaue= =Parameters!ip_launch_list.Value
> >
> > However, if I click the generic query design button and just type
> > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > "Define Query Parameters" screen. After typing the necessary data and hit
> > the
> > "OK" button, I am able to see the query result in the buttom pane.
> >
> > I am confused what's wrong when I hit the Preview button.
> >
> > Any clues will be very appreciated!
> >
> > James
> >
> >
>
>|||Here is some misc things I've kept around. I haven't used Oracle with RS
(although I did use 8.1.7 extensively in the past).
Oracle has a few unique things going on. First, my recommendation is to use
the generic data designer (2 panes). The button to switch to this is to the
right of the ...
Second, because the development environment was not designed for managed
providers they got tricky with what is used under the covers (hence my
recommendation to use the generic designer). Here is a description from
Robert Bruckner [MSFT].
/Snip
Note: the behavior of PREVIEW in Report Designer is identical to the
ReportServer behavior! However the DATA view in Report Designer is
different for the visual designer: * the visual query designer with 4 panes
will internally always use OleDB providers for verifying and executing
queries directly in "Data" view. (Main reason: the visual query designer
does not work with managed providers). Example: if you choose "Oracle" in
the data source dialog, the Data view has to use the OleDB provider for
Oracle behind the scenes, but Preview and Server will use the managed Oracle
provider. The generic text-based query designer (2 panes) will _always_ use
the data provider you specified.
/End Snip
Just a little background for you. OK, now, from the generic query designer.
He then had this to say about stored procedures:
/Snip
In addition, how do you return the data from your stored procedure? Note:
only an out ref cursor is supported. Please follow the guidelines in the
following article on MSDN (scroll down to the section where it talks about
"Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
To use a stored procedure with regular out parameters, you should either
remove the parameter (if it is possible) or write a little wrapper around
the original stored procedure which checks the result of the out parameter
and just returns the out ref cursor but no out parameter. Finally, in the
generic query designer, just specify the name of the stored procedure
without arguments and the parameters should get detected automatically.
/End Snip
And more from Robert:
/Snip
Managed Oracle provider (named parameters):
select * from table where ename = :parameter
OleDB for Oracle (unnamed parameters):
select * from table where ename = ?
The managed Oracle data provider uses a ':' to mark named parameters
(instead of '@.'); the OleDB provider for Oracle only allows unnamed
parameters (using '?'). The following KB article explains more details:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
/End Snip
Hope that helps. Definitely not intuitive but it works.
One last thing. The MS managed provider for Oracle need 8.1.7 or higher (8i)
client installed for it to work.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <James@.discussions.microsoft.com> wrote in message
news:14B75C3F-7959-47B9-A563-EF5436510BFC@.microsoft.com...
>I did this in the generic query and got the following error:
> An error occurred while retrieving the parameters in the query.
> ORA-00911: invalid character
> ORA-06512: at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Is there any document about call a Oracle proc?
> Thanks,
> James
> "Bruce L-C [MVP]" wrote:
>> Not sure but I think in the generic query window you can do this:
>> MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
>> It should create the report parameters for you.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "James" <James@.discussions.microsoft.com> wrote in message
>> news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
>> > Hi,
>> >
>> > I received an error message after running a report by pushing the
>> > Preview
>> > button:
>> >
>> > ORA-00972:identifier is too long
>> > ORA-06512:at "SYS.DBMS_UTILITY", line 114
>> > ORA-06512: at line 1
>> >
>> > Here is the Oracle proc:
>> >
>> > CREATE OR REPLACE PACKAGE BODY MPC IS
>> > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
>> >
>> > ip_launch_list IN VARCHAR2,
>> >
>> > results_cur
>> > OUT T_RESULT_CURSOR)
>> >
>> > Here is the Query string:
>> > "MPC"."Report_Performance_Main_5 :ip_response_interval,
>> > :ip_launch_list"
>> >
>> > At the same time, in the Parameters tab of the Dataset screen, I also
>> > create
>> > two parameters:
>> > Name= :ip_response_interval
>> > Value= =Parameters!ip_response_interval.Value
>> >
>> > Name= :ip_launch_list
>> > Vaue= =Parameters!ip_launch_list.Value
>> >
>> > However, if I click the generic query design button and just type
>> > "MPC.Report_Performance_Main_5" and click the Run button, it pops out
>> > the
>> > "Define Query Parameters" screen. After typing the necessary data and
>> > hit
>> > the
>> > "OK" button, I am able to see the query result in the buttom pane.
>> >
>> > I am confused what's wrong when I hit the Preview button.
>> >
>> > Any clues will be very appreciated!
>> >
>> > James
>> >
>> >
>>|||James:
Any luck making this successful..I am strugling with this issue as well.
Please reply if you have this working,
"James" wrote:
> I did this in the generic query and got the following error:
> An error occurred while retrieving the parameters in the query.
> ORA-00911: invalid character
> ORA-06512: at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Is there any document about call a Oracle proc?
> Thanks,
> James
> "Bruce L-C [MVP]" wrote:
> > Not sure but I think in the generic query window you can do this:
> >
> > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> >
> > It should create the report parameters for you.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "James" <James@.discussions.microsoft.com> wrote in message
> > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > Hi,
> > >
> > > I received an error message after running a report by pushing the Preview
> > > button:
> > >
> > > ORA-00972:identifier is too long
> > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > ORA-06512: at line 1
> > >
> > > Here is the Oracle proc:
> > >
> > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > >
> > > ip_launch_list IN VARCHAR2,
> > > results_cur
> > > OUT T_RESULT_CURSOR)
> > >
> > > Here is the Query string:
> > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > >
> > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > create
> > > two parameters:
> > > Name= :ip_response_interval
> > > Value= =Parameters!ip_response_interval.Value
> > >
> > > Name= :ip_launch_list
> > > Vaue= =Parameters!ip_launch_list.Value
> > >
> > > However, if I click the generic query design button and just type
> > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > the
> > > "OK" button, I am able to see the query result in the buttom pane.
> > >
> > > I am confused what's wrong when I hit the Preview button.
> > >
> > > Any clues will be very appreciated!
> > >
> > > James
> > >
> > >
> >
> >
> >|||anyone lucky enough to make oracle refcursor working under RS? kindly share
with us, thanks...
"MSOracle" wrote:
> James:
> Any luck making this successful..I am strugling with this issue as well.
> Please reply if you have this working,
> "James" wrote:
> > I did this in the generic query and got the following error:
> > An error occurred while retrieving the parameters in the query.
> > ORA-00911: invalid character
> > ORA-06512: at "SYS.DBMS_UTILITY", line 114
> > ORA-06512: at line 1
> >
> > Is there any document about call a Oracle proc?
> >
> > Thanks,
> >
> > James
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Not sure but I think in the generic query window you can do this:
> > >
> > > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> > >
> > > It should create the report parameters for you.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "James" <James@.discussions.microsoft.com> wrote in message
> > > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > > Hi,
> > > >
> > > > I received an error message after running a report by pushing the Preview
> > > > button:
> > > >
> > > > ORA-00972:identifier is too long
> > > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > > ORA-06512: at line 1
> > > >
> > > > Here is the Oracle proc:
> > > >
> > > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > > >
> > > > ip_launch_list IN VARCHAR2,
> > > > results_cur
> > > > OUT T_RESULT_CURSOR)
> > > >
> > > > Here is the Query string:
> > > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > > >
> > > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > > create
> > > > two parameters:
> > > > Name= :ip_response_interval
> > > > Value= =Parameters!ip_response_interval.Value
> > > >
> > > > Name= :ip_launch_list
> > > > Vaue= =Parameters!ip_launch_list.Value
> > > >
> > > > However, if I click the generic query design button and just type
> > > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > > the
> > > > "OK" button, I am able to see the query result in the buttom pane.
> > > >
> > > > I am confused what's wrong when I hit the Preview button.
> > > >
> > > > Any clues will be very appreciated!
> > > >
> > > > James
> > > >
> > > >
> > >
> > >
> > >|||I have been able to do i just recenly but Only after installing the Oracle
10g client. & using the Orcale provider.
"J" wrote:
> anyone lucky enough to make oracle refcursor working under RS? kindly share
> with us, thanks...
> "MSOracle" wrote:
> > James:
> >
> > Any luck making this successful..I am strugling with this issue as well.
> > Please reply if you have this working,
> >
> > "James" wrote:
> >
> > > I did this in the generic query and got the following error:
> > > An error occurred while retrieving the parameters in the query.
> > > ORA-00911: invalid character
> > > ORA-06512: at "SYS.DBMS_UTILITY", line 114
> > > ORA-06512: at line 1
> > >
> > > Is there any document about call a Oracle proc?
> > >
> > > Thanks,
> > >
> > > James
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Not sure but I think in the generic query window you can do this:
> > > >
> > > > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> > > >
> > > > It should create the report parameters for you.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "James" <James@.discussions.microsoft.com> wrote in message
> > > > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I received an error message after running a report by pushing the Preview
> > > > > button:
> > > > >
> > > > > ORA-00972:identifier is too long
> > > > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > > > ORA-06512: at line 1
> > > > >
> > > > > Here is the Oracle proc:
> > > > >
> > > > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > > > >
> > > > > ip_launch_list IN VARCHAR2,
> > > > > results_cur
> > > > > OUT T_RESULT_CURSOR)
> > > > >
> > > > > Here is the Query string:
> > > > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > > > >
> > > > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > > > create
> > > > > two parameters:
> > > > > Name= :ip_response_interval
> > > > > Value= =Parameters!ip_response_interval.Value
> > > > >
> > > > > Name= :ip_launch_list
> > > > > Vaue= =Parameters!ip_launch_list.Value
> > > > >
> > > > > However, if I click the generic query design button and just type
> > > > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > > > the
> > > > > "OK" button, I am able to see the query result in the buttom pane.
> > > > >
> > > > > I am confused what's wrong when I hit the Preview button.
> > > > >
> > > > > Any clues will be very appreciated!
> > > > >
> > > > > James
> > > > >
> > > > >
> > > >
> > > >
> > > >

Passing Report Parameters through URL

Hi, I've read some threads in regards to passing report parameters through the 'URL' but I am struggling to fully understand. To keep things simple I have 1 report parameter, named par_pub which brings back either ww06 or ww07. What I require is to click on a hyperlink and it directs me to my report and then populates the par_pub paramerter with ww07.

I am trying the below

http://emp6/Reports/Pages/Report.aspx?ItemPath=%2fTest+Reports%2fIT+Dev%2fSales+Summary+Detail&prefixar_pub=%WW07%

The part in bold is the location + name of the report and the later is where I am trying to pass the parameter. Can anyone please help as this is not working.

Thanks

Quarry,

I tried using parameter in reportserver (instead of manager) and they worked fine.

reportserver can be launched as http://server_name/reportserver.

No idea why they are not working with report manager.

|||

Quarry,

I've gotten my reports to work with passing parameters through the URL; with the exception of date fields. They have been giving me fits. To answer your question though, try this:

Try opening a browser and start off just typing the name of your report server's URL. For instance: http://machinename/reportServicesInstancename.

That will open up the list of folders where your reports are located. Click on the folders and files until you have selected and opened your report. Then go into the address box and copy that value. Paste that into your code as the string you want to deal with and appendan "&par_pub=WWW07" to it (I'm not sure why you have %'s around the WWW07 there, unless it's something your app needs.

Ron

|||

Hi Ron, I've tried this but my URL link opens up the report in Report Manager but does not poplulate the parameter value.

|||

Irrespective of URL,when you select the parameter manually, like "www07".does the URL get changed? If not there is no point we can pass parameter through URL?

if this is true, the other way we are speaking should be true.

Guys,please correct me if i am not correct.

I dont have Enough knowledge on passing parameters through URL,if you have done any work around before like this.defly i am interested share to knowledge.

Once again,please correct me if i am wrong.I am not sure

|||I have tried this manually as you suggested, and the URL does not change when I select a parameter value. Does this mean that I cannot pass the value of the parameter from within my URL link to my report in Report Manger?|||

It's not going to change when you select it manually. The reason I was telling you to open it up manually is to make sure that you have the first part of the string working correctly. Once you have the report opened, select the entire path and all querystring parameters that are included along with it there in the address bar. I would then suggest you open up a new browser window and try pasting that into the browser window and make sure that when you select it, it will run (without the parameter being set yet). If it does work, then go into the address bar in the very same window and add in the "&parametername=parameterValue" statement and try running it again from the command line.

In other words, get your report running (using your parameter) from the command line first. Once you do that, THEN go paste that line of code into your code and get it running via code. I just find this approach more easier than trying to debug from within code when I have SSRS syntax errors.

|||

How did you solve the problem?

I have the same issue?

Thanks

Passing Report Parameters through URL

Hi, I've read some threads in regards to passing report parameters through the 'URL' but I am struggling to fully understand. To keep things simple I have 1 report parameter, named par_pub which brings back either ww06 or ww07. What I require is to click on a hyperlink and it directs me to my report and then populates the par_pub paramerter with ww07.

I am trying the below

http://emp6/Reports/Pages/Report.aspx?ItemPath=%2fTest+Reports%2fIT+Dev%2fSales+Summary+Detail&prefixar_pub=%WW07%

The part in bold is the location + name of the report and the later is where I am trying to pass the parameter. Can anyone please help as this is not working.

Thanks

Quarry,

I tried using parameter in reportserver (instead of manager) and they worked fine.

reportserver can be launched as http://server_name/reportserver.

No idea why they are not working with report manager.

|||

Quarry,

I've gotten my reports to work with passing parameters through the URL; with the exception of date fields. They have been giving me fits. To answer your question though, try this:

Try opening a browser and start off just typing the name of your report server's URL. For instance: http://machinename/reportServicesInstancename.

That will open up the list of folders where your reports are located. Click on the folders and files until you have selected and opened your report. Then go into the address box and copy that value. Paste that into your code as the string you want to deal with and appendan "&par_pub=WWW07" to it (I'm not sure why you have %'s around the WWW07 there, unless it's something your app needs.

Ron

|||

Hi Ron, I've tried this but my URL link opens up the report in Report Manager but does not poplulate the parameter value.

|||

Irrespective of URL,when you select the parameter manually, like "www07".does the URL get changed? If not there is no point we can pass parameter through URL?

if this is true, the other way we are speaking should be true.

Guys,please correct me if i am not correct.

I dont have Enough knowledge on passing parameters through URL,if you have done any work around before like this.defly i am interested share to knowledge.

Once again,please correct me if i am wrong.I am not sure

|||I have tried this manually as you suggested, and the URL does not change when I select a parameter value. Does this mean that I cannot pass the value of the parameter from within my URL link to my report in Report Manger?|||

It's not going to change when you select it manually. The reason I was telling you to open it up manually is to make sure that you have the first part of the string working correctly. Once you have the report opened, select the entire path and all querystring parameters that are included along with it there in the address bar. I would then suggest you open up a new browser window and try pasting that into the browser window and make sure that when you select it, it will run (without the parameter being set yet). If it does work, then go into the address bar in the very same window and add in the "&parametername=parameterValue" statement and try running it again from the command line.

In other words, get your report running (using your parameter) from the command line first. Once you do that, THEN go paste that line of code into your code and get it running via code. I just find this approach more easier than trying to debug from within code when I have SSRS syntax errors.

|||

How did you solve the problem?

I have the same issue?

Thanks