parsing XML string in a text field

I am trying to build a query on a SQL2000 text field which stores XML
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4</
usedby></history>
<androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegr oups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4
</usedby></history>
>

parsing XML string in a text field

I am trying to build a query on a SQL2000 text field which stores XML
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>nul
l</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</
threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user4</
usedby></history><androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegroups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>
use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>use
r2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er4
</usedby></history>
>

Parent Child relation ship table in SQL Express

I want to build Parent child relation . i have two aproaches .I would like to know which is the best solution ?

1)1st method:-

Parent table with parent id . Child table with child id and parent id.Foreign key relationship exists between parent and child tables with cascade delete option enabled.

Parent TAble

Id name

1 XYZ

Child table

id name parent id

1 abc 1

2 qwe 1

2)2nd method

table with id and parent ID. Top level element will have null value in table. eg

id name parent id

1 xyz

2 abc 1

3 qwe 2

4 adf 1

Retrieve data using recursive queries supported in SQL Express.

Which is the best solution to store parent child relationship?

To know which is better I think I'd have to know what you're doing with the data. Are you developing a deep family tree? A shallow workplace report-to list? How much data? How big are the operations?

Regarding your proposed solutions, your first solution separates people into two tables. That may make it hard to do a single list operation, such as a full roster including parents and children. The second solution leaves you doing recursive SQL to get a hierarchical list of everyone, say if a is parent of b who is parent of c who is parent of d. To get a full list the SQL can be annoying.

There's another possible configuration as well using a join table. You have one table of people, another of relationships. The people table is entirely simple. The relationship table is simply two foreign keys into the people table.

Table USER:

id Name

1 Bill

2 Bob

3 Jim

Table RELATIONSHIP:

parent child

1 2

3 1

Parent Child Dimension

Hi

I have a little problem.

I build a Parent - Child dimension an I need to change the default listing of this kind of dimensions to display some information from another column than ID and ParentID.

Is there eny way to solve that?

Tanks in advance for your help.

(AS 2005; Excel 2007)

You can change this in the name column for the the parent child primary key. Check the attribute pane in the dimension editor.

Regards

Thomas Ivarsson

|||thank you Thomas for your quick answer

Parameters with ODBC server

