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 |
|
taunt
Posting Yak Master
128 Posts |
 Posted - 2014-06-10 : 17:53:06
|
| Hello, I'm trying to import data from a delimited file into a table. I have truncate set to ignore in import file. I run the job it will succeed, but imports nothing. Is there a way to turn off truncate in a table? I believe that is where the issue is. Is there any issues with turning truncate off? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-10 : 17:57:44
|
| You can't turn it off. TRUNCATE cannot activate a trigger either.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-06-11 : 01:30:58
|
| Can you confirm you have INSERT permissions to the table?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-06-11 : 04:18:27
|
It depends which truncate you are talking about? Truncate table as in "empty" the table?Are you talking about truncating data in column? This is what I think you refer to. You are importing larger pieces of data that can fit into the target columns.If that's the case, you can turn off warning and error for truncate information.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2014-06-12 : 13:40:42
|
quote:
Originally posted by SwePeso
It depends which truncate you are talking about? Truncate table as in "empty" the table?Are you talking about truncating data in column? This is what I think you refer to. You are importing larger pieces of data that can fit into the target columns.If that's the case, you can turn off warning and error for truncate information.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
If you do that, don't you also have to setup an error path for the rows that will be skipped due to the truncation? If not, then you would not get that row and have no way of identifying the failure.The only other options are to increase the size of the destination column, or perform a data conversion to truncate the data so it will fit in the destination column. |
|
|
|
taunt
Posting Yak Master
128 Posts |
Posted - 2014-06-12 : 18:56:03
|
| OK I finally figured out what my issue was. I had to turn on retain null values in the original file. That was the issue why the import wouldn't import. I still have a issue with a column truncating (after the 49th character). I have ignore truncate turned on. The column is set to nvarchar(MAX). The conversion is set to length of 4000, but yet the column still gets cut off. What might be the cause of this? |
|
|
|
taunt
Posting Yak Master
128 Posts |
Posted - 2014-06-18 : 15:16:51
|
| OK I'm beyond stumped. Here's my info... The field in sql is set up as this: nvarchar(max). I'm trying to import from a flat file (a comma separated txt file). The column in question in advanced settings type: string [DT_STR], width: 4000. That goes to a data conversion that has the settings convert to Unicode string [DT_WSTR], width: 4000, but what keeps happening is after the 50th character it will cut off on the additional text. What's the issue? What am I missing?Thanks |
|
|
|
|
|
|
|
|
Turn Truncate Off in a Table