Parse the field

I have a table that has a "Problem" field with the following data
Customer Service...Jam...Jam 5...Planned
Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
New Drive
Sale Made
I need to separate this "Problem" field into separate fields the issue1,
issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
) .
What would be the easiest way to do so within the SELECT statement ?here's a quick and dirty way of parsing out the data:::
declare @.myString varchar(2000) --Set to max length of field
declare @.Issue1 varchar(100) --Set to max length of any single entry
set @.myString = 'Customer Service...Jam...Jam 5...Planned '
set @.myString = 'Technical
Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting '
--set @.myString = 'New Drive'
declare @.Index int -- Holds the next position of delimeter
declare @.delimiter char(1) -- holds delimeter character
set @.delimiter = '.' -- set the delimer value
set @.Index = patindex( '%'+@.delimiter+'%',@.myString) -- get first instance
of delimeter
-- run a loop while we still have delimeters in the string
while @.Index>0
begin
-- get the next value
set @.Issue1 = substring(@.myString,1,@.Index-1)
-- do whatever you wish with the value print, save to table, array, etc)
print @.Issue1
-- parse the latest value from the current string
set @.myString = substring(@.myString,@.Index+3,len(@.myString))
-- get the next instance of the delimeter
set @.Index = patindex( '%'+@.delimiter+'%',@.myString)
end
-- Get the last value from what's left
set @.Issue1 = ltrim(rtrim(@.myString))
-- do whatever you wish with the value print, save to table, array, etc)
print @.Issue1
Copy and paste it into QA and you can see how it works for you
Tony
"agenda9533" wrote:
> I have a table that has a "Problem" field with the following data
> Customer Service...Jam...Jam 5...Planned
> Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
> New Drive
> Sale Made
> I need to separate this "Problem" field into separate fields the issue1,
> issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
> ) .
> What would be the easiest way to do so within the SELECT statement ?|||This is awesome!
Only thing, Ijust don't know how to run/call this script within RS.
As far as I know RS only recognize T-Sql SELECT statements (in order to
create a dataset).
Am I wrong?
"Logicalman" wrote:
> here's a quick and dirty way of parsing out the data:::
>
> declare @.myString varchar(2000) --Set to max length of field
> declare @.Issue1 varchar(100) --Set to max length of any single entry
> set @.myString = 'Customer Service...Jam...Jam 5...Planned '
> set @.myString = 'Technical
> Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting '
> --set @.myString = 'New Drive'
> declare @.Index int -- Holds the next position of delimeter
> declare @.delimiter char(1) -- holds delimeter character
> set @.delimiter = '.' -- set the delimer value
> set @.Index = patindex( '%'+@.delimiter+'%',@.myString) -- get first instance
> of delimeter
> -- run a loop while we still have delimeters in the string
> while @.Index>0
> begin
> -- get the next value
> set @.Issue1 = substring(@.myString,1,@.Index-1)
> -- do whatever you wish with the value print, save to table, array, etc)
> print @.Issue1
> -- parse the latest value from the current string
> set @.myString = substring(@.myString,@.Index+3,len(@.myString))
> -- get the next instance of the delimeter
> set @.Index = patindex( '%'+@.delimiter+'%',@.myString)
> end
> -- Get the last value from what's left
> set @.Issue1 = ltrim(rtrim(@.myString))
> -- do whatever you wish with the value print, save to table, array, etc)
> print @.Issue1
>
> Copy and paste it into QA and you can see how it works for you
> Tony
> "agenda9533" wrote:
> > I have a table that has a "Problem" field with the following data
> >
> > Customer Service...Jam...Jam 5...Planned
> >
> > Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
> > New Drive
> >
> > Sale Made
> >
> > I need to separate this "Problem" field into separate fields the issue1,
> > issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
> > ) .
> >
> > What would be the easiest way to do so within the SELECT statement ?|||My first suggestion would be to place this in a Stored Procedure, and call
the SProc as the dataset for the RS report.
"agenda9533" wrote:
> This is awesome!
> Only thing, Ijust don't know how to run/call this script within RS.
> As far as I know RS only recognize T-Sql SELECT statements (in order to
> create a dataset).
> Am I wrong?
> "Logicalman" wrote:
> > here's a quick and dirty way of parsing out the data:::
> >
> >
> > declare @.myString varchar(2000) --Set to max length of field
> > declare @.Issue1 varchar(100) --Set to max length of any single entry
> >
> > set @.myString = 'Customer Service...Jam...Jam 5...Planned '
> > set @.myString = 'Technical
> > Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting '
> > --set @.myString = 'New Drive'
> >
> > declare @.Index int -- Holds the next position of delimeter
> > declare @.delimiter char(1) -- holds delimeter character
> > set @.delimiter = '.' -- set the delimer value
> >
> > set @.Index = patindex( '%'+@.delimiter+'%',@.myString) -- get first instance
> > of delimeter
> > -- run a loop while we still have delimeters in the string
> > while @.Index>0
> > begin
> > -- get the next value
> > set @.Issue1 = substring(@.myString,1,@.Index-1)
> > -- do whatever you wish with the value print, save to table, array, etc)
> > print @.Issue1
> > -- parse the latest value from the current string
> > set @.myString = substring(@.myString,@.Index+3,len(@.myString))
> > -- get the next instance of the delimeter
> > set @.Index = patindex( '%'+@.delimiter+'%',@.myString)
> >
> > end
> > -- Get the last value from what's left
> > set @.Issue1 = ltrim(rtrim(@.myString))
> > -- do whatever you wish with the value print, save to table, array, etc)
> > print @.Issue1
> >
> >
> > Copy and paste it into QA and you can see how it works for you
> >
> > Tony
> >
> > "agenda9533" wrote:
> >
> > > I have a table that has a "Problem" field with the following data
> > >
> > > Customer Service...Jam...Jam 5...Planned
> > >
> > > Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
> > > New Drive
> > >
> > > Sale Made
> > >
> > > I need to separate this "Problem" field into separate fields the issue1,
> > > issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
> > > ) .
> > >
> > > What would be the easiest way to do so within the SELECT statement ?

