Parameterized Expression: odbc

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
> > > >> > >>
> > > >> > >>
> > > >> > >
> > > >> > >
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> > >
>
>

Tuesday, March 20, 2012

Parameters with ODBC server

Friday, March 9, 2012

Parameters in data flow task with Oracle database source

Monday, February 20, 2012

parameterized query string in Openrowset

Parameterized Expression

Blog Archive

About Me