Any workaround for row size > 8060?

Hi All,
We're converting Oracle databases to SQL Server. In Oracle there's no such limit for row size and SQL has.
We've Centura as front-end (Gupta SQL formerly).
While testing is going on......we are getting row size exceeds max limit of 8060 bytes...and we don't know what exactly we need to do.
In the problem tables, I have one TEXT column and various varchar 4000 and varchar 2000 columns.
The application will insert more than 8060 characters for sure..
Can anybody suggest any workaround for this? or Any pointers?
The table structure is something like this:
CREATE TABLE [Table1] (
[Table_ID] [varchar] (10) NOT NULL ,
[T_NOTES1] [TEXT] NULL ,
[T_NOTES2] [varchar] (4000) NULL ,
[C_NOTES] [varchar] (4000) NULL ,
[P_NOTES] [varchar] (4000) NULL ,
[G_NOTES] [varchar] (256) NULL ,
[F_NOTES] [varchar] (256) NULL ,
[L_NOTES] [varchar] (256) NULL ,
[P_NEEDS] [varchar] (256) NULL ,
[F_NEEDS] [varchar] (256) NULL ,
[O_NOTE] [varchar] (250) NULL ,
[V_SOURCES] [varchar] (2000) NULL ,
[T_PROOFS] [varchar] (250) NULL ,
[V_SKILLS] [varchar] (2000) NULL ,
[R_CREDS] [varchar] (2000) NULL ,
[ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
(
[PRO_ID]
) REFERENCES [Table_INFO1] (
[PRO_ID]
) ON DELETE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
If we split the table, will it help?
Can we have more than one column as TEXT data type?
-----
One more point: I appreciate if somebody can clarify this:
TEXT data type can store lot of data...right?
Then if we're inserting > 8060 (row size limit) into the TEXT column why would it error?
If this is so, what is the point in using TEXT column?
I'm little bit confused........I appreciate if somebody can clarify this..
Thanks much,
John..
John,
You can split the table vertically (and of course carry the PK to each table).
As for TEXT (and NTEXT and IMAGE), yes, in these you can have up to 2GB per row. They are, however, more
involved to program against. At the TSQL level, you use stuff like READTEXT, UPDATETEXT, TEXTPRT etc (think of
reading and writing in a loop, using a pointer). The API/dev tool you are using might abstract this from you,
though.
Yes, you can have several columns of the "BLOB" datatypes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <johnfull_2000@.yahoo.com> wrote in message news:C8579948-714C-4CA5-9244-9AC486144518@.microsoft.com...
> Hi All,
> We're converting Oracle databases to SQL Server. In Oracle there's no such limit for row size and SQL has.
> We've Centura as front-end (Gupta SQL formerly).
> While testing is going on......we are getting row size exceeds max limit of 8060 bytes...and we don't know
what exactly we need to do.
> In the problem tables, I have one TEXT column and various varchar 4000 and varchar 2000 columns.
> The application will insert more than 8060 characters for sure..
> Can anybody suggest any workaround for this? or Any pointers?
> The table structure is something like this:
> CREATE TABLE [Table1] (
> [Table_ID] [varchar] (10) NOT NULL ,
> [T_NOTES1] [TEXT] NULL ,
> [T_NOTES2] [varchar] (4000) NULL ,
> [C_NOTES] [varchar] (4000) NULL ,
> [P_NOTES] [varchar] (4000) NULL ,
> [G_NOTES] [varchar] (256) NULL ,
> [F_NOTES] [varchar] (256) NULL ,
> [L_NOTES] [varchar] (256) NULL ,
> [P_NEEDS] [varchar] (256) NULL ,
> [F_NEEDS] [varchar] (256) NULL ,
> [O_NOTE] [varchar] (250) NULL ,
> [V_SOURCES] [varchar] (2000) NULL ,
> [T_PROOFS] [varchar] (250) NULL ,
> [V_SKILLS] [varchar] (2000) NULL ,
> [R_CREDS] [varchar] (2000) NULL ,
> [ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
> CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
> (
> [PRO_ID]
> ) REFERENCES [Table_INFO1] (
> [PRO_ID]
> ) ON DELETE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> If we split the table, will it help?
> Can we have more than one column as TEXT data type?
> -----
> One more point: I appreciate if somebody can clarify this:
> TEXT data type can store lot of data...right?
> Then if we're inserting > 8060 (row size limit) into the TEXT column why would it error?
> If this is so, what is the point in using TEXT column?
> I'm little bit confused........I appreciate if somebody can clarify this..
> Thanks much,
> John..
|||John,
Does Centura support SQL Server? Perhaps an engineer from Centura would be
required to do this migration work. The problem you are having is that you
will need to use the text data type if you need to store more than 8060
bytes per row. Application changes may need to be made to accomodate this
data type, either at the stored procedure layer or the data access layer in
dynamic SQL in the application itself.
There are many gotchas with the text datatype and they cannot be treated in
the same way as a varchar due to the nature in the way they are stored. i.e.
if the text cannot be stored in row, as in its greater than 8K, then a
pointer is stored in the row to the data pages where the text data can be
found. There is a lot of information on this data type in Books Online, I
recommend you read it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"John" <johnfull_2000@.yahoo.com> wrote in message
news:C8579948-714C-4CA5-9244-9AC486144518@.microsoft.com...
> Hi All,
> We're converting Oracle databases to SQL Server. In Oracle there's no such
limit for row size and SQL has.
> We've Centura as front-end (Gupta SQL formerly).
> While testing is going on......we are getting row size exceeds max limit
of 8060 bytes...and we don't know what exactly we need to do.
> In the problem tables, I have one TEXT column and various varchar 4000 and
varchar 2000 columns.
> The application will insert more than 8060 characters for sure..
> Can anybody suggest any workaround for this? or Any pointers?
> The table structure is something like this:
> CREATE TABLE [Table1] (
> [Table_ID] [varchar] (10) NOT NULL ,
> [T_NOTES1] [TEXT] NULL ,
> [T_NOTES2] [varchar] (4000) NULL ,
> [C_NOTES] [varchar] (4000) NULL ,
> [P_NOTES] [varchar] (4000) NULL ,
> [G_NOTES] [varchar] (256) NULL ,
> [F_NOTES] [varchar] (256) NULL ,
> [L_NOTES] [varchar] (256) NULL ,
> [P_NEEDS] [varchar] (256) NULL ,
> [F_NEEDS] [varchar] (256) NULL ,
> [O_NOTE] [varchar] (250) NULL ,
> [V_SOURCES] [varchar] (2000) NULL ,
> [T_PROOFS] [varchar] (250) NULL ,
> [V_SKILLS] [varchar] (2000) NULL ,
> [R_CREDS] [varchar] (2000) NULL ,
> [ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
> CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
> (
> [PRO_ID]
> ) REFERENCES [Table_INFO1] (
> [PRO_ID]
> ) ON DELETE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> If we split the table, will it help?
> Can we have more than one column as TEXT data type?
> ----
--
> One more point: I appreciate if somebody can clarify this:
> TEXT data type can store lot of data...right?
> Then if we're inserting > 8060 (row size limit) into the TEXT column why
would it error?
> If this is so, what is the point in using TEXT column?
> I'm little bit confused........I appreciate if somebody can clarify
this..
> Thanks much,
> John..
|||For your question, Mark:
Yes. Centura supports SQL Server.
I want to get clear about one more thing:
You mean to say, If I have a TEXT column, it can accommodate more than 8060 bytes..right?
That means excluding the TEXT column, the total row size shouldn't be greater than 8060....
That means, the error is coming bcoz of the total exceeds 8060....
Please clarify..
For you, Tibor:
I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK DUMP and EXCEPTION HANDLING ERRORS etc....
and nobody could connect to the server.
Then I had to stop the server and restart the server.
Then I had to change the columns back to varchar(4000)......but the error 'row size cannot be greater than > 8060 is coming'..
Any inputs, please let me know.
Thanks,
John..
|||> You mean to say, If I have a TEXT column, it can accommodate more than 8060 bytes..right?
Correct.

> That means excluding the TEXT column, the total row size shouldn't be greater than 8060....
Correct.

> That means, the error is coming bcoz of the total exceeds 8060....
Correct.

> I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK DUMP and EXCEPTION
HANDLING ERRORS etc....
> and nobody could connect to the server.
Then you had the unfortunate to (most likely) hit some bug in SQL Server. You can make sure you are updated on
service packs, search KB for resolution or possibly open a case with MS on this.
Important to understand it that it takes special programming to work with these datatypes. It is not only a
matter of changing the table structure. As I mentioned in my earlier post, the dev tool/API might abstract you
from that, but that is up to each dev tool/API vendor.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <johnfull_2000@.yahoo.com> wrote in message news:A06EB4C0-2FA1-48F8-B0E3-692EA32246B3@.microsoft.com...
> For your question, Mark:
> Yes. Centura supports SQL Server.
> I want to get clear about one more thing:
> You mean to say, If I have a TEXT column, it can accommodate more than 8060 bytes..right?
> That means excluding the TEXT column, the total row size shouldn't be greater than 8060....
> That means, the error is coming bcoz of the total exceeds 8060....
> Please clarify..
> For you, Tibor:
> I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK DUMP and EXCEPTION
HANDLING ERRORS etc....
> and nobody could connect to the server.
> Then I had to stop the server and restart the server.
> Then I had to change the columns back to varchar(4000)......but the error 'row size cannot be greater than
> 8060 is coming'..
> Any inputs, please let me know.
> Thanks,
> John..
>
|||THANK YOU.....for the info
John.
sql

Any workaround for row size > 8060?

Hi All,
We're converting Oracle databases to SQL Server. In Oracle there's no such l
imit for row size and SQL has.
We've Centura as front-end (Gupta SQL formerly).
While testing is going on......we are getting row size exceeds max limit o
f 8060 bytes...and we don't know what exactly we need to do.
In the problem tables, I have one TEXT column and various varchar 4000 and v
archar 2000 columns.
The application will insert more than 8060 characters for sure..
Can anybody suggest any workaround for this? or Any pointers?
The table structure is something like this:
---
CREATE TABLE [Table1] (
[Table_ID] [varchar] (10) NOT NULL ,
[T_NOTES1] [TEXT] NULL ,
[T_NOTES2] [varchar] (4000) NULL ,
[C_NOTES] [varchar] (4000) NULL ,
[P_NOTES] [varchar] (4000) NULL ,
[G_NOTES] [varchar] (256) NULL ,
[F_NOTES] [varchar] (256) NULL ,
[L_NOTES] [varchar] (256) NULL ,
[P_NEEDS] [varchar] (256) NULL ,
[F_NEEDS] [varchar] (256) NULL ,
[O_NOTE] [varchar] (250) NULL ,
[V_SOURCES] [varchar] (2000) NULL ,
[T_PROOFS] [varchar] (250) NULL ,
[V_SKILLS] [varchar] (2000) NULL ,
[R_CREDS] [varchar] (2000) NULL ,
[ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
(
[PRO_ID]
) REFERENCES [Table_INFO1] (
[PRO_ID]
) ON DELETE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
If we split the table, will it help?
Can we have more than one column as TEXT data type?
----
--
One more point: I appreciate if somebody can clarify this:
TEXT data type can store lot of data...right?
Then if we're inserting > 8060 (row size limit) into the TEXT column why wou
ld it error?
If this is so, what is the point in using TEXT column?
I'm little bit confused........I appreciate if somebody can clarify this..
Thanks much,
John..John,
You can split the table vertically (and of course carry the PK to each table
).
As for TEXT (and NTEXT and IMAGE), yes, in these you can have up to 2GB per
row. They are, however, more
involved to program against. At the TSQL level, you use stuff like READTEXT,
UPDATETEXT, TEXTPRT etc (think of
reading and writing in a loop, using a pointer). The API/dev tool you are us
ing might abstract this from you,
though.
Yes, you can have several columns of the "BLOB" datatypes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <johnfull_2000@.yahoo.com> wrote in message news:C8579948-714C-4CA5-9244-9AC486144518@.
microsoft.com...
> Hi All,
> We're converting Oracle databases to SQL Server. In Oracle there's no such
limit for row size and SQL has.
> We've Centura as front-end (Gupta SQL formerly).
> While testing is going on......we are getting row size exceeds max limit of 8060
bytes...and we don't know
what exactly we need to do.
> In the problem tables, I have one TEXT column and various varchar 4000 and
varchar 2000 columns.
> The application will insert more than 8060 characters for sure..
> Can anybody suggest any workaround for this? or Any pointers?
> The table structure is something like this:
> ---
> CREATE TABLE [Table1] (
> [Table_ID] [varchar] (10) NOT NULL ,
> [T_NOTES1] [TEXT] NULL ,
> [T_NOTES2] [varchar] (4000) NULL ,
> [C_NOTES] [varchar] (4000) NULL ,
> [P_NOTES] [varchar] (4000) NULL ,
> [G_NOTES] [varchar] (256) NULL ,
> [F_NOTES] [varchar] (256) NULL ,
> [L_NOTES] [varchar] (256) NULL ,
> [P_NEEDS] [varchar] (256) NULL ,
> [F_NEEDS] [varchar] (256) NULL ,
> [O_NOTE] [varchar] (250) NULL ,
> [V_SOURCES] [varchar] (2000) NULL ,
> [T_PROOFS] [varchar] (250) NULL ,
> [V_SKILLS] [varchar] (2000) NULL ,
> [R_CREDS] [varchar] (2000) NULL ,
> [ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
> CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
> (
> [PRO_ID]
> ) REFERENCES [Table_INFO1] (
> [PRO_ID]
> ) ON DELETE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> If we split the table, will it help?
> Can we have more than one column as TEXT data type?
> ----
--
> One more point: I appreciate if somebody can clarify this:
> TEXT data type can store lot of data...right?
> Then if we're inserting > 8060 (row size limit) into the TEXT column why w
ould it error?
> If this is so, what is the point in using TEXT column?
> I'm little bit confused........I appreciate if somebody can clarify this
.
> Thanks much,
> John..|||John,
Does Centura support SQL Server? Perhaps an engineer from Centura would be
required to do this migration work. The problem you are having is that you
will need to use the text data type if you need to store more than 8060
bytes per row. Application changes may need to be made to accomodate this
data type, either at the stored procedure layer or the data access layer in
dynamic SQL in the application itself.
There are many gotchas with the text datatype and they cannot be treated in
the same way as a varchar due to the nature in the way they are stored. i.e.
if the text cannot be stored in row, as in its greater than 8K, then a
pointer is stored in the row to the data pages where the text data can be
found. There is a lot of information on this data type in Books Online, I
recommend you read it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"John" <johnfull_2000@.yahoo.com> wrote in message
news:C8579948-714C-4CA5-9244-9AC486144518@.microsoft.com...
> Hi All,
> We're converting Oracle databases to SQL Server. In Oracle there's no such
limit for row size and SQL has.
> We've Centura as front-end (Gupta SQL formerly).
> While testing is going on......we are getting row size exceeds max limit
of 8060 bytes...and we don't know what exactly we need to do.
> In the problem tables, I have one TEXT column and various varchar 4000 and
varchar 2000 columns.
> The application will insert more than 8060 characters for sure..
> Can anybody suggest any workaround for this? or Any pointers?
> The table structure is something like this:
> ---
> CREATE TABLE [Table1] (
> [Table_ID] [varchar] (10) NOT NULL ,
> [T_NOTES1] [TEXT] NULL ,
> [T_NOTES2] [varchar] (4000) NULL ,
> [C_NOTES] [varchar] (4000) NULL ,
> [P_NOTES] [varchar] (4000) NULL ,
> [G_NOTES] [varchar] (256) NULL ,
> [F_NOTES] [varchar] (256) NULL ,
> [L_NOTES] [varchar] (256) NULL ,
> [P_NEEDS] [varchar] (256) NULL ,
> [F_NEEDS] [varchar] (256) NULL ,
> [O_NOTE] [varchar] (250) NULL ,
> [V_SOURCES] [varchar] (2000) NULL ,
> [T_PROOFS] [varchar] (250) NULL ,
> [V_SKILLS] [varchar] (2000) NULL ,
> [R_CREDS] [varchar] (2000) NULL ,
> [ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
> CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
> (
> [PRO_ID]
> ) REFERENCES [Table_INFO1] (
> [PRO_ID]
> ) ON DELETE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> If we split the table, will it help?
> Can we have more than one column as TEXT data type?
> ----
--
> One more point: I appreciate if somebody can clarify this:
> TEXT data type can store lot of data...right?
> Then if we're inserting > 8060 (row size limit) into the TEXT column why
would it error?
> If this is so, what is the point in using TEXT column?
> I'm little bit confused........I appreciate if somebody can clarify
this..
> Thanks much,
> John..|||For your question, Mark:
Yes. Centura supports SQL Server.
I want to get clear about one more thing:
You mean to say, If I have a TEXT column, it can accommodate more than 8060
bytes..right?
That means excluding the TEXT column, the total row size shouldn't be greate
r than 8060....
That means, the error is coming bcoz of the total exceeds 8060....
Please clarify..
For you, Tibor:
I changed couple of columns (3 to 4 columns) to TEXT and I started receiving
STACK DUMP and EXCEPTION HANDLING ERRORS etc....
and nobody could connect to the server.
Then I had to stop the server and restart the server.
Then I had to change the columns back to varchar(4000)......but the error '
row size cannot be greater than > 8060 is coming'..
Any inputs, please let me know.
Thanks,
John..|||> You mean to say, If I have a TEXT column, it can accommodate more than 806
0 bytes..right?
Correct.

> That means excluding the TEXT column, the total row size shouldn't be greater than
8060....
Correct.

> That means, the error is coming bcoz of the total exceeds 8060....
Correct.

> I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK
DUMP and EXCEPTION
HANDLING ERRORS etc....
> and nobody could connect to the server.
Then you had the unfortunate to (most likely) hit some bug in SQL Server. Yo
u can make sure you are updated on
service packs, search KB for resolution or possibly open a case with MS on t
his.
Important to understand it that it takes special programming to work with th
ese datatypes. It is not only a
matter of changing the table structure. As I mentioned in my earlier post, t
he dev tool/API might abstract you
from that, but that is up to each dev tool/API vendor.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <johnfull_2000@.yahoo.com> wrote in message news:A06EB4C0-2FA1-48F8-B0E3-692EA32246B3@.
microsoft.com...
> For your question, Mark:
> Yes. Centura supports SQL Server.
> I want to get clear about one more thing:
> You mean to say, If I have a TEXT column, it can accommodate more than 806
0 bytes..right?
> That means excluding the TEXT column, the total row size shouldn't be grea
ter than 8060....
> That means, the error is coming bcoz of the total exceeds 8060....
> Please clarify..
> For you, Tibor:
> I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK
DUMP and EXCEPTION
HANDLING ERRORS etc....
> and nobody could connect to the server.
> Then I had to stop the server and restart the server.
> Then I had to change the columns back to varchar(4000)......but the error
'row size cannot be greater than
> 8060 is coming'..
> Any inputs, please let me know.
> Thanks,
> John..
>|||THANK YOU.....for the info
John.

Any workaround for row size > 8060?

Hi All
We're converting Oracle databases to SQL Server. In Oracle there's no such limit for row size and SQL has
We've Centura as front-end (Gupta SQL formerly).
While testing is going on......we are getting row size exceeds max limit of 8060 bytes...and we don't know what exactly we need to do
In the problem tables, I have one TEXT column and various varchar 4000 and varchar 2000 columns
The application will insert more than 8060 characters for sure.
Can anybody suggest any workaround for this? or Any pointers
The table structure is something like this
----
CREATE TABLE [Table1]
[Table_ID] [varchar] (10) NOT NULL
[T_NOTES1] [TEXT] NULL
[T_NOTES2] [varchar] (4000) NULL
[C_NOTES] [varchar] (4000) NULL
[P_NOTES] [varchar] (4000) NULL
[G_NOTES] [varchar] (256) NULL
[F_NOTES] [varchar] (256) NULL
[L_NOTES] [varchar] (256) NULL
[P_NEEDS] [varchar] (256) NULL
[F_NEEDS] [varchar] (256) NULL
[O_NOTE] [varchar] (250) NULL
[V_SOURCES] [varchar] (2000) NULL
[T_PROOFS] [varchar] (250) NULL
[V_SKILLS] [varchar] (2000) NULL
[R_CREDS] [varchar] (2000) NULL
[ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
[PRO_ID
) REFERENCES [Table_INFO1]
[PRO_ID
) ON DELETE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY
G
If we split the table, will it help
Can we have more than one column as TEXT data type
----
One more point: I appreciate if somebody can clarify this
TEXT data type can store lot of data...right
Then if we're inserting > 8060 (row size limit) into the TEXT column why would it error
If this is so, what is the point in using TEXT column
I'm little bit confused........I appreciate if somebody can clarify this.
Thanks much
John..John,
You can split the table vertically (and of course carry the PK to each table).
As for TEXT (and NTEXT and IMAGE), yes, in these you can have up to 2GB per row. They are, however, more
involved to program against. At the TSQL level, you use stuff like READTEXT, UPDATETEXT, TEXTPRT etc (think of
reading and writing in a loop, using a pointer). The API/dev tool you are using might abstract this from you,
though.
Yes, you can have several columns of the "BLOB" datatypes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <johnfull_2000@.yahoo.com> wrote in message news:C8579948-714C-4CA5-9244-9AC486144518@.microsoft.com...
> Hi All,
> We're converting Oracle databases to SQL Server. In Oracle there's no such limit for row size and SQL has.
> We've Centura as front-end (Gupta SQL formerly).
> While testing is going on......we are getting row size exceeds max limit of 8060 bytes...and we don't know
what exactly we need to do.
> In the problem tables, I have one TEXT column and various varchar 4000 and varchar 2000 columns.
> The application will insert more than 8060 characters for sure..
> Can anybody suggest any workaround for this? or Any pointers?
> The table structure is something like this:
> ---
> CREATE TABLE [Table1] (
> [Table_ID] [varchar] (10) NOT NULL ,
> [T_NOTES1] [TEXT] NULL ,
> [T_NOTES2] [varchar] (4000) NULL ,
> [C_NOTES] [varchar] (4000) NULL ,
> [P_NOTES] [varchar] (4000) NULL ,
> [G_NOTES] [varchar] (256) NULL ,
> [F_NOTES] [varchar] (256) NULL ,
> [L_NOTES] [varchar] (256) NULL ,
> [P_NEEDS] [varchar] (256) NULL ,
> [F_NEEDS] [varchar] (256) NULL ,
> [O_NOTE] [varchar] (250) NULL ,
> [V_SOURCES] [varchar] (2000) NULL ,
> [T_PROOFS] [varchar] (250) NULL ,
> [V_SKILLS] [varchar] (2000) NULL ,
> [R_CREDS] [varchar] (2000) NULL ,
> [ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
> CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
> (
> [PRO_ID]
> ) REFERENCES [Table_INFO1] (
> [PRO_ID]
> ) ON DELETE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> If we split the table, will it help?
> Can we have more than one column as TEXT data type?
> -----
> One more point: I appreciate if somebody can clarify this:
> TEXT data type can store lot of data...right?
> Then if we're inserting > 8060 (row size limit) into the TEXT column why would it error?
> If this is so, what is the point in using TEXT column?
> I'm little bit confused........I appreciate if somebody can clarify this..
> Thanks much,
> John..|||John,
Does Centura support SQL Server? Perhaps an engineer from Centura would be
required to do this migration work. The problem you are having is that you
will need to use the text data type if you need to store more than 8060
bytes per row. Application changes may need to be made to accomodate this
data type, either at the stored procedure layer or the data access layer in
dynamic SQL in the application itself.
There are many gotchas with the text datatype and they cannot be treated in
the same way as a varchar due to the nature in the way they are stored. i.e.
if the text cannot be stored in row, as in its greater than 8K, then a
pointer is stored in the row to the data pages where the text data can be
found. There is a lot of information on this data type in Books Online, I
recommend you read it.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"John" <johnfull_2000@.yahoo.com> wrote in message
news:C8579948-714C-4CA5-9244-9AC486144518@.microsoft.com...
> Hi All,
> We're converting Oracle databases to SQL Server. In Oracle there's no such
limit for row size and SQL has.
> We've Centura as front-end (Gupta SQL formerly).
> While testing is going on......we are getting row size exceeds max limit
of 8060 bytes...and we don't know what exactly we need to do.
> In the problem tables, I have one TEXT column and various varchar 4000 and
varchar 2000 columns.
> The application will insert more than 8060 characters for sure..
> Can anybody suggest any workaround for this? or Any pointers?
> The table structure is something like this:
> ---
> CREATE TABLE [Table1] (
> [Table_ID] [varchar] (10) NOT NULL ,
> [T_NOTES1] [TEXT] NULL ,
> [T_NOTES2] [varchar] (4000) NULL ,
> [C_NOTES] [varchar] (4000) NULL ,
> [P_NOTES] [varchar] (4000) NULL ,
> [G_NOTES] [varchar] (256) NULL ,
> [F_NOTES] [varchar] (256) NULL ,
> [L_NOTES] [varchar] (256) NULL ,
> [P_NEEDS] [varchar] (256) NULL ,
> [F_NEEDS] [varchar] (256) NULL ,
> [O_NOTE] [varchar] (250) NULL ,
> [V_SOURCES] [varchar] (2000) NULL ,
> [T_PROOFS] [varchar] (250) NULL ,
> [V_SKILLS] [varchar] (2000) NULL ,
> [R_CREDS] [varchar] (2000) NULL ,
> [ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
> CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
> (
> [PRO_ID]
> ) REFERENCES [Table_INFO1] (
> [PRO_ID]
> ) ON DELETE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> If we split the table, will it help?
> Can we have more than one column as TEXT data type?
> ----
--
> One more point: I appreciate if somebody can clarify this:
> TEXT data type can store lot of data...right?
> Then if we're inserting > 8060 (row size limit) into the TEXT column why
would it error?
> If this is so, what is the point in using TEXT column?
> I'm little bit confused........I appreciate if somebody can clarify
this..
> Thanks much,
> John..|||For your question, Mark
Yes. Centura supports SQL Server
I want to get clear about one more thing
You mean to say, If I have a TEXT column, it can accommodate more than 8060 bytes..right
That means excluding the TEXT column, the total row size shouldn't be greater than 8060...
That means, the error is coming bcoz of the total exceeds 8060...
Please clarify.
For you, Tibor
I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK DUMP and EXCEPTION HANDLING ERRORS etc...
and nobody could connect to the server
Then I had to stop the server and restart the server
Then I had to change the columns back to varchar(4000)......but the error 'row size cannot be greater than > 8060 is coming'.
Any inputs, please let me know
Thanks
John.|||> You mean to say, If I have a TEXT column, it can accommodate more than 8060 bytes..right?
Correct.
> That means excluding the TEXT column, the total row size shouldn't be greater than 8060....
Correct.
> That means, the error is coming bcoz of the total exceeds 8060....
Correct.
> I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK DUMP and EXCEPTION
HANDLING ERRORS etc....
> and nobody could connect to the server.
Then you had the unfortunate to (most likely) hit some bug in SQL Server. You can make sure you are updated on
service packs, search KB for resolution or possibly open a case with MS on this.
Important to understand it that it takes special programming to work with these datatypes. It is not only a
matter of changing the table structure. As I mentioned in my earlier post, the dev tool/API might abstract you
from that, but that is up to each dev tool/API vendor.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <johnfull_2000@.yahoo.com> wrote in message news:A06EB4C0-2FA1-48F8-B0E3-692EA32246B3@.microsoft.com...
> For your question, Mark:
> Yes. Centura supports SQL Server.
> I want to get clear about one more thing:
> You mean to say, If I have a TEXT column, it can accommodate more than 8060 bytes..right?
> That means excluding the TEXT column, the total row size shouldn't be greater than 8060....
> That means, the error is coming bcoz of the total exceeds 8060....
> Please clarify..
> For you, Tibor:
> I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK DUMP and EXCEPTION
HANDLING ERRORS etc....
> and nobody could connect to the server.
> Then I had to stop the server and restart the server.
> Then I had to change the columns back to varchar(4000)......but the error 'row size cannot be greater than
> 8060 is coming'..
> Any inputs, please let me know.
> Thanks,
> John..
>|||THANK YOU.....for the inf
John.

Any Movement on the 4 gig limit?

We're very interested in having our application use SQL/e but we can't have the 4 gig limit. It makes sense to me that SQL/e simply should not be able to access a file over the network, and then you wouldn't have any reason to put a 4 gig limit on it.

At that point it becomes a very flexible alternative for remote users that need to have a large amount of data (i.e. documents, images etc.) with them.

We'd love to start building an abstraction layer so that we can support both SQL Server and SQL/e so that we can support network and remote users and not have the nightmare that is SQL Server Express installation. (care of the windows installer group's bugs...)

Thanks! Hoping for a favourable answer!

The limit is one of the restrictions on having a free db engine, you might want to look at the workgroup edition of SQL Server 2005. The other option is to have muiltiple database files as the limit is on the size of the database files not the combined engine. You could store archived information in files and keep the production/live data in the current file.

|||Ya, but the point to the whole deal is that the 4 gig limit is not applicable in the case of a single user database scenario like SQL/e because it's specifically there to prevent people from using SSE to share the database and not buy SQL Server.

With SQL Server 2000 we had the personal edition which was $99. That was fine and dandy and I could live with that on client machines. However, that's gone for SQL Server 2005 and as a result it gets incredibly expensive to outfit laptops with a database engine that doesn't have a 4 gig limit.

SQL/e is the perfect way to distribute your application in a smart client (online/offline) environment because it's drag and drop installable instead of the disaster that is SSE installation. And because it's designed for a single user scenario there should be no reason for the 4 gig limit. They should just prevent the APIs from connecting to a datasource on a network device. Problem solved and you get a great little database for distributed applications.

And since this is exactly the scenario that the product was designed for it just makes sense to not have a limit and lock it down.

Any limit on number of characters for FLATFILE connection ?

Any one knows for sure if there is any limit on the number of characters/letters that a FLATFILE connection manager can maximally have?

Is the following name (36 letters) valid ?

Code Snippet

<DTS:Property DTS:Name="ObjectName">Load Ready Output Connection Manager</DTS:Property>

Why do you ask?|||

my observation is that FLATFILE connection manager often failed on those long-name connections. I wonder if this is the reason causing packages run unstably. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860426&SiteID=1

On another observation -- we have some PCs that have only one processor/CPU. SSIS packages run always successfully on those PCs. The unstable issue occur only on dual-processor or 4-processor PCs. I wonder if threading on >1 CPU causing any issue (although not theoretically). This again seems weird enough.

Any limit of the length of the characters in table name ?

Hi,
We are a team that are developing a projet which has in itself a good number
of tables in sql server (not temporary ones !). The entire team is pretty
knowledgeable in MSSQL server and in SQL programming in general... However
we kind of want to double check what are the limitations of naming tables
(not temporary ones ! "#"). ?
e.g
1. Any limit of the length of the characters in table name ?
2. Any special characters which are not allowed or is not advised to be in a
table name ?
3. or any other constrains or limitations I can't think of now.
Thank you very much in advance,
G.Y
Software Engineer,
QuadraMed, Reston VA
> 1. Any limit of the length of the characters in table name ?
They are stored using SYSNAME datatype, which is NVARCHAR(128).
DECLARE @.s VARCHAR(255)
SET @.s = 'CREATE TABLE '+REPLICATE('x', 129)+' (i INT)'
EXEC(@.s)
Yields:
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'xxxxxxxxxxxxxxxxxxxxxx[...]' is too long.
Maximum length is 128.

> 2. Any special characters which are not allowed or is not advised to be in
> a table name ?
Spaces. Punctuation. Accent characters. Do not start a table name with a
number or a dash. Underscores are the only non-alphanumeric that I allow,
only if deemed absolutely necessary by someone else
(ThisCaseWorksFineForMe), and only if they are at a logical place (not at
the beginning). In fact even numbers are questionable, but it really
depends on the entity you are trying to model.
Here are my thoughts on SQL Server naming conventions, maybe useful to you,
maybe not:
http://www.aspfaq.com/2538

> 3. or any other constrains or limitations I can't think of now.
http://www.aspfaq.com/2345

Any limit of the length of the characters in table name ?

Hi,
We are a team that are developing a projet which has in itself a good number
of tables in sql server (not temporary ones !). The entire team is pretty
knowledgeable in MSSQL server and in SQL programming in general... However
we kind of want to double check what are the limitations of naming tables
(not temporary ones ! "#"). ?
e.g
1. Any limit of the length of the characters in table name ?
2. Any special characters which are not allowed or is not advised to be in a
table name ?
3. or any other constrains or limitations I can't think of now.
Thank you very much in advance,
G.Y
Software Engineer,
QuadraMed, Reston VA> 1. Any limit of the length of the characters in table name ?
They are stored using SYSNAME datatype, which is NVARCHAR(128).
DECLARE @.s VARCHAR(255)
SET @.s = 'CREATE TABLE '+REPLICATE('x', 129)+' (i INT)'
EXEC(@.s)
Yields:
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'xxxxxxxxxxxxxxxxxxxxxx[...]' is too lon
g.
Maximum length is 128.

> 2. Any special characters which are not allowed or is not advised to be in
> a table name ?
Spaces. Punctuation. Accent characters. Do not start a table name with a
number or a dash. Underscores are the only non-alphanumeric that I allow,
only if deemed absolutely necessary by someone else
(ThisCaseWorksFineForMe), and only if they are at a logical place (not at
the beginning). In fact even numbers are questionable, but it really
depends on the entity you are trying to model.
Here are my thoughts on SQL Server naming conventions, maybe useful to you,
maybe not:
http://www.aspfaq.com/2538

> 3. or any other constrains or limitations I can't think of now.
http://www.aspfaq.com/2345

Any limit of the length of the characters in table name ?

Hi,
We are a team that are developing a projet which has in itself a good number
of tables in sql server (not temporary ones !). The entire team is pretty
knowledgeable in MSSQL server and in SQL programming in general... However
we kind of want to double check what are the limitations of naming tables
(not temporary ones ! "#"). ?
e.g
1. Any limit of the length of the characters in table name ?
2. Any special characters which are not allowed or is not advised to be in a
table name ?
3. or any other constrains or limitations I can't think of now.
Thank you very much in advance,
G.Y
Software Engineer,
QuadraMed, Reston VA> 1. Any limit of the length of the characters in table name ?
They are stored using SYSNAME datatype, which is NVARCHAR(128).
DECLARE @.s VARCHAR(255)
SET @.s = 'CREATE TABLE '+REPLICATE('x', 129)+' (i INT)'
EXEC(@.s)
Yields:
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'xxxxxxxxxxxxxxxxxxxxxx[...]' is too long.
Maximum length is 128.
> 2. Any special characters which are not allowed or is not advised to be in
> a table name ?
Spaces. Punctuation. Accent characters. Do not start a table name with a
number or a dash. Underscores are the only non-alphanumeric that I allow,
only if deemed absolutely necessary by someone else
(ThisCaseWorksFineForMe), and only if they are at a logical place (not at
the beginning). In fact even numbers are questionable, but it really
depends on the entity you are trying to model.
Here are my thoughts on SQL Server naming conventions, maybe useful to you,
maybe not:
http://www.aspfaq.com/2538
> 3. or any other constrains or limitations I can't think of now.
http://www.aspfaq.com/2345