Partitioned View Pruning Issue

I'm

using Sql Server 2000 Standard Edition,

service pack 3. I'm fairly new to

partitioned views and merge replication.

We have a 3-year set of monthly

tables with about 1 million rows per table from some code we inherited. To ease development, I'm trying to create a

local partitioned view. We wanted it to be UPDATE-able and be able to select

data by a date range or by id without incurring very much cost. The problem is

that my SELECT statements correctly prune the unneeded tables while my INSERT, UPDATE ,

and DELETE statements do not.

Here's our basic structure:

CREATE

TABLE [dbo].[Call_2004_01] (
[id] [varchar] (64) COLLATE

SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[callStartedOn] [datetime] NOT

NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT

[DF_Call_2004_01_rowguid] DEFAULT (newid()),
CONSTRAINT

[PK_Call_2004_01] PRIMARY KEY CLUSTERED
(
[id]
)

WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_Call_2004_01_id]

CHECK ([id] >= '200401' and [id] < '200402')
) ON

[PRIMARY]

CREATE TABLE [dbo].[Call_2004_02] (
[id] [varchar] (64)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[callStartedOn]

[datetime] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL

CONSTRAINT [DF_Call_2004_02_rowguid] DEFAULT (newid()),
CONSTRAINT

[PK_Call_2004_02] PRIMARY KEY CLUSTERED
(
[id]
)

WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_Call_2004_02_id]

CHECK ([id] >= '200402' and [id] < '200403')
) ON

[PRIMARY]

CREATE VIEW
Call
AS
SELECT * FROM

dbo.Call_2004_02
UNION ALL
SELECT * FROM

dbo.Call_2004_01
GO

The ids themselves contain the date information,

