(Is there a better NG for SSRS questions?)

I created three tables in SQL Server 2008, all with

Code char(1) not null,
Description varchar(50)

then three sprocs, all by

create name_menu as
begin
set nocount on;
select Code,
Code + ' - ' Description as Description
from name;
end;

(name being the part that varies)

Added each sproc to an SSRS 2008 report as a dataset. then added each
dataset as a parameter, with Code as Value, Description as label

In the report, two of the menus look as expected, but _one_ shows only
the code, the ' - ' and the first letter of the description. If I add
the parameter value to the report as a textbox, the description part has
the "boxes" typical of trying to display UTF-16 little endian in an
ASCII field.

I searched the XML of the report and found that all non-numeric
datatypes are String or System.String

The Parameter Properties dialog has a datatype menu which does not
allow for this distinction. Choices are
text
date/time
integer
boolean
float

The dataset properties dialog has no place to see or say anything
about datatypes.

I checked

SELECT inf...schema.columns where DATATYPE = 'NVARCHAR'

....and there are none.

I deleted the sproc and ran the create statement again, then told SSRS
to refresh the fields.

Older versions of SSRS used to cache data and sometimes would fail to
refresh, so I looked for cache files to delete. Didn't find any.

I should be able to cast the results, but I shouldn't have to. (And it
wouldn't surprise to be told "you can't do that")

Advice?

--
Wes Groleau

How to lose an argument gracefully
http://Ideas.Lang-Learn.us/BlindDog?itemid=4013

I don't know much about SSRS.

Having said that, you should at least double check that the column
Description is really declared as varchar(50), and not accidentally as
varchar(1) (which you would get if your original create script used the
datatype "varchar").
--
Gert-Jan


Wes Groleau wrote:

On 03-09-2011 14:16, Gert-Jan Strik wrote:
Yes, it is. I checked all details and foud them all as they should be.
All details except ONE:

Don't know how this happened, but "something" had converted the
original text in the table to UTF-16, and inserted it back into
the table as if it were ASCII, making every other character a null.

SSRS was attempting to render what was actually there as the datatype it
was claimed to be.

Since I had seen the correct text in the table shortly before seeing the
bogus text in SSRS, I didn't bother looking there again. A colleague
actually found it.

This reveals two bugs though: SSRS did the right thing in a text box,
showing each null as a "box." But in the _menu_, it treated the first
null as the end of the string. And SSMS did the same thing in the
results grid of a select.

--
Wes Groleau

Hail that dog!
http://Ideas.Lang-Learn.us/BlindDog?itemid=4020