declare string that can hold more than 8000 characters in T-sql

dataforums · May 3, 2005

Hi,

I need to declare a variable that can hold a string of more than 8000 characters. Like oracle provides LONG data type, does t-sql have an equivalent data type.

Thanks in advance

chiph · May 3, 2005

It does. TEXT and NTEXT columns.

You can find this and more info in Books On Line, by searching on "Data Types".

Chip H.

____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

dataforums · May 4, 2005

Thanks for the reply. I am actually trying to write a program in dyanmic SQL. I tried this earlier but it still gives me the following error:

Server: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.

JamesLean · May 4, 2005

You can't have a local variable of text data type. Are you sure you need to use a dynamic SQL string that is more than 8000 characters? Could you split it up into smaller chunks?

--James

dataforums · May 4, 2005

Thanks for the reply.

I am actually trying to build a a string to create a table dynamically that has more than 80 coulmns and this makes the string exceed the 8000 char limit with the varchar data type. In oracle, we use a LONG data type that can handle this, but i am not sure if there is any other data type in t-sql that can do this.

Pattycake245 · May 4, 2005

There isn't, if you are creating the query dynamically. You weill have to use two varchar(8000) variables and concatenate them at the end when you execute it.

Tim

gmmastros · May 4, 2005

You don't necessarily have to create a table with all 80 columns in it. You could create a table with the first 20 columns (or so) and then add columns to the table for the remaining columns.

Alter Table MyTable Add MyColumn(DataType)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

dataforums · May 4, 2005

I will just concatenate the strings.

Thanks for all the replies.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

declare string that can hold more than 8000 characters in T-sql

dataforums

Programmer

chiph

Programmer

dataforums

Programmer

JamesLean

Programmer

dataforums

Programmer

Pattycake245

Programmer

gmmastros

Programmer

dataforums

Programmer

Similar threads

Part and Inventory Search

Sponsor