so we decided to use the id as the partition key. (I originally had the date and

the id as the primary key and it still didn't work.)

If you

run:

SET STATISTICS IO ON
SELECT * FROM Call WHERE [id] =

'20040104-fake-asdf'

You'll get the exact query plan and IO you would

expect. It only shows the Call_2004_01 table. However, if you run:

SET

STATISTICS IO ON
UPDATE Call SET [callStartedOn] = '2004-01-01 00:00:00.000'

WHERE [id] = '20040104-fake-asdf'

or

SET STATISTICS IO

ON
DELETE FROM Call WHERE [id] = '20040104-fake-asdf'

It scans all of

the tables in the view. If I add an index on top of the primary key, the

STATISTICS IO comes back as 0's for the unused table, but the query plan still

shows execution on them.

To add injury to insult, we have merge

replication set up for these tables. So, when the INSERT, UPDATE, or DELETE statement

occurs on the tables that it isn't supposed to run on, the triggers for those

tables fire. This becomes apparent when you look at the STATISTICS IO, query

plan, and/or the fact that a simple query which should take less than 1 second

is now taking at least 8.

The problem goes away, of course, when you

specify the table name instead of the view.

So, what am I missing here?

At this point, I'm just grasping at straws.

Also, I tried upgrading to service pack 4 just now and that didn't change anything.|||

It is a bug in SQL Server 2000 due to auto-parameterization. It has been fixed in SQL Server 2005 so your examples will work fine there. You can make it work in SQL Server 2000 by adding some bogus parameter to the WHERE clause of the UPDATE & DELETE statement.

declare @.p int

SET STATISTICS IO ON

UPDATE Call SET [callStartedOn] = '2004-01-01 00:00:00.000'

WHERE [id] = '20040104-fake-asdf' and @.p is null

go

declare @.p int

SET STATISTICS IO ON

DELETE FROM Call WHERE [id] = '20040104-fake-asdf' and @.p is null

go

To summarize, the partition pruning will not happen in cases where auto-parameterization of the statement occurs. This typically happens in cases where you use constants/literals in WHERE clause.

|||

Thanks. I'll give that a try when I get back to work tomorrow. However, I had originally been trying with a variable when I noticed the problem. Something like

DECLARE @.callId varchar(64)
SET @.callId = '20040104-fake-asdf'
DELETE FROM Call WHERE [id] = @.callId

Maybe the bogus data will help.

|||The bogus data helped some. Now it's behaving the same way it would if I manually created additional indexes on the PK. However, it's still not properly pruning the data out of the view. Here's the query I'm running and the output I'm getting.

DECLARE @.p bit
SET @.p = NULL

DECLARE @.callId varchar(64)
SET @.callId = '2004010100281315'

DELETE FROM Call WHERE [id] = @.callId AND @.p IS NULL

STATISTICS IO (I deleted quite a bit):
Table 'Call_2006_12'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
... [All other tables between are the same as the above] ...
Table 'Call_2004_02'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'Call_2004_01'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

(0 row(s) affected)

Table 'sysmergearticles'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.
Table 'MSmerge_replinfo'. Scan count 22, logical reads 44, physical reads 0, read-ahead reads 0.
Table 'sysmergesubscriptions'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'MSmerge_tombstone'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'MSmerge_contents'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sysmergearticles'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

(15 row(s) affected)

[the above (starting with the first "Table 'sysmergearticles'") repeats 35 more times]

SHOWPLAN_TEXT Results (again, edited for size):
|--Sequence
|--Clustered Index Delete(OBJECT:([Callbright].[dbo].[Call_2004_01].[PK_Call_2004_01]))
| |--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
| |--Filter(WHERE:(STARTUP EXPR([@.callId]<'200402' AND [@.callId]>='200401')))
| |--Clustered Index Seek(OBJECT:([Callbright].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]=[@.callId]) ORDERED FORWARD)
... [All other tables between are the same as the above] ...
|--Clustered Index Delete(OBJECT:([Callbright].[dbo].[Call_2006_12].[PK_Call_2006_12]))
|--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
|--Filter(WHERE:(STARTUP EXPR([@.callId]<'200701' AND [@.callId]>='200612')))
|--Clustered Index Seek(OBJECT:([Callbright].[dbo].[Call_2006_12].[PK_Call_2006_12]), SEEK:([Call_2006_12].[id]=[@.callId]) ORDERED FORWARD)

Also, I should mention that the Clustered Index Delete takes 2% of the execution time and performs 1 execution while the Clustered Index Seek takes 1% of the execution time and performs 0 executions on the tables that should be pruned.

Any ideas?|||

There are two ways in which the partitions can be eliminated and it depends on the type of query.

1. Partition elimination at compile-time - This will happen usually if you specify the partition key value as a constant in the WHERE clause. You can observe this behavior in the showplan output (estimated or actual execution plan)

2. Partition elimination at run-time - This will happen in cases where the partition key value is not known at compile-time or the plan needs to handle cases where the key could change between executions and so on. In this case, you will not see the partition(s) being eliminated in the showplan output (estimated or actual). You will however see STARTUP EXPR filters in the various branches. You can think of this as short-circuit mechanisms that gets evaluated at run-time and prevents that branch from executing.

