How to use transaction in a SSIS Package

I try ton use a transaction in a SSIS package. When running i have an error :

[source [1]] Error: The AcquireConnection method call to the connection manager "myconnection" failed with error code 0xC0202009.

[Connection manager "myconnection"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "Le partenaire du gestionnaire de transactions a dsactiv la prise en charge des transactions à distance/par rseau.".

Can someone help ?

thanks

Where is that 'myconnection' connection manager pointing to?

Make sure you open it an test its connection.

|||The connection is pointing an oracle database and is correct. When i run tha package whitout the transaction it works correctly. This error appears only when i implement the transaction.|||I believe SSIS uses Microsoft's Distributed Transaction Coordinator - Oracle needs to have support for that enabled explicitly (or at least it did last time I used it).|||I try to do the same thing using SQL SERVER 2005 databases for source and destination. And i have the same error.|||

Make sure you have Distributed Transaction Coordinator running on both the source and destination machines.

Brian Knight has a good video about it on Jumpstart TV - Using Transactions in SSIS

|||

Try to use checkpoints too!

Regards

|||

PedroCGD wrote:

Try to use checkpoints too!

Regards

Not the same as transactions though...|||

I know is not the same..

but could help... some people dont know checkpoint and it features.

How to use transaction in a SSIS Package

I try ton use a transaction in a SSIS package. When running i have an error :

[source [1]] Error: The AcquireConnection method call to the connection manager "myconnection" failed with error code 0xC0202009.

[Connection manager "myconnection"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "Le partenaire du gestionnaire de transactions a dsactiv la prise en charge des transactions à distance/par rseau.".

Can someone help ?

thanks

Where is that 'myconnection' connection manager pointing to?

Make sure you open it an test its connection.

|||The connection is pointing an oracle database and is correct. When i run tha package whitout the transaction it works correctly. This error appears only when i implement the transaction.|||I believe SSIS uses Microsoft's Distributed Transaction Coordinator - Oracle needs to have support for that enabled explicitly (or at least it did last time I used it).|||I try to do the same thing using SQL SERVER 2005 databases for source and destination. And i have the same error.|||

Make sure you have Distributed Transaction Coordinator running on both the source and destination machines.

Brian Knight has a good video about it on Jumpstart TV - Using Transactions in SSIS

|||

Try to use checkpoints too!

Regards

|||

PedroCGD wrote:

Try to use checkpoints too!

Regards

Not the same as transactions though...|||

I know is not the same..

but could help... some people dont know checkpoint and it features.

How to use the split function

Hi everyone,

i need to split a string in different columns in my database.

But now i m using the len method to seperate my data, this works well with date and time function cause its static.

But when there's a name in the string it will give problems cause the LEN method is not flexibel.

So i try to use the split function, but i dont know where to put in my following query:

Declare @.fileline Nvarchar(100)

Declare @.Datum nvarchar(100), @.tijd nvarchar(100)

Declare @.Count INT

Createtable #h(s varchar(100))

bulkinsert #h from'c:\Logfile.txt'

Declare Log_cursor cursor

ForSelect s from #h

Open Log_cursor

Set @.count = 0

Fetchnextfrom Log_cursor into @.fileline

While@.@.fetch_status= 0

Begin

Select @.count = @.count + 1

If @.count = 1

Begin

Select @.Datum =CAST(Left(@.fileline,len 10 -Charindex(' ,',@.fileline))asnvarchar(100))

end

elseif @.Count = 2

Begin

Select @.Tijd =CAST(left(@.fileline,10 -Charindex(' ',@.fileline))asnvarchar(100))

Insertinto Logon (Datum,Tijd)

Values(@.datum,@.Tijd)

Select @.datum =Null

Select @.Count=0

END

Fetchnextfrom Log_cursor into @.fileline

END

CLOSE log_cursor

Deallocate log_cursor

Droptable #h

This is wat my result now is:

I have read many topics about split so pls dont post any links.

I tryied something and it doesnt work thats why i am posting this.

Tnx

XXX-Sheila

One way to apply the SPLIT function if you are using SQL 2005 is to use the CROSS APPLY join in something like:

Code Snippet

select datum,
b.OccurenceId as col#,
b.splitValue
from ( select '01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon' as datum union all
select '01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon' as datum
) a
cross apply split(datum, ',') b

/*
datum col# splitValue
- --
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 1 01-04-2007
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 2 08:24:05
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 3 D01-TS-503
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 4 wsmeel
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 5 Logon
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 1 01-04-2007
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 2 08:24:05
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 3 D01-TS-503
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 4 wsmeel
01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon 5 Logon
*/

The other thing to consider is to replace your cursor with a set based process. Also, this looks to me like a good candidate to use an SSIS package to do the work. If you make a package you shouldn't need to SPLIT function and you might be able to eliminate the step of loading the data into the temp table.

|||

This looks like a candidate for BCP or BULK INSERT processing...

But beyond that, this function may help:

Code Snippet

IFEXISTS(

SELECT*FROMsys.objects

WHEREobject_id=OBJECT_ID(N'[Util].[list2set]')

ANDtypein(N'FN', N'IF', N'TF', N'FS', N'FT')

)

DROPFUNCTION [Util].[list2set];

GO

CREATEFUNCTION Util.list2set( @.list nvarchar(max), @.delim nvarchar(10))

RETURNS @.resultset TABLE( pos intidentity, item nvarchar(max))

AS

BEGIN

IFlen(@.list)<1 RETURN;

DECLARE @.xList XML;

-- no validity tests are performed, depending input this could fail

SET @.xList =Convert(XML,'<list><item>'+REPLACE(@.list, @.delim,'</item><item>')+'</item></list>')

INSERTINTO @.resultset

SELECT data.listitem.value('.','nvarchar(max)')as item

FROM @.xList.nodes('/list/item') data(listitem)

RETURN

END

GO

INSERT INTO Logon

SELECT * FROMutil.list2set(@.fileline, N',')

|||

Guys very thnx for the reactions.

But the whole SSIS story looks a bit complicated somebody got some weblink so i can understand it.

M really a noob doing this but my will is to learn it.
DaleJ i really dont understand your code, and dont know how to put my own values into it.

Code Snippet

IFEXISTS(

SELECT*FROMsys.objects This should be my database?

WHEREobject_id=OBJECT_ID(N'[Util].[list2set]')

ANDtypein(N'FN', N'IF', N'TF', N'FS', N'FT') Wat means the (N'FN?

)

DROPFUNCTION [Util].[list2set]; Whats the util or listset?

GO

CREATEFUNCTION Util.list2set( @.list nvarchar(max), @.delim nvarchar(10)) Creating delimeter i understand

RETURNS @.resultset TABLE( pos intidentity, item nvarchar(max)) @.resulset should me my database and pos means the position

AS

BEGIN

IFlen(@.list)<1 RETURN;

DECLARE @.xList XML;

-- no validity tests are performed, depending input this could fail

SET @.xList =Convert(XML,'<list><item>'+REPLACE(@.list, @.delim,'</item><item>')+'</item></list>') i dont understand the above line

INSERTINTO @.resultset

SELECT data.listitem.value('.','nvarchar(max)')as item what should be the data.listitem?

FROM @.xList.nodes('/list/item') data(listitem)

RETURN

END

GO

INSERT INTO Logon

SELECT * FROMutil.list2set(@.fileline, N',') why the list2set?

Second thing where i must read in the txt file?

Sorry being not so smart as you guys.

|||

Look this is my scenario:

This my logfile:

Datum,Tijd,Computernaam,Username,Actie
01-04-2007,08:24:05,D01-TS-S03,wsmeel,Logon
02-05-2007,06:23:05,D02-TS-S04,atest,Logoff

This need to be inserted in my database

Il show the database structure:

Now i have the following query:

Code Snippet

Declare @.fileline Nvarchar(100)

Declare @.Datum INT

Declare @.Count INT

Createtable #e(s varchar(100))

bulkinsert #e from'c:\Logfile.txt'

Declare Log_cursor cursor

ForSelect s from #e

Open Log_cursor

Set @.count = 0

Fetchnextfrom Log_cursor into @.fileline

While@.@.fetch_status= 0

Begin

Select @.count = @.count + 1

If @.count = 1

Begin

Select @.Datum =CAST(Right(@.fileline,len(@.fileline)-Charindex(' ',@.fileline))asINT)

Insertinto Logon (Datum)

Values(@.datum)

Select @.datum =Null

Select @.Count=0

END

Fetchnextfrom Log_cursor into @.fileline

END

CLOSE log_cursor

Deallocate log_cursor

Droptable #e

when is do this all the data from the logfile comes in 1 column, but it needs to split over different columns.

|||

Here the solution,

Code Snippet

/*

Alter function split(@.input varchar(max), @.delimter varchar(10))

returns @.data table (OccurenceId int, SplitValue Varchar(max))

as

Begin

Declare @.Numbers Table(Number int);

Declare @.i as int;

Set @.i = 1

Set @.input= @.delimter + @.input + @.delimter;

While (@.i < 100)

Begin

Insert Into @.Numbers Values(@.i);

Set @.i = @.i +1;

End

Insert Into @.data

Select Row_Number() Over (Order By Number),Data From (Select Number,Substring(@.input,Number,CharIndex(@.delimter,@.input,Number) - Number) Data from @.Numbers Where Number <= Len(@.input) And Substring(@.input,Number-1,1)= @.delimter) as Data

Return;

End

*/

Select

UniqueId,

Max(Case When b.OccurenceId=1 Then b.splitValue End) Datum,

Max(Case When b.OccurenceId=2 Then b.splitValue End) Tijd,

Max(Case When b.OccurenceId=3 Then b.splitValue End) ComputerNaam,

Max(Case When b.OccurenceId=4 Then b.splitValue End) Username,

Max(Case When b.OccurenceId=5 Then b.splitValue End) Actie

from

(

Select *,Row_Number() Over (Order By Datum) as UniqueId

From

(

Select '01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon' as datum

Union All

Select '01-04-2007,08:24:05,D01-TS-503,wsmeel,Logon' as datum

) Data

) A

Cross Apply

Split(datum, ',') B

Group BY

UniqueId

|||

Sheila,

Try this:

Code Snippet

createtable dbo.Logon(Datum datetime, Tijd varchar(50), Computernaam varchar(50),

Username varchar(50), Actie varchar(50))

BULKINSERT dbo.Logon

FROM'C:\logfile.txt'

WITH

(

FIELDTERMINATOR=',',

ROWTERMINATOR='\n'

)

select*

from dbo.Logon

Since your table already exists, you can remove the 'create table' statement.

|||

Yeah its working really tnx

Sheila,

How to use the Render method to save a report directly to disk ?

Hi there,

Is there a way to programmatically save a RS results into Excel format using the render method ?

I had read about that capability but I can't seem to find any sample code on how to do it. Is this a parameter that you have to set in the render method ?

Any suggestion or tips are much appreciated !

Thanks !

Here's one way...

http://sqljunkies.com/WebLog/roman/archive/category/370.aspx

rs -i "C:\RS Script\MyScript.rss" -s http://myserver/reportserver

Here is the code from MyScript.rss. It renders the Product Line Sales report, it's one of the sample reports that comes with RS:

Public Sub Main()
Dim format as string = "EXCEL"
Dim fileName as String = "C:\RS Script\Product Line Sales.xls"
Dim reportPath as String = "/SampleReports/Product Line Sales"

' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing

results = rs.Render(reportPath, format, _
Nothing, Nothing, Nothing, _
Nothing, Nothing, encoding, mimeType, _
reportHistoryParameters, warnings, streamIDs)

' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()
End Sub

cheers,

Andrew

|||

Hi andrew,

Thanks for the quick reply !

Yup, that works ! For now I am using this to save my reports to Excel.

I am not sure whether it is actually possible for the Render method to actually save a report to Excel without having to "see the report" or perhaps changing one of the parameters.

Can anyone verify this ?

Thanks.

|||

Hi there,

Glad to hear. Not sure what you mean about having to see the report? If you take a look at running from a URL and specifying the Excel Export option in the query parameters, you should not have to see the report. You would need to either define default parameters or specify required parameters for this to work.

You should be able to automate everything.

cheers,

Andrew

|||

Hi Andrew,

Well the problem with what I am doing is that I need to use the Render method so as not to expose the reporting server URL to outside sources. In other words through ASMX. I am trying not to "render" the report to the browser in Excel format.

So the regular way of rendering a report in the browser is the following:-

Response.ClearContent()

Response.AppendHeader("content-length", result.Length.ToString())

Response.ContentType = mimeType

Response.BinaryWrite(result)

Response.Flush()

Response.Close()

This will render the report in Excel to the browser. I was wondering whether there was a way to save the file as an excel without having to render it to a browser. I am also curious whether there was just more than setting the Format parameter to "EXCEL" and whether there were other parameters that I need to set ? (just like you mentioned in the post)

Thanks

How to use Render() SOAP API?

Hi
How do i use the Render() method? Where do i begin?
I am try out avoiding the grey pop-up message when exporting reports.
Currently, i have a java servlet web application. There are hyperlinks to the reports in some JSP pages. I am using URL Access now. How do i use SOAP API instead?
Thank youCheck
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_6x0z.asp.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chiara" <Chiara@.discussions.microsoft.com> wrote in message
news:30F51016-EEA1-4D91-BCAF-A28124806033@.microsoft.com...
> Hi
> How do i use the Render() method? Where do i begin?
> I am try out avoiding the grey pop-up message when exporting reports.
> Currently, i have a java servlet web application. There are hyperlinks to
the reports in some JSP pages. I am using URL Access now. How do i use SOAP
API instead?
> Thank you

how to use parameters in method (filter by parameter)

Hello!

I've begin to do a tutorial of this site: Working with Data in ASP.NET 2.0 c#, but in the 3. step I can't make something:

In this step I can't add parameterized methods to the TableAdapter. The problem come up when I want to add Sql query.

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = @.CategoryID

The error message:

Error in WHERE clause near'@.'.
Unable to parse query text.

So, how can i use parameter in this method?

tnx for the help

Simpson

Are you sure you are passing in the parameter values to the TableAdapter when calling in your code?

Thanks

|||Hi

I have the same problem have you found a solution.

Regards
Demetrios|||Hi

I have the same problem have you found a solution.

Regards
Demetrios|||Hi

I have the same problem have you found a solution.

Regards
Demetrios|||

Hi MaoBranca,

This might because the @.CategoryID was not declared properly.

If this is a SQL Server database, please make sure that you have added the parameter to the SqlCommand parameter collection. If it has been added, please also confirm that the data type, size and value are correct.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||i have found the solution

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = @.CategoryID

this works for sqlserver if you are using an access database then it is

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = ?

if you are using mysql then it is

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = #CategoryID

but i an not sure with mysql

how to use listavailablesqlserver method

Hai

I am studying sqldmo with asp.net . I wanted to generate a list of available sqlservers in my network. so i used the listavailablesqlservers method which is using the application object. when i tried to use that i am getting an error. the error is as below

QueryInterface for interface SQLDMO.NameList failed.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: QueryInterface for interface SQLDMO.NameList failed.

Source Error:

Line 128: Dim i As Integer
Line 129: cmbtest.Items.Clear()
Line 130: namex = oapp.ListAvailableSQLServers
Line 131: For i = 0 To namex.Count
Line 132: cmbtest.Items.Add(namex.Item(i).ToString)

The full coding is shown below for reference if anyone able to help me out to get rid of this problem do reply to my id
I have declared the oapp and namex in the begining itself as

Public oapp As New SQLDMO.Application()
Public namex As SQLDMO.NameList

Private Sub cmdcheck_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdcheck.Click
Dim i As Integer
cmbtest.Items.Clear()
namex = oapp.ListAvailableSQLServers
For i = 0 To namex.Count
cmbtest.Items.Add(namex.Item(i).ToString)
Next
End Sub

sasidar_d@.hotmail.com

Thanks in advance

SasidarHi Sasidar!
I m also facing the same problem. Kindly reply back if u got the soln...

Thnx & Bye.
Lita|||Hi!
Just install sqlserver 2000 service pack 3 (in case u r using sql server 2000) & it will be solved. Mine is solved...

Bye.|||Which SQL Server version and service pack are you running?

Terri

How to use default values of parameters

My report has 6 parameters. 3 Of them are defined with default values. How can I via the webservice method use these default values? I am building the parameters dynamically. You can see that the first 3 parameters are filled in depending on the value selected in a dropdown box. With the last 3 I want to use the default values. Is this possible and how do I do this. When with the first 3 parameters, no value is selected in the dropdown box, the default values shouls also be used but this is not yet programmed. Can anyone help me with this?
ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(reportPath, historyID, forRendering, reportHistoryParameters, credentials);
ParameterValue[] rptParameters = new ParameterValue[parameters.GetLength(0)];
int intParam = 0;
foreach (ReportParameter rp in parameters)
{
rptParameters[intParam] = new ParameterValue();
rptParameters[intParam].Name = rp.Name;
switch(rp.Name.ToUpper())
{
case "OPCO":
rptParameters[intParam].Value = this.Opco;
break;
case "CONFIGTYPEGROUP":
rptParameters[intParam].Value = this.ConfigtypeGroup;
break;
case "CONFIGTYPE":
rptParameters[intParam].Value = this.ConfigType;
break;
case "ERRORCODE":
rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
case "VISITDATETIME_FROM":
rptParameters[intParam].Value = rp.DefaultValues.ToString())
break;
case "VISITDATETIME_TO":
rptParameters[intParam].Value = rp.DefaultValues.ToString();
break;
}
intParam++;
}
firstPage = rs.Render( reportPath,
format,
null,
deviceInfo,
rptParameters,
null,
null,
out encoding,
out mimeType,
out reportHistoryParameters,
out warnings,
out streamIDs);I already found the solution.
I have to write:
rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
in stead of:
rptParameters[intParam].Value = rp.DefaultValues.ToString();
This will give back the default value defined in the report
"Gert" wrote:
> My report has 6 parameters. 3 Of them are defined with default values. How can I via the webservice method use these default values? I am building the parameters dynamically. You can see that the first 3 parameters are filled in depending on the value selected in a dropdown box. With the last 3 I want to use the default values. Is this possible and how do I do this. When with the first 3 parameters, no value is selected in the dropdown box, the default values shouls also be used but this is not yet programmed. Can anyone help me with this?
> ReportParameter[] parameters = null;
> parameters = rs.GetReportParameters(reportPath, historyID, forRendering, reportHistoryParameters, credentials);
> ParameterValue[] rptParameters = new ParameterValue[parameters.GetLength(0)];
> int intParam = 0;
> foreach (ReportParameter rp in parameters)
> {
> rptParameters[intParam] = new ParameterValue();
> rptParameters[intParam].Name = rp.Name;
> switch(rp.Name.ToUpper())
> {
> case "OPCO":
> rptParameters[intParam].Value = this.Opco;
> break;
> case "CONFIGTYPEGROUP":
> rptParameters[intParam].Value = this.ConfigtypeGroup;
> break;
> case "CONFIGTYPE":
> rptParameters[intParam].Value = this.ConfigType;
> break;
> case "ERRORCODE":
> rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
> case "VISITDATETIME_FROM":
> rptParameters[intParam].Value = rp.DefaultValues.ToString())
> break;
> case "VISITDATETIME_TO":
> rptParameters[intParam].Value = rp.DefaultValues.ToString();
> break;
> }
> intParam++;
> }
> firstPage = rs.Render( reportPath,
> format,
> null,
> deviceInfo,
> rptParameters,
> null,
> null,
> out encoding,
> out mimeType,
> out reportHistoryParameters,
> out warnings,
> out streamIDs);|||If you want the server to use the current default values, then don't pass in
anything for this values. Only pass in the three parameters that don't have
defaults and the server will then use the current default value during
rendering.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert" <Gert@.discussions.microsoft.com> wrote in message
news:66030BB4-9273-4AF1-B608-9B01F77ED7FD@.microsoft.com...
> I already found the solution.
> I have to write:
> rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
> in stead of:
> rptParameters[intParam].Value = rp.DefaultValues.ToString();
> This will give back the default value defined in the report
>
> "Gert" wrote:
> > My report has 6 parameters. 3 Of them are defined with default values.
How can I via the webservice method use these default values? I am building
the parameters dynamically. You can see that the first 3 parameters are
filled in depending on the value selected in a dropdown box. With the last 3
I want to use the default values. Is this possible and how do I do this.
When with the first 3 parameters, no value is selected in the dropdown box,
the default values shouls also be used but this is not yet programmed. Can
anyone help me with this?
> >
> > ReportParameter[] parameters = null;
> >
> > parameters = rs.GetReportParameters(reportPath, historyID, forRendering,
reportHistoryParameters, credentials);
> >
> > ParameterValue[] rptParameters = new
ParameterValue[parameters.GetLength(0)];
> >
> > int intParam = 0;
> > foreach (ReportParameter rp in parameters)
> > {
> > rptParameters[intParam] = new ParameterValue();
> > rptParameters[intParam].Name = rp.Name;
> > switch(rp.Name.ToUpper())
> > {
> > case "OPCO":
> > rptParameters[intParam].Value = this.Opco;
> > break;
> > case "CONFIGTYPEGROUP":
> > rptParameters[intParam].Value = this.ConfigtypeGroup;
> > break;
> > case "CONFIGTYPE":
> > rptParameters[intParam].Value = this.ConfigType;
> > break;
> > case "ERRORCODE":
> > rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
> > case "VISITDATETIME_FROM":
> > rptParameters[intParam].Value = rp.DefaultValues.ToString())
> > break;
> > case "VISITDATETIME_TO":
> > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> > break;
> > }
> >
> > intParam++;
> > }
> >
> > firstPage = rs.Render( reportPath,
> > format,
> > null,
> > deviceInfo,
> > rptParameters,
> > null,
> > null,
> > out encoding,
> > out mimeType,
> > out reportHistoryParameters,
> > out warnings,
> > out streamIDs);|||I can't use your option because I have to loop through all the report parameterss because I have different reports with different paramaters and I don't know in the beginning of the program for which parameter the user will select a value or not. If no value selected, the default value of the report is retrieved.
I use the following code and this works perfect. I use a Hash table(see below) to store the paramaters and its values if they are selected.
********************************************************
ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(reportPath, historyID, forRendering, reportHistoryParameters, credentials);
ParameterValue[] rptParameters = new ParameterValue[parameters.GetLength(0)];
int intParam = 0;
foreach (ReportParameter rp in parameters)
{
rptParameters[intParam] = new ParameterValue();
rptParameters[intParam].Name = rp.Name.ToUpper();
if (hashParameters.ContainsKey(rp.Name.ToUpper()))
{
if (hashParameters[rp.Name].ToString() != "")
rptParameters[intParam].Value = hashParameters[rp.Name].ToString();
else
rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
}
intParam++;
}
********************************************************
The Hash table is filled as follows:
********************************************************
hashParameters = new Hashtable();
hashParameters.Add("OPCO", this.Opco);
hashParameters.Add("CONFIGTYPEGROUP", this.ConfigtypeGroup);
hashParameters.Add("CONFIGTYPE", this.ConfigType);
hashParameters.Add("ERRORCODE", "");
hashParameters.Add("VISITDATETIME_FROM", "");
hashParameters.Add("VISITDATETIME_TO", "");
hashParameters.Add("COUNTERNUMBER", "");
hashParameters.Add("ERRORDATETIME_FROM", "");
hashParameters.Add("ERRORDATETIME_TO", "");
hashParameters.Add("PARAMETERNUMBER", "");
hashParameters.Add("MODIFICATIONNR", "");
********************************************************
"Daniel Reib [MSFT]" wrote:
> If you want the server to use the current default values, then don't pass in
> anything for this values. Only pass in the three parameters that don't have
> defaults and the server will then use the current default value during
> rendering.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Gert" <Gert@.discussions.microsoft.com> wrote in message
> news:66030BB4-9273-4AF1-B608-9B01F77ED7FD@.microsoft.com...
> > I already found the solution.
> > I have to write:
> > rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
> > in stead of:
> > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> >
> > This will give back the default value defined in the report
> >
> >
> > "Gert" wrote:
> >
> > > My report has 6 parameters. 3 Of them are defined with default values.
> How can I via the webservice method use these default values? I am building
> the parameters dynamically. You can see that the first 3 parameters are
> filled in depending on the value selected in a dropdown box. With the last 3
> I want to use the default values. Is this possible and how do I do this.
> When with the first 3 parameters, no value is selected in the dropdown box,
> the default values shouls also be used but this is not yet programmed. Can
> anyone help me with this?
> > >
> > > ReportParameter[] parameters = null;
> > >
> > > parameters = rs.GetReportParameters(reportPath, historyID, forRendering,
> reportHistoryParameters, credentials);
> > >
> > > ParameterValue[] rptParameters = new
> ParameterValue[parameters.GetLength(0)];
> > >
> > > int intParam = 0;
> > > foreach (ReportParameter rp in parameters)
> > > {
> > > rptParameters[intParam] = new ParameterValue();
> > > rptParameters[intParam].Name = rp.Name;
> > > switch(rp.Name.ToUpper())
> > > {
> > > case "OPCO":
> > > rptParameters[intParam].Value = this.Opco;
> > > break;
> > > case "CONFIGTYPEGROUP":
> > > rptParameters[intParam].Value = this.ConfigtypeGroup;
> > > break;
> > > case "CONFIGTYPE":
> > > rptParameters[intParam].Value = this.ConfigType;
> > > break;
> > > case "ERRORCODE":
> > > rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
> > > case "VISITDATETIME_FROM":
> > > rptParameters[intParam].Value = rp.DefaultValues.ToString())
> > > break;
> > > case "VISITDATETIME_TO":
> > > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> > > break;
> > > }
> > >
> > > intParam++;
> > > }
> > >
> > > firstPage = rs.Render( reportPath,
> > > format,
> > > null,
> > > deviceInfo,
> > > rptParameters,
> > > null,
> > > null,
> > > out encoding,
> > > out mimeType,
> > > out reportHistoryParameters,
> > > out warnings,
> > > out streamIDs);
>
>|||Sure you could, just dynamically allocate an array and add to that array as
you find parameters the user has set. Then use the dynamic array to pass to
render. The only problem with your code would be if the default values
changed between your call to GetReportParameters and Render. In your code
you will use the old value. If you are displaying this value to the user,
then this is probably what you want. If you are displaying something like
'Use default' then the user may not get the actual default at render time.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert" <Gert@.discussions.microsoft.com> wrote in message
news:C8CD66F1-CFB0-4A33-A90E-00919BB04B1B@.microsoft.com...
> I can't use your option because I have to loop through all the report
parameterss because I have different reports with different paramaters and I
don't know in the beginning of the program for which parameter the user will
select a value or not. If no value selected, the default value of the report
is retrieved.
> I use the following code and this works perfect. I use a Hash table(see
below) to store the paramaters and its values if they are selected.
> ********************************************************
> ReportParameter[] parameters = null;
> parameters = rs.GetReportParameters(reportPath, historyID, forRendering,
reportHistoryParameters, credentials);
> ParameterValue[] rptParameters = new
ParameterValue[parameters.GetLength(0)];
> int intParam = 0;
> foreach (ReportParameter rp in parameters)
> {
> rptParameters[intParam] = new ParameterValue();
> rptParameters[intParam].Name = rp.Name.ToUpper();
> if (hashParameters.ContainsKey(rp.Name.ToUpper()))
> {
> if (hashParameters[rp.Name].ToString() != "")
> rptParameters[intParam].Value =hashParameters[rp.Name].ToString();
> else
> rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
> }
> intParam++;
> }
> ********************************************************
> The Hash table is filled as follows:
> ********************************************************
> hashParameters = new Hashtable();
> hashParameters.Add("OPCO", this.Opco);
> hashParameters.Add("CONFIGTYPEGROUP", this.ConfigtypeGroup);
> hashParameters.Add("CONFIGTYPE", this.ConfigType);
> hashParameters.Add("ERRORCODE", "");
> hashParameters.Add("VISITDATETIME_FROM", "");
> hashParameters.Add("VISITDATETIME_TO", "");
> hashParameters.Add("COUNTERNUMBER", "");
> hashParameters.Add("ERRORDATETIME_FROM", "");
> hashParameters.Add("ERRORDATETIME_TO", "");
> hashParameters.Add("PARAMETERNUMBER", "");
> hashParameters.Add("MODIFICATIONNR", "");
> ********************************************************
>
> "Daniel Reib [MSFT]" wrote:
> > If you want the server to use the current default values, then don't
pass in
> > anything for this values. Only pass in the three parameters that don't
have
> > defaults and the server will then use the current default value during
> > rendering.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Gert" <Gert@.discussions.microsoft.com> wrote in message
> > news:66030BB4-9273-4AF1-B608-9B01F77ED7FD@.microsoft.com...
> > > I already found the solution.
> > > I have to write:
> > > rptParameters[intParam].Value = rp.DefaultValues[0].ToString();
> > > in stead of:
> > > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> > >
> > > This will give back the default value defined in the report
> > >
> > >
> > > "Gert" wrote:
> > >
> > > > My report has 6 parameters. 3 Of them are defined with default
values.
> > How can I via the webservice method use these default values? I am
building
> > the parameters dynamically. You can see that the first 3 parameters are
> > filled in depending on the value selected in a dropdown box. With the
last 3
> > I want to use the default values. Is this possible and how do I do this.
> > When with the first 3 parameters, no value is selected in the dropdown
box,
> > the default values shouls also be used but this is not yet programmed.
Can
> > anyone help me with this?
> > > >
> > > > ReportParameter[] parameters = null;
> > > >
> > > > parameters = rs.GetReportParameters(reportPath, historyID,
forRendering,
> > reportHistoryParameters, credentials);
> > > >
> > > > ParameterValue[] rptParameters = new
> > ParameterValue[parameters.GetLength(0)];
> > > >
> > > > int intParam = 0;
> > > > foreach (ReportParameter rp in parameters)
> > > > {
> > > > rptParameters[intParam] = new ParameterValue();
> > > > rptParameters[intParam].Name = rp.Name;
> > > > switch(rp.Name.ToUpper())
> > > > {
> > > > case "OPCO":
> > > > rptParameters[intParam].Value = this.Opco;
> > > > break;
> > > > case "CONFIGTYPEGROUP":
> > > > rptParameters[intParam].Value = this.ConfigtypeGroup;
> > > > break;
> > > > case "CONFIGTYPE":
> > > > rptParameters[intParam].Value = this.ConfigType;
> > > > break;
> > > > case "ERRORCODE":
> > > > rptParameters[intParam].Value = rp.DefaultValues.ToString(); break;
> > > > case "VISITDATETIME_FROM":
> > > > rptParameters[intParam].Value = rp.DefaultValues.ToString())
> > > > break;
> > > > case "VISITDATETIME_TO":
> > > > rptParameters[intParam].Value = rp.DefaultValues.ToString();
> > > > break;
> > > > }
> > > >
> > > > intParam++;
> > > > }
> > > >
> > > > firstPage = rs.Render( reportPath,
> > > > format,
> > > > null,
> > > > deviceInfo,
> > > > rptParameters,
> > > > null,
> > > > null,
> > > > out encoding,
> > > > out mimeType,
> > > > out reportHistoryParameters,
> > > > out warnings,
> > > > out streamIDs);
> >
> >
> >