Query Parameter Problem

I choose Microsoft OLE DB for ODBC driver provider and my SQL is "SELECT * FROM EMPLOYEE WHERE EMPL_ID = ?. I have defined ? in report parameters. When I preview the report, no problem is found. But when I deploy it in report server, the following error is shown:
ORA-01036: illegal variable name/number
In fact, both report server and report designer are installed in same machine.Please try these steps:
* delete the published report from the report server (through the report
manager: http://localhost/reports)
* verify that your report really works in Report Designer Preview (click the
green "refresh" icon in the Preview toolbar)
* deploy the report again to report server
* try to run it again through report manager
BTW: is there any specific reason why you choose the MS OleDB provider for
ODBC and connect to an Oracle ODBC data source?
Why don't you use the managed Oracle provider (by selecting "Oracle" in the
data source dialog) which would support named parameters (example for
parameter syntax: SELECT * FROM EMPLOYEE WHERE EMPL_ID = :employeeID )? You
can also use the MS OleDB provider for Oracle (which directly connects to
Oracle, rather than using the ODBC provider) or the Oracle OleDB provider.
Note: when designing Oracle queries you should always use the text-based
query designer (with 2 panes) rather than the graphical query designer (with
4 panes).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <May Liu@.discussions.microsoft.com> wrote in message
news:4FEB9C4F-DF19-451C-899D-37A63F53389D@.microsoft.com...
> I choose Microsoft OLE DB for ODBC driver provider and my SQL is "SELECT *
FROM EMPLOYEE WHERE EMPL_ID = ?. I have defined ? in report parameters. When
I preview the report, no problem is found. But when I deploy it in report
server, the following error is shown:
> ORA-01036: illegal variable name/number
> In fact, both report server and report designer are installed in same
machine.|||thanks !!!
The error is gone if I use MS OleDB provider for Oracle.
"Robert Bruckner [MSFT]" wrote:
> Please try these steps:
> * delete the published report from the report server (through the report
> manager: http://localhost/reports)
> * verify that your report really works in Report Designer Preview (click the
> green "refresh" icon in the Preview toolbar)
> * deploy the report again to report server
> * try to run it again through report manager
> BTW: is there any specific reason why you choose the MS OleDB provider for
> ODBC and connect to an Oracle ODBC data source?
> Why don't you use the managed Oracle provider (by selecting "Oracle" in the
> data source dialog) which would support named parameters (example for
> parameter syntax: SELECT * FROM EMPLOYEE WHERE EMPL_ID = :employeeID )? You
> can also use the MS OleDB provider for Oracle (which directly connects to
> Oracle, rather than using the ODBC provider) or the Oracle OleDB provider.
> Note: when designing Oracle queries you should always use the text-based
> query designer (with 2 panes) rather than the graphical query designer (with
> 4 panes).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "May Liu" <May Liu@.discussions.microsoft.com> wrote in message
> news:4FEB9C4F-DF19-451C-899D-37A63F53389D@.microsoft.com...
> > I choose Microsoft OLE DB for ODBC driver provider and my SQL is "SELECT *
> FROM EMPLOYEE WHERE EMPL_ID = ?. I have defined ? in report parameters. When
> I preview the report, no problem is found. But when I deploy it in report
> server, the following error is shown:
> > ORA-01036: illegal variable name/number
> >
> > In fact, both report server and report designer are installed in same
> machine.
>
>|||My SQL is base on two parameters. One is mandatroy and the other is optional.
e.g.
SELECT DEPART_NO, EMPL_ID FROM EMPL_TABLE
WHERE DEPART_NO = :DEPT_NO AND EMPL_ID = :EMPL_ID
:DEPT_NO is mandatory
:EMPL_ID is optional
When user input null/blank employee ID, no result is retrieved. How can I override the SQL so that if user don't put employee ID, the SQL become
SELECT DEPART_NO, EMPL_ID FROM EMPL_TABLE
WHERE DEPART_NO = :DEPT_NO
I am using MS OleDB provider for Oracle.
"May Liu" wrote:
> thanks !!!
> The error is gone if I use MS OleDB provider for Oracle.
> "Robert Bruckner [MSFT]" wrote:
> > Please try these steps:
> > * delete the published report from the report server (through the report
> > manager: http://localhost/reports)
> > * verify that your report really works in Report Designer Preview (click the
> > green "refresh" icon in the Preview toolbar)
> > * deploy the report again to report server
> > * try to run it again through report manager
> >
> > BTW: is there any specific reason why you choose the MS OleDB provider for
> > ODBC and connect to an Oracle ODBC data source?
> > Why don't you use the managed Oracle provider (by selecting "Oracle" in the
> > data source dialog) which would support named parameters (example for
> > parameter syntax: SELECT * FROM EMPLOYEE WHERE EMPL_ID = :employeeID )? You
> > can also use the MS OleDB provider for Oracle (which directly connects to
> > Oracle, rather than using the ODBC provider) or the Oracle OleDB provider.
> >
> > Note: when designing Oracle queries you should always use the text-based
> > query designer (with 2 panes) rather than the graphical query designer (with
> > 4 panes).
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> >
> > "May Liu" <May Liu@.discussions.microsoft.com> wrote in message
> > news:4FEB9C4F-DF19-451C-899D-37A63F53389D@.microsoft.com...
> > > I choose Microsoft OLE DB for ODBC driver provider and my SQL is "SELECT *
> > FROM EMPLOYEE WHERE EMPL_ID = ?. I have defined ? in report parameters. When
> > I preview the report, no problem is found. But when I deploy it in report
> > server, the following error is shown:
> > > ORA-01036: illegal variable name/number
> > >
> > > In fact, both report server and report designer are installed in same
> > machine.
> >
> >
> >