Memo field will not display more than 255 chars

Hello;

I am ammending another developer's app who used Crystal Reports 7.0. I am new to Crystal Reports. I have a field on a report that is defined in our Oracle database as a varchar2(4000). The ttx file for the report defines the field as String 4000. But the report will only print out 255 characters or less. We are only noticing this now as in the past the clients have not used this field to its extent (they have always enetered less than 255) But now they want to use it the way we set it up and the data is displaying accordingly in the application (multi-line textbox) when they enter more than 255, but the report won't print it. Please help if possible - it has been 2 days now looking at this. I will include ttx file content:

labour.ttx:

wokorder_no String 30
complaint_id String 40
complaint String 150
ata_code String 14
ata_description String 100
mech_name String 30
service_description String 4000<--field that won't print
estimate_description String 25
estimate String 5
actual_billing String 5
rate String 10
rate_description String 25
total String 12

I have also ensured that CanGrow is set to true, and the maximum number of lines is at '0' which according to documentation should ensure no limit on the number of lines. Any ideers anyone??

Thanks muchMuch to my surprise, the fix was:

data type "String" in ttx file needs to be "Memo" and the cursor location of the connection object must be client side. This is typically a great forum - I am surprised no one replied.|||I have same problem...in this case I not define datatype on ttx file but use recodset. The recodset use textbox object for stored on crystal report.

Memo field problem using Access97 with SQL2000 Backend

I have an application written in Access 97 that connects to a SQL2000
backend. One field is a description field that is a data type NTEXT in the
SQL database. In my access form, I can not enter more than 255 characters.

Before I converted the backend to SQL, the description field was a memo
field in Access.

What do I need to do to make it so I can enter more text into this field?NChar, NVarChar, and NText are double-byte unicode types. For
example, nchar(10) will hold 10 bytes of data but takes 20 bytes of
space. Convert the field's datatype to Text and you should be fine.

Look up "data types-SQL Server, described" in Books Online for more
info.

"Bob" <bobh@.wolv.tds.net> wrote in message news:<vta8f56bkredfd@.corp.supernews.com>...
> I have an application written in Access 97 that connects to a SQL2000
> backend. One field is a description field that is a data type NTEXT in the
> SQL database. In my access form, I can not enter more than 255 characters.
> Before I converted the backend to SQL, the description field was a memo
> field in Access.
> What do I need to do to make it so I can enter more text into this field?

Memo field in SQL Server 2005

I'm trying to create a site which allows me to add Memo type fields but when I insert or edit my record it will not take any of my text after an enter (vbNewLine).

In Access I used the field type "Memo" but I do not see that type in SQL Server 2005 just a nvarchar(max) which does not seam to work.

Thanks for the help!

Chad

Are you trying to enter the data directly or through a query in query analyzer. There is no Memo field in SQL 2005. varchar should be sufficient. Whether you need varchar(max) depends on how much data you want to put into it.|||

I tried directly in SQL Server and I also tried through my edit form in my application.

No work!

|||

If you are doing it from your application, and assuming you have some kind of textbox for users to enter data, you can replace linebreaks with vbcrlf when you isnert the data into the table.

txtBox1.text.replace(environment.newline,"<br>")

and do the reverse when you need to show the data back to the user.

|||How would you do that in ASP.net when I'm using a FormView to update and insert?|||Whether its update or insert, you have to pass the value the user entered to the proc/T-SQl. Instead of passing the value directly you can use the code I provided.|||

I'm sorry, I'm still not sure where you would add your code...I actually have used the replace alot in ASP but .net is a little diferent.

Do you add this in the code behind somewhere or in the aspx page?

again sorry for asking so much!

|||Yes it would go in your code behind. What I provided is .NET code. I assume for insert/update you prbly have the event code for the respective action.sql

Memo Field Comparison

I have SQL Server 7 running.

I recieve input from the user via a PHP form.

I have to match that input to the contents fo the memo field.

No matter what I do I receive errors like

1. Cannot compare VARCHAR
2.Warning: SQL error: [MERANT][ODBC SQLBase driver][SQLBase]00979 PRS PSR Plus sign required for outer join, SQL state 37000 in SQLExecDirect in F:\www\Apache2\htdocs\include\func_db.inc on line 25

generated by this code:
$bits_search_string = trim($bits_search_string);

