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

Query Notification / SQLDependency fails on table w/ computed column

I have a table with a computed column defined on it. I can't get SQLDependency to work with that table unless I remove the computed column definition. The select statement I am using does not include the computed column, it just exists on the table.

When I execute the SQLCommand, the SQLDependency immediately fires the OnChange event with a SQLNotificationEventArgs.Info of 8 {Query}. I can't find any documentation in SQL Books Online that the base table must not have a computed column, even if the select does not include that column. Is this a bug or by design? Thanks.

Here is a sample create table statement:

create table dbo.test

(id int,datechanged datetime

,mycomputedcolumn as (1+1)

)

go

insert into dbo.test (id,datechanged)

values (46,getdate())

And here is a sample VB program that duplicates my problem (the sub cb will be called immediately with a e.Info=8)

Module Module1

Sub Main()

Dim connString As String = "server=paulg\yukon;database=bbinfinity;integrated security=sspi"

SqlClient.SqlDependency.Start(connString)

Dim conn As New SqlClient.SqlConnection(connString)

conn.Open()

Dim cmd As SqlClient.SqlCommand = conn.CreateCommand

cmd.CommandText = "select id,datechanged from dbo.test;"

cmd.Connection = conn

Dim dep As New SqlClient.SqlDependency(cmd, Nothing, 500000)

AddHandler dep.OnChange, AddressOf cb

Dim o As Object = cmd.ExecuteScalar

Console.ReadLine()

End Sub

Sub cb(ByVal sender As Object, ByVal e As SqlClient.SqlNotificationEventArgs)

Debug.WriteLine(e.ToString)

End Sub

End Module

This is a behavior of the underlying notification engine in the server. I'm moving the thread to a server forum.|||

Paul,

Using a query notification on a table with a computed field is NOT supported by Microsoft. I was told it would be added to the BOL in the list of scenarios not supported, but only after I reported it as a bug.

Chris

|||

Thanks for the reply. It helps to know that it is officially not supported vs. something I was doing wrong.

Seems like a bizarre limitation to me. I always thought computed columns were just some extra metadata with very little cost that were convenient when you had common expressions you knew you might need frequently. Under that impression, I've used them all over the place where I know I'll often need an expression (such as a CASE..WHEN statement) to avoid having to repeat the expression in every TSQL statement that needs that logic. I know I can use UDFs but my experience shows that in-line expressions can be much faster than UDFs, so in simple cases where inline expressions work I try to use them. Computed columns seems like a great way to have the re-usability of UDFs with the performance of in-line expressions. But now I'm scared that computed columns are more "special" than one would think.

Obviously there are many workarounds so I'll get by. But this still smells to me like something that must have just been overlooked rather than a well reasoned design choice. Thanks again for the reply.

|||

No problem. It was pretty frustrating for me as well. It seemed that some people I talked to still using the Beta versions of Visual Studio could get this to work, but I never could. It took over a month for MS to tell me that it was not supported after I reported it as a bug. Luckily we didn't have too many computed fields in the tables we wanted to use for query notifications, but it was still a major annoyance.