Row size increase....

Hi,
I have large test data. I have face problem with row size greater than 8060
then it give me warning/error. If i want to store in row greater than 8060
then how to do? Is it any possible way? I try with ntext and sp_tableoption
but it gives me upto 256.
And i read in theory BLOB and all but how to use that?
Thanks in advance.
MilindHi
You may want to read
http://msdn.microsoft.com/library/d...r />
_8orl.asp
http://msdn.microsoft.com/library/d...r />
_6zec.asp
There is alot of other information regarding ntext/image in Books online.
John
"Milind" wrote:

> Hi,
> I have large test data. I have face problem with row size greater than 806
0
> then it give me warning/error. If i want to store in row greater than 8060
> then how to do? Is it any possible way? I try with ntext and sp_tableoptio
n
> but it gives me upto 256.
> And i read in theory BLOB and all but how to use that?
> Thanks in advance.
> Milind|||"Milind" schrieb:
> Hi,
> I have large test data. I have face problem with row size greater than 806
0
> then it give me warning/error. If i want to store in row greater than 8060
> then how to do? Is it any possible way? I try with ntext and sp_tableoptio
n
> but it gives me upto 256.
> And i read in theory BLOB and all but how to use that?
> Thanks in advance.
> Milind
There is no way to store more than 8060 bytes in a record (you cannot exceed
the page size with a record). Split the data and spread them to several
tables, connected by a common primary key!
The only exception to that rule are the blobs (images and texts) as they are
stored in a different location and the record contains only a pointer to tha
t
location. A single text field can contain up to 2 GB of text, but requires
only 4 Bytes in your record. But beware: it is not as easy to handle these
fields later (indexing, searching etc.) as it is with 'normal' data fields.|||If your table has one or more varchar columns resulting in a record layout
with a maximum possible size > 8060, then you can ignore the warning so long
as you don't actually submit an insert or update that would store more than
that maximum.
"Milind" <Milind@.discussions.microsoft.com> wrote in message
news:86D1FA64-C1FB-4781-AE05-FB910464A2E2@.microsoft.com...
> Hi,
> I have large test data. I have face problem with row size greater than
8060
> then it give me warning/error. If i want to store in row greater than 8060
> then how to do? Is it any possible way? I try with ntext and
sp_tableoption
> but it gives me upto 256.
> And i read in theory BLOB and all but how to use that?
> Thanks in advance.
> Milind

Row retrieval problem in small table

Dear Participant,

I face following problem by last few day, please help me for the same

My Mssql server 2000 with service pack 3 use for my lan bas users, normally they can work fine without any problem, but some time user not able to retrieve information from server. I had debugged this problem and found one small table with 50/60 records not retrieving for so long at clients machine. I open enterprise manage and trough that try to open table, but server in try mode only not show a single row after long time and give message client time out.

I open query analyzer and try to select * from table_name, it is also not retrieve a single row after long time and nothing got as a message.

Shutdown the server and restart , I am able to retrieve that table from EM, Query analyzer and from application also.

I dont understand what is the problem.

Thanks

R.MallSounds like a blocking problem to me. The next time this happens, take a look in Enterprise Manager Management->Current Activity->Process Info, and see if people are getting blocked in general. After that, you can try to narrow down the table, but I think you already have that table.|||I will see it, but I had look over the same matter on server itself, without a single user in LAN.|||Is there any jobs running in parallel... Jobs by default is a transaction... u can see once a transaction is running and if u try to get data thru enterprise manage and trough that try to open table it will not display any... but select should run in that case.....

I got a doubt what does this blocking means? is that 'lock' u guys are mentioning... If its lock u can run the below query to check if there is any lock still running,

SELECT spid, cmd, status, loginame, open_tran,
datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)|||Is there any easy going methods to resolve this problem.

Thanks

R.Mall|||Use sql profiler to watch it.|||i suggest you use SP_WHO to check the instances running and status of each instance. you would be able to see also if there are blocking...

use the Profiler if you want to know the different SQL commands being processed by the server. However, use this with caution since it might cause your system to slow down thus giving you the false impression that your script is slow.