Solved

SQL Error for long memo / text field

Posted on 2008-10-01
6
428 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL tables, .net application and ETL connundrum 3 54
SQL Replication question 9 41
Sql Server group by 10 26
New to SSRS, extremely slow running report. 8 19
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how the fundamental information of how to create a table.

776 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