$sql = "SELECT P.ID, P.DESCRIPTION"
. " FROM PART P, PART_BINARY PB"
. " WHERE P.ID = PB.PART_ID "
. " AND CONTAINS(PB.BITS, '$bits_search_string')";

or the latest

3.Warning: SQL error: [Microsoft][ODBC Cursor Library] Positioned request cannot be performed because result set was generated by a join condition, SQL state SL002 in SQLGetData in F:\www\Apache2\htdocs\include\func_db.inc on line 42

generated by this code:
$bits_search_string = trim($bits_search_string);

$sql = "SELECT P.ID, P.DESCRIPTION, PB.BITS"
. " FROM PART P, PART_BINARY PB"
. " WHERE P.ID = PB.PART_ID ";

$vdb->query($sql);
while ($row = $vdb->getRow(true)){
$memo = $row['PB.BITS'];

Where PB.BITS is the retarded memo field.

I can not change the database to have the field as text, there are
legacy applications dependent upon it.

I would appreciate any insight on how the memo field is structured.
What can I do to manipulate it. I can use PHP or JSP to create this
function. Those are the limitations.

Thank you very much,

I appreciate any feedback.There are a lot of usefull string functions that cannot be used on text (memo) fields, but you can use the CAST or CONVERT functions within your sql query to treat the fields as varchar datatypes. The disadvantage is that you can't index them.

blindman

MEMO datatype

i am migrating access database to sql server2005.
there is field with datatype "MEMO" in access.
can anebody tell me what is the compatible datatype for memo in sqlserver2005
I tried with varchar,varchar(max).
the field in access database conatains large comments

If by large comments you mean a lot of character data, most likely a nvarchar(max) or varchar(max) should work. What happened when you tried with varchar and varchar(max) ?

-Sue

|||

yes by large comments means lots of character data.

I am using SSIS package for importing the data.this package is created by using wizard.

To migrate the database i used upsizing wizard in access .

the column comments which is having datatype memo gets automatically converted to text_stream(DT_TEXT) datatype.

when I used varchar or varchar(max) for the corresponding column in Sql server 2005 the data got migrated but truncation occured. But when I modified the datatype from varchar to nvarchar the SSIS throws the warning that it can not convert unicode to non-unicode

|||

If you used [ varchar ] without a number indicating how many characters (maximum possibility is 8000) then the default of varchar(50) was used and your data would have been truncated.

If you used [varchar(max) ], you should not have experienced any truncation.

|||

,i used varchar(max) datatype . but problem is that the data in access contains special character end of line.

and I can see that after the first special character is encountered the data gets truncated from that point.

e.g

data in access is

08/04/05: Received request for a new Automatic YRT treaty covering UL plans. Assignment and Request Forms given to Rashmika to set up in the PDA & CTM. THE RETRO TREATY IS SUNL-99.
08/11/05: JEAN HAS REVIEWED THE TREATY - FINAL CHANGES MADE. JEAN EMAILED THE TREATY TO TIM IN WORD FORMAT SO THAT HE MAY INCLUDE THE BOLI LANGUAGE. WE STILL NEED TO DRAFT THE COVER NOTE FOR SUN LIFE.
08/11/05: Tim receives email from Carmen Walter - premiums are monthly in arrears with reinsurance premium = reinsurance rate x 1/12 annual guaranteed COI x NAR. Note annual guaranteed COI has a q/(1-q/12) adjustment in it.

and data that goes to sql server 2005 is

08/04/05: Received request for a new Automatic YRT treaty covering UL plans. Assignment and Request Forms given to Rashmika to set up in the PDA & CTM. THE RETRO TREATY IS SUNL-99.

can you suggest any solution for this?

|||Instruct the data migration process to use a different 'end of record' delimiter.

MEMO datatype

i am migrating access database to sql server2005.
there is field with datatype "MEMO" in access.
can anebody tell me what is the compatible datatype for memo in sqlserver2005
I tried with varchar,varchar(max).
the field in access database conatains large comments

If by large comments you mean a lot of character data, most likely a nvarchar(max) or varchar(max) should work. What happened when you tried with varchar and varchar(max) ?

-Sue

|||

yes by large comments means lots of character data.

I am using SSIS package for importing the data.this package is created by using wizard.

To migrate the database i used upsizing wizard in access .

the column comments which is having datatype memo gets automatically converted to text_stream(DT_TEXT) datatype.

when I used varchar or varchar(max) for the corresponding column in Sql server 2005 the data got migrated but truncation occured. But when I modified the datatype from varchar to nvarchar the SSIS throws the warning that it can not convert unicode to non-unicode

|||

If you used [ varchar ] without a number indicating how many characters (maximum possibility is 8000) then the default of varchar(50) was used and your data would have been truncated.

If you used [varchar(max) ], you should not have experienced any truncation.

|||

,i used varchar(max) datatype . but problem is that the data in access contains special character end of line.

and I can see that after the first special character is encountered the data gets truncated from that point.

e.g

data in access is

08/04/05: Received request for a new Automatic YRT treaty covering UL plans. Assignment and Request Forms given to Rashmika to set up in the PDA & CTM. THE RETRO TREATY IS SUNL-99.
08/11/05: JEAN HAS REVIEWED THE TREATY - FINAL CHANGES MADE. JEAN EMAILED THE TREATY TO TIM IN WORD FORMAT SO THAT HE MAY INCLUDE THE BOLI LANGUAGE. WE STILL NEED TO DRAFT THE COVER NOTE FOR SUN LIFE.
08/11/05: Tim receives email from Carmen Walter - premiums are monthly in arrears with reinsurance premium = reinsurance rate x 1/12 annual guaranteed COI x NAR. Note annual guaranteed COI has a q/(1-q/12) adjustment in it.

and data that goes to sql server 2005 is

08/04/05: Received request for a new Automatic YRT treaty covering UL plans. Assignment and Request Forms given to Rashmika to set up in the PDA & CTM. THE RETRO TREATY IS SUNL-99.

can you suggest any solution for this?

|||Instruct the data migration process to use a different 'end of record' delimiter.

Median calculation

I want to calculate the MEDIAN time interval between the two date field. In an OLAP cube one of the dimension consists of the two date fields as well as Interval(days) field (calculated at the data source view). How to create a Measure in the OLAP cube to calculate the MEDIAN Interval?

MEDIAN( <<Set>>[, <<Numeric Expression>>])

Since I'm not sure of the details your dimension, here's an Adventure Works sample query, for the Median duration in days of a Promotion:

>>

With Member [Measures].[MedPromoDays] as

Median(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Promotion].Properties("Start Date", TYPED),

[Promotion].[Promotion].Properties("End Date", TYPED)))

