Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Error for long memo / text field

Posted on 2008-10-01
6
Medium Priority
?
451 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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 2000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 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