Hi all,
I have to build a report from SQL tables thru an ODBC server. When I
try to pass (nonnamed) parameters to the report, the SQL request runs
but returns nothing, as if the value was not understood. I have put
"?" in the SQL request in place of the parameter.
Do I need to do something else ? What is the format of the capture in
the run-time parameter dialog box : are quotes needed, double quotes,
or other character ?
Thanks ODBC users !
Jean-MarcI'm not sure what you mean by non-named parameters. RS uses the @. format
for parameters in queries, like so:
... WHERE FieldName = @.ParameterName
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> Hi all,
> I have to build a report from SQL tables thru an ODBC server. When I
> try to pass (nonnamed) parameters to the report, the SQL request runs
> but returns nothing, as if the value was not understood. I have put
> "?" in the SQL request in place of the parameter.
> Do I need to do something else ? What is the format of the capture in
> the run-time parameter dialog box : are quotes needed, double quotes,
> or other character ?
> Thanks ODBC users !
> Jean-Marc|||Umm, no it does not. It uses that for SQL Server but otherwise it needs
unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
It sounds to me like you are trying to put in the values in the dialog box
that pops up with you hit the exclamation point. When prompted by the dialog
box do not put in any quotes (single or double) just put the value in.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you mean by non-named parameters. RS uses the @. format
> for parameters in queries, like so:
> ... WHERE FieldName = @.ParameterName
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> > Hi all,
> >
> > I have to build a report from SQL tables thru an ODBC server. When I
> > try to pass (nonnamed) parameters to the report, the SQL request runs
> > but returns nothing, as if the value was not understood. I have put
> > "?" in the SQL request in place of the parameter.
> > Do I need to do something else ? What is the format of the capture in
> > the run-time parameter dialog box : are quotes needed, double quotes,
> > or other character ?
> >
> > Thanks ODBC users !
> >
> > Jean-Marc
>|||Thanks for the correction, Bruce.
Rats! Now I've got more work to do when we port to Oracle.
...sigh...
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> Umm, no it does not. It uses that for SQL Server but otherwise it needs
> unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> It sounds to me like you are trying to put in the values in the dialog box
> that pops up with you hit the exclamation point. When prompted by the
> dialog
> box do not put in any quotes (single or double) just put the value in.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
>> I'm not sure what you mean by non-named parameters. RS uses the @. format
>> for parameters in queries, like so:
>> ... WHERE FieldName = @.ParameterName
>> --
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
>> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
>> > Hi all,
>> >
>> > I have to build a report from SQL tables thru an ODBC server. When I
>> > try to pass (nonnamed) parameters to the report, the SQL request runs
>> > but returns nothing, as if the value was not understood. I have put
>> > "?" in the SQL request in place of the parameter.
>> > Do I need to do something else ? What is the format of the capture in
>> > the run-time parameter dialog box : are quotes needed, double quotes,
>> > or other character ?
>> >
>> > Thanks ODBC users !
>> >
>> > Jean-Marc
>>
>|||I'm not sure about Oracle. Oracle might be the same and it might not. The
reason is that Oracle is a special case. Here is a tidbit for you. Only
Oracle and SQL Server use the dotnet managed provider in RS. But, wait, it
is not that simple. When you use the GUI query designer it uses the OLEDB
provider because the GUI query designer knows nothing about dotnet. But, if
you go to the generic designer it uses the managed provider. At runtime it
uses the managed provider. The reason I mention this is two fold. First, so
all testing is done with the same provider as used at runtime I would
recommend always going to the generic query designer. Second, I'm not sure
whether managed provider uses named parameters or not. If it does then you
will not have to change this.
One last point, the next version should have a query designer that knows
about dotnet (V2, not SP2).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> Thanks for the correction, Bruce.
> Rats! Now I've got more work to do when we port to Oracle.
> ...sigh...
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> > Umm, no it does not. It uses that for SQL Server but otherwise it needs
> > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> >
> > It sounds to me like you are trying to put in the values in the dialog
box
> > that pops up with you hit the exclamation point. When prompted by the
> > dialog
> > box do not put in any quotes (single or double) just put the value in.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> >> I'm not sure what you mean by non-named parameters. RS uses the @.
format
> >> for parameters in queries, like so:
> >>
> >> ... WHERE FieldName = @.ParameterName
> >>
> >> --
> >> '(' Jeff A. Stucker
> >> \
> >>
> >> Business Intelligence
> >> www.criadvantage.com
> >> ---
> >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> >> > Hi all,
> >> >
> >> > I have to build a report from SQL tables thru an ODBC server. When I
> >> > try to pass (nonnamed) parameters to the report, the SQL request runs
> >> > but returns nothing, as if the value was not understood. I have put
> >> > "?" in the SQL request in place of the parameter.
> >> > Do I need to do something else ? What is the format of the capture in
> >> > the run-time parameter dialog box : are quotes needed, double quotes,
> >> > or other character ?
> >> >
> >> > Thanks ODBC users !
> >> >
> >> > Jean-Marc
> >>
> >>
> >
> >
>|||The syntax used by the managed Oracle provider to mark named parameters is a
colon (":") instead of @.. This is nothing specific to RS, this is just how
the data provider works.
E.g., select * from emp where deptno = :Dept
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm not sure about Oracle. Oracle might be the same and it might not. The
> reason is that Oracle is a special case. Here is a tidbit for you. Only
> Oracle and SQL Server use the dotnet managed provider in RS. But, wait, it
> is not that simple. When you use the GUI query designer it uses the OLEDB
> provider because the GUI query designer knows nothing about dotnet. But,
if
> you go to the generic designer it uses the managed provider. At runtime
it
> uses the managed provider. The reason I mention this is two fold. First,
so
> all testing is done with the same provider as used at runtime I would
> recommend always going to the generic query designer. Second, I'm not sure
> whether managed provider uses named parameters or not. If it does then you
> will not have to change this.
> One last point, the next version should have a query designer that knows
> about dotnet (V2, not SP2).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> > Thanks for the correction, Bruce.
> >
> > Rats! Now I've got more work to do when we port to Oracle.
> >
> > ...sigh...
> > --
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> > > Umm, no it does not. It uses that for SQL Server but otherwise it
needs
> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> > >
> > > It sounds to me like you are trying to put in the values in the dialog
> box
> > > that pops up with you hit the exclamation point. When prompted by the
> > > dialog
> > > box do not put in any quotes (single or double) just put the value in.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> > >> I'm not sure what you mean by non-named parameters. RS uses the @.
> format
> > >> for parameters in queries, like so:
> > >>
> > >> ... WHERE FieldName = @.ParameterName
> > >>
> > >> --
> > >> '(' Jeff A. Stucker
> > >> \
> > >>
> > >> Business Intelligence
> > >> www.criadvantage.com
> > >> ---
> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> > >> > Hi all,
> > >> >
> > >> > I have to build a report from SQL tables thru an ODBC server. When
I
> > >> > try to pass (nonnamed) parameters to the report, the SQL request
runs
> > >> > but returns nothing, as if the value was not understood. I have put
> > >> > "?" in the SQL request in place of the parameter.
> > >> > Do I need to do something else ? What is the format of the capture
in
> > >> > the run-time parameter dialog box : are quotes needed, double
quotes,
> > >> > or other character ?
> > >> >
> > >> > Thanks ODBC users !
> > >> >
> > >> > Jean-Marc
> > >>
> > >>
> > >
> > >
> >
> >
>|||So the big question is: What's the best way to manage reports that need to
work against both SQL Server and Oracle databases, depending where they are
deployed? (The database structure will be identical, but the platform is
customer-specific.)
Thanks,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eiNx1TC2EHA.2012@.TK2MSFTNGP15.phx.gbl...
> The syntax used by the managed Oracle provider to mark named parameters is
> a
> colon (":") instead of @.. This is nothing specific to RS, this is just how
> the data provider works.
> E.g., select * from emp where deptno = :Dept
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
>> I'm not sure about Oracle. Oracle might be the same and it might not. The
>> reason is that Oracle is a special case. Here is a tidbit for you. Only
>> Oracle and SQL Server use the dotnet managed provider in RS. But, wait,
>> it
>> is not that simple. When you use the GUI query designer it uses the OLEDB
>> provider because the GUI query designer knows nothing about dotnet. But,
> if
>> you go to the generic designer it uses the managed provider. At runtime
> it
>> uses the managed provider. The reason I mention this is two fold. First,
> so
>> all testing is done with the same provider as used at runtime I would
>> recommend always going to the generic query designer. Second, I'm not
>> sure
>> whether managed provider uses named parameters or not. If it does then
>> you
>> will not have to change this.
>> One last point, the next version should have a query designer that knows
>> about dotnet (V2, not SP2).
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
>> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
>> > Thanks for the correction, Bruce.
>> >
>> > Rats! Now I've got more work to do when we port to Oracle.
>> >
>> > ...sigh...
>> > --
>> > '(' Jeff A. Stucker
>> > \
>> >
>> > Business Intelligence
>> > www.criadvantage.com
>> > ---
>> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
>> > > Umm, no it does not. It uses that for SQL Server but otherwise it
> needs
>> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
>> > >
>> > > It sounds to me like you are trying to put in the values in the
>> > > dialog
>> box
>> > > that pops up with you hit the exclamation point. When prompted by the
>> > > dialog
>> > > box do not put in any quotes (single or double) just put the value
>> > > in.
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > >
>> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
>> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
>> > >> I'm not sure what you mean by non-named parameters. RS uses the @.
>> format
>> > >> for parameters in queries, like so:
>> > >>
>> > >> ... WHERE FieldName = @.ParameterName
>> > >>
>> > >> --
>> > >> '(' Jeff A. Stucker
>> > >> \
>> > >>
>> > >> Business Intelligence
>> > >> www.criadvantage.com
>> > >> ---
>> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
>> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
>> > >> > Hi all,
>> > >> >
>> > >> > I have to build a report from SQL tables thru an ODBC server. When
> I
>> > >> > try to pass (nonnamed) parameters to the report, the SQL request
> runs
>> > >> > but returns nothing, as if the value was not understood. I have
>> > >> > put
>> > >> > "?" in the SQL request in place of the parameter.
>> > >> > Do I need to do something else ? What is the format of the capture
> in
>> > >> > the run-time parameter dialog box : are quotes needed, double
> quotes,
>> > >> > or other character ?
>> > >> >
>> > >> > Thanks ODBC users !
>> > >> >
>> > >> > Jean-Marc
>> > >>
>> > >>
>> > >
>> > >
>> >
>> >
>>
>|||Hi Jeff,
Did you get any answers to your big question? I find myself in the exact
same situation (except its either Sql Server or Sybase)..I was just curious
what you did to solve this problem...
Thanks...
--Aparna.
"Jeff A. Stucker" wrote:
> So the big question is: What's the best way to manage reports that need to
> work against both SQL Server and Oracle databases, depending where they are
> deployed? (The database structure will be identical, but the platform is
> customer-specific.)
> Thanks,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:eiNx1TC2EHA.2012@.TK2MSFTNGP15.phx.gbl...
> > The syntax used by the managed Oracle provider to mark named parameters is
> > a
> > colon (":") instead of @.. This is nothing specific to RS, this is just how
> > the data provider works.
> > E.g., select * from emp where deptno = :Dept
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
> >> I'm not sure about Oracle. Oracle might be the same and it might not. The
> >> reason is that Oracle is a special case. Here is a tidbit for you. Only
> >> Oracle and SQL Server use the dotnet managed provider in RS. But, wait,
> >> it
> >> is not that simple. When you use the GUI query designer it uses the OLEDB
> >> provider because the GUI query designer knows nothing about dotnet. But,
> > if
> >> you go to the generic designer it uses the managed provider. At runtime
> > it
> >> uses the managed provider. The reason I mention this is two fold. First,
> > so
> >> all testing is done with the same provider as used at runtime I would
> >> recommend always going to the generic query designer. Second, I'm not
> >> sure
> >> whether managed provider uses named parameters or not. If it does then
> >> you
> >> will not have to change this.
> >>
> >> One last point, the next version should have a query designer that knows
> >> about dotnet (V2, not SP2).
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> >> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> >> > Thanks for the correction, Bruce.
> >> >
> >> > Rats! Now I've got more work to do when we port to Oracle.
> >> >
> >> > ...sigh...
> >> > --
> >> > '(' Jeff A. Stucker
> >> > \
> >> >
> >> > Business Intelligence
> >> > www.criadvantage.com
> >> > ---
> >> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> >> > > Umm, no it does not. It uses that for SQL Server but otherwise it
> > needs
> >> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> >> > >
> >> > > It sounds to me like you are trying to put in the values in the
> >> > > dialog
> >> box
> >> > > that pops up with you hit the exclamation point. When prompted by the
> >> > > dialog
> >> > > box do not put in any quotes (single or double) just put the value
> >> > > in.
> >> > >
> >> > > --
> >> > > Bruce Loehle-Conger
> >> > > MVP SQL Server Reporting Services
> >> > >
> >> > >
> >> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> >> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> >> > >> I'm not sure what you mean by non-named parameters. RS uses the @.
> >> format
> >> > >> for parameters in queries, like so:
> >> > >>
> >> > >> ... WHERE FieldName = @.ParameterName
> >> > >>
> >> > >> --
> >> > >> '(' Jeff A. Stucker
> >> > >> \
> >> > >>
> >> > >> Business Intelligence
> >> > >> www.criadvantage.com
> >> > >> ---
> >> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> >> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> >> > >> > Hi all,
> >> > >> >
> >> > >> > I have to build a report from SQL tables thru an ODBC server. When
> > I
> >> > >> > try to pass (nonnamed) parameters to the report, the SQL request
> > runs
> >> > >> > but returns nothing, as if the value was not understood. I have
> >> > >> > put
> >> > >> > "?" in the SQL request in place of the parameter.
> >> > >> > Do I need to do something else ? What is the format of the capture
> > in
> >> > >> > the run-time parameter dialog box : are quotes needed, double
> > quotes,
> >> > >> > or other character ?
> >> > >> >
> >> > >> > Thanks ODBC users !
> >> > >> >
> >> > >> > Jean-Marc
> >> > >>
> >> > >>
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>
>|||I have the same issue. I have a whole lot of reports that are going against
a process control database in Sybase and will be switched to go against a
datamart in SQL Server. I would love to have an easy way to switch it back
if I needed to. Putting aside having compatible SQL (Sybase does not have
Top and some other SQL) I don't see an easy way to do this. I plan on
manually changing the reports by going into the RDL and changing it (rather
than doing it from the IDE). The only way I can see this would work is if
you make your dataset sql be based on an expression. You can use the generic
query screen and do this:
= "Select * from something where somefield = " & Parameters!ParamName.value
But if you have a date or character field you have to do this:
= "Select * from something where somefield = '" & Parameters!ParamName.value
& "'" (that is a double quote, a single quote, a double quote)
So it is slow and potentially buggy. When done though if the SQL created is
compatible this will work against any databases.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:C4540199-F9D9-4D37-A491-BC5EE4AEE91A@.microsoft.com...
> Hi Jeff,
> Did you get any answers to your big question? I find myself in the exact
> same situation (except its either Sql Server or Sybase)..I was just
curious
> what you did to solve this problem...
> Thanks...
> --Aparna.
> "Jeff A. Stucker" wrote:
> > So the big question is: What's the best way to manage reports that need
to
> > work against both SQL Server and Oracle databases, depending where they
are
> > deployed? (The database structure will be identical, but the platform
is
> > customer-specific.)
> >
> > Thanks,
> >
> > --
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:eiNx1TC2EHA.2012@.TK2MSFTNGP15.phx.gbl...
> > > The syntax used by the managed Oracle provider to mark named
parameters is
> > > a
> > > colon (":") instead of @.. This is nothing specific to RS, this is just
how
> > > the data provider works.
> > > E.g., select * from emp where deptno = :Dept
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > >
> > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
> > >> I'm not sure about Oracle. Oracle might be the same and it might not.
The
> > >> reason is that Oracle is a special case. Here is a tidbit for you.
Only
> > >> Oracle and SQL Server use the dotnet managed provider in RS. But,
wait,
> > >> it
> > >> is not that simple. When you use the GUI query designer it uses the
OLEDB
> > >> provider because the GUI query designer knows nothing about dotnet.
But,
> > > if
> > >> you go to the generic designer it uses the managed provider. At
runtime
> > > it
> > >> uses the managed provider. The reason I mention this is two fold.
First,
> > > so
> > >> all testing is done with the same provider as used at runtime I would
> > >> recommend always going to the generic query designer. Second, I'm not
> > >> sure
> > >> whether managed provider uses named parameters or not. If it does
then
> > >> you
> > >> will not have to change this.
> > >>
> > >> One last point, the next version should have a query designer that
knows
> > >> about dotnet (V2, not SP2).
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >>
> > >> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > >> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> > >> > Thanks for the correction, Bruce.
> > >> >
> > >> > Rats! Now I've got more work to do when we port to Oracle.
> > >> >
> > >> > ...sigh...
> > >> > --
> > >> > '(' Jeff A. Stucker
> > >> > \
> > >> >
> > >> > Business Intelligence
> > >> > www.criadvantage.com
> > >> > ---
> > >> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > >> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> > >> > > Umm, no it does not. It uses that for SQL Server but otherwise it
> > > needs
> > >> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> > >> > >
> > >> > > It sounds to me like you are trying to put in the values in the
> > >> > > dialog
> > >> box
> > >> > > that pops up with you hit the exclamation point. When prompted by
the
> > >> > > dialog
> > >> > > box do not put in any quotes (single or double) just put the
value
> > >> > > in.
> > >> > >
> > >> > > --
> > >> > > Bruce Loehle-Conger
> > >> > > MVP SQL Server Reporting Services
> > >> > >
> > >> > >
> > >> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > >> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> > >> > >> I'm not sure what you mean by non-named parameters. RS uses the
@.
> > >> format
> > >> > >> for parameters in queries, like so:
> > >> > >>
> > >> > >> ... WHERE FieldName = @.ParameterName
> > >> > >>
> > >> > >> --
> > >> > >> '(' Jeff A. Stucker
> > >> > >> \
> > >> > >>
> > >> > >> Business Intelligence
> > >> > >> www.criadvantage.com
> > >> > >> ---
> > >> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> > >> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> > >> > >> > Hi all,
> > >> > >> >
> > >> > >> > I have to build a report from SQL tables thru an ODBC server.
When
> > > I
> > >> > >> > try to pass (nonnamed) parameters to the report, the SQL
request
> > > runs
> > >> > >> > but returns nothing, as if the value was not understood. I
have
> > >> > >> > put
> > >> > >> > "?" in the SQL request in place of the parameter.
> > >> > >> > Do I need to do something else ? What is the format of the
capture
> > > in
> > >> > >> > the run-time parameter dialog box : are quotes needed, double
> > > quotes,
> > >> > >> > or other character ?
> > >> > >> >
> > >> > >> > Thanks ODBC users !
> > >> > >> >
> > >> > >> > Jean-Marc
> > >> > >>
> > >> > >>
> > >> > >
> > >> > >
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
> >|||Hey Bruce, Thanks so much! You know, I never even thought of using
expressions within the Query Designer... You are right that this approach is
not very elegant, but its good to know that there is a last resort...I think
I am going to investigate using a custom data extension to just read a
dataset produced by an external proram....and see if that can help in
anyway...
But once again, thanks for the tip...
"Bruce L-C [MVP]" wrote:
> I have the same issue. I have a whole lot of reports that are going against
> a process control database in Sybase and will be switched to go against a
> datamart in SQL Server. I would love to have an easy way to switch it back
> if I needed to. Putting aside having compatible SQL (Sybase does not have
> Top and some other SQL) I don't see an easy way to do this. I plan on
> manually changing the reports by going into the RDL and changing it (rather
> than doing it from the IDE). The only way I can see this would work is if
> you make your dataset sql be based on an expression. You can use the generic
> query screen and do this:
> = "Select * from something where somefield = " & Parameters!ParamName.value
> But if you have a date or character field you have to do this:
> = "Select * from something where somefield = '" & Parameters!ParamName.value
> & "'" (that is a double quote, a single quote, a double quote)
> So it is slow and potentially buggy. When done though if the SQL created is
> compatible this will work against any databases.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> news:C4540199-F9D9-4D37-A491-BC5EE4AEE91A@.microsoft.com...
> > Hi Jeff,
> > Did you get any answers to your big question? I find myself in the exact
> > same situation (except its either Sql Server or Sybase)..I was just
> curious
> > what you did to solve this problem...
> > Thanks...
> > --Aparna.
> >
> > "Jeff A. Stucker" wrote:
> >
> > > So the big question is: What's the best way to manage reports that need
> to
> > > work against both SQL Server and Oracle databases, depending where they
> are
> > > deployed? (The database structure will be identical, but the platform
> is
> > > customer-specific.)
> > >
> > > Thanks,
> > >
> > > --
> > > '(' Jeff A. Stucker
> > > \
> > >
> > > Business Intelligence
> > > www.criadvantage.com
> > > ---
> > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > > news:eiNx1TC2EHA.2012@.TK2MSFTNGP15.phx.gbl...
> > > > The syntax used by the managed Oracle provider to mark named
> parameters is
> > > > a
> > > > colon (":") instead of @.. This is nothing specific to RS, this is just
> how
> > > > the data provider works.
> > > > E.g., select * from emp where deptno = :Dept
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > > rights.
> > > >
> > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > > news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
> > > >> I'm not sure about Oracle. Oracle might be the same and it might not.
> The
> > > >> reason is that Oracle is a special case. Here is a tidbit for you.
> Only
> > > >> Oracle and SQL Server use the dotnet managed provider in RS. But,
> wait,
> > > >> it
> > > >> is not that simple. When you use the GUI query designer it uses the
> OLEDB
> > > >> provider because the GUI query designer knows nothing about dotnet.
> But,
> > > > if
> > > >> you go to the generic designer it uses the managed provider. At
> runtime
> > > > it
> > > >> uses the managed provider. The reason I mention this is two fold.
> First,
> > > > so
> > > >> all testing is done with the same provider as used at runtime I would
> > > >> recommend always going to the generic query designer. Second, I'm not
> > > >> sure
> > > >> whether managed provider uses named parameters or not. If it does
> then
> > > >> you
> > > >> will not have to change this.
> > > >>
> > > >> One last point, the next version should have a query designer that
> knows
> > > >> about dotnet (V2, not SP2).
> > > >>
> > > >> --
> > > >> Bruce Loehle-Conger
> > > >> MVP SQL Server Reporting Services
> > > >>
> > > >>
> > > >> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > > >> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> > > >> > Thanks for the correction, Bruce.
> > > >> >
> > > >> > Rats! Now I've got more work to do when we port to Oracle.
> > > >> >
> > > >> > ...sigh...
> > > >> > --
> > > >> > '(' Jeff A. Stucker
> > > >> > \
> > > >> >
> > > >> > Business Intelligence
> > > >> > www.criadvantage.com
> > > >> > ---
> > > >> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > >> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> > > >> > > Umm, no it does not. It uses that for SQL Server but otherwise it
> > > > needs
> > > >> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> > > >> > >
> > > >> > > It sounds to me like you are trying to put in the values in the
> > > >> > > dialog
> > > >> box
> > > >> > > that pops up with you hit the exclamation point. When prompted by
> the
> > > >> > > dialog
> > > >> > > box do not put in any quotes (single or double) just put the
> value
> > > >> > > in.
> > > >> > >
> > > >> > > --
> > > >> > > Bruce Loehle-Conger
> > > >> > > MVP SQL Server Reporting Services
> > > >> > >
> > > >> > >
> > > >> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > > >> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> > > >> > >> I'm not sure what you mean by non-named parameters. RS uses the
> @.
> > > >> format
> > > >> > >> for parameters in queries, like so:
> > > >> > >>
> > > >> > >> ... WHERE FieldName = @.ParameterName
> > > >> > >>
> > > >> > >> --
> > > >> > >> '(' Jeff A. Stucker
> > > >> > >> \
> > > >> > >>
> > > >> > >> Business Intelligence
> > > >> > >> www.criadvantage.com
> > > >> > >> ---
> > > >> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> > > >> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> > > >> > >> > Hi all,
> > > >> > >> >
> > > >> > >> > I have to build a report from SQL tables thru an ODBC server.
> When
> > > > I
> > > >> > >> > try to pass (nonnamed) parameters to the report, the SQL
> request
> > > > runs
> > > >> > >> > but returns nothing, as if the value was not understood. I
> have
> > > >> > >> > put
> > > >> > >> > "?" in the SQL request in place of the parameter.
> > > >> > >> > Do I need to do something else ? What is the format of the
> capture
> > > > in
> > > >> > >> > the run-time parameter dialog box : are quotes needed, double
> > > > quotes,
> > > >> > >> > or other character ?
> > > >> > >> >
> > > >> > >> > Thanks ODBC users !
> > > >> > >> >
> > > >> > >> > Jean-Marc
> > > >> > >>
> > > >> > >>
> > > >> > >
> > > >> > >
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> > >
>
>

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