So given these different plan strategies, the best way to determine which partition(s) are being eliminated is to look at the SET STATISTICS IO output and look for read counters with zero values to identify the partition(s) that are being eliminated. Another technique is to look at SET STATISTICS PROFILE ON (or SET STATISTICS XML PROFILE ON in SQL Server 2005) output. In this case, you will get the actual execution plan along with the execution statistics. You would specifically look at rows in the SET STATISTICS PROFILE output (In SQL Server 2000) for rows which have EXECUTES = 0 and/or ROWS = 0. This will tell you the partition(s) that were eliminated.

To summarize, from your showplan output the partition elimination is happening fine and it uses mechanism #2 described above. As for the replication tables, my guess is that it is a result of the replication trigger on the table that is propogating the deletes. Hope this helps.

|||I would have thought that if the partition was actually doing any pruning that the merge subscription triggers on the pruned tables wouldn't be firing. Is that wrong?|||Here's my query and SET STATISTICS PROFILE ON output. As you can see, it's still performing 3 executions per "pruned" table (and one of those is a delete). Shouldn't this be all zeros except for the Call_2004_01 table?

SET STATISTICS PROFILE ON
DECLARE @.bogusVariable bit
DELETE FROM Call WHERE [id] = '2004010100000000001' AND @.bogusVariable IS NULL

0 1 DELETE FROM Call WHERE [id] = '2004010100000000001' AND @.bogusVariable IS NULL 436 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 0.4912329 NULL NULL DELETE 0 NULL
0 1 |--Sequence 436 2 1 Sequence Sequence NULL NULL 1.0 0.0 3.6000001E-5 56 0.4912329 NULL NULL PLAN_ROW 0 1.0
0 1 |--Clustered Index Delete(OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01])) 436 3 2 Clustered Index Delete Delete OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01]) NULL 1.0 1.0161194E-2 0.000001 56 1.3445652E-2 NULL NULL PLAN_ROW 0 1.0
0 1 | |--Filter(WHERE:(STARTUP EXPR([@.bogusVariable]=NULL))) 436 5 3 Filter Filter WHERE:(STARTUP EXPR([@.bogusVariable]=NULL)) NULL 1.0 0.0 2.7999999E-7 311 3.2833579E-3 [Bmk1006] NULL PLAN_ROW 0 1.0
0 1 | |--Clustered Index Seek(OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]='2004010100000000001') ORDERED FORWARD) 436 6 5 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]='2004010100000000001') ORDERED FORWARD [Bmk1006] 1.0 3.2034749E-3 7.9603E-5 311 3.2830781E-3 [Bmk1006] NULL PLAN_ROW 0 1.0
0 1 |--Clustered Index Delete(OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02])) 436 9 2 Clustered Index Delete Delete OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02]) NULL 1.0 1.0161523E-2 0.000001 56 1.3445981E-2 NULL NULL PLAN_ROW 0 1.0
0 1 | |--Filter(WHERE:(STARTUP EXPR([@.bogusVariable]=NULL))) 436 11 9 Filter Filter WHERE:(STARTUP EXPR([@.bogusVariable]=NULL)) NULL 1.0 0.0 2.7999999E-7 307 3.2833579E-3 [Bmk1008] NULL PLAN_ROW 0 1.0
0 1 | |--Filter(WHERE:(STARTUP EXPR(0))) 436 12 11 Filter Filter WHERE:(STARTUP EXPR(0)) NULL 1.0 3.2034749E-3 7.9603E-5 307 3.2830781E-3 [Bmk1008] NULL PLAN_ROW 0 1.0
0 0 | |--Clustered Index Seek(OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02]), SEEK:([Call_2004_02].[id]='2004010100000000001') ORDERED FORWARD) 436 13 12 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[Call_2004_02].[PK_Call_2004_02]), SEEK:([Call_2004_02].[id]='2004010100000000001') ORDERED FORWARD [Bmk1008] 1.0 3.2034749E-3 7.9603E-5 307 3.2830781E-3 [Bmk1008] NULL PLAN_ROW 0 1.0
... [The rest of the tables are here and mirror the above statement] ...
0 1 |--Clustered Index Delete(OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12])) 436 281 2 Clustered Index Delete Delete OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12]) NULL 1.0 1.6756756E-2 0.000001 56 2.0041214E-2 NULL NULL PLAN_ROW 0 1.0
0 1 |--Filter(WHERE:(STARTUP EXPR([@.bogusVariable]=NULL))) 436 283 281 Filter Filter WHERE:(STARTUP EXPR([@.bogusVariable]=NULL)) NULL 1.0 0.0 2.7999999E-7 324 3.2833579E-3 [Bmk1552] NULL PLAN_ROW 0 1.0
0 1 |--Filter(WHERE:(STARTUP EXPR(0))) 436 284 283 Filter Filter WHERE:(STARTUP EXPR(0)) NULL 1.0 3.2034749E-3 7.9603E-5 324 3.2830781E-3 [Bmk1552] NULL PLAN_ROW 0 1.0
0 0 |--Clustered Index Seek(OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12]), SEEK:([Call_2006_12].[id]='2004010100000000001') ORDERED FORWARD) 436 285 284 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[Call_2006_12].[PK_Call_2006_12]), SEEK:([Call_2006_12].[id]='2004010100000000001') ORDERED FORWARD [Bmk1552] 1.0 3.2034749E-3 7.9603E-5 324 3.2830781E-3 [Bmk1552] NULL PLAN_ROW 0 1.0

