Multi-Parameter select problem with Oracle Database

We use Oracle database and SQL server 2005 Reporting
service Beta2. I made a simple SQL report using query:
select sum(spend)
from travel_cost
where department in (:p_dept)
I set parameter p_dept to be Multi-value: dept1 and
dept2. When I run report, if I only select dept1 or
dept2, it works fine. But when I select both or Select
All, I will get a error message:
An error has occurred during report processing. Query
execution failed for data set 'DataSet1'. ORA-12704:
Character set missmatch.
Please help.
Frank
.Is this the September CTP? June CTP? I'm not sure what beta 2 is, when did
it come out?
If it is not the September CTP then that is the issue. Get the September CTP
and try again.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:45D08B41-3791-4B9B-8C25-903F0DEC1CF4@.microsoft.com...
> We use Oracle database and SQL server 2005 Reporting
> service Beta2. I made a simple SQL report using query:
> select sum(spend)
> from travel_cost
> where department in (:p_dept)
> I set parameter p_dept to be Multi-value: dept1 and
> dept2. When I run report, if I only select dept1 or
> dept2, it works fine. But when I select both or Select
> All, I will get a error message:
> An error has occurred during report processing. Query
> execution failed for data set 'DataSet1'. ORA-12704:
> Character set missmatch.
> Please help.
> Frank
> .
>|||Is the Department column defined as NVarchar2?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:45D08B41-3791-4B9B-8C25-903F0DEC1CF4@.microsoft.com...
> We use Oracle database and SQL server 2005 Reporting
> service Beta2. I made a simple SQL report using query:
> select sum(spend)
> from travel_cost
> where department in (:p_dept)
> I set parameter p_dept to be Multi-value: dept1 and
> dept2. When I run report, if I only select dept1 or
> dept2, it works fine. But when I select both or Select
> All, I will get a error message:
> An error has occurred during report processing. Query
> execution failed for data set 'DataSet1'. ORA-12704:
> Character set missmatch.
> Please help.
> Frank
> .
>|||Actually, what is the data source type you are using? Are you using "Oracle"
(i.e. the MS managed provider for Oracle)?
What version of the Oracle client and what version of Oracle server are you
connecting?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%237VyvMxvFHA.1648@.TK2MSFTNGP10.phx.gbl...
> Is the Department column defined as NVarchar2?
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> news:45D08B41-3791-4B9B-8C25-903F0DEC1CF4@.microsoft.com...
>> We use Oracle database and SQL server 2005 Reporting
>> service Beta2. I made a simple SQL report using query:
>> select sum(spend)
>> from travel_cost
>> where department in (:p_dept)
>> I set parameter p_dept to be Multi-value: dept1 and
>> dept2. When I run report, if I only select dept1 or
>> dept2, it works fine. But when I select both or Select
>> All, I will get a error message:
>> An error has occurred during report processing. Query
>> execution failed for data set 'DataSet1'. ORA-12704:
>> Character set missmatch.
>> Please help.
>> Frank
>> .
>>
>|||1. I just updated SQL Server Beta 2 from June version to September version.
Error message are same.
2. Our Oracle database is 8.1.7.4.0. All character fields are varchar2
and number fields are number.
3. I can get report data when I select one parameter. So it is not
character or Oracle database connection problem. I think it is Multi-Value
select function problem with Oracle database application.
Thanks
Frank
"Robert Bruckner [MSFT]" wrote:
> Actually, what is the data source type you are using? Are you using "Oracle"
> (i.e. the MS managed provider for Oracle)?
> What version of the Oracle client and what version of Oracle server are you
> connecting?
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%237VyvMxvFHA.1648@.TK2MSFTNGP10.phx.gbl...
> > Is the Department column defined as NVarchar2?
> >
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> >
> > "Frank" <Frank@.discussions.microsoft.com> wrote in message
> > news:45D08B41-3791-4B9B-8C25-903F0DEC1CF4@.microsoft.com...
> >>
> >> We use Oracle database and SQL server 2005 Reporting
> >> service Beta2. I made a simple SQL report using query:
> >>
> >> select sum(spend)
> >> from travel_cost
> >> where department in (:p_dept)
> >>
> >> I set parameter p_dept to be Multi-value: dept1 and
> >> dept2. When I run report, if I only select dept1 or
> >> dept2, it works fine. But when I select both or Select
> >> All, I will get a error message:
> >>
> >> An error has occurred during report processing. Query
> >> execution failed for data set 'DataSet1'. ORA-12704:
> >> Character set missmatch.
> >>
> >> Please help.
> >>
> >> Frank
> >>
> >> .
> >>
> >>
> >
> >
>
>|||Dear Robert:
1. When I set Datasource, I select Type 'Oracle'.
2. Client site, Oracle Net 8 version is 8.1.7. Oracle server version is
8.1.7.4.0.
3. Before I used June CTP software. I download September CTP software
SQLSTD.exe and run installing on the same computer. I don't remove June
version and install to default folder SQLSTD. No any error occurs. When I
run same report and get same error. How do I check the the June version has
been replaced by September version?
Thanks,
Frank
"Robert Bruckner [MSFT]" wrote:
> Actually, what is the data source type you are using? Are you using "Oracle"
> (i.e. the MS managed provider for Oracle)?
> What version of the Oracle client and what version of Oracle server are you
> connecting?
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%237VyvMxvFHA.1648@.TK2MSFTNGP10.phx.gbl...
> > Is the Department column defined as NVarchar2?
> >
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> >
> > "Frank" <Frank@.discussions.microsoft.com> wrote in message
> > news:45D08B41-3791-4B9B-8C25-903F0DEC1CF4@.microsoft.com...
> >>
> >> We use Oracle database and SQL server 2005 Reporting
> >> service Beta2. I made a simple SQL report using query:
> >>
> >> select sum(spend)
> >> from travel_cost
> >> where department in (:p_dept)
> >>
> >> I set parameter p_dept to be Multi-value: dept1 and
> >> dept2. When I run report, if I only select dept1 or
> >> dept2, it works fine. But when I select both or Select
> >> All, I will get a error message:
> >>
> >> An error has occurred during report processing. Query
> >> execution failed for data set 'DataSet1'. ORA-12704:
> >> Character set missmatch.
> >>
> >> Please help.
> >>
> >> Frank
> >>
> >> .
> >>
> >>
> >
> >
>
>|||The instructions said to remove everything before installing the CTP. This
means the beta for the dotnet framework as well as the rest of teh June CTP.
I would not trust any problem found unless you do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:CDE79054-C9CF-4DA0-B2CD-892024B5D347@.microsoft.com...
> Dear Robert:
> 1. When I set Datasource, I select Type 'Oracle'.
> 2. Client site, Oracle Net 8 version is 8.1.7. Oracle server version is
> 8.1.7.4.0.
> 3. Before I used June CTP software. I download September CTP software
> SQLSTD.exe and run installing on the same computer. I don't remove June
> version and install to default folder SQLSTD. No any error occurs. When
> I
> run same report and get same error. How do I check the the June version
> has
> been replaced by September version?
> Thanks,
> Frank
> "Robert Bruckner [MSFT]" wrote:
>> Actually, what is the data source type you are using? Are you using
>> "Oracle"
>> (i.e. the MS managed provider for Oracle)?
>> What version of the Oracle client and what version of Oracle server are
>> you
>> connecting?
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:%237VyvMxvFHA.1648@.TK2MSFTNGP10.phx.gbl...
>> > Is the Department column defined as NVarchar2?
>> >
>> >
>> > -- Robert
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> >
>> >
>> > "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> > news:45D08B41-3791-4B9B-8C25-903F0DEC1CF4@.microsoft.com...
>> >>
>> >> We use Oracle database and SQL server 2005 Reporting
>> >> service Beta2. I made a simple SQL report using query:
>> >>
>> >> select sum(spend)
>> >> from travel_cost
>> >> where department in (:p_dept)
>> >>
>> >> I set parameter p_dept to be Multi-value: dept1 and
>> >> dept2. When I run report, if I only select dept1 or
>> >> dept2, it works fine. But when I select both or Select
>> >> All, I will get a error message:
>> >>
>> >> An error has occurred during report processing. Query
>> >> execution failed for data set 'DataSet1'. ORA-12704:
>> >> Character set missmatch.
>> >>
>> >> Please help.
>> >>
>> >> Frank
>> >>
>> >> .
>> >>
>> >>
>> >
>> >
>>|||Dear Bruce:
I removed all June CTP using control pannel add/remove tool include
Microfoft.net Framework 1.1 and Microsoft.net Deta 2. Then delete folder
SQLSTD and Program Files\Microsoft SQL Server. Then rerun SQLSTD.exe and
install SQL SERVER and Reporting service. But when I run Microsoft Visual
Studio 2005, my report is already here and the errors are same.
Please advice
Thanks
Frank
"Bruce L-C [MVP]" wrote:
> The instructions said to remove everything before installing the CTP. This
> means the beta for the dotnet framework as well as the rest of teh June CTP.
> I would not trust any problem found unless you do this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> news:CDE79054-C9CF-4DA0-B2CD-892024B5D347@.microsoft.com...
> > Dear Robert:
> > 1. When I set Datasource, I select Type 'Oracle'.
> > 2. Client site, Oracle Net 8 version is 8.1.7. Oracle server version is
> > 8.1.7.4.0.
> > 3. Before I used June CTP software. I download September CTP software
> > SQLSTD.exe and run installing on the same computer. I don't remove June
> > version and install to default folder SQLSTD. No any error occurs. When
> > I
> > run same report and get same error. How do I check the the June version
> > has
> > been replaced by September version?
> > Thanks,
> > Frank
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> Actually, what is the data source type you are using? Are you using
> >> "Oracle"
> >> (i.e. the MS managed provider for Oracle)?
> >> What version of the Oracle client and what version of Oracle server are
> >> you
> >> connecting?
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> >> news:%237VyvMxvFHA.1648@.TK2MSFTNGP10.phx.gbl...
> >> > Is the Department column defined as NVarchar2?
> >> >
> >> >
> >> > -- Robert
> >> > This posting is provided "AS IS" with no warranties, and confers no
> >> > rights.
> >> >
> >> >
> >> >
> >> > "Frank" <Frank@.discussions.microsoft.com> wrote in message
> >> > news:45D08B41-3791-4B9B-8C25-903F0DEC1CF4@.microsoft.com...
> >> >>
> >> >> We use Oracle database and SQL server 2005 Reporting
> >> >> service Beta2. I made a simple SQL report using query:
> >> >>
> >> >> select sum(spend)
> >> >> from travel_cost
> >> >> where department in (:p_dept)
> >> >>
> >> >> I set parameter p_dept to be Multi-value: dept1 and
> >> >> dept2. When I run report, if I only select dept1 or
> >> >> dept2, it works fine. But when I select both or Select
> >> >> All, I will get a error message:
> >> >>
> >> >> An error has occurred during report processing. Query
> >> >> execution failed for data set 'DataSet1'. ORA-12704:
> >> >> Character set missmatch.
> >> >>
> >> >> Please help.
> >> >>
> >> >> Frank
> >> >>
> >> >> .
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>|||The instructions say to remove the framework 2.0 beta, VS 2005, SQL Server
2005 june ctp. They all share the framework beta and that is why everything
needs to be gone prior to upgrading. I'm not saying that your problem won't
still be there, just that this was what the instructions said to do. Read
the file that comes with the Sept CTP.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:B8294B8E-F15F-419C-8B5E-32FF43A4CD3A@.microsoft.com...
> Dear Bruce:
> I removed all June CTP using control pannel add/remove tool include
> Microfoft.net Framework 1.1 and Microsoft.net Deta 2. Then delete folder
> SQLSTD and Program Files\Microsoft SQL Server. Then rerun SQLSTD.exe and
> install SQL SERVER and Reporting service. But when I run Microsoft Visual
> Studio 2005, my report is already here and the errors are same.
> Please advice
> Thanks
> Frank
> "Bruce L-C [MVP]" wrote:
>> The instructions said to remove everything before installing the CTP.
>> This
>> means the beta for the dotnet framework as well as the rest of teh June
>> CTP.
>> I would not trust any problem found unless you do this.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> news:CDE79054-C9CF-4DA0-B2CD-892024B5D347@.microsoft.com...
>> > Dear Robert:
>> > 1. When I set Datasource, I select Type 'Oracle'.
>> > 2. Client site, Oracle Net 8 version is 8.1.7. Oracle server version
>> > is
>> > 8.1.7.4.0.
>> > 3. Before I used June CTP software. I download September CTP software
>> > SQLSTD.exe and run installing on the same computer. I don't remove
>> > June
>> > version and install to default folder SQLSTD. No any error occurs.
>> > When
>> > I
>> > run same report and get same error. How do I check the the June
>> > version
>> > has
>> > been replaced by September version?
>> > Thanks,
>> > Frank
>> >
>> > "Robert Bruckner [MSFT]" wrote:
>> >
>> >> Actually, what is the data source type you are using? Are you using
>> >> "Oracle"
>> >> (i.e. the MS managed provider for Oracle)?
>> >> What version of the Oracle client and what version of Oracle server
>> >> are
>> >> you
>> >> connecting?
>> >>
>> >> -- Robert
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
>> >> message
>> >> news:%237VyvMxvFHA.1648@.TK2MSFTNGP10.phx.gbl...
>> >> > Is the Department column defined as NVarchar2?
>> >> >
>> >> >
>> >> > -- Robert
>> >> > This posting is provided "AS IS" with no warranties, and confers no
>> >> > rights.
>> >> >
>> >> >
>> >> >
>> >> > "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> >> > news:45D08B41-3791-4B9B-8C25-903F0DEC1CF4@.microsoft.com...
>> >> >>
>> >> >> We use Oracle database and SQL server 2005 Reporting
>> >> >> service Beta2. I made a simple SQL report using query:
>> >> >>
>> >> >> select sum(spend)
>> >> >> from travel_cost
>> >> >> where department in (:p_dept)
>> >> >>
>> >> >> I set parameter p_dept to be Multi-value: dept1 and
>> >> >> dept2. When I run report, if I only select dept1 or
>> >> >> dept2, it works fine. But when I select both or Select
>> >> >> All, I will get a error message:
>> >> >>
>> >> >> An error has occurred during report processing. Query
>> >> >> execution failed for data set 'DataSet1'. ORA-12704:
>> >> >> Character set missmatch.
>> >> >>
>> >> >> Please help.
>> >> >>
>> >> >> Frank
>> >> >>
>> >> >> .
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||I remove all SQL server program and Microsoft Visual Studio and Microsoft
framework.net program. Physical delete program files\Miscosoft SQL Server,
Microsoft Visual Studio 8 and Miscrosoft Visual Studio and Miscrosoft.net
folders. Even I delete my report file folder. Then re-install SQL Server
2005 September CTP. Re-create the simple report, but I get same error when I
select multi parameters.
Thanks
Frank
"Bruce L-C [MVP]" wrote:
> The instructions say to remove the framework 2.0 beta, VS 2005, SQL Server
> 2005 june ctp. They all share the framework beta and that is why everything
> needs to be gone prior to upgrading. I'm not saying that your problem won't
> still be there, just that this was what the instructions said to do. Read
> the file that comes with the Sept CTP.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> news:B8294B8E-F15F-419C-8B5E-32FF43A4CD3A@.microsoft.com...
> > Dear Bruce:
> >
> > I removed all June CTP using control pannel add/remove tool include
> > Microfoft.net Framework 1.1 and Microsoft.net Deta 2. Then delete folder
> > SQLSTD and Program Files\Microsoft SQL Server. Then rerun SQLSTD.exe and
> > install SQL SERVER and Reporting service. But when I run Microsoft Visual
> > Studio 2005, my report is already here and the errors are same.
> >
> > Please advice
> >
> > Thanks
> > Frank
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> The instructions said to remove everything before installing the CTP.
> >> This
> >> means the beta for the dotnet framework as well as the rest of teh June
> >> CTP.
> >>
> >> I would not trust any problem found unless you do this.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> >> news:CDE79054-C9CF-4DA0-B2CD-892024B5D347@.microsoft.com...
> >> > Dear Robert:
> >> > 1. When I set Datasource, I select Type 'Oracle'.
> >> > 2. Client site, Oracle Net 8 version is 8.1.7. Oracle server version
> >> > is
> >> > 8.1.7.4.0.
> >> > 3. Before I used June CTP software. I download September CTP software
> >> > SQLSTD.exe and run installing on the same computer. I don't remove
> >> > June
> >> > version and install to default folder SQLSTD. No any error occurs.
> >> > When
> >> > I
> >> > run same report and get same error. How do I check the the June
> >> > version
> >> > has
> >> > been replaced by September version?
> >> > Thanks,
> >> > Frank
> >> >
> >> > "Robert Bruckner [MSFT]" wrote:
> >> >
> >> >> Actually, what is the data source type you are using? Are you using
> >> >> "Oracle"
> >> >> (i.e. the MS managed provider for Oracle)?
> >> >> What version of the Oracle client and what version of Oracle server
> >> >> are
> >> >> you
> >> >> connecting?
> >> >>
> >> >> -- Robert
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
> >> >> message
> >> >> news:%237VyvMxvFHA.1648@.TK2MSFTNGP10.phx.gbl...
> >> >> > Is the Department column defined as NVarchar2?
> >> >> >
> >> >> >
> >> >> > -- Robert
> >> >> > This posting is provided "AS IS" with no warranties, and confers no
> >> >> > rights.
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Frank" <Frank@.discussions.microsoft.com> wrote in message
> >> >> > news:45D08B41-3791-4B9B-8C25-903F0DEC1CF4@.microsoft.com...
> >> >> >>
> >> >> >> We use Oracle database and SQL server 2005 Reporting
> >> >> >> service Beta2. I made a simple SQL report using query:
> >> >> >>
> >> >> >> select sum(spend)
> >> >> >> from travel_cost
> >> >> >> where department in (:p_dept)
> >> >> >>
> >> >> >> I set parameter p_dept to be Multi-value: dept1 and
> >> >> >> dept2. When I run report, if I only select dept1 or
> >> >> >> dept2, it works fine. But when I select both or Select
> >> >> >> All, I will get a error message:
> >> >> >>
> >> >> >> An error has occurred during report processing. Query
> >> >> >> execution failed for data set 'DataSet1'. ORA-12704:
> >> >> >> Character set missmatch.
> >> >> >>
> >> >> >> Please help.
> >> >> >>
> >> >> >> Frank
> >> >> >>
> >> >> >> .
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||I'll get it posted as a bug.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:5801832D-79C4-4F52-AAC9-F49E9FC65C5A@.microsoft.com...
>I remove all SQL server program and Microsoft Visual Studio and Microsoft
> framework.net program. Physical delete program files\Miscosoft SQL
> Server,
> Microsoft Visual Studio 8 and Miscrosoft Visual Studio and Miscrosoft.net
> folders. Even I delete my report file folder. Then re-install SQL Server
> 2005 September CTP. Re-create the simple report, but I get same error
> when I
> select multi parameters.
> Thanks
> Frank
> "Bruce L-C [MVP]" wrote:
>> The instructions say to remove the framework 2.0 beta, VS 2005, SQL
>> Server
>> 2005 june ctp. They all share the framework beta and that is why
>> everything
>> needs to be gone prior to upgrading. I'm not saying that your problem
>> won't
>> still be there, just that this was what the instructions said to do. Read
>> the file that comes with the Sept CTP.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> news:B8294B8E-F15F-419C-8B5E-32FF43A4CD3A@.microsoft.com...
>> > Dear Bruce:
>> >
>> > I removed all June CTP using control pannel add/remove tool include
>> > Microfoft.net Framework 1.1 and Microsoft.net Deta 2. Then delete
>> > folder
>> > SQLSTD and Program Files\Microsoft SQL Server. Then rerun SQLSTD.exe
>> > and
>> > install SQL SERVER and Reporting service. But when I run Microsoft
>> > Visual
>> > Studio 2005, my report is already here and the errors are same.
>> >
>> > Please advice
>> >
>> > Thanks
>> > Frank
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> The instructions said to remove everything before installing the CTP.
>> >> This
>> >> means the beta for the dotnet framework as well as the rest of teh
>> >> June
>> >> CTP.
>> >>
>> >> I would not trust any problem found unless you do this.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> >> news:CDE79054-C9CF-4DA0-B2CD-892024B5D347@.microsoft.com...
>> >> > Dear Robert:
>> >> > 1. When I set Datasource, I select Type 'Oracle'.
>> >> > 2. Client site, Oracle Net 8 version is 8.1.7. Oracle server
>> >> > version
>> >> > is
>> >> > 8.1.7.4.0.
>> >> > 3. Before I used June CTP software. I download September CTP
>> >> > software
>> >> > SQLSTD.exe and run installing on the same computer. I don't remove
>> >> > June
>> >> > version and install to default folder SQLSTD. No any error occurs.
>> >> > When
>> >> > I
>> >> > run same report and get same error. How do I check the the June
>> >> > version
>> >> > has
>> >> > been replaced by September version?
>> >> > Thanks,
>> >> > Frank
>> >> >
>> >> > "Robert Bruckner [MSFT]" wrote:
>> >> >
>> >> >> Actually, what is the data source type you are using? Are you using
>> >> >> "Oracle"
>> >> >> (i.e. the MS managed provider for Oracle)?
>> >> >> What version of the Oracle client and what version of Oracle server
>> >> >> are
>> >> >> you
>> >> >> connecting?
>> >> >>
>> >> >> -- Robert
>> >> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> >> rights.
>> >> >>
>> >> >>
>> >> >> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
>> >> >> message
>> >> >> news:%237VyvMxvFHA.1648@.TK2MSFTNGP10.phx.gbl...
>> >> >> > Is the Department column defined as NVarchar2?
>> >> >> >
>> >> >> >
>> >> >> > -- Robert
>> >> >> > This posting is provided "AS IS" with no warranties, and confers
>> >> >> > no
>> >> >> > rights.
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> >> >> > news:45D08B41-3791-4B9B-8C25-903F0DEC1CF4@.microsoft.com...
>> >> >> >>
>> >> >> >> We use Oracle database and SQL server 2005 Reporting
>> >> >> >> service Beta2. I made a simple SQL report using query:
>> >> >> >>
>> >> >> >> select sum(spend)
>> >> >> >> from travel_cost
>> >> >> >> where department in (:p_dept)
>> >> >> >>
>> >> >> >> I set parameter p_dept to be Multi-value: dept1 and
>> >> >> >> dept2. When I run report, if I only select dept1 or
>> >> >> >> dept2, it works fine. But when I select both or Select
>> >> >> >> All, I will get a error message:
>> >> >> >>
>> >> >> >> An error has occurred during report processing. Query
>> >> >> >> execution failed for data set 'DataSet1'. ORA-12704:
>> >> >> >> Character set missmatch.
>> >> >> >>
>> >> >> >> Please help.
>> >> >> >>
>> >> >> >> Frank
>> >> >> >>
>> >> >> >> .
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Well, I can explain in detail how we perform the multi-value parameter query
rewrite. Since we have to support arbitrary Unicode characters for the IN
clause, we follow the SQL standard when rewriting the query. We will
generate an Oracle statement like this:
.... column IN (N'First Value', N'Second Value', N'Third Value') ...
Note the usage of N'...' which marks the string contents as Unicode. It
seems like you are using an Oracle character set (e.g. 7bit Ascii) that does
not support comparing with Unicode characters. Consequently, Oracle will
fail to execute the query.
If you want RS multi-value parameter support to work with your specific
Oracle character set, you may want to look into writing a custom data
extension that implements the IDataMultiValueParameter interface (among
other RS interfaces) and implement the query rewrite algorithm yourself to
fit your Oracle character set needs.
-- Robert
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:uFCJfBFwFHA.3400@.TK2MSFTNGP14.phx.gbl...
> I'll get it posted as a bug.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> news:5801832D-79C4-4F52-AAC9-F49E9FC65C5A@.microsoft.com...
>>I remove all SQL server program and Microsoft Visual Studio and Microsoft
>> framework.net program. Physical delete program files\Miscosoft SQL
>> Server,
>> Microsoft Visual Studio 8 and Miscrosoft Visual Studio and Miscrosoft.net
>> folders. Even I delete my report file folder. Then re-install SQL
>> Server
>> 2005 September CTP. Re-create the simple report, but I get same error
>> when I
>> select multi parameters.
>> Thanks
>> Frank
>> "Bruce L-C [MVP]" wrote:
>> The instructions say to remove the framework 2.0 beta, VS 2005, SQL
>> Server
>> 2005 june ctp. They all share the framework beta and that is why
>> everything
>> needs to be gone prior to upgrading. I'm not saying that your problem
>> won't
>> still be there, just that this was what the instructions said to do.
>> Read
>> the file that comes with the Sept CTP.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> news:B8294B8E-F15F-419C-8B5E-32FF43A4CD3A@.microsoft.com...
>> > Dear Bruce:
>> >
>> > I removed all June CTP using control pannel add/remove tool include
>> > Microfoft.net Framework 1.1 and Microsoft.net Deta 2. Then delete
>> > folder
>> > SQLSTD and Program Files\Microsoft SQL Server. Then rerun SQLSTD.exe
>> > and
>> > install SQL SERVER and Reporting service. But when I run Microsoft
>> > Visual
>> > Studio 2005, my report is already here and the errors are same.
>> >
>> > Please advice
>> >
>> > Thanks
>> > Frank
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> The instructions said to remove everything before installing the CTP.
>> >> This
>> >> means the beta for the dotnet framework as well as the rest of teh
>> >> June
>> >> CTP.
>> >>
>> >> I would not trust any problem found unless you do this.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> >> news:CDE79054-C9CF-4DA0-B2CD-892024B5D347@.microsoft.com...
>> >> > Dear Robert:
>> >> > 1. When I set Datasource, I select Type 'Oracle'.
>> >> > 2. Client site, Oracle Net 8 version is 8.1.7. Oracle server
>> >> > version
>> >> > is
>> >> > 8.1.7.4.0.
>> >> > 3. Before I used June CTP software. I download September CTP
>> >> > software
>> >> > SQLSTD.exe and run installing on the same computer. I don't remove
>> >> > June
>> >> > version and install to default folder SQLSTD. No any error
>> >> > occurs.
>> >> > When
>> >> > I
>> >> > run same report and get same error. How do I check the the June
>> >> > version
>> >> > has
>> >> > been replaced by September version?
>> >> > Thanks,
>> >> > Frank
>> >> >
>> >> > "Robert Bruckner [MSFT]" wrote:
>> >> >
>> >> >> Actually, what is the data source type you are using? Are you
>> >> >> using
>> >> >> "Oracle"
>> >> >> (i.e. the MS managed provider for Oracle)?
>> >> >> What version of the Oracle client and what version of Oracle
>> >> >> server
>> >> >> are
>> >> >> you
>> >> >> connecting?
>> >> >>
>> >> >> -- Robert
>> >> >> This posting is provided "AS IS" with no warranties, and confers
>> >> >> no
>> >> >> rights.
>> >> >>
>> >> >>
>> >> >> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
>> >> >> message
>> >> >> news:%237VyvMxvFHA.1648@.TK2MSFTNGP10.phx.gbl...
>> >> >> > Is the Department column defined as NVarchar2?
>> >> >> >
>> >> >> >
>> >> >> > -- Robert
>> >> >> > This posting is provided "AS IS" with no warranties, and confers
>> >> >> > no
>> >> >> > rights.
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> >> >> > news:45D08B41-3791-4B9B-8C25-903F0DEC1CF4@.microsoft.com...
>> >> >> >>
>> >> >> >> We use Oracle database and SQL server 2005 Reporting
>> >> >> >> service Beta2. I made a simple SQL report using query:
>> >> >> >>
>> >> >> >> select sum(spend)
>> >> >> >> from travel_cost
>> >> >> >> where department in (:p_dept)
>> >> >> >>
>> >> >> >> I set parameter p_dept to be Multi-value: dept1 and
>> >> >> >> dept2. When I run report, if I only select dept1 or
>> >> >> >> dept2, it works fine. But when I select both or Select
>> >> >> >> All, I will get a error message:
>> >> >> >>
>> >> >> >> An error has occurred during report processing. Query
>> >> >> >> execution failed for data set 'DataSet1'. ORA-12704:
>> >> >> >> Character set missmatch.
>> >> >> >>
>> >> >> >> Please help.
>> >> >> >>
>> >> >> >> Frank
>> >> >> >>
>> >> >> >> .
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>|||I just posted a bug on the Yukon beta site related to this same issue. We
are running against an Oracle 9i database and get a similar error. Is the
answer for all those running against Oracle to write custom extensions? This
is going to be a tough sell to my organization. The limited documentation in
the online books for sql 2005 says Oracle is supported - it doesn't say
anything about a unicode exception. Is there an Oracle setting that is
assumed when they say Oracle is supported? I really want to make this work.
--
JO
"Robert Bruckner [MSFT]" wrote:
> Well, I can explain in detail how we perform the multi-value parameter query
> rewrite. Since we have to support arbitrary Unicode characters for the IN
> clause, we follow the SQL standard when rewriting the query. We will
> generate an Oracle statement like this:
> ..... column IN (N'First Value', N'Second Value', N'Third Value') ...
> Note the usage of N'...' which marks the string contents as Unicode. It
> seems like you are using an Oracle character set (e.g. 7bit Ascii) that does
> not support comparing with Unicode characters. Consequently, Oracle will
> fail to execute the query.
> If you want RS multi-value parameter support to work with your specific
> Oracle character set, you may want to look into writing a custom data
> extension that implements the IDataMultiValueParameter interface (among
> other RS interfaces) and implement the query rewrite algorithm yourself to
> fit your Oracle character set needs.
> -- Robert
> 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:uFCJfBFwFHA.3400@.TK2MSFTNGP14.phx.gbl...
> > I'll get it posted as a bug.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Frank" <Frank@.discussions.microsoft.com> wrote in message
> > news:5801832D-79C4-4F52-AAC9-F49E9FC65C5A@.microsoft.com...
> >>I remove all SQL server program and Microsoft Visual Studio and Microsoft
> >> framework.net program. Physical delete program files\Miscosoft SQL
> >> Server,
> >> Microsoft Visual Studio 8 and Miscrosoft Visual Studio and Miscrosoft.net
> >> folders. Even I delete my report file folder. Then re-install SQL
> >> Server
> >> 2005 September CTP. Re-create the simple report, but I get same error
> >> when I
> >> select multi parameters.
> >> Thanks
> >> Frank
> >>
> >> "Bruce L-C [MVP]" wrote:
> >>
> >> The instructions say to remove the framework 2.0 beta, VS 2005, SQL
> >> Server
> >> 2005 june ctp. They all share the framework beta and that is why
> >> everything
> >> needs to be gone prior to upgrading. I'm not saying that your problem
> >> won't
> >> still be there, just that this was what the instructions said to do.
> >> Read
> >> the file that comes with the Sept CTP.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> >> news:B8294B8E-F15F-419C-8B5E-32FF43A4CD3A@.microsoft.com...
> >> > Dear Bruce:
> >> >
> >> > I removed all June CTP using control pannel add/remove tool include
> >> > Microfoft.net Framework 1.1 and Microsoft.net Deta 2. Then delete
> >> > folder
> >> > SQLSTD and Program Files\Microsoft SQL Server. Then rerun SQLSTD.exe
> >> > and
> >> > install SQL SERVER and Reporting service. But when I run Microsoft
> >> > Visual
> >> > Studio 2005, my report is already here and the errors are same.
> >> >
> >> > Please advice
> >> >
> >> > Thanks
> >> > Frank
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> The instructions said to remove everything before installing the CTP.
> >> >> This
> >> >> means the beta for the dotnet framework as well as the rest of teh
> >> >> June
> >> >> CTP.
> >> >>
> >> >> I would not trust any problem found unless you do this.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> >> >> news:CDE79054-C9CF-4DA0-B2CD-892024B5D347@.microsoft.com...
> >> >> > Dear Robert:
> >> >> > 1. When I set Datasource, I select Type 'Oracle'.
> >> >> > 2. Client site, Oracle Net 8 version is 8.1.7. Oracle server
> >> >> > version
> >> >> > is
> >> >> > 8.1.7.4.0.
> >> >> > 3. Before I used June CTP software. I download September CTP
> >> >> > software
> >> >> > SQLSTD.exe and run installing on the same computer. I don't remove
> >> >> > June
> >> >> > version and install to default folder SQLSTD. No any error
> >> >> > occurs.
> >> >> > When
> >> >> > I
> >> >> > run same report and get same error. How do I check the the June
> >> >> > version
> >> >> > has
> >> >> > been replaced by September version?
> >> >> > Thanks,
> >> >> > Frank
> >> >> >
> >> >> > "Robert Bruckner [MSFT]" wrote:
> >> >> >
> >> >> >> Actually, what is the data source type you are using? Are you
> >> >> >> using
> >> >> >> "Oracle"
> >> >> >> (i.e. the MS managed provider for Oracle)?
> >> >> >> What version of the Oracle client and what version of Oracle
> >> >> >> server
> >> >> >> are
> >> >> >> you
> >> >> >> connecting?
> >> >> >>
> >> >> >> -- Robert
> >> >> >> This posting is provided "AS IS" with no warranties, and confers
> >> >> >> no
> >> >> >> rights.
> >> >> >>
> >> >> >>
> >> >> >> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:%237VyvMxvFHA.1648@.TK2MSFTNGP10.phx.gbl...
> >> >> >> > Is the Department column defined as NVarchar2?
> >> >> >> >
> >> >> >> >
> >> >> >> > -- Robert
> >> >> >> > This posting is provided "AS IS" with no warranties, and confers
> >> >> >> > no
> >> >> >> > rights.
> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >> > "Frank" <Frank@.discussions.microsoft.com> wrote in message
> >> >> >> > news:45D08B41-3791-4B9B-8C25-903F0DEC1CF4@.microsoft.com...
> >> >> >> >>
> >> >> >> >> We use Oracle database and SQL server 2005 Reporting
> >> >> >> >> service Beta2. I made a simple SQL report using query:
> >> >> >> >>
> >> >> >> >> select sum(spend)
> >> >> >> >> from travel_cost
> >> >> >> >> where department in (:p_dept)
> >> >> >> >>
> >> >> >> >> I set parameter p_dept to be Multi-value: dept1 and
> >> >> >> >> dept2. When I run report, if I only select dept1 or
> >> >> >> >> dept2, it works fine. But when I select both or Select
> >> >> >> >> All, I will get a error message:
> >> >> >> >>
> >> >> >> >> An error has occurred during report processing. Query
> >> >> >> >> execution failed for data set 'DataSet1'. ORA-12704:
> >> >> >> >> Character set missmatch.
> >> >> >> >>
> >> >> >> >> Please help.
> >> >> >> >>
> >> >> >> >> Frank
> >> >> >> >>
> >> >> >> >> .
> >> >> >> >>
> >> >> >> >>
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >
> >
>
>|||It sounds like you run into a different problem, than what was discussed on
the original thread. BTW: the original thread continued on a SQL Server 2005
specific newsgroup (microsoft.private.sqlserver2005.reportingsvcs).
There may be a solution for you that does not require writing a custom data
extension. Please try the following approach to make it work with your
character set - by using an expression-based query commandtext. E.g.:
="select sum(spend) from travel_cost where department in (" &
Join(Parameters!Department.Value, ",") & ")"
Notes:
* you may also need to take into account the handling of quotes in Oracle in
that command text expression to make it work.
* you may need to first play with the expression in a report textbox and run
the report till the evaluated expression results in a valid multi-value IN
clause and then replace the old non-expression based command text.
Hope this helps,
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"John O" <JohnO@.discussions.microsoft.com> wrote in message
news:3974E8C8-33EA-4971-B4F9-F648559EBFAF@.microsoft.com...
>I just posted a bug on the Yukon beta site related to this same issue. We
> are running against an Oracle 9i database and get a similar error. Is the
> answer for all those running against Oracle to write custom extensions?
> This
> is going to be a tough sell to my organization. The limited documentation
> in
> the online books for sql 2005 says Oracle is supported - it doesn't say
> anything about a unicode exception. Is there an Oracle setting that is
> assumed when they say Oracle is supported? I really want to make this
> work.
> --
> JO
>
> "Robert Bruckner [MSFT]" wrote:
>> Well, I can explain in detail how we perform the multi-value parameter
>> query
>> rewrite. Since we have to support arbitrary Unicode characters for the IN
>> clause, we follow the SQL standard when rewriting the query. We will
>> generate an Oracle statement like this:
>> ..... column IN (N'First Value', N'Second Value', N'Third Value') ...
>> Note the usage of N'...' which marks the string contents as Unicode. It
>> seems like you are using an Oracle character set (e.g. 7bit Ascii) that
>> does
>> not support comparing with Unicode characters. Consequently, Oracle will
>> fail to execute the query.
>> If you want RS multi-value parameter support to work with your specific
>> Oracle character set, you may want to look into writing a custom data
>> extension that implements the IDataMultiValueParameter interface (among
>> other RS interfaces) and implement the query rewrite algorithm yourself
>> to
>> fit your Oracle character set needs.
>> -- Robert
>> 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:uFCJfBFwFHA.3400@.TK2MSFTNGP14.phx.gbl...
>> > I'll get it posted as a bug.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> > news:5801832D-79C4-4F52-AAC9-F49E9FC65C5A@.microsoft.com...
>> >>I remove all SQL server program and Microsoft Visual Studio and
>> >>Microsoft
>> >> framework.net program. Physical delete program files\Miscosoft SQL
>> >> Server,
>> >> Microsoft Visual Studio 8 and Miscrosoft Visual Studio and
>> >> Miscrosoft.net
>> >> folders. Even I delete my report file folder. Then re-install SQL
>> >> Server
>> >> 2005 September CTP. Re-create the simple report, but I get same error
>> >> when I
>> >> select multi parameters.
>> >> Thanks
>> >> Frank
>> >>
>> >> "Bruce L-C [MVP]" wrote:
>> >>
>> >> The instructions say to remove the framework 2.0 beta, VS 2005, SQL
>> >> Server
>> >> 2005 june ctp. They all share the framework beta and that is why
>> >> everything
>> >> needs to be gone prior to upgrading. I'm not saying that your problem
>> >> won't
>> >> still be there, just that this was what the instructions said to do.
>> >> Read
>> >> the file that comes with the Sept CTP.
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> >> news:B8294B8E-F15F-419C-8B5E-32FF43A4CD3A@.microsoft.com...
>> >> > Dear Bruce:
>> >> >
>> >> > I removed all June CTP using control pannel add/remove tool include
>> >> > Microfoft.net Framework 1.1 and Microsoft.net Deta 2. Then delete
>> >> > folder
>> >> > SQLSTD and Program Files\Microsoft SQL Server. Then rerun
>> >> > SQLSTD.exe
>> >> > and
>> >> > install SQL SERVER and Reporting service. But when I run Microsoft
>> >> > Visual
>> >> > Studio 2005, my report is already here and the errors are same.
>> >> >
>> >> > Please advice
>> >> >
>> >> > Thanks
>> >> > Frank
>> >> >
>> >> > "Bruce L-C [MVP]" wrote:
>> >> >
>> >> >> The instructions said to remove everything before installing the
>> >> >> CTP.
>> >> >> This
>> >> >> means the beta for the dotnet framework as well as the rest of teh
>> >> >> June
>> >> >> CTP.
>> >> >>
>> >> >> I would not trust any problem found unless you do this.
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Bruce Loehle-Conger
>> >> >> MVP SQL Server Reporting Services
>> >> >>
>> >> >> "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> >> >> news:CDE79054-C9CF-4DA0-B2CD-892024B5D347@.microsoft.com...
>> >> >> > Dear Robert:
>> >> >> > 1. When I set Datasource, I select Type 'Oracle'.
>> >> >> > 2. Client site, Oracle Net 8 version is 8.1.7. Oracle server
>> >> >> > version
>> >> >> > is
>> >> >> > 8.1.7.4.0.
>> >> >> > 3. Before I used June CTP software. I download September CTP
>> >> >> > software
>> >> >> > SQLSTD.exe and run installing on the same computer. I don't
>> >> >> > remove
>> >> >> > June
>> >> >> > version and install to default folder SQLSTD. No any error
>> >> >> > occurs.
>> >> >> > When
>> >> >> > I
>> >> >> > run same report and get same error. How do I check the the June
>> >> >> > version
>> >> >> > has
>> >> >> > been replaced by September version?
>> >> >> > Thanks,
>> >> >> > Frank
>> >> >> >
>> >> >> > "Robert Bruckner [MSFT]" wrote:
>> >> >> >
>> >> >> >> Actually, what is the data source type you are using? Are you
>> >> >> >> using
>> >> >> >> "Oracle"
>> >> >> >> (i.e. the MS managed provider for Oracle)?
>> >> >> >> What version of the Oracle client and what version of Oracle
>> >> >> >> server
>> >> >> >> are
>> >> >> >> you
>> >> >> >> connecting?
>> >> >> >>
>> >> >> >> -- Robert
>> >> >> >> This posting is provided "AS IS" with no warranties, and
>> >> >> >> confers
>> >> >> >> no
>> >> >> >> rights.
>> >> >> >>
>> >> >> >>
>> >> >> >> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
>> >> >> >> message
>> >> >> >> news:%237VyvMxvFHA.1648@.TK2MSFTNGP10.phx.gbl...
>> >> >> >> > Is the Department column defined as NVarchar2?
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > -- Robert
>> >> >> >> > This posting is provided "AS IS" with no warranties, and
>> >> >> >> > confers
>> >> >> >> > no
>> >> >> >> > rights.
>> >> >> >> >
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> >> >> >> > news:45D08B41-3791-4B9B-8C25-903F0DEC1CF4@.microsoft.com...
>> >> >> >> >>
>> >> >> >> >> We use Oracle database and SQL server 2005 Reporting
>> >> >> >> >> service Beta2. I made a simple SQL report using query:
>> >> >> >> >>
>> >> >> >> >> select sum(spend)
>> >> >> >> >> from travel_cost
>> >> >> >> >> where department in (:p_dept)
>> >> >> >> >>
>> >> >> >> >> I set parameter p_dept to be Multi-value: dept1 and
>> >> >> >> >> dept2. When I run report, if I only select dept1 or
>> >> >> >> >> dept2, it works fine. But when I select both or Select
>> >> >> >> >> All, I will get a error message:
>> >> >> >> >>
>> >> >> >> >> An error has occurred during report processing. Query
>> >> >> >> >> execution failed for data set 'DataSet1'. ORA-12704:
>> >> >> >> >> Character set missmatch.
>> >> >> >> >>
>> >> >> >> >> Please help.
>> >> >> >> >>
>> >> >> >> >> Frank
>> >> >> >> >>
>> >> >> >> >> .
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>> >
>> >
>>|||It seems like in your case the RDL just contains an expression-based and
parameterized query commandtext, but there is NO query parameters defined in
the RDL. At report runtime the command text expression gets evaluated and RS
executes the command text and Oracle throws an error about a query parameter
being present but not bound to a value.
So, instead of using the Join(...) approach, you could add a query
parameter, and bind it to a multi-value report parameter and it should work.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23iExJShzFHA.2884@.TK2MSFTNGP09.phx.gbl...
> It sounds like you run into a different problem, than what was discussed
> on the original thread. BTW: the original thread continued on a SQL Server
> 2005 specific newsgroup (microsoft.private.sqlserver2005.reportingsvcs).
> There may be a solution for you that does not require writing a custom
> data extension. Please try the following approach to make it work with
> your character set - by using an expression-based query commandtext. E.g.:
> ="select sum(spend) from travel_cost where department in (" &
> Join(Parameters!Department.Value, ",") & ")"
> Notes:
> * you may also need to take into account the handling of quotes in Oracle
> in that command text expression to make it work.
> * you may need to first play with the expression in a report textbox and
> run the report till the evaluated expression results in a valid
> multi-value IN clause and then replace the old non-expression based
> command text.
> Hope this helps,
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "John O" <JohnO@.discussions.microsoft.com> wrote in message
> news:3974E8C8-33EA-4971-B4F9-F648559EBFAF@.microsoft.com...
>>I just posted a bug on the Yukon beta site related to this same issue. We
>> are running against an Oracle 9i database and get a similar error. Is
>> the
>> answer for all those running against Oracle to write custom extensions?
>> This
>> is going to be a tough sell to my organization. The limited
>> documentation in
>> the online books for sql 2005 says Oracle is supported - it doesn't say
>> anything about a unicode exception. Is there an Oracle setting that is
>> assumed when they say Oracle is supported? I really want to make this
>> work.
>> --
>> JO
>>
>> "Robert Bruckner [MSFT]" wrote:
>> Well, I can explain in detail how we perform the multi-value parameter
>> query
>> rewrite. Since we have to support arbitrary Unicode characters for the
>> IN
>> clause, we follow the SQL standard when rewriting the query. We will
>> generate an Oracle statement like this:
>> ..... column IN (N'First Value', N'Second Value', N'Third Value') ...
>> Note the usage of N'...' which marks the string contents as Unicode. It
>> seems like you are using an Oracle character set (e.g. 7bit Ascii) that
>> does
>> not support comparing with Unicode characters. Consequently, Oracle will
>> fail to execute the query.
>> If you want RS multi-value parameter support to work with your specific
>> Oracle character set, you may want to look into writing a custom data
>> extension that implements the IDataMultiValueParameter interface (among
>> other RS interfaces) and implement the query rewrite algorithm yourself
>> to
>> fit your Oracle character set needs.
>> -- Robert
>> 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:uFCJfBFwFHA.3400@.TK2MSFTNGP14.phx.gbl...
>> > I'll get it posted as a bug.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> > news:5801832D-79C4-4F52-AAC9-F49E9FC65C5A@.microsoft.com...
>> >>I remove all SQL server program and Microsoft Visual Studio and
>> >>Microsoft
>> >> framework.net program. Physical delete program files\Miscosoft SQL
>> >> Server,
>> >> Microsoft Visual Studio 8 and Miscrosoft Visual Studio and
>> >> Miscrosoft.net
>> >> folders. Even I delete my report file folder. Then re-install SQL
>> >> Server
>> >> 2005 September CTP. Re-create the simple report, but I get same
>> >> error
>> >> when I
>> >> select multi parameters.
>> >> Thanks
>> >> Frank
>> >>
>> >> "Bruce L-C [MVP]" wrote:
>> >>
>> >> The instructions say to remove the framework 2.0 beta, VS 2005, SQL
>> >> Server
>> >> 2005 june ctp. They all share the framework beta and that is why
>> >> everything
>> >> needs to be gone prior to upgrading. I'm not saying that your
>> >> problem
>> >> won't
>> >> still be there, just that this was what the instructions said to do.
>> >> Read
>> >> the file that comes with the Sept CTP.
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> >> news:B8294B8E-F15F-419C-8B5E-32FF43A4CD3A@.microsoft.com...
>> >> > Dear Bruce:
>> >> >
>> >> > I removed all June CTP using control pannel add/remove tool
>> >> > include
>> >> > Microfoft.net Framework 1.1 and Microsoft.net Deta 2. Then delete
>> >> > folder
>> >> > SQLSTD and Program Files\Microsoft SQL Server. Then rerun
>> >> > SQLSTD.exe
>> >> > and
>> >> > install SQL SERVER and Reporting service. But when I run
>> >> > Microsoft
>> >> > Visual
>> >> > Studio 2005, my report is already here and the errors are same.
>> >> >
>> >> > Please advice
>> >> >
>> >> > Thanks
>> >> > Frank
>> >> >
>> >> > "Bruce L-C [MVP]" wrote:
>> >> >
>> >> >> The instructions said to remove everything before installing the
>> >> >> CTP.
>> >> >> This
>> >> >> means the beta for the dotnet framework as well as the rest of
>> >> >> teh
>> >> >> June
>> >> >> CTP.
>> >> >>
>> >> >> I would not trust any problem found unless you do this.
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Bruce Loehle-Conger
>> >> >> MVP SQL Server Reporting Services
>> >> >>
>> >> >> "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> >> >> news:CDE79054-C9CF-4DA0-B2CD-892024B5D347@.microsoft.com...
>> >> >> > Dear Robert:
>> >> >> > 1. When I set Datasource, I select Type 'Oracle'.
>> >> >> > 2. Client site, Oracle Net 8 version is 8.1.7. Oracle server
>> >> >> > version
>> >> >> > is
>> >> >> > 8.1.7.4.0.
>> >> >> > 3. Before I used June CTP software. I download September CTP
>> >> >> > software
>> >> >> > SQLSTD.exe and run installing on the same computer. I don't
>> >> >> > remove
>> >> >> > June
>> >> >> > version and install to default folder SQLSTD. No any error
>> >> >> > occurs.
>> >> >> > When
>> >> >> > I
>> >> >> > run same report and get same error. How do I check the the
>> >> >> > June
>> >> >> > version
>> >> >> > has
>> >> >> > been replaced by September version?
>> >> >> > Thanks,
>> >> >> > Frank
>> >> >> >
>> >> >> > "Robert Bruckner [MSFT]" wrote:
>> >> >> >
>> >> >> >> Actually, what is the data source type you are using? Are you
>> >> >> >> using
>> >> >> >> "Oracle"
>> >> >> >> (i.e. the MS managed provider for Oracle)?
>> >> >> >> What version of the Oracle client and what version of Oracle
>> >> >> >> server
>> >> >> >> are
>> >> >> >> you
>> >> >> >> connecting?
>> >> >> >>
>> >> >> >> -- Robert
>> >> >> >> This posting is provided "AS IS" with no warranties, and
>> >> >> >> confers
>> >> >> >> no
>> >> >> >> rights.
>> >> >> >>
>> >> >> >>
>> >> >> >> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote
>> >> >> >> in
>> >> >> >> message
>> >> >> >> news:%237VyvMxvFHA.1648@.TK2MSFTNGP10.phx.gbl...
>> >> >> >> > Is the Department column defined as NVarchar2?
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > -- Robert
>> >> >> >> > This posting is provided "AS IS" with no warranties, and
>> >> >> >> > confers
>> >> >> >> > no
>> >> >> >> > rights.
>> >> >> >> >
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> >> >> >> > news:45D08B41-3791-4B9B-8C25-903F0DEC1CF4@.microsoft.com...
>> >> >> >> >>
>> >> >> >> >> We use Oracle database and SQL server 2005 Reporting
>> >> >> >> >> service Beta2. I made a simple SQL report using query:
>> >> >> >> >>
>> >> >> >> >> select sum(spend)
>> >> >> >> >> from travel_cost
>> >> >> >> >> where department in (:p_dept)
>> >> >> >> >>
>> >> >> >> >> I set parameter p_dept to be Multi-value: dept1 and
>> >> >> >> >> dept2. When I run report, if I only select dept1 or
>> >> >> >> >> dept2, it works fine. But when I select both or Select
>> >> >> >> >> All, I will get a error message:
>> >> >> >> >>
>> >> >> >> >> An error has occurred during report processing. Query
>> >> >> >> >> execution failed for data set 'DataSet1'. ORA-12704:
>> >> >> >> >> Character set missmatch.
>> >> >> >> >>
>> >> >> >> >> Please help.
>> >> >> >> >>
>> >> >> >> >> Frank
>> >> >> >> >>
>> >> >> >> >> .
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>