Parameters on Reporting Services 2005 REPOST

Hi, I experiencing some problems to access the Parameters collection inside
a custom code to build a sql statement.
Below is my code (very simple):
DataSet:
=Code.SQL(Parameters)
Custom Code:
Public Function SQL(ByRef pars As Object) As String
Dim stmt as String
stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
return stmt
End Function
This code works just fine on the Preview(Designer) but if I test the report
on the
browser, it doesnt work and returns the following error:
a.. An error has occurred during report processing.
a.. Cannot set the command text for data set 'ExpoMedios'.
a.. Error during processing of the CommandText expression of dataset
'ExpoMedios'.
Doing some debugging the error message inside the function is:
Attempt to access the method failed.
Can anyone pleae explain why this is happening. Your help will be
appreciated.
Regards,
FabianHi,
have you tried to declare pars As Parameter and not as Object ?
"Fabian von Romberg" wrote:
> Hi, I experiencing some problems to access the Parameters collection inside
> a custom code to build a sql statement.
> Below is my code (very simple):
> DataSet:
> =Code.SQL(Parameters)
> Custom Code:
> Public Function SQL(ByRef pars As Object) As String
> Dim stmt as String
> stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
> return stmt
> End Function
>
> This code works just fine on the Preview(Designer) but if I test the report
> on the
> browser, it doesnt work and returns the following error:
> a.. An error has occurred during report processing.
> a.. Cannot set the command text for data set 'ExpoMedios'.
> a.. Error during processing of the CommandText expression of dataset
> 'ExpoMedios'.
> Doing some debugging the error message inside the function is:
> Attempt to access the method failed.
>
> Can anyone pleae explain why this is happening. Your help will be
> appreciated.
> Regards,
> Fabian
>
>|||Whatt!!!!!!!!!!!!,
I should have tried that before. It did it. I used this code on the older
version of Reporting Services and never got that error message, actually I
think I was not able to set a function parameter as type of Parameters but
Object. On 2005's seems to be the correct way to do it.
Thanks Cedric, I appreciated it.
Regards,
Fabian von Romberg
"Cedric" <Cedric@.discussions.microsoft.com> wrote in message
news:C8A38357-8A82-473A-8208-6DEEBF40DC9F@.microsoft.com...
> Hi,
> have you tried to declare pars As Parameter and not as Object ?
>
> "Fabian von Romberg" wrote:
> > Hi, I experiencing some problems to access the Parameters collection
inside
> > a custom code to build a sql statement.
> >
> > Below is my code (very simple):
> >
> > DataSet:
> > =Code.SQL(Parameters)
> >
> > Custom Code:
> >
> > Public Function SQL(ByRef pars As Object) As String
> > Dim stmt as String
> > stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
> > return stmt
> > End Function
> >
> >
> > This code works just fine on the Preview(Designer) but if I test the
report
> > on the
> > browser, it doesnt work and returns the following error:
> > a.. An error has occurred during report processing.
> > a.. Cannot set the command text for data set 'ExpoMedios'.
> > a.. Error during processing of the CommandText expression of
dataset
> > 'ExpoMedios'.
> >
> > Doing some debugging the error message inside the function is:
> >
> > Attempt to access the method failed.
> >
> >
> > Can anyone pleae explain why this is happening. Your help will be
> > appreciated.
> >
> > Regards,
> > Fabian
> >
> >
> >
> >

