-1

I'm trying to update the contents of a Text datatype with a new version of o file with no success. The table in SQL Server 2012 has a column of the text datatype and already has the contents of a text file in it.

I want to update those contents. I know I can update it manually using

UPDATE [MANUTXML].[dbo].[MANUTXML]
   SET [SOURCEXML] = REPLACE(CAST([SOURCEXML] AS VARCHAR(max))

But I need to make a lot of changes to the original file so it's a lot easier to replace the entire file.

I've already tried to do it in Management Studio 2012 and today I've tried using C# do do the same. The result is the same

String or binary data would be truncated. The statement has been terminated.

Any help please. Either C# or T-SQL. I just need to insert a file from a string read or copied from the filesystem?

Kevin Hogg
  • 1,771
  • 25
  • 34
Luís Marinho Dinis Ferreira
  • 27
  • 6
  • you are casting to varchar(max), that's 8192 characters. It will also mess up any encoding, should be nvarchar(max) which is only 4096 characters. Text data type is effectively unlimited... Have alook at this, http://stackoverflow.com/questions/2579373/saving-any-file-to-in-the-database-just-convert-it-to-a-byte-array – Tony Hopkinson May 26 '14 at 11:15
  • http://stackoverflow.com/questions/564755/sql-server-text-type-vs-varchar-data-type#answer-564811 – Bharadwaj May 26 '14 at 11:17
  • Why are you casting to `varchar(max)` ? – st4hoo May 26 '14 at 11:20
  • its quite unclear what you are asking? pls do provide a example ! supporting your `query` ! – vhadalgi May 26 '14 at 11:23
  • 2
    @TonyHopkinson: `Varchar(max)` is actually **2 billion** characters (not 8192) .... – marc_s May 26 '14 at 11:49
  • 5
    `ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) - and for XML contents, use `XML` - that's what that datatype is there for! – marc_s May 26 '14 at 11:51
  • this is a legacy database that they have me workking with.I can't change it although i know it will be deprecated in future versions. I don't really know much about SQL SERVER. All i want is to update a text field with an update statement. – Luís Marinho Dinis Ferreira May 26 '14 at 12:58
  • SQL server has limit (8k) while updating record through C#. If the string size is more than 8K then you need to chunk it in smaller parts and update it.. – Aravinth Kannan May 26 '14 at 14:07
  • 8K is the string (varchar) limit, as sqldbtype.string, c # has a 4gb limit (2gb unicode) and none of this has anything to do with the OPs issue as such, unless this here file is > 4gb??? – Tony Hopkinson May 26 '14 at 16:09
  • @LuísMarinhoDinisFerreira, The link I pointed you at shows you how to store a file (as text, image or even Varchar(max), as long as it less than 4bg in size. Seeing as you never posted your c# code we are unable to assist with what you did wrong – Tony Hopkinson May 26 '14 at 16:13

1 Answers1

0

I figured it out.

I forgot to escape a caracter using T-SQL.Thnks all )I had a lot of ' characters in my xml file. I forgot to replace one of them.I need to do a replace all inn my text editor before trying to put the file source in the sql statement, replacing all the ' with ''. Lesson learned

Luís Marinho Dinis Ferreira
  • 27
  • 6
  • 4
    You might want to explain the exact solution, in order to help future visitors. Which character? – keyser Jun 21 '14 at 10:19