Memo fields from Access is empty in SQL Server

Hi
I have a DTS packet where I import data from a Access database to SQL Server.
But when I try to import data in memo fields in Access to a nvarchar field in a table on SQL server, there is no data imported.
Anyone know how to solve this problem?Not positive on this one, but I would try to map a memo field in access to a TEXT field in SQL. It should be pretty straightforward to test.

Regards,

Hugh Scott

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.

memo data type Import error while importing data from Access file into SQl Server 2005

I have one column in SQL Server 2005 of data type VARCHAR(4000).

I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column

data type converted into the memo type in the Access database.

now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.

Could you please let me know what is the reason?

I know that memo data type does not supported into the SQl Server 2005.

I am with SQL Server 2005 Standard Edition with SP2.

Please help me to understans this issue correctly?

If the wizard error says Unicode then change your data type in SQL Server 2005 to Nvarchar(max) because memo can grow above 4000 which is the limit for Nvarchar data type without the max key word. Try the link below for details.

http://msdn2.microsoft.com/en-us/library/ms186939.aspx

|||

Thanks for the reply.

However why SQL Server 2000 is not throwing this error? MSSQL 2000 is also contains the nvarchar data type.

|||That could the related to 2005 data types definition is more strict than 2000.

Memo Data Type

can this datatype hold formatted? e.g. carriage returns?

There is no Memo data type... that is used in Access, you should look at the nvarchar(max) data type, it will do what you need.

|||nice one cheers for that.

Craigsql

Memo Data Type

I am transferring my access backend to sql 2005. access has a memo
data type. I tried using NVarChar(MAX), it will let me enter 254
characters. Then it gives me an error that the field is too small - it
won't even accept the 254. Is there another data type in SQL that I
can use that would be similar to the memo data type?
-doodleHi
"doodle" wrote:

> I am transferring my access backend to sql 2005. access has a memo
> data type. I tried using NVarChar(MAX), it will let me enter 254
> characters. Then it gives me an error that the field is too small - it
> won't even accept the 254. Is there another data type in SQL that I
> can use that would be similar to the memo data type?
> -doodle
>
nvarchar(max) can hold 2^31 -1 bytes see
http://msdn2.microsoft.com/en-gb/library/ms130896.aspx. It is most likely a
limit of the application that you are using that restricts imput/output to
254 bytes, for instance the tools/options menus in Query Analyser or SQL
Server Management Studio can be configure to set how many characters are
displayed.
John|||John,
I checked it in SQL, it is currently set to 2147483647 bytes. What do
I need to change it to?
-doodle|||"doodle" <ADraughn@.mazakcorp.com> wrote in message
news:1172510102.622413.4400@.k78g2000cwa.googlegroups.com...
> John,
> I checked it in SQL, it is currently set to 2147483647 bytes. What do
> I need to change it to?
> -doodle
>
I don't believe you can. If you need to store more than 2 gigs in a memo,
you may want to rethink your datastructure.
(perhaps a pointer to a file on a disk or something.)
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi
"doodle" wrote:

> John,
> I checked it in SQL, it is currently set to 2147483647 bytes. What do
> I need to change it to?
> -doodle
>
You do not say how you are updating this value a statement such as
UPDATE MyTable
SET MyMemoColumn = 'A very long memo text that is much longer than this'
Will work fine.
To see what size this value is use the query
SELECT LEN(MyMemoColumn)
FROM MyTable
Query Analyser will not allow you to set a the number of output characters
to a value as large as 2147483647 neither will SSMS which has a maximum
display value of 8192. How did you obtain this figure?
John|||It's being updated by forms in access 97.
-doodle|||Hi
"doodle" wrote:

> It's being updated by forms in access 97.
> -doodle
>
Did you run the query from my last reply to see how big the data is?
John

Memo Data Type

I am transferring my access backend to sql 2005. access has a memo
data type. I tried using NVarChar(MAX), it will let me enter 254
characters. Then it gives me an error that the field is too small - it
won't even accept the 254. Is there another data type in SQL that I
can use that would be similar to the memo data type?
-doodleWhere are you trying to enter data, what is the exact error message
you are getting, and what application is generating the error?
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
doodle wrote:

>I am transferring my access backend to sql 2005. access has a memo
>data type. I tried using NVarChar(MAX), it will let me enter 254
>characters. Then it gives me an error that the field is too small - it
>won't even accept the 254. Is there another data type in SQL that I
>can use that would be similar to the memo data type?
>-doodle
>
>

Memo Data Type

