?
Solved

importing Access Data via linked table to sql server express 2008: error SYSTEM RESOURCE EXCEEDED

Posted on 2009-12-21
6
Medium Priority
?
828 Views
Last Modified: 2012-05-08
access 2003
ODBC linked table to sql server 2008 express

I'm getting a  error "SYSTEM RESOURCE EXCEEDED"
When appedning data to a sql sever  database via query from access table OR a text file ?

3 fields and their types
text- 15  to  varchar(15)
text 255 to varchar(255)
memo     to   text

Any suggestions.  I have also tried using the import/export wizard..
Still will not import.
I have also tried just importing the table from access database. It will not import !   ??

Thanks
fordraiders







0
Comment
Question by:Fordraiders
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:HetAlps
ID: 26103202
Refer this


http://support.microsoft.com/kb/918814

You will have to change your complex queries to simple one.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26103899
>> sql server express 2008: error SYSTEM RESOURCE EXCEEDED

Since you are using SQL Server 2008 Express, you have some resource limitations like

* 1 Processor
* 1 GB RAM
* 4GB Data File..

And hope you have huge records in either the Access DB or your existing Express database and hence you receive this error during IMPORT operation from Access to this table..

Also check if you have huge data in your memo column so that it might increase the data file size and might receive this error out of this..
0
 
LVL 3

Expert Comment

by:Paul Thompson
ID: 26103938
The SQL Server "text" field could be the problem.

The "text" data type is depreciated in SQL 2005 onwards.

Try using an "nvarchar(max)" column to link to your Access "Memo" data field.

nvarchar(max) handles resources very differently to a "text" field.

DJ
0
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1000 total points
ID: 26104842
Dr_Jacuzzi,
     text datatype is going to be deprecated in future versions but is still available in SQL Server 2008.
But as I mentioned earlier, data present in the memo column would be having huge size causing the database to grow beyond 4 GB which might be causing this problem..

http://msdn.microsoft.com/en-us/library/ms187993.aspx

fordraiders,
    Kindly convert text column to varchar(max) as a best practice..
0
 
LVL 3

Assisted Solution

by:Paul Thompson
Paul Thompson earned 1000 total points
ID: 26105930
Hi  rrjegan17,

TEXT, NTEXT and IMAGE were all deprecated in SQL Server 2005.

http://msdn.microsoft.com/en-us/library/ms143729%28SQL.90%29.aspx

They still exist for backward compatibility but may be removed completely at some future point. All new development projects should use the VARCHAR(MAX) data type instead of TEXT.

I can't see how Microsoft are going to get rid of such a widely used data type, Millions of applications will not be able to upgrade because of extensive use of TEXT fields.  

In Access 2003, the Memo data type can only be 65,536 bytes so even if each Memo field was fully populated to it's limit, fordraiders could have about 60,000 rows before he fills the SQL database to it's 4Gb capacity.

fordraiders,
rrjegan17 has a point, You may want to check the size of your SQL Data files.
Do you know if your Memo fields are full (64K) or is it a Memo field to handle a data that "could" be large or small?
SQL TEXT fields in SQL Server can be bigger than the Memo field in Access, This can cause a problem if some other application or script has altered the data so it's larger than Access can read.
Either way, you should be using varchar(max) or nvarchar(max) instead of TEXT.

Of course the problem may be something completely unrelated.

DJ
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26106529
>> TEXT, NTEXT and IMAGE were all deprecated in SQL Server 2005.

even that was mentioned in the link which I provided earlier and it do exists in SQL Server 2008..
And I am able to use TEXT, NTEXT and IMAGE in SQL Server 2008( just confirmed it)..

And agree with your recommendation not to use it out..

>> In Access 2003, the Memo data type can only be 65,536 bytes so even if each Memo field was fully populated to it's limit, fordraiders could have about 60,000 rows before he fills the SQL database to it's 4Gb capacity.

yes.. you are correct..
But we need to think about the pre-existing tables in the database along with their data too right.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question