After this, there's a bunch of stuff for merge replication which really means the triggers on the "pruned" tables fired. To me, this indicates some kind of a pruning error (the table definitions are wrong, sql server 2000 is wrong, etc.). Am I wrong in thinking that? Does Sql Server 2000 execute insert/update/delete statements this way by design?|||

Don't look at the DELETE operator executes only. Start with the clustered index seek for each partition and you can see how many rows it produces & the number of executes. This way you can figure out if a parent operator (delete in this case) did any work. I tried your example with some sample data and following DELETE statement:

-- I added one row in each partition:

DECLARE @.p bit
DELETE FROM Call WHERE [id] = '20040104-fake-asdf' and @.p is null

-- set statistics io output

/*

Table 'Call_2004_01'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'Call_2004_02'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
*/

-- set statistics profile partial output

/*

Rows Executes StmtText

- -- -
1 1 DELETE FROM Call WHERE [id] = '20040104-fake-asdf' COLLATE SQL_Latin1_General_CP1_CI_AS and @.p is null
1 1 |--Sequence
0 1 |--Clustered Index Delete(OBJECT:([MyTestDB].[dbo].[Call_2004_02].[PK_Call_2004_02]))
0 1 | |--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
0 1 | |--Filter(WHERE:(STARTUP EXPR(0)))
0 0 | |--Clustered Index Seek(OBJECT:([MyTestDB].[dbo].[Call_2004_02].[PK_Call_2004_02]), SEEK:([Call_2004_02].[id]='20040104-fake-asdf') ORDERED FORWARD)
1 1 |--Clustered Index Delete(OBJECT:([MyTestDB].[dbo].[Call_2004_01].[PK_Call_2004_01]))
1 1 |--Filter(WHERE:(STARTUP EXPR([@.p]=NULL)))
1 1 |--Clustered Index Seek(OBJECT:([MyTestDB].[dbo].[Call_2004_01].[PK_Call_2004_01]), SEEK:([Call_2004_01].[id]='20040104-fake-asdf') ORDERED FORWARD)

*/

Now, zero rows went through the delete operators but we will still fire the triggers on the partition tables. The triggers should be intelligent enough to perform no work in this case. This is also no different than doing DELETE t WHERE 1=0 which will still fire the trigger. This may seem kind of strange but this is how triggers work. There is one exception in case of distributed partitioned views where the triggers may not fire on the remote tables. Hope this helps explain the behavior you are seeing.

And you should start a different thread in the replication newsgroup about their trigger behavior in this case.

|||

I guess I figured that the view would prevent any execution on a table that didn't meet the constraints, but I can see how your example is related to this situation. The subscription triggers are smart enough not to perform any inserts, but they do perform enough work to cause problems. I'd considered modifying them to make them do even less work (where it would check the Inserted and Deleted tables for any rows before executing), but maintenance on those triggers seemed painful since they're auto-generated, so instead, I added INSTEAD OF triggers to the view that prune the tables the way I expected them to be pruned.

Thanks.

Parameters via a web service

Hy,

I have to build a report. this report has to be call by a web service. My method to call this report is :

---

[WebMethod]

public void Amende()

