Hi All,
I have created a table in sql server 2000 where at the time of creating
it, the row size excced 8K. I understand why I get the warning below:
The table 'tbl_detail' has been created but its maximum row size
(12367) exceeds the maximum number of bytes per row (8060). INSERT or
UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
However, when I call a stored procedure from my ASP Code, which returns
me this warning, my ASP page displays the warning and does not move to
the next line.
What can I do not to get this warning? How do I turn off warning
messages? I tried to wrap my stored procedure call code within SET
NOCOUNT ON and SET NOCOUNT OFF but that didn't help.
Any help would be really appreciated,
Thanks,
Boris 6 17974
You know, I don't know how to turn errors off; wouldn't it be a better
idea to handle the error, rather than turning it off?
Another question I would have is why ignore the error; if your dataset
is that large, have you considered a 1-to-1 join, or perhaps replacing
some of your varchar fields with text?
Just asking.
Thanks for quick reply,
Well, chaning the table structure would be a lot more difficult, since
that table name is used in a lots of places and within many stored
procedures.
I want to keep the error on, since I am handling errors. It is the
warning that I want to ignore. Now I don't know if errors and warnings
are treated and returned in the same way or not.
Either way, if I don't turn off the warning, how do I handle that
warning? Is there a warning # or something I can check?
Thanks in advance,
Boris
Stu wrote: You know, I don't know how to turn errors off; wouldn't it be a better idea to handle the error, rather than turning it off?
Another question I would have is why ignore the error; if your dataset is that large, have you considered a 1-to-1 join, or perhaps replacing some of your varchar fields with text?
Just asking.
Boris (su**************@gmail.com) writes: I have created a table in sql server 2000 where at the time of creating it, the row size excced 8K. I understand why I get the warning below:
The table 'tbl_detail' has been created but its maximum row size (12367) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
However, when I call a stored procedure from my ASP Code, which returns me this warning, my ASP page displays the warning and does not move to the next line.
What can I do not to get this warning? How do I turn off warning messages? I tried to wrap my stored procedure call code within SET NOCOUNT ON and SET NOCOUNT OFF but that didn't help.
You cannot turn off the warning on the SQL Server side.
I am a little surprised that ASP stops on the warning. Usually ADO
swallows informational messages completely. You could customize the
error handler in the ASP code, to check on the error number, and
ignore this message.
However, there is something fishy here. If the table already exists,
running the stored procedure should not give you the warning. So I
suspect one three things:
1) You are creating a temp table in the SP which also possibly could
exceed 8K. In this case, you could as a last resort split up the
temp table into two.
2) You insert data that exceeds 8K, so you actually get an error, not
a warning.
3) You are dropping and recreating tbl_detail. Dynamically changing the
schema is usuaally poor design.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Thanks Erland,
1 and 2 are not true, however, I am creating and dropping table
dynamically. And when my stored procedure tries to an update operation
on that temp_detail table, it issues the warning. Now the table does
get updated, as I have tested it. When I run the stored procedure
outside ASP (in query analyzer), I see the same message. Its just that
it does not fail. The ASP Code however stops at the warning message,
which is surprising to me too.
Since I can not turn off that warning message, I guess I need to do the
error handling, or warning handling in my ASP code. I just need to know
how. If you know how can I do that, please let me know. Any help would
be really appreciated.
As far as changing the table structure and spiliting in two tables, it
is almost impossible. That table name has been used at many different
places. Everyone would have to change the logic in that case.
Thanks,
Boris
Erland Sommarskog wrote: Boris (su**************@gmail.com) writes: I have created a table in sql server 2000 where at the time of creating it, the row size excced 8K. I understand why I get the warning below:
The table 'tbl_detail' has been created but its maximum row size (12367) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
However, when I call a stored procedure from my ASP Code, which returns me this warning, my ASP page displays the warning and does not move to the next line.
What can I do not to get this warning? How do I turn off warning messages? I tried to wrap my stored procedure call code within SET NOCOUNT ON and SET NOCOUNT OFF but that didn't help.
You cannot turn off the warning on the SQL Server side.
I am a little surprised that ASP stops on the warning. Usually ADO swallows informational messages completely. You could customize the error handler in the ASP code, to check on the error number, and ignore this message.
However, there is something fishy here. If the table already exists, running the stored procedure should not give you the warning. So I suspect one three things:
1) You are creating a temp table in the SP which also possibly could exceed 8K. In this case, you could as a last resort split up the temp table into two. 2) You insert data that exceeds 8K, so you actually get an error, not a warning. 3) You are dropping and recreating tbl_detail. Dynamically changing the schema is usuaally poor design. -- Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Boris (su**************@gmail.com) writes: 1 and 2 are not true, however, I am creating and dropping table dynamically.
So this where you would concentrate on changing things. I would
never permit tables being created dynamically in databases I am
responsible for.
If all you want to do is to clear out the table, just use TRUNCATE
TABLE instead.
But what happens if two instances of that ASP page runs at the same
time.
Since I can not turn off that warning message, I guess I need to do the error handling, or warning handling in my ASP code. I just need to know how. If you know how can I do that, please let me know. Any help would be really appreciated.
You are likely to know more ASP than I do, because I know nothing about
ASP. (It's a tree, isn't it? :-)
But assuming that you use ADO as the client library, you can check
the Errors collection for errors. The Error object has a .Number
property, and this is the error number from SQL Server. Which number
you should check for, I don't know, but that's very easy to find
out.
If you have more detailed questions about ASP programming, you are
definitely better of in an ASP forum.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Cant the table be split into two, then create a VIEW to join the two
tables. If you use the current table's name as the view, then the other
procedures wont know the difference?
I could be way off base..
hth This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Navin |
last post by:
Ado Sort -Relate, Compute By, or Sort operations cannot be done on
column(s) whose key length is unknown or exceeds 10 KB.
hi,
guys
i have asp application running on iis 5.0 windows 2000
i use...
|
by: Peter |
last post by:
Hi everyone
I had an access database running as the source for a website but it
has become too large to run correctly so it has been ported to MS-SQL
the problem is that 4 of the fields were Memo...
|
by: Joseph A Romeo |
last post by:
I have written an XSLT transformation on an ASP.NET page. The resulting HTML
is primarily a table of links. I have found that when the resulting HTML is
less than or equal to 16040 bytes, the...
|
by: Joseph A Romeo |
last post by:
I have written an XSLT transformation on an ASP.NET page. The resulting HTML
is primarily a table of links. I have found that when the resulting HTML is
less than or equal to 16040 bytes, the...
|
by: George |
last post by:
HI ,
I am getting the below error while i compile a Store procedure which
is my main store procedure and does the call to all other supporting
stored procedure.
Compartively its a large file...
|
by: venkat |
last post by:
Hi,
Can any one tell me the best way to truncate a file if it
exceeds a specified file size? I need to truncate the start of the file by
the appropriate amount (The data to insert). Is there any...
|
by: Lubomir |
last post by:
Hi,
Where in .NET are definded constants for maximal file name length and
maximal file path?
Thanks,
Lubomir
|
by: =?Utf-8?B?Z3Jva25yb2xs?= |
last post by:
Is there a way that I can define a WebMethod with a parameter that is a fixed
length string? I'm using VB.Net 2005 and would like to define a webmethod
that will prevent the caller from passing a...
|
by: anupalavila |
last post by:
Hi
me with C#.net , I have 2 textboxes and a string s="Hai to all in bytes forum"
I am assigning this string s to textbox1.test. How can I do such a way that if textbox1.text visible portion...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |