Garbage appearing when displaying Oracle character fields

Hi,
I am having troubles with character fields being displayed as garbage characters in Reporting Services. I have a shared data source defined as "Oracle" not "OLE DB" going against an Oracle 9i database. When I run a query in the Wizard Query Builder (which I hear uses the OLE DB connection to Oracle) the characters display correctly without any garbage characters. When I run the query in the Generic Query Builder (which I hear uses the Oracle data processing extension) the character fields display with garbage in them. Also, when I view the data in the data layout under the Preview tab, I get the garbage characters. The garbage characters appear on the second or following rows, in which the prior row had the same value in that field. The fields can either be defined as varchar2 or char in Oracle of more than 1 character in length. varchar2(1) and char(1) data types seem to display correctly without the garbage. The problem also shows up when I deploy the report to the ReportServer and view it in the ReportManager.
Does anyone have any ideas?
Thanks!
MelindaI found out that our issue was that we were using the Oracle 8.1.5 client to connect to an Oracle 9i database. This was causing garbage characters to appear in our reports. Once I upgraded to use the Oracle 9.1 client, it worked as expected. I did some digging and found a small line in the documentation that suggests the minimum client is Oracle 8.1.7 or later.
"Melinda" wrote:
> Hi,
> I am having troubles with character fields being displayed as garbage characters in Reporting Services. I have a shared data source defined as "Oracle" not "OLE DB" going against an Oracle 9i database. When I run a query in the Wizard Query Builder (which I hear uses the OLE DB connection to Oracle) the characters display correctly without any garbage characters. When I run the query in the Generic Query Builder (which I hear uses the Oracle data processing extension) the character fields display with garbage in them. Also, when I view the data in the data layout under the Preview tab, I get the garbage characters. The garbage characters appear on the second or following rows, in which the prior row had the same value in that field. The fields can either be defined as varchar2 or char in Oracle of more than 1 character in length. varchar2(1) and char(1) data types seem to display correctly without the garbage. The problem also shows up when I deploy the report to the ReportServer and view it in the ReportManager.
> Does anyone have any ideas?
> Thanks!
> Melinda

Function to return "remaining" of field after it finds a character in the field.

Hi,

another problem I have is that have compounded fields in my sql table.

Example

product@.customer

I need a simple function to return "customer", so it should return the value
after "@.", unfortunate "@." will sometimes be character number 6, sometimes
character number 7 etc.

regards
JorgenSolutions was :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [sapserviceaccount].[UNTRUNK2] (@.inp as varchar(100))

RETURNS varchar(20) AS

BEGIN

declare @.out varchar(20);

if LEN(@.inp) 0

begin

set @.out = right( @.INP, (LEN(@.INP)-CHARINDEX('@.', @.INP)))

end

else

begin

set @.out = @.inp;

end

return @.out

END

"Jorgen [DK/2600]" <nyhedsgruppe_hejhej_@.gmail.comwrote in message
news:45daf1e7$0$90272$14726298@.news.sunsite.dk...

Quote:

Originally Posted by

Hi,
>
another problem I have is that have compounded fields in my sql table.
>
Example
>
product@.customer
>
I need a simple function to return "customer", so it should return the
value
after "@.", unfortunate "@." will sometimes be character number 6, sometimes
character number 7 etc.
>
regards
Jorgen
>
>
>

Function to find character in string

