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 whitepaper that talks on Web Servers to SQL server connections

I can understand when theres SQL authentication involved and how one may
pass the SQL Server credentials that way ..
But cant figure out how would IIS connect using Windows Authentication ?
What Windows login would these web servers need ? Do i create that same
login on all Web Servers and also on SQL .. What this anonymous login ? We
want to tighten the controls using Windows Authentication and dont know how
to do so with Web Servers..
Authentication and Security White Paper for Internet Developers
http://support.microsoft.com/default...b;en-us;174811
Understanding Internet Information Security
http://www.microsoft.com/ntserver/te.../iissecure.asp
AMB
"Hassan" wrote:

> I can understand when theres SQL authentication involved and how one may
> pass the SQL Server credentials that way ..
> But cant figure out how would IIS connect using Windows Authentication ?
> What Windows login would these web servers need ? Do i create that same
> login on all Web Servers and also on SQL .. What this anonymous login ? We
> want to tighten the controls using Windows Authentication and dont know how
> to do so with Web Servers..
>
>
|||This paper discusses the ASP.NET model. Basically it looks like you have to
grant the ASPNET user database and some other permissions. Hope this helps:
http://msdn.microsoft.com/library/de...SecNetch08.asp
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OneQ%23N3RFHA.3732@.tk2msftngp13.phx.gbl...
>I can understand when theres SQL authentication involved and how one may
> pass the SQL Server credentials that way ..
> But cant figure out how would IIS connect using Windows Authentication ?
> What Windows login would these web servers need ? Do i create that same
> login on all Web Servers and also on SQL .. What this anonymous login ? We
> want to tighten the controls using Windows Authentication and dont know
> how
> to do so with Web Servers..
>

Any whitepaper that talks on Web Servers to SQL server connections

I can understand when theres SQL authentication involved and how one may
pass the SQL Server credentials that way ..
But cant figure out how would IIS connect using Windows Authentication ?
What Windows login would these web servers need ? Do i create that same
login on all Web Servers and also on SQL .. What this anonymous login ? We
want to tighten the controls using Windows Authentication and dont know how
to do so with Web Servers..Authentication and Security White Paper for Internet Developers
http://support.microsoft.com/default.aspx?scid=kb;en-us;174811
Understanding Internet Information Security
http://www.microsoft.com/ntserver/techresources/webserv/iissecure.asp
AMB
"Hassan" wrote:
> I can understand when theres SQL authentication involved and how one may
> pass the SQL Server credentials that way ..
> But cant figure out how would IIS connect using Windows Authentication ?
> What Windows login would these web servers need ? Do i create that same
> login on all Web Servers and also on SQL .. What this anonymous login ? We
> want to tighten the controls using Windows Authentication and dont know how
> to do so with Web Servers..
>
>|||This paper discusses the ASP.NET model. Basically it looks like you have to
grant the ASPNET user database and some other permissions. Hope this helps:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetch08.asp
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OneQ%23N3RFHA.3732@.tk2msftngp13.phx.gbl...
>I can understand when theres SQL authentication involved and how one may
> pass the SQL Server credentials that way ..
> But cant figure out how would IIS connect using Windows Authentication ?
> What Windows login would these web servers need ? Do i create that same
> login on all Web Servers and also on SQL .. What this anonymous login ? We
> want to tighten the controls using Windows Authentication and dont know
> how
> to do so with Web Servers..
>

Any whitepaper that talks on Web Servers to SQL server connections

I can understand when theres SQL authentication involved and how one may
pass the SQL Server credentials that way ..
But cant figure out how would IIS connect using Windows Authentication ?
What Windows login would these web servers need ? Do i create that same
login on all Web Servers and also on SQL .. What this anonymous login ? We
want to tighten the controls using Windows Authentication and dont know how
to do so with Web Servers..Authentication and Security White Paper for Internet Developers
http://support.microsoft.com/defaul...kb;en-us;174811
Understanding Internet Information Security
http://www.microsoft.com/ntserver/t...v/iissecure.asp
AMB
"Hassan" wrote:

> I can understand when theres SQL authentication involved and how one may
> pass the SQL Server credentials that way ..
> But cant figure out how would IIS connect using Windows Authentication ?
> What Windows login would these web servers need ? Do i create that same
> login on all Web Servers and also on SQL .. What this anonymous login ? We
> want to tighten the controls using Windows Authentication and dont know ho
w
> to do so with Web Servers..
>
>|||This paper discusses the ASP.NET model. Basically it looks like you have to
grant the ASPNET user database and some other permissions. Hope this helps:
Netch08.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
Netch08.asp
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OneQ%23N3RFHA.3732@.tk2msftngp13.phx.gbl...
>I can understand when theres SQL authentication involved and how one may
> pass the SQL Server credentials that way ..
> But cant figure out how would IIS connect using Windows Authentication ?
> What Windows login would these web servers need ? Do i create that same
> login on all Web Servers and also on SQL .. What this anonymous login ? We
> want to tighten the controls using Windows Authentication and dont know
> how
> to do so with Web Servers..
>

any plans for SQL 2K on x64 systems ?

Theres SQL 2000 on IA-64 but none for x64 .. why ? and are there plans for
launching it soon ? Pls let me know . Thanks
SQL 2005 (Yukon) Beta 2 is available to MSDN subscribers. One of the build
platforms is x64. I doubt that this will be retrofitted back into SQL 2000.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ud5SY3yhEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Theres SQL 2000 on IA-64 but none for x64 .. why ? and are there plans for
> launching it soon ? Pls let me know . Thanks
>

any plans for SQL 2K on x64 systems ?

Theres SQL 2000 on IA-64 but none for x64 .. why ? and are there plans for
launching it soon ? Pls let me know . ThanksSQL 2005 (Yukon) Beta 2 is available to MSDN subscribers. One of the build
platforms is x64. I doubt that this will be retrofitted back into SQL 2000.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ud5SY3yhEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Theres SQL 2000 on IA-64 but none for x64 .. why ? and are there plans for
> launching it soon ? Pls let me know . Thanks
>

any plans for SQL 2K on x64 systems ?

Theres SQL 2000 on IA-64 but none for x64 .. why ? and are there plans for
launching it soon ? Pls let me know . ThanksSQL 2005 (Yukon) Beta 2 is available to MSDN subscribers. One of the build
platforms is x64. I doubt that this will be retrofitted back into SQL 2000.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ud5SY3yhEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Theres SQL 2000 on IA-64 but none for x64 .. why ? and are there plans for
> launching it soon ? Pls let me know . Thanks
>

Any easy way of getting a columnlist for use with an INSERT statement

Hi
I'm having some data that I need to copy from one table to another using an
INSERT statement. I'm wondering if there's an easier way to enter the column
list than having to type it in manually? In this case the table has about 20
columns (..of course with a lot of odd names...) and I find a bit
cumbersome to type all these in by hand, so hopefully some of you knows an
easier way of doing it?
Regards
Steen
In Query Analyzer, right-click on the table in the Object Browser. Mouse
over "Script Object to New Window as" and click "Insert".
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:erbYBVZmEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi
> I'm having some data that I need to copy from one table to another using
an
> INSERT statement. I'm wondering if there's an easier way to enter the
column
> list than having to type it in manually? In this case the table has about
20
> columns (..of course with a lot of odd names...) and I find a bit
> cumbersome to type all these in by hand, so hopefully some of you knows an
> easier way of doing it?
> Regards
> Steen
>