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 way to prevent SSAS memory from paging?

This morning msmdsrv.exe looked like the following in Task Manager:

Memory Usage: 600,000 K

VM Size: 2,500,000 K

If I understand correctly, that means that a good deal of SSAS memory has been swapped to disk by the OS. Is there any way to prevent this? (I have read about using the Lock Pages In Memory privilege at http://msdn2.microsoft.com/en-us/library/ms179301.aspx which lets SQL prevent the OS from paging sqlservr.exe memory. I don't suppose there's an equivalent SSAS setting.)

In particular, the symptom I'm seeing is that after we finish processing, all that paged memory has to be loaded back into memory before the transaction can be committed. Committing the transaction is usually very quick. But when most of the SSAS process memory is pages, it takes quite a while, during which I can see the Memory Usage number in Task Manager growing. (I don't think it's a blocked transaction commit.)

Looks like maybe there is a way to do this in SP2. Can anyone explain the PreAllocate setting further?

http://www.sdsqlug.org/presentations/November2006/November_2006_64-bit_SQL_Server.pdf (page 36)

Any way to create 5KB size of Database?

Hi
when we create new database that will take base structure and size of the
model database. it will be approx 1.43 MB as per version. now i want to
create database that size is 5kb any way to create database like that size,
why the reason is for CD technolgy we have less space.. can u suggest me?
Thanks
Sriram.Srikanth
SQL-Server Database Administrator,
Hi
The newly created DB gets it's sizing from Model DB. Model DB can not be
made smaller. With all the structures needed in the MDF and LDF files,
1.43mb is the smallest you can get.
Why ship the DB? Create the DB though T-SQL code on the client machine.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>
|||What you could do is distribute the MDF file only, in a zipped version...
Then unzip it, and use sp_attach_single_file_db to load it up..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
> size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>
|||You could also simply distribute the create database/object script. This
would insulate you from physical changes to the mdf file, which is a risk
with the other way I mentioned.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
> size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>

Any way to create 5KB size of Database?

Hi
when we create new database that will take base structure and size of the
model database. it will be approx 1.43 MB as per version. now i want to
create database that size is 5kb any way to create database like that size,
why the reason is for CD technolgy we have less space.. can u suggest me?
Thanks
--
Sriram.Srikanth
SQL-Server Database Administrator,Hi
The newly created DB gets it's sizing from Model DB. Model DB can not be
made smaller. With all the structures needed in the MDF and LDF files,
1.43mb is the smallest you can get.
Why ship the DB? Create the DB though T-SQL code on the client machine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>|||What you could do is distribute the MDF file only, in a zipped version...
Then unzip it, and use sp_attach_single_file_db to load it up..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
> size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>|||You could also simply distribute the create database/object script. This
would insulate you from physical changes to the mdf file, which is a risk
with the other way I mentioned.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
> size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>

Any way to create 5KB size of Database?

Hi
when we create new database that will take base structure and size of the
model database. it will be approx 1.43 MB as per version. now i want to
create database that size is 5kb any way to create database like that size,
why the reason is for CD technolgy we have less space.. can u suggest me?
Thanks
--
Sriram.Srikanth
SQL-Server Database Administrator,Hi
The newly created DB gets it's sizing from Model DB. Model DB can not be
made smaller. With all the structures needed in the MDF and LDF files,
1.43mb is the smallest you can get.
Why ship the DB? Create the DB though T-SQL code on the client machine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>|||What you could do is distribute the MDF file only, in a zipped version...
Then unzip it, and use sp_attach_single_file_db to load it up..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
> size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>|||You could also simply distribute the create database/object script. This
would insulate you from physical changes to the mdf file, which is a risk
with the other way I mentioned.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SRIRAM" <SRIRAM_SRIKANTH@.HOTMAIL.COM> wrote in message
news:4F0ECF58-54FD-40E6-A0BF-96F6EAF97ED0@.microsoft.com...
> Hi
> when we create new database that will take base structure and size of the
> model database. it will be approx 1.43 MB as per version. now i want to
> create database that size is 5kb any way to create database like that
> size,
> why the reason is for CD technolgy we have less space.. can u suggest me?
> Thanks
> --
> Sriram.Srikanth
> SQL-Server Database Administrator,
>

Any help?

I am having a issue with a customers server. the specs are:
2003 server
sql 6.5
log file size is 1998MB (customer increased this 2 times this week) Log
space avail:600mb

In EM i try and truncate the log file... but the free space never
increases. Also there is no maint. plan on this database. When I try
and create one it advises against creating one with databases over
400MB.

The past day or so these errors are showing up:

Any help?

Event Type:Error
Event Source:MSSQLServer
Event Category:(2)
Event ID:17060
Date:8/4/2006
Time:7:51:20 AM
User:N/A
Computer:AUX-SERVER
Description:
The description for Event ID ( 17060 ) in Source ( MSSQLServer ) cannot
be found. The local computer may not have the necessary registry
information or message DLL files to display messages from a remote
computer. You may be able to use the /AUXSOURCE= flag to retrieve this
description; see Help and Support for details. The following
information is part of the event: Error : 701, Severity: 17, State: 2,
There is insufficient system memory to run this query..
Data:
0000: bd 02 00 00 11 00 00 00 ......
0008: 00 00 00 00 07 00 00 00 ......
0010: 6d 61 73 74 65 72 00 master.tommy2 (tommy2@.ptd.net) writes:

Quote:

Originally Posted by

I am having a issue with a customers server. the specs are:
2003 server
sql 6.5

SQL 6.5 on SQL 2003? I didn't even think that was possible. Has the
OS been upgraded from Win 2000?

Quote:

Originally Posted by

log file size is 1998MB (customer increased this 2 times this week) Log
space avail:600mb
>
In EM i try and truncate the log file... but the free space never
increases. Also there is no maint. plan on this database. When I try
and create one it advises against creating one with databases over
400MB.

Try running this from a query window:

sp_spaceused syslogs, true

I would be interested in seeing the output, at least if the problem
persists. It could also be interested in seeing the output from sp_helpdb.

Quote:

Originally Posted by

The past day or so these errors are showing up:
>
Any help?
>...
There is insufficient system memory to run this query..

That could be about anything. Have you checked the SQL Server Errorlog?

Don't know what experience you have of SQL 6.5, but in case you don't
know: this is a very different architecture from later releases.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This database used to run on NT 4.0 6 months ago. They moved it to a
win2003 server. It ran fine since being moved up till last week.

Erland Sommarskog wrote:

Quote:

Originally Posted by

tommy2 (tommy2@.ptd.net) writes:

Quote:

Originally Posted by

I am having a issue with a customers server. the specs are:
2003 server
sql 6.5

>
SQL 6.5 on SQL 2003? I didn't even think that was possible. Has the
OS been upgraded from Win 2000?
>

Quote:

Originally Posted by

log file size is 1998MB (customer increased this 2 times this week) Log
space avail:600mb

In EM i try and truncate the log file... but the free space never
increases. Also there is no maint. plan on this database. When I try
and create one it advises against creating one with databases over
400MB.

>
Try running this from a query window:
>
sp_spaceused syslogs, true
>
I would be interested in seeing the output, at least if the problem
persists. It could also be interested in seeing the output from sp_helpdb.
>

Quote:

Originally Posted by

The past day or so these errors are showing up:

Any help?
...
There is insufficient system memory to run this query..

>
That could be about anything. Have you checked the SQL Server Errorlog?
>
Don't know what experience you have of SQL 6.5, but in case you don't
know: this is a very different architecture from later releases.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||tommy2 (tommy2@.ptd.net) writes:

Quote:

Originally Posted by

This database used to run on NT 4.0 6 months ago. They moved it to a
win2003 server. It ran fine since being moved up till last week.

Wonder if that was just pure luck? I happened to look at
http://www.microsoft.com/sql/howtobuy/sqlonvista.mspx today. It does
not talk about 6.5 on Win2003...

Anyway, if their transaction log grows in size, there is some activity
going on, but what I cannot say from a distance. Did you try running
any of:

Quote:

Originally Posted by

Quote:

Originally Posted by

>Try running this from a query window:
>>
> sp_spaceused syslogs, true
>>
>I would be interested in seeing the output, at least if the problem
>persists. It could also be interested in seeing the output from
>sp_helpdb.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Any experience with SQLLITE

Anyone has ever used the new 'SQLLITE' tool? Looks like it reduces the backup time and size by almost 70%. Any feedback?Yup, was tasked with it, and tested the living heck out of it. The maximum database size I tested it with was 400G, - the resulting backup size with no compression was 82G vs. native backup - 381G.

Also wrote a wrapper that would take all possible parameters that SQLLiteSpeed supports and produce filenames for backup devices that are similar to the ones produced by SQLMAINT utility.

What else would you like to know? Oh, they also are coming up with a GUI for it, so that you can restore just like in EM.|||So your 400 GB DB was compressed to 82 GB? That's a good ratio, any improvement with backup and restore time?
Also have you noticed any performance degradation on your box running the SQL Server?|||I didn't retain time statistics, our convern was the space constraints, but I know that it was taking less than native. As for performance, - it is CPU-intensive, on both backup and restore. But if you're using default compression and no encryption, - it may max out 2 out of 4 CPU's for short periods of time.