I am transferring my access backend to sql 2005. access has a memo
data type. I tried using NVarChar(MAX), it will let me enter 254
characters. Then it gives me an error that the field is too small - it
won't even accept the 254. Is there another data type in SQL that I
can use that would be similar to the memo data type?
-doodleWhere are you trying to enter data, what is the exact error message
you are getting, and what application is generating the error?
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
doodle wrote:
>I am transferring my access backend to sql 2005. access has a memo
>data type. I tried using NVarChar(MAX), it will let me enter 254
>characters. Then it gives me an error that the field is too small - it
>won't even accept the 254. Is there another data type in SQL that I
>can use that would be similar to the memo data type?
>-doodle
>
>

Memo Data Type

I am transferring my access backend to sql 2005. access has a memo
data type. I tried using NVarChar(MAX), it will let me enter 254
characters. Then it gives me an error that the field is too small - it
won't even accept the 254. Is there another data type in SQL that I
can use that would be similar to the memo data type?
-doodleHi
"doodle" wrote:
> I am transferring my access backend to sql 2005. access has a memo
> data type. I tried using NVarChar(MAX), it will let me enter 254
> characters. Then it gives me an error that the field is too small - it
> won't even accept the 254. Is there another data type in SQL that I
> can use that would be similar to the memo data type?
> -doodle
>
nvarchar(max) can hold 2^31 -1 bytes see
http://msdn2.microsoft.com/en-gb/library/ms130896.aspx. It is most likely a
limit of the application that you are using that restricts imput/output to
254 bytes, for instance the tools/options menus in Query Analyser or SQL
Server Management Studio can be configure to set how many characters are
displayed.
John|||John,
I checked it in SQL, it is currently set to 2147483647 bytes. What do
I need to change it to?
-doodle|||"doodle" <ADraughn@.mazakcorp.com> wrote in message
news:1172510102.622413.4400@.k78g2000cwa.googlegroups.com...
> John,
> I checked it in SQL, it is currently set to 2147483647 bytes. What do
> I need to change it to?
> -doodle
>
I don't believe you can. If you need to store more than 2 gigs in a memo,
you may want to rethink your datastructure.
(perhaps a pointer to a file on a disk or something.)
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi
"doodle" wrote:
> John,
> I checked it in SQL, it is currently set to 2147483647 bytes. What do
> I need to change it to?
> -doodle
>
You do not say how you are updating this value a statement such as
UPDATE MyTable
SET MyMemoColumn = 'A very long memo text that is much longer than this'
Will work fine.
To see what size this value is use the query
SELECT LEN(MyMemoColumn)
FROM MyTable
Query Analyser will not allow you to set a the number of output characters
to a value as large as 2147483647 neither will SSMS which has a maximum
display value of 8192. How did you obtain this figure?
John|||It's being updated by forms in access 97.
-doodle|||Hi
"doodle" wrote:
> It's being updated by forms in access 97.
> -doodle
>
Did you run the query from my last reply to see how big the data is?
John

Memo Data Type

Hi
"doodle" wrote:

> I am transferring my access backend to sql 2005. access has a memo
> data type. I tried using NVarChar(MAX), it will let me enter 254
> characters. Then it gives me an error that the field is too small - it
> won't even accept the 254. Is there another data type in SQL that I
> can use that would be similar to the memo data type?
> -doodle
>
nvarchar(max) can hold 2^31 -1 bytes see
http://msdn2.microsoft.com/en-gb/library/ms130896.aspx. It is most likely a
limit of the application that you are using that restricts imput/output to
254 bytes, for instance the tools/options menus in Query Analyser or SQL
Server Management Studio can be configure to set how many characters are
displayed.
John
"doodle" <ADraughn@.mazakcorp.com> wrote in message
news:1172510102.622413.4400@.k78g2000cwa.googlegrou ps.com...
> John,
> I checked it in SQL, it is currently set to 2147483647 bytes. What do
> I need to change it to?
> -doodle
>
I don't believe you can. If you need to store more than 2 gigs in a memo,
you may want to rethink your datastructure.
(perhaps a pointer to a file on a disk or something.)
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hi
"doodle" wrote:

> John,
> I checked it in SQL, it is currently set to 2147483647 bytes. What do
> I need to change it to?
> -doodle
>
You do not say how you are updating this value a statement such as
UPDATE MyTable
SET MyMemoColumn = 'A very long memo text that is much longer than this'
Will work fine.
To see what size this value is use the query
SELECT LEN(MyMemoColumn)
FROM MyTable
Query Analyser will not allow you to set a the number of output characters
to a value as large as 2147483647 neither will SSMS which has a maximum
display value of 8192. How did you obtain this figure?
John
|||Hi
"doodle" wrote:

> It's being updated by forms in access 97.
> -doodle
>
Did you run the query from my last reply to see how big the data is?
John
sql