select {[Measures].[MedPromoDays]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

MedPromoDays
All Promotions 83.5
Discontinued Product 53
Excess Inventory 61
New Product 91
No Discount 1309
Seasonal Discount 30
Volume Discount 1095

>>

|||

And to slightly change Deepak's query to be more efficient:

With Member [Measures].[MedPromoDays] as

Median(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Start Date].MemberValue,

[Promotion].[End Date].MemberValue))

select {[Measures].[MedPromoDays]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

Median Calc

I'm trying to use the following to calculate the median by year for the fiel
d
"labor_rate". This should calculate the median for any year where the count
is ODD. I haven't started on the "ELSE" part to calculate when the counts
are EVEN.
Anyway, the following doesn't work. I get an "incorrect syntax" error
message near line 3 and it doesn't like my order by clause either.
Any help would be greatly appreciated. Thanks.
SELECT [YYYY], median from
(SELECT [YYYY]
(SELECT TOP 1 [LABOR_RATE] FROM
(SELECT TOP 50 PERCENT [LABOR_RATE] , [YYYY]
FROM [dbo].[tbl_Data] WHERE [YYYY] = z.[YYYY]
order by labor_rate) sub
WHERE [YYYY] = z.[YYYY]
group by [YYYY], [LABOR_RATE]
having COUNT(*) % 2 <> 1
ORDER BY [LABOR_RATE] DESC))
as median
FROM [dbo].[tbl_Data] z
group by [YYYY], median
order by [YYYY]
CraigThere is a whole chapter on Medians in SQL FOR SMARTIES, with several
different methods.
If you have SQL-2005, you can use the row numbering to create an
ascending column and a descendng column with an OVER() clause.
The median is the row wher these two columns:
1) Are equal to each other (odd number of rows)
2) The average of the columns where they differ by one (even number of
rows) .
I have not tried this yet; I am haivng a XXXXX of time getting 2005 on
my machines for some reason.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||Craig,
You're missing a comma after [YYYY] in line 2. That's why the
error is in line 3, where the ( is, because that's where the error
becomes apparent.
I didn't look further.
Steve Kass
Drew University
Craig wrote:

>I'm trying to use the following to calculate the median by year for the fie
ld
>"labor_rate". This should calculate the median for any year where the coun
t
>is ODD. I haven't started on the "ELSE" part to calculate when the counts
>are EVEN.
>Anyway, the following doesn't work. I get an "incorrect syntax" error
>message near line 3 and it doesn't like my order by clause either.
>Any help would be greatly appreciated. Thanks.
>
>SELECT [YYYY], median from
>(SELECT [YYYY]
>(SELECT TOP 1 [LABOR_RATE] FROM
> (SELECT TOP 50 PERCENT [LABOR_RATE] , [YYYY]
> FROM [dbo].[tbl_Data] WHERE [YYYY] = z.[YYYY]
> order by labor_rate) sub
>WHERE [YYYY] = z.[YYYY]
>group by [YYYY], [LABOR_RATE]
>having COUNT(*) % 2 <> 1
>ORDER BY [LABOR_RATE] DESC))
>as median
>FROM [dbo].[tbl_Data] z
>group by [YYYY], median
>order by [YYYY]
>
>|||Thanks. That cleared up the error. But one error is left: There is
incorrect syntax in the "From" line...can't figure it out.
Any help would be greatly appreciated. Thanks.
--
Craig
"Steve Kass" wrote:

> Craig,
> You're missing a comma after [YYYY] in line 2. That's why the
> error is in line 3, where the ( is, because that's where the error
> becomes apparent.
> I didn't look further.
> Steve Kass
> Drew University
> Craig wrote:
>
>|||...it's the last "From" line, FYI..
--
Craig
"Craig" wrote:
> Thanks. That cleared up the error. But one error is left: There is
> incorrect syntax in the "From" line...can't figure it out.
> Any help would be greatly appreciated. Thanks.
> --
> Craig
>
> "Steve Kass" wrote:
>|||Craig,
If you format the code more clearly, you'll see:
SELECT [YYYY], median from (
SELECT
[YYYY],
(
SELECT TOP 1
[LABOR_RATE]
FROM (
SELECT TOP 50 PERCENT
[LABOR_RATE],
[YYYY]
FROM [dbo].[tbl_Data]
WHERE [YYYY] = z.[YYYY]
order by labor_rate
) sub
WHERE [YYYY] = z.[YYYY]
group by [YYYY], [LABOR_RATE]
having COUNT(*) % 2 <> 1
ORDER BY [LABOR_RATE] DESC
)
) as median
FROM [dbo].[tbl_Data] z
group by [YYYY], median
order by [YYYY]
Note what "as median" applies to. It's the name of a table,
not the name of a column, as you probably intend. This
query currently says "select stuff from T from U", basically.
Get rid of the ) before "as median", and add it before "z",
and get into the habit of reading your code, not just moving around
symbols until it stops generating syntax errors.
SK
Craig wrote:

>...it's the last "From" line, FYI..
>

measure counting distinct values from a field

How do I count the amount of distinct values from a column in a fact table as a calculated measure

Assume the primary key of my fact table is a composite of three columns (a,b,c).

How can I code a calculated measure to count every distinct value of column a, not the amount of rows in my fact table.

Any advise help would be appreciated!

All you need to do is change the measure type from Count to Distinct Count ... make sure you select Column A as your key column....

MDX Question - Counting members in a Dimension with a "dateCreated" attirbute

Hello,

I'm fairly new to MDX and would like to add a calculated member:

In a dimension called DimCustomers I have a field called dateJoined which specifies when. Customers have the ability to create comments.

I have created a Fact table called FactComments, which has a id, customerid, timeid

What I would like is create a calculated member which uses the dateJoined field as a way to accumulate how many members had joined at given time.

e.g.

2005 - commentCount - memeberCountat2005 - comments/member-ratio
2006 - commentCount - memberCountin2006 - comments/member-ratio

The question is, is this possible with MDX or do i have to look at the data transferred to the DW and create a field called memberCount (or something like that... )

hope anyone can help.The PeriodsToDate MDX function seems like it would work for your scenario.