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 Input: Converting from Sybase to SQL

Any Input: Converting from Sybase to SQLMonte Tiernan wrote:

Quote:

Originally Posted by

>

[this space intentionally left blank]

ANSI_NULLS and null comparison

Hi All,

I'm converting some stored procs from Sybase to SQL Server, and I'm
having serious problems with Null comparisons.

When I run the code below on Sybase, it returns both rows. When I run
it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
the code (and on the session through Query Analyzer), but it doesn't
seem to make any difference. Am I missing something?

-----
set ANSI_NULLS off

drop table #TestNull

create table #TestNull (Field1 varchar(10), Field2 varchar(10))

insert into #TestNull values (1, null)
insert into #TestNull values (1,1)

declare @.TestVar varchar(10)

select @.TestVar = Null

select * from #TestNull where Field1 = @.TestVar
-----

Thanks in advance,
SaulYour SQL Server select should look like this:

SELECT * FROM #TextNull WHERE Field1 IS NULL

CJ
"Saul Margolis" <saul_margolis@.hotmail.com> wrote in message
news:c20aa081.0402171416.744303af@.posting.google.c om...
> Hi All,
> I'm converting some stored procs from Sybase to SQL Server, and I'm
> having serious problems with Null comparisons.
> When I run the code below on Sybase, it returns both rows. When I run
> it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
> the code (and on the session through Query Analyzer), but it doesn't
> seem to make any difference. Am I missing something?
>
> -----
> set ANSI_NULLS off
> drop table #TestNull
> create table #TestNull (Field1 varchar(10), Field2 varchar(10))
> insert into #TestNull values (1, null)
> insert into #TestNull values (1,1)
> declare @.TestVar varchar(10)
> select @.TestVar = Null
> select * from #TestNull where Field1 = @.TestVar
> -----
>
> Thanks in advance,
> Saul|||"Saul Margolis" <saul_margolis@.hotmail.com> wrote in message
news:c20aa081.0402171416.744303af@.posting.google.c om...
> Hi All,
> I'm converting some stored procs from Sybase to SQL Server, and I'm
> having serious problems with Null comparisons.
> When I run the code below on Sybase, it returns both rows. When I run
> it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
> the code (and on the session through Query Analyzer), but it doesn't
> seem to make any difference. Am I missing something?
>
> -----
> set ANSI_NULLS off
> drop table #TestNull
> create table #TestNull (Field1 varchar(10), Field2 varchar(10))
> insert into #TestNull values (1, null)
> insert into #TestNull values (1,1)
> declare @.TestVar varchar(10)
> select @.TestVar = Null
> select * from #TestNull where Field1 = @.TestVar
> -----
>
> Thanks in advance,
> Saul

I guess you mean WHERE Field2 = @.TestVar ? That will return the (1, NULL)
row.

See SET ANSI_NULLS in Books Online - there are a number of examples. By the
way, you should generally say which version of MSSQL you're using, as some
behaviour may vary - see the comments in Books Online.

Simon|||Saul Margolis (saul_margolis@.hotmail.com) writes:
> I'm converting some stored procs from Sybase to SQL Server, and I'm
> having serious problems with Null comparisons.
> When I run the code below on Sybase, it returns both rows. When I run
> it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
> the code (and on the session through Query Analyzer), but it doesn't
> seem to make any difference. Am I missing something?
-----
> set ANSI_NULLS off
> drop table #TestNull
> create table #TestNull (Field1 varchar(10), Field2 varchar(10))
> insert into #TestNull values (1, null)
> insert into #TestNull values (1,1)
> declare @.TestVar varchar(10)
> select @.TestVar = Null
> select * from #TestNull where Field1 = @.TestVar
> -----

I fail to see how the batch above could return any rows with any
setting. Looks like there is a typo in your repro.

Anyway, first of all, try to write your code so that you can use
ANSI_NULLS ON. There are features in MS SQL Server that requires
ANSI_NULLS to be ON:

o Indexed views.
o Index on computed columns.
o Access to linked servers.

With ANSI_NULLS off, these features are unavailable.

Also, be aware of when writing stored procedures, that the setting
of ANSI_NULLS when you create the procedure applies. That is, not
the run-time setting.

Also, you may need to have a go for ANSI_NULLS to have effect.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The Sybase family used to allow "foobar = NULL" to mean "foobar IS
NULL"; you just need to set everything to ANSI and change all your code.
Also, why are you putting integers into VARCHAR(10) columns in your
sample code?

Now you know why experienced progrmamers laugh at newbies who use
proprietary code because they think they will never have to port it :)

That is probably not funny right now; and it might get worse if the
Sybase is old -- look for *= as the outer join and other non-standard
behavior in some of the predicates.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!