Solved

SQL Error for long memo / text field

Posted on 2008-10-01
6
438 Views
Last Modified: 2012-06-22
I'm trying to import data from Excel to a SQL table (SSIS)

All fields go in ok, except a memo field, which has variable, long text (notes).

I've attached the SQL error, it says it cannot got in because it's trunkated. I'm not sure if the field type is correct (nvchar), I've tried to change that, it did not help.

Please help... thank you!
Report1.txt
0
Comment
Question by:Moujan19
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 22619994
try 'text' in case of nvarchar
0
 

Author Comment

by:Moujan19
ID: 22620088
Yes, very strange, on other fields it works, on this field, when I change it, it still reads (at the bottom) nvchar (255) although the drop down has been changed to text
0
 
LVL 1

Expert Comment

by:MaxLyd
ID: 22623102
I 've come across a similar situation in the past with the truncation error.

Is it necessary to use SSIS in this case?

I ask this because you can create a linked server in SQL 2005 that will read directly from the spreadsheet and allow you to in effect query from the spreadsheet as if it were a table. This way, prior to import, you can choose to cast the memo field as nvarchar(max) which will normally facilitate the length of text you are mentioning.  Acts like a staging area for the data so you can more actively control the import process and deal with the errors.  Not as automated as using SSIS, but could help in the short run.

Also, another question would be if you needed to keep all the data in the memo field. If that wasn't the case, you could always take the left 255 characters and discard the remainder. This would stop the pipeline from blowing up on you on import.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:Moujan19
ID: 22627707
Not sure why SSIS, I was instructed to do that by someone here. I'll try it without.. I think I see where you are talking about, during import we can run the query on it and import only what we need. I think this is where you are talking about? and how would I cast it as navchar(max) on the way in?

Thankyou!
0
 
LVL 1

Accepted Solution

by:
MaxLyd earned 500 total points
ID: 22629328
What I was originally describing was setting the Excel file as Linked server through your SQL Server Management Studio.
To do this you expand the 'Server Objects' folder, then right click on the 'Linked Server' Folder, then select 'New Linked Server'
You will be prompted for:
'Linked Server' - which is the name you choose for the linked server
'Provider' - which you will have to select from a drop down list
  • For Excel 2003 use - 'Microsoft Jet 4.0 OLE DB Provider'
  • For Excel 2007 use - 'Microsoft Office 12.0 Access Database Engine OLE DB Provider'
'Product Name'
  • For Excel 2003 use - 'Microsoft OLE DB Provider for Jet'
  • For Excel 2007 use - 'Microsoft .ace.oldedb.12.0'
'Data source' - this is the file path including file name for the excel file you are linking
'Provider String'
  • For Excel 2003 use - 'Excel 5.0'
  • For Excel 2007 use - 'Excel 12.0'
Once you do that you can query this data directly without using the import wizard.
 I included an example of some code that pulls from linked excel file with with the linked server name 'CUSTOMER_LINKED' and the sheet name 'Sheet1'.   The data is queried and only the fields that are needed are inserted into a table 'Customer' in the 'test' database.  I also include an example of the CAST you mentioned.
I hope this proves helpful.

Insert test.dbo.Customer
Select 
CustomerID, Customer_Name, Customer_Country,
Customer_State, CAST(Customer_notes as nvarchar(max)) as Customer_notes
from
   (
   SELECT * 
   FROM OPENQUERY(CUSTOMER_LINKED, 'SELECT * FROM [Sheet1$]')
   ) as A

Open in new window

0
 

Author Closing Comment

by:Moujan19
ID: 31502199
Superb! Thank you for the GREAT Help! - This was very helpful.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

734 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