longtext

Question

longtext

LOVER OF SQL

SSCommitted

Points: 1900
More actions
October 30, 2013 at 11:03 am

#395748

Howdy everybody,
I want to keep 11321 character in field on table SQL Server, but varchar(max) is not allow...
It's because i'm doing a database migration MySQL to SQL Server and i need a big field (similar bigtext)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736607 More actions · Answer 1

varchar(max) should allow 2GB of space. Are you sure this is what the field is set to? How are you determining the 11kb will not fit?

This works:

create table mytest (mychar varchar(max));

insert into mytest select replicate( cast( 'A' as varchar(max)), 11000);

Note there are issue with implicit conversions. http://stackoverflow.com/questions/9172516/how-to-run-a-more-than-8000-characters-sql-statement-from-a-variable

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 2

but the problem is openrowset

Msg 103, Level 15, State 1, Line 4

The character string that starts with 'select `ID`,if(`STATUS`='',null,`STATUS`) as `STATUS`,if(`DIGITADOR`='',null,`DIGITADOR`) as `DIGITADOR`,if(`DATA_INCLUSAO`='',nu' is too long. Maximum length is 8000.

is the big problem..

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 3

I've this problem: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7a5c76c5-e6f3-49c7-9c52-95935d7530fd/the-character-string-that-starts-with-is-too-long-maximum-length-is-8000?forum=sqlreportingservices

OCTom SSChampion Points: 11785 More actions · Answer 4

Read the info on this page about OPENROWSET:

http://technet.microsoft.com/en-us/library/ms190312.aspx

A little over half way down it describes how it handles data exceeding 8,000 bytes using an XML format file.

Tom

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 5

I don't know if it's appropriate here or not but I find that a lot of people use OPENROWSET as a defacto standard for a lot of things where something like OPENQUERY might be much more appropriate especially since OPENQUERY doesn't require "SA" privs like OPENROWSET does.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LOVER OF SQL SSCommitted Points: 1900 More actions · Answer 6

Jeff, my problem is maximum size 8... I was tried using SSIS, but it's the same problem... I have a 180 columns and 1 million rows (MySQL).... when I show all columns in varchar (after transformation, data type, etc), I have 13421 character, and the maximum is 8... it's the big problem.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 7

LOVER OF SQL (10/30/2013)
Jeff, my problem is maximum size 8... I was tried using SSIS, but it's the same problem... I have a 180 columns and 1 million rows [font="Arial Black"](MySQL)[/font].... when I show all columns in varchar (after transformation, data type, etc), I have 13421 character, and the maximum is 8... it's the big problem.

Even SQL Server has such a display problem (although I don't know why you would want to ever display a million rows on screen). I don't know much about MySQL (for example, I don't even know if MySQL has a VARCHAR(MAX) datatype), but would something like the following do it for you (works in SQL Server and seems to be part of the XML standard).

SELECT @LongString AS [processing-instruction(LongString)]

FOR XML PATH(''), TYPE;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)