{

ReportingService rs = new localhost.ReportingService();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

byte[] ResultStream; // bytearray for result stream

string[] StreamIdentifiers; // string array for stream idenfiers

string OptionalParam = null; // string out param for optional parameters

ParameterValue[] optionalParams = null; // parametervalue array for optional parameters

Warning[] optionalWarnings = null; // warning array for optional warnings

ResultStream = rs.Render("/SwatFillingDocuments/AMAD AYDIN 1198 2005 MD", "PDF", null,

"<DeviceInfo><StreamRoot>/RSWebServiceXS/</StreamRoot></DeviceInfo>", null, null,

null, out OptionalParam, out OptionalParam, out optionalParams,

out optionalWarnings, out StreamIdentifiers);

// Write the report to Response

HttpContext.Current.Response.BinaryWrite(ResultStream);

}

---

But in my report I have a parameter. And I have to give a value at this parameter via my web service. Is it possible to do that with the method Render?

oki I found how to give a parameter. So my code looks like that now:

[WebMethod]

public void Amende( string dossierId )

{

ReportingService rs = new localhost.ReportingService();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

byte[] resultStream; // bytearray for result stream

string[] streamIdentifiers; // string array for stream idenfiers

string optionalParam = null; // string out param for optional parameters

ParameterValue[] optionalParams = null; // parametervalue array for optional parameters

// Prparation de la valeur passe en paramètre

ParameterValue[] parameters = new ParameterValue[1];

parameters[0] = new ParameterValue();

parameters[0].Name = "DossiersId";

parameters[0].Value = dossierId;

DataSourceCredentials[] credentials = null;

string showHideToggle = null;

string historyID = null;

Warning[] optionalWarnings = null; // warning array for optional warnings

resultStream = rs.Render("/SwatFillingDocuments/AMAD AYDIN 1198 2005 MD", "PDF",

historyID, @."False", parameters,credentials,showHideToggle,out optionalParam,

out optionalParam,out optionalParams, out optionalWarnings,out streamIdentifiers);

HttpContext.Current.Response.BinaryWrite(resultStream);

}

But when I put a parameter in my web service I have an error :

System.Net.WebException: The request failed with HTTP status 400: Bad Request.

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at localhost.ReportingService.Render(String Report, String Format, String HistoryID, String DeviceInfo, ParameterValue[] Parameters, DataSourceCredentials[] Credentials, String ShowHideToggle, String& Encoding, String& MimeType, ParameterValue[]& ParametersUsed, Warning[]& Warnings, String[]& StreamIds) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\listreports\5d760f1a\6c0731c0\App_WebReferences.n1-mytpw.0.cs:line 1706

at Service.Amende(String dossierId) in c:\projects\ListReports\App_Code\Service.cs:line 36

Can you help me please? If I use this mothed without parameter it works but when i try to give a parameter it fails.Thank you|||

I do this in VB. This writes the report out to a disk after you get the byte array. This code is from inside a class that I wrap the process in so I havent't looked at it in awhile but it has been working without errors.

Public Sub RenderWriter()

Dim parameters() As MyReportService.ParameterValue

parameters = GetParameters()

Dim encoding As String

Dim mimeType As String

Dim parametersUsed() As MyReportService.ParameterValue

Dim warnings() As MyReportService.Warning

Dim streamIds() As String

'render the report

Dim data() As Byte

'data = _rs.Render(Me._ReportItem.Path, _Format.Name, Nothing, Nothing, parameters, Nothing, Nothing, encoding, mimeType, parametersUsed, warnings, streamIds)

data = _rs.Render(ReportItem.Path, Format.Name, Nothing, Nothing, parameters, Nothing, Nothing, encoding, mimeType, parametersUsed, warnings, streamIds)

'//create a file stream to write the output

'Dim fileName As String = _OutputPath & "\" & _ReportItem.Name & _Format.Extension

Write(data)

End Sub

Private Sub Write(ByVal data() As Byte)

Dim fs As New System.IO.FileStream(Me.FileName, System.IO.FileMode.OpenOrCreate)

Dim writer As New System.IO.BinaryWriter(fs)

writer.Write(Data, 0, Data.Length)

writer.Close()

fs.Close()

End Sub

Private Function GetParameters() As MyReportService.ParameterValue()

Dim i As Integer

Dim len As Integer = _ParamValues.Count - 1

Dim returnValues(len) As MyReportService.ParameterValue

For i = 0 To len

returnValues(i) = New MyReportService.ParameterValue

returnValues(i).Name = _ParamValues.Item(i).Name

returnValues(i).Value = _ParamValues.Item(i).Value

Next i

Return returnValues

End Function