Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
trebor
Starting Member
5 Posts |
 Posted - 2006-05-17 : 13:41:12
|
| I have an MS Access Database with a memo field containing nearly 8000 characters. I can see all the data in the database. However, whenever I try to import the table using DTS into SQL Server the data for the memo field in the table does not import. The field header comes across, the data type changes, and no data is brought into the rows.All the other fields in the table come across perfect, including the data. Can someone shed some light on what I may be possibly doing wrong? Win2k Server SP 4SQL 2000 SP 4 |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2006-05-17 : 13:59:09
|
| You will need to use text or varchar(8000)JimUsers <> Logic |
 |
|
|
trebor
Starting Member
5 Posts |
Posted - 2006-05-17 : 14:03:31
|
quote:
Originally posted by JimL
You will need to use text or varchar(8000)JimUsers <> Logic
Jim, how do use text or varchar? Can you direct me to a link that explains the process? Thank you in advance, |
|
|
|
trebor
Starting Member
5 Posts |
Posted - 2006-05-17 : 14:11:43
|
| In the DTS wizard of SQL Server, I selected Access as the data source and browsed to the MDB file.I selected the database I wanted the data to be imported to. Selected "Copy table and views to the data source" radio option.Selected the table and transform option in the table row.In the trasnform grid, I modified the "description field" (that is the field that holds the 8000 characters of information) to varchar and 8000 as the size.Ran the wizard and still the data does not come across. Any help would be greatly appreciated. |
|
|
|
trebor
Starting Member
5 Posts |
Posted - 2006-05-17 : 15:01:48
|
| Okay, this is getting too funky. I ran the DTS again. Ran the column mapping and transformations for the particular table. Selected the transform tab and selected the transform informatin while it is being copied to source.I made sure in column mapping and transformations to make sure the datatype for the description field is varchar and the size is 8000.I selected preview and I can see the data in the field. However, once I run the package and go to the table and return all rows, nothing is in the field!Any ideas anyone? Please. |
|
|
|
trebor
Starting Member
5 Posts |
Posted - 2006-05-21 : 23:02:28
|
| Someone has got to have an idea about this issue?I decided to manually place the data back into the field after I exported the table and data from MS Access. So I decided to link the SQL Server tables from MS Access and then copy and paste the contents. Surprise! Surprise!I open the link tables in access and the data is there!When I link the table from MS Access I can see the memo field data of the sql server table. However, when I return all rows from SQL Server using the enterprise manager on the table it does not show the data. Some of you might think, "well the data is there but just not showing." Okay, I have ASP code that attempts to pull the data with an SQL Statement and read it into a web page. Nothing gets pulled or placed. Where is the data? This is a very upsetting? Any clues anyone! And please no jokes about MS products, switch to a real DB, or that is what I get for using a moped with training wheels. Let's just all try and deal with the issue please.TIA |
|
|
|
Crummock
Starting Member
4 Posts |
Posted - 2006-06-06 : 05:28:00
|
| In your data table add a row below the field heading row that states the data type to be used by the Access import wizard (memo for the big text field), save the file in csv file format and use the "Get external data wizard" to import the data. When I had this problem I imported my data in the csv file as a new table then I used an update query to distribute the new data to the appropriate records. The record containing the field types was then ignored by the update query.Andy |
|
|
|
|
|
|
|
|
Access Memo Field Does Not Import to SQL Server