Hi:
I was looking through BOL for a function that would return the
numerical place where a string first occurs (similar to Crystal
Report's 'instr' function):
so
FUNKYFUNCTION('abcdefgd','d',1)
would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
argument is the starting place in the string.
Basically, what I want to do is trim everything after the first space
is found. I might have certain field values that end up as:
5445 UNWANTEDSTRINGETXT
I want this field to be changed to '5445' when it finds unwanted text.
So having this function would make this easy. Or maybe there is another
way in the absence of such a function?
Thanks for the help,
Kaydadeclare @.str varchar(80)
set @.str = '5445 UNWANTEDSTRINGETXT'
SELECT CHARINDEX(' ', @.str),
CASE WHEN CHARINDEX(' ', @.str) = 0
THEN @.str
ELSE SUBSTRING(@.str,1,CHARINDEX(' ', @.str)-1)
END
On 16 Feb 2006 16:13:03 -0800, "Kayda" <blairjee@.gmail.com> wrote:

>Hi:
>I was looking through BOL for a function that would return the
>numerical place where a string first occurs (similar to Crystal
>Report's 'instr' function):
>so
>FUNKYFUNCTION('abcdefgd','d',1)
>would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
>argument is the starting place in the string.
>Basically, what I want to do is trim everything after the first space
>is found. I might have certain field values that end up as:
>5445 UNWANTEDSTRINGETXT
>I want this field to be changed to '5445' when it finds unwanted text.
>So having this function would make this easy. Or maybe there is another
>way in the absence of such a function?
>Thanks for the help,
>Kayda|||The equivalent of instr in MS SQL is CHARINDEX(stringtofind,thewholestring)
with the parameter reversed from that of instr.
Regards,
Willson
"Kayda" wrote:

> Hi:
> I was looking through BOL for a function that would return the
> numerical place where a string first occurs (similar to Crystal
> Report's 'instr' function):
> so
> FUNKYFUNCTION('abcdefgd','d',1)
> would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
> argument is the starting place in the string.
> Basically, what I want to do is trim everything after the first space
> is found. I might have certain field values that end up as:
> 5445 UNWANTEDSTRINGETXT
> I want this field to be changed to '5445' when it finds unwanted text.
> So having this function would make this easy. Or maybe there is another
> way in the absence of such a function?
> Thanks for the help,
> Kayda
>

Function to Convert Char to Hexadecimal

Hi,
I'm looking for a function to convert a character to hexadecimal. E.g., 7 ->
37
Can anyone help pls ?
TIA !Desmond wrote:
> Hi,
> I'm looking for a function to convert a character to hexadecimal. E.g., 7
->
> 37
> Can anyone help pls ?
> TIA !
This works for the ASCII characters (0-255):
DECLARE @.c CHAR(1);
SET @.c = '7';
PRINT
SUBSTRING('0123456789ABCDEF',ASCII(@.c)/16+1,1)+
SUBSTRING('0123456789ABCDEF',ASCII(@.c)%1
6+1,1);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I'd created the function as below :
CREATE FUNCTION fn_Ascii2Hex (@.InputAscii varchar(2))
RETURNS int
AS
BEGIN
return SUBSTRING('0123456789ABCDEF',ASCII(@.Inpu
tAscii)/16+1,1) +
SUBSTRING('0123456789ABCDEF',ASCII(@.Inpu
tAscii)%16+1,1);
END
However, I got :
Server: Msg 195, Level 15, State 10, Line 1
'fn_Ascii2Hex' is not a recognized function name.
when I try to test the function using "select fn_Ascii2Hex(f1) from tab1;"
f1 is a varchar(2) column.
Advice please. Tia.
"David Portas" wrote:

> Desmond wrote:
> This works for the ASCII characters (0-255):
> DECLARE @.c CHAR(1);
> SET @.c = '7';
> PRINT
> SUBSTRING('0123456789ABCDEF',ASCII(@.c)/16+1,1)+
> SUBSTRING('0123456789ABCDEF',ASCII(@.c)%1
6+1,1);
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||did you try prefixing your function call with the owner name?
i.e.
select dbo.fn_Ascii2Hex(f1) from tab1
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:2CA29BBC-7988-4FD2-BA01-63AC829E943E@.microsoft.com...
> I'd created the function as below :
> CREATE FUNCTION fn_Ascii2Hex (@.InputAscii varchar(2))
> RETURNS int
> AS
> BEGIN
> return SUBSTRING('0123456789ABCDEF',ASCII(@.Inpu
tAscii)/16+1,1) +
> SUBSTRING('0123456789ABCDEF',ASCII(@.Inpu
tAscii)%16+1,1);
> END
> However, I got :
> Server: Msg 195, Level 15, State 10, Line 1
> 'fn_Ascii2Hex' is not a recognized function name.
> when I try to test the function using "select fn_Ascii2Hex(f1) from tab1;"
> f1 is a varchar(2) column.
> Advice please. Tia.
> "David Portas" wrote:
>
E.g., 7 ->|||Oh ! Now it works after I prefix it with dbo !
Many thanks Jims !
"Jim Underwood" wrote:

> did you try prefixing your function call with the owner name?
> i.e.
> select dbo.fn_Ascii2Hex(f1) from tab1
> "Desmond" <Desmond@.discussions.microsoft.com> wrote in message
> news:2CA29BBC-7988-4FD2-BA01-63AC829E943E@.microsoft.com...
> E.g., 7 ->
>
>