Parameters in named queries

Hi,

I try to build a named query that would either take a parameter or run a procedure to get a value.

The idea is to have a named query used by the cube partition. The named query must limit the lower boud time item according to a complex logic build in a stored procedure.

First I am not sure if it will work even if I can get the named query to behave this way, however I would rather avoid to alter the view behind the cube.

If possible, how can I create a parameter in a named query, something like this in pseudo-code

? = (EXEC up_FirstCrawl_Qtr)
SELECT *
FROM dbo.V_ST_Crawl
WHERE (RSD_QRTR = ?)

Or

SELECT *
FROM dbo.V_ST_Crawl
WHERE (RSD_QRTR = (EXEC up_FirstCrawl_Qtr) )

Thanks,

Philippe

Not to answer your question directly, but I think you would find this very useful.

Project REAL just released entire set of scripts and packages and whole lot of other stuff you can use to create your data warehouse and Analysis Services cubes.

I think this is great material and you should be able to find answers to many of your questions there:

Here is the link: http://www.microsoft.com/downloads/thankyou.aspx?familyId=b61a37b6-5852-4018-bba9-795a34123ed0&displayLang=en&oRef=

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Woowh, Great.
I always wanted to get more from RealProject than abstracts.
Looks like the real meat is there now, 240MB or so of it.

I am sure going to spend a week-end my wife will remember :-)

In the meantime and to cope with management deadline, I will create another view. I am under the gun.

I always wanted to avoid providing things that work now but require more maintenance, however it is not always possible. problem is that management sees value only in brand new stuff never done before.
No budget nor time is allowed to re-create "working" existing legacy stuff in a much better way, but, they complain when the stuff is down for maintenance... The Chicken or the Egg? No both please.

Have a nice week-end

Philippe