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?
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies View RelatedHi, can anyone post the steps to upgrade the backend of issuetracker to sqlexpress 2005? Thanks a lot.
View 1 Replies View RelatedHi,
I'm importing an Access database to SQL Server 2000.
The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import?
I would appreciate any pointers.
Hi all,
i've a reasonable amount of experience with MS Access and less
experience with SQL Server. I've just written an .NET application that
uses an SQL Server database. I need to collate lots of data from around
the company in the simplest way, that can then be loaded into the SQL
Server database.
I decided to collect the info in Excel because that's what most people
know best and is the quickest to use. The idea being i could just copy
and paste the records directly into the SQL Server database table (in
the same format) using the SQL Server Management Studio, for
example.
Trouble is, i have a problem with line feed characters. If an Excel
cell contains a chunk of text with line breaks (Chr(10) or Chr(13))
then the copy'n'paste doesn't work - only the text up to the first line
break is pasted into the SQL Server database cell. The rest is not
pasted for some reason.
I've tried with MS Access too, copying and pasting the contents of a
memo field into SQL Server database, but with exactly the same problem.
I've tried with 'text' or 'varchar' SQL Server database field formats.
Since i've no experience of using different types of databases
interacting together, can someone suggest the simplest way of
transferring the data without getting this problem with the line feeds?
I don't want to spend hours writing scripts/programs when it's just
this linefeed problem that is preventing the whole lot just being
cut'n'pasted in 5 seconds!
cheers
Dominic
Hi,
Can anyone point me any solution how to export a MEMO field from an Access database to a TEXT field from an MS SQL Server 2000. The import export tool from SQL server doesn't import these fields if they are very large - around 9000 characters.
Thanks.
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
Hey guys. I have a SQL query I'm trying to create. It's nice and dandy, but I have a memo field that's being trunicated to 256 characters.
SQL is something like this:
Code:
Select distinct `group`,sortorder,HideLabel,category,img,Description,Null as num,Null as subcat
from catalogimages
where Inact=0 and subcateg is NULL
group by `group`,sortorder,HideLabel,category,img,Description
order by `group`,sortorder,category
Description would be the memo field.
Is the 'GROUP BY' clause even necessary here?
I'm also willing to bet that the 'DISTINCT' clause might not be necessary.
Any help would be greatly appreciated.
I have an Access application with various DB's linked together. One of the DB's contains a field, SOURCECODE, which was mistakenly entered in as an nvarchar(4000) field in MS Sql Server. When I link the table, Access converts it to a memo field.
There will never be more than 10 chars in that field. The company that created the DB says its will be too risky to change the data type. I need to link this field to another field that's a VARCHAR.
How can I do this? Access doesn't allow the CAST feature.
Hello All,
Does anyone know how to convert data in a Memo field from Foxpro into a Text field in SQL table? Straight import via DTS package seems to entirely ignore this memo field and result in blank.
Thanks in advance for your reply.
Koann
Our company is migrating a Microsoft Access 2010 backend database to a SQL Server 2008 database. One of the memo fields in the Access backend can store up to 150 Kb of Unicode data. To store this data in SQL server, we found that we can use the following data types:
ntext = (2^30) - 1 = 1,073,741,823 bytes.
nvarchar(max) = (2^31) - 1 = 2,147,483,647 bytes.
Because ntext will be deprecated in future releases of SQL Server, the only good alternative to store an Access memo field in SQL server is to use nvarchar(max), which is what Microsoft recommends for large Unicode texts.Storing a large amount of text like 150 Kb in an nvarchar(max) field using only SQL server works as expected. However, if Access is used to store the data in a table linked to SQL server, the maximum number of characters allowed is only 4000. We found that this limitation is imposed by the ODBC driver that limits nvarchar(max) to 4000 characters.
The connection string we are currently using to link a table to SQL server is this:
ODBC;DRIVER={SQL Server Native Client 10.0};SERVER= SQLEXPRESS;DATABASE=TestDB;Trusted_Connection=No;UID=uid;PWD=pwd;
Any solution for this limitation storing large amounts of data in a Microsoft Access memo field mapped to an nvarchar(max) data field in a SQL Server database?
I'm having some problems importing data from a memo column (Access) into varchar column in SQL Server.
My idea was to use slowly changing dimesion to identify modified and new rows. No matter what data type I use to convert the memo column (using Data Conversion Transformation) and then using the converted column in SCD, I get the following error :
'The SCD transform does not allow mapping between columns of different types except for DT_STR and DT_WSTR.'
What do I have to do to get Memo column 'to behave' as a string?
Same problem with a different data type - decimal (18,5) in sql server - no matter what datatype I use in Data Conversion Trans, I get the same error trying to generate scd.
Thank you for your answers.
I have used DTS in SQL Server 2000 to import an MDB filed (MS ACCESS) of a table. When the table is imported the primary key is lost and the memo field data is completely gone.
I use the tranformation option in the DTS wizard to add the primary key and make sure the data type for the memo field is varchar and has a size of 8000. I need that large size since I am storing lots of html code.
When I preview the data I see the html code that is supposed to get imported. However, when I return all rows from the table in Enterprise Manager the field is empty.
So I tried to manually copy the data from the MS Access Database into SQL Server. Could not figure out if SQL Server has an interface like MS Access to simply copy data into a table. So I linked to the tables from MS Access to the SQL Server table.
When I opened the linked table I see the data in the description field. However, if I return the rows from within SQL Server no data is present.
I have some ASP code trying to read the data in the SQL Server table. However, nothing is returned and when I run the SQL Statement, nothing gets returned. The SQL statement returns all rows. All the other data is present but nothing in the description field.
What am I doing wrong? Any suggestions anyone, please!
TIA
I would like to retrieve and view .jpg pictures that have been stored in a Data Type "image", Length "16", SQL2000 field.
Any ideas on "How to......."
Thanks alot and enjoy
HandyMac
Hi AllI am finding unexpected results when inserted into a newly createdtable that has a field of datatype int identity (1,1).Basically the order I sort on when inserting into the table is notreflected in the order of the values from the identity field.Have I been wrong in assuming that it should reflect the order from thesort?The code is ...create table tmp (A varchar(50), L float, C int identity(1,1))insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Lland I don't understand why the values in tmp.C aren't in the ordersuggested by the sort.Any comments most appreciatedBevan
View 13 Replies View RelatedI have migrated a database from Access to SQL. Promlem is that I cannot edit the records in the now
linked tables. We are still using the Access front end. Why can't we edit the records?
Thanks in advance.
Hi. Silly question time please.
I'm a newbie when it comes to SQL7.
I'm trying to connect via Access 97 VBA to a SQL server table. I can't seem to get it working at a basic level.
Has anyone got the basic syntax or sample piece of VBA code to connect to a SQL server. I've been searching everywhere to no avail so far.
Dave.
If i create ODBC links for a SQL server db in Access and make the file read-only, i find that 2 people cannot open teh file simultaneously...
any ideas why and any suggestions how to over come thi problem...any solutions will be highly appreciated
regards,
resh
I have an Access 97 database that I would like to "mirror" in my SQL server 2000 database.
Basically I would like SQL server to keep a current copy of an Access 97 database table which exists on remote machine on our network. Any time there is a change made to the Access table, I would like the SQL server to be updated automatically.
Is this something that is possible to do with replication/synchronization, or do I have to push the data up with another app?
Thanks
The website i'm building is using an access97 database. Actually the database isn't very big but i hope it will become. So i would like to convert it to SQL 7.0 which seems to be more powerfull. Is there a software which can do it automatically? What are the modifications needed by ODBC ?
Thanks !
When I try to link tables from Access97 to SQL7, all the columns have #deleted# in them. Does any one know why this is?
Thanks
Phil
I am trying to link a table from a MS SQL database into access97 thru ODBC. When I do this, I get the message "Can't define field more than once." When I look at the table in SQL, there are no duplicate field names, however, when I bring the table in to do a Crystal Report, I see there are 2 fields that have duplicate field names 6 times. (Evidently Crystal doesn't care about this as Access does.) Any clues on what is happening?
View 2 Replies View RelatedI would like to create a MSAccess97 front end for some SQL7 data.
I created an odbc source system dsn for the server holding the SQL7 data.
When I try to add a table to my Access97 database by linking it to a SQL7 table, the only choices presented me are tables in the default database for SQL7 on my server. How do I link to tables in another database on that server? Thanks.
When Access date/time data is transformed to SQL7, it seems to work fine as long as the data contains date information. But, if the data is time only, there is the 2-day diff. coming out of Access.
For date/time data in Access, day 0 is 12/30/1899--for SQL7 it is 1/1/1900.
So, it appears that DTS first converts Access date/time data to a date formatted string and then sends to SQL. This will only be a problem for times....8:00 AM is stored to SQL as 8:00 AM on 12/30/1899, not 1/1/1900. If I try to calculate hours or minutes from midnight (date/time 0) I get a negative number.
Has anyone else encountered this? I guess my choice is to modify DTS to correct for the dates or to run an update once the data is moved.
I`m trying out SQLServer7, have installed it on my workstation, have been able to open access97 and link to sql7 tables I created. Want another user to use access97 to set up her own database and link to my sql7 tables. Do I need to install anything from the sql7 beta disk on her desktop? Right now she can`t link a sql7 table to her access97 db. Thanks.
View 1 Replies View RelatedWe have an Access97 database with 122 tables. I try to use import and export wizard to import the data from access into SQL 2005 database. I find an interesting problem. When select all tables, the wizard give following errors:
Pre-execute (Error)
Messages
Error 0xc0202009: {DBD1EAB5-7865-4B89-A7BB-DDC8507D8119}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 64 - PlanClasses" (5206) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)
But if I divide importing as 2 steps:
1. import all tables before PlanClasses, it works.
2. import all tables after PlanClasses including PlanClasses, it works.
If I just import PlanClasses itself, it works, but I can not import all tables at once. Interesting thing is if I just unselect PlanClasses, it will have same error at the class right after PlanClasses. Is there size limitation when doing import at once?
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
Does Access2000 upsizing wizard support migration of tables to Sql 2000?
View 1 Replies View RelatedGreetings!
I recently rewrote an old ACCESS 97 application to work with MSSQL 7.
The program works fine until I use a form whiche opens several linked tables at the same time. When closing the Form I get (At unpredictable moments) an ODBC SQL SERVER DRIVER update failure error message.
All rights are set on the tables for Public and even then i keep getting this error at very odd moments. I also had a look in Access at the ODBC refresh rate which is set at 10sec , OLEDB refreshrate 10 sec, and refresh is set at 5sec.
I tried different combinations of timeout settings and even renewing the MDAC to version 2.7.
Nothing helps.
Can anyone give me a tip as to look for the possible source of the error and how to fix it?
Thanks!
Vincent JS
Hi everyone,
I'm extremely new to SQL so be nice
I am attempting to write a script to add onto the end of a text field the words " -- Disposed " (About 60 rows worth).
The field is a TEXT field, so unlike a varchar field I can't just use Update as shown below.
Code:
Update AR_Primary_asset
Set AR_Primary_asset.description = AR_Primary_asset.description + ' -- Disposed'
Where AR_Primary_Asset.ASSET_REF in ('1','2','4')
I found on the Mircosoft pages about UPDATETEXT, but this only seem to work to update one row (In the case below Asset_ref = 3, was the only row effected) .
Code:
DECLARE @Dispose binary(16)
SELECT @Dispose = TEXTPTR(DESCRIPTION)
FROM AR_PRIMARY_ASSET
WHERE AR_Primary_Asset.ASSET_REF in ('1','2','3')
UPDATETEXT AR_PRIMARY_ASSET.DESCRIPTION @ptrval null null ' -- Disposed'
So i wrapped it into a cursor, this worked on my test SQL server which runs SQL2005.
Code:
DECLARE @Dispose varbinary(16)
DECLARE cursor1 CURSOR FOR
SELECT TEXTPTR(DESCRIPTION)
FROM AR_Primary_Asset
Where AR_Primary_Asset.ASSET_REF in('1','2','3')
OPEN cursor1
FETCH NEXT FROM cursor1
INTO @Dispose
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATETEXT AR_Primary_Asset.DESCRIPTION @Dispose NULL NULL ' -- Disposed'
FETCH NEXT FROM cursor1
INTO @Dispose
END
CLOSE cursor1
DEALLOCATE cursor1
But when it was run on our SQL2000 server it gave the following error message
Quote: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
I've never used vars, cursors, updatetext or even text fields before. So maybe I am going about it totally the wrong way.
Is anyone able to tell me a better way to write this? or how to make it compatible to SQL2000?
Hi, currently i am doing discussion forum for my project.
What I want to ask is what data type that I have to set for the message. Previously I am using nvarchar which I thought it would be ok, but when the time I save the message the ENTER command is become space in the database. For example I type
Message 1
Message 2
When I save into the database it become
Message 1 Message 2
I am new in using MS SQL server 7, so please give any suggestion about this.
Thanks,
RED
I have several memo fields in Access, whenever I use DTS to import the data into SQL,
These MEMO fields gets truncated.
Any tricks or suggestions?
Thanks in advance,
Harry
I have an access database that I am about to move over to SQL Server. In there I have member table that has a memo field. This table has the potential to grow quite big. My question is: what issues does having a large text field have on my database. Will it slow down my updates, searches or inserts? I plan on using this field for such things like a biography of the member. Is using a text field the best way to go, or should I look at text files instead? Many thanks,
View 1 Replies View Related