ParametersUsed Always Polulated after calling ReportingService.Ren

In SSRS 2000 SP2 the documentation for ReportingService.Render states that
the ParametersUsed output parameter should only be populated only if the
report being rendered is a report history snapshot. I'm invoking the Render
method with a null HistoryId as follows, so I would expect the ParametersUsed
output parameter not to get populated.
results = m_RptService.Render(m_RptCatalogItem.Path /*Report*/,
m_Format /*Format*/,
null /*HistoryID*/,
null /*DeviceInfo*/,
m_RptParameters /*Parameters*/,
null /*Credentials*/,
null /*ShowHideToggle*/,
out encoding /*Encoding*/,
out mimeType /*MimeType*/,
out historyParms /*ParametersUsed*/,
out warnings /*Warnings*/,
out streamIDs /*StreamIds*/);
The call generates the following HTTP traffic. As you can see the HistoryId
is not sent in the request yet ParametersUsed is populated in the result. If
I'm interpreting the documentation correctly this value should not be
populated in the response. I really don't what ParametersUsed to be
populated in the response. Is this the correct behavior?
POST /ReportServer/ReportService.asmx HTTP/1.1
Content-Type: text/xml; charset=utf-8
SOAPAction:
"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/Render"
Content-Length: 524
Expect: 100-continue
Host: localhost
Cookie: sqlAuthCookie=****; UsrTkn=>****; AppTkn=>****
<?xml version="1.0" encoding="utf-8"?><soap:Envelope
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><Render
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><Report>/ReportServerStatistics/PTest</Report><Format>HTML4.0</Format><Parameters><ParameterValue><Name>p1</Name><Value>2/11/2007
7:32:50
PM</Value></ParameterValue></Parameters></Render></soap:Body></soap:Envelope>
HTTP/1.1 200 OK
Date: Mon, 12 Feb 2007 00:32:50 GMT
Server: Microsoft-IIS/6.0
X-Powered-By: ASP.NET
X-AspNet-Version: 1.1.4322
Cache-Control: private, max-age=0
Content-Type: text/xml; charset=utf-8
Content-Length: 9021
<?xml version="1.0" encoding="utf-8"?><soap:Envelope
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Header><ServerInfoHeader
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><ReportServerVersionNumber>Microsoft
SQL Server Reporting Services Version
8.00.1038.00</ReportServerVersionNumber><ReportServerEdition>Enterprise</ReportServerEdition></ServerInfoHeader><SessionHeader
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><SessionId>glbci3rl3utt1iqmon253t45</SessionId><IsNewExecution>true</IsNewExecution><ExecutionDateTime>2007-02-11T19:32:50</ExecutionDateTime><ExpirationDateTime>2007-02-11T19:32:50</ExpirationDateTime></SessionHeader></soap:Header><soap:Body><RenderResponse
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><Result>
Base 64 Encoded response removed to shorten post
</Result><Encoding>Unicode
(UTF-8)</Encoding><MimeType>text/html</MimeType><ParametersUsed><ParameterValue><Name>p1</Name><Value>2/11/2007
7:32:50 PM</Value></ParameterValue></ParametersUsed><StreamIds
/></RenderResponse></soap:Body></soap:Envelope>Reporting Services 2005 exhibits the same behavior. The following
application produces the following output "Length: 3" indicating that the
ParametersUsed output parameter was populated even though no histryid was
provided. Is this the correct behavior? If so how do I turn it off?
According to the following documentation this does not seem like the correct
behavior (http://msdn2.microsoft.com/en-us/library/aa258532(SQL.80).aspx).
Notice that the description for ParametersUsed states that the output
parameter is only populated of a history snapshot is being rendered. This
causes fairly significant performance issues for me because in some cases I
am passing large parameters. Instead of these parameters just being in the
request, they are in both the request and the response causing the response
to be much larger than I desire.
using System;
using System.Collections.Generic;
using System.Text;
using ConsoleApplication9.rs;
namespace ConsoleApplication9
{
class Program
{
static void Main(string[] args)
{
string encoding, mimeType;
ParameterValue[] pout;
Warning[] w;
string[] s;
ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
ParameterValue[] pin = new ParameterValue[3];
pin[0] = new ParameterValue();
pin[0].Name = "stdt";
pin[0].Value = "2/19/2007";
pin[1] = new ParameterValue();
pin[1].Name = "enddt";
pin[1].Value = "2/20/2007";
pin[2] = new ParameterValue();
pin[2].Name = "sopco";
pin[2].Value = "A";
rs.Render("/VRU Reports/VRUAppSuccessPercent" /*Report*/,
"PDF" /*Format*/,
null /*HistoryID*/,
null /*DeviceInfo*/,
pin /*Parameters*/,
null /*Credentials*/,
null /*ShowHideToggle*/,
out encoding /*Encoding*/,
out mimeType /*MimeType*/,
out pout /*ParametersUsed*/,
out w /*Warnings*/,
out s /*StreamIds*/);
Console.WriteLine("Length: " + pout.Length);
Console.ReadLine();
}
}
}
"Jerry" wrote:
> In SSRS 2000 SP2 the documentation for ReportingService.Render states that
> the ParametersUsed output parameter should only be populated only if the
> report being rendered is a report history snapshot. I'm invoking the Render
> method with a null HistoryId as follows, so I would expect the ParametersUsed
> output parameter not to get populated.
> results = m_RptService.Render(m_RptCatalogItem.Path /*Report*/,
> m_Format /*Format*/,
> null /*HistoryID*/,
> null /*DeviceInfo*/,
> m_RptParameters /*Parameters*/,
> null /*Credentials*/,
> null /*ShowHideToggle*/,
> out encoding /*Encoding*/,
> out mimeType /*MimeType*/,
> out historyParms /*ParametersUsed*/,
> out warnings /*Warnings*/,
> out streamIDs /*StreamIds*/);
> The call generates the following HTTP traffic. As you can see the HistoryId
> is not sent in the request yet ParametersUsed is populated in the result. If
> I'm interpreting the documentation correctly this value should not be
> populated in the response. I really don't what ParametersUsed to be
> populated in the response. Is this the correct behavior?
> POST /ReportServer/ReportService.asmx HTTP/1.1
> Content-Type: text/xml; charset=utf-8
> SOAPAction:
> "http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices/Render"
> Content-Length: 524
> Expect: 100-continue
> Host: localhost
> Cookie: sqlAuthCookie=****; UsrTkn=>****; AppTkn=>****
> <?xml version="1.0" encoding="utf-8"?><soap:Envelope
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="<soap:Body><Render">http://www.w3.org/2001/XMLSchema"><soap:Body><Render
> xmlns="<Report>/ReportServerStatistics/PTest</Report><Format>HTML4.0</Format><Parameters><ParameterValue><Name>p1</Name><Value>2/11/2007">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><Report>/ReportServerStatistics/PTest</Report><Format>HTML4.0</Format><Parameters><ParameterValue><Name>p1</Name><Value>2/11/2007
> 7:32:50
> PM</Value></ParameterValue></Parameters></Render></soap:Body></soap:Envelope>
> HTTP/1.1 200 OK
> Date: Mon, 12 Feb 2007 00:32:50 GMT
> Server: Microsoft-IIS/6.0
> X-Powered-By: ASP.NET
> X-AspNet-Version: 1.1.4322
> Cache-Control: private, max-age=0
> Content-Type: text/xml; charset=utf-8
> Content-Length: 9021
> <?xml version="1.0" encoding="utf-8"?><soap:Envelope
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="<soap:Header><ServerInfoHeader">http://www.w3.org/2001/XMLSchema"><soap:Header><ServerInfoHeader
> xmlns="<ReportServerVersionNumber>Microsoft">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><ReportServerVersionNumber>Microsoft
> SQL Server Reporting Services Version
> 8.00.1038.00</ReportServerVersionNumber><ReportServerEdition>Enterprise</ReportServerEdition></ServerInfoHeader><SessionHeader
> xmlns="<SessionId>glbci3rl3utt1iqmon253t45</SessionId><IsNewExecution>true</IsNewExecution><ExecutionDateTime>2007-02-11T19:32:50</ExecutionDateTime><ExpirationDateTime>2007-02-11T19:32:50</ExpirationDateTime></SessionHeader></soap:Header><soap:Body><RenderResponse">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><SessionId>glbci3rl3utt1iqmon253t45</SessionId><IsNewExecution>true</IsNewExecution><ExecutionDateTime>2007-02-11T19:32:50</ExecutionDateTime><ExpirationDateTime>2007-02-11T19:32:50</ExpirationDateTime></SessionHeader></soap:Header><soap:Body><RenderResponse
> xmlns="<Result>">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"><Result>
> Base 64 Encoded response removed to shorten post
> </Result><Encoding>Unicode
> (UTF-8)</Encoding><MimeType>text/html</MimeType><ParametersUsed><ParameterValue><Name>p1</Name><Value>2/11/2007
> 7:32:50 PM</Value></ParameterValue></ParametersUsed><StreamIds
> /></RenderResponse></soap:Body></soap:Envelope>
>

Parameters Pane

Is there a way to absolutely bar a user from calling up the Parameters
Pane outside of taking away the prompts for a parameter? I tried doing
that and it won't allow me to pass parameters in a URL.
I also tried '&rc:parameters = false' but the allows a user to pull the
parameters pane back. I'd like to keep it from ever being expandable.
thanks
adamIf you have SP1 or greater then you can hide the parameters. A hidden
parameter will not show up in the parameter pane but can be passed in via
the url. Of course there are no security restrictions on this so a user can
still modify the url to pass in any parameter.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
<adam.rhodes@.gmail.com> wrote in message
news:1126123266.374180.41760@.z14g2000cwz.googlegroups.com...
> Is there a way to absolutely bar a user from calling up the Parameters
> Pane outside of taking away the prompts for a parameter? I tried doing
> that and it won't allow me to pass parameters in a URL.
> I also tried '&rc:parameters = false' but the allows a user to pull the
> parameters pane back. I'd like to keep it from ever being expandable.
> thanks
> adam
>

Parameters for Lookup Transformation Query!

Hi Gurus,

I have a Dataflow Task which has an OLE DB Source calling a SP with parameters (?, ?). Then this OLE DB Source is conencted to a Lookup Transform which also calls a SP but on a different database. I am unable to figure out how to pass parameters in a Look up Transform.

In the 'Use Results of an SQL Query' pane of Lookup Transform:

Code Snippet

EXEC GetMonthlyDataExtract 4, 2007

( I am passing month and year values) this works ok.

But when I chage to

Code Snippet

EXEC GetMonthlyDataExtract ?, ?

It says EXEC not supported. Also I can not figure out how to configure parameters since 'Reference Table' Tab of the Lookup Transform does not have any option where we can attach variables to parameters.

Also I am interested to map parameters to variables not to input columns.

If mention if that is not possible or any other alternative.

Your help will be appreciated.

Thanks,

Paraclete

It's a bit of a workaround, but I think you could put your variables in data columns and then use the Lookup in partially cached mode. Go over to the Advanced tab, check Enable Memory Restriction, Enable Caching, and Modify SQL Statement. Put your parameterized EXEC in the statment and set the parameters based on your variable/columns. Instead of executing your procedure in the PreExecute phase, now it will get executed on the first row. Since you're caching the results, the procedure should never get executed again. So it's effectively the same functionality.

Update:
On second thought, the Lookup workaround described here probably won't work. When the Lookup is in partial cache mode, it doesn't perform its own matching, but relies on the modified query and the database engine to perform the matching. Since the parameters to your procedure are not the columns you want to match on, the Lookup won't return the correct matching row.

You may also consider using the Merge Join instead of Lookup.

Parameters - Coalesce!

Hi all,
Just a quick question.
I am calling a stored procedure which has two optional parameters.
Optional meaning they are declared with a default value NULL.
If for some reason and empty string send sent to the procedure will the
internal value of that parameter be NULL or "" ?
Area these the same thing in TSQL ?
Following on from that:
Will the Coalesce Function treat "" & NULL the same?
IE: I need to know if the following will result in 10 if an empty string is
sent to a stored procedure for the 'myParam' parameter..
CREATE PROCEDURE [dbo].[myProcedure]
@.myParam INT = NULL
As
BEGIN
SELECT * FROM myTable
WHERE myTableID = Coalesce(@.myParam,10)
END
Many thanks to those who respond!!!
Adam"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:eNcdq2gyFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> Just a quick question.
> I am calling a stored procedure which has two optional parameters.
> Optional meaning they are declared with a default value NULL.
> If for some reason and empty string send sent to the procedure will the
> internal value of that parameter be NULL or "" ?
An "Empty String" passed in as a parameter is treated as an empty string in
SQL ('')

> Area these the same thing in TSQL ?
A empty string, or zero-length string (ZLS) is not the same thing as NULL.

> Following on from that:
> Will the Coalesce Function treat "" & NULL the same?
COALESCE(value1, value2, ...) returns the first non-NULL value from the list
of values. A common usage of coalesce is something like this:
COALESCE(column, '')
To return a zero-length string in place of a NULL.

> IE: I need to know if the following will result in 10 if an empty string
> is sent to a stored procedure for the 'myParam' parameter..
> CREATE PROCEDURE [dbo].[myProcedure]
> @.myParam INT = NULL
> As
> BEGIN
> SELECT * FROM myTable
> WHERE myTableID = Coalesce(@.myParam,10)
> END
>
An empty string is not a NULL. You shouldn't be passing a 'string' value in
to this procedure anyway - it's an INT parameter. For your example would it
make sense to set the default for @.myParam INT = 10 and get rid of the
COALESCE() function call?

> Many thanks to those who respond!!!
> Adam
>