Received error code 0x80040e57

I've check out that this error code says: A literal value in the command overflowed the range of the type of the associated column.

I received the error message when inserting large block data into a image column in SQL Server 2000. As I know, image support max 2G data to store. I use the C code function below to insert the data:

DBCreateParamBinary (hStmt, "", DB_PARAM_INPUT,&(pMonitorDBData->SampDat),NUM_SAMPLES_SETDBMONITOR*sizeof(float64) );

in which, the NUM_SAMPLES_SETDBMONITOR*sizeof(float64) stands for the data length by byte. and ,&(pMonitorDBData->SampDat) stands for the pointer address of the data block. (pls note: NUM_SAMPLES_SETDBMONITOR*sizeof(float64) = 4096*8=32768 bytes)

In addation, I've tried to use certain numbers instead of "NUM_SAMPLES_SETDBMONITOR*sizeof(float64)", and I found I could only set this value up to 8003 bytes. If 8003 above, this error message would be received.

What should I do with this issue? Any help?
Thanks a million!

I believe that you want to construct your table such that your images are out of row to avoid the 8k row item limit.

http://msdn2.microsoft.com/en-us/library/ms173530.aspx

Hope this helps,

John Gordon (MSFT)

Received error code 0x80040e57

I've check out that this error code says: A literal value in the command overflowed the range of the type of the associated column.

I received the error message when inserting large block data into a image column in SQL Server 2000. As I know, image support max 2G data to store. I use the C code function below to insert the data:

DBCreateParamBinary (hStmt, "", DB_PARAM_INPUT,&(pMonitorDBData->SampDat),NUM_SAMPLES_SETDBMONITOR*sizeof(float64) );

in which, the NUM_SAMPLES_SETDBMONITOR*sizeof(float64) stands for the data length by byte. and ,&(pMonitorDBData->SampDat) stands for the pointer address of the data block. (pls note: NUM_SAMPLES_SETDBMONITOR*sizeof(float64) = 4096*8=32768 bytes)

In addation, I've tried to use certain numbers instead of "NUM_SAMPLES_SETDBMONITOR*sizeof(float64)", and I found I could only set this value up to 8003 bytes. If 8003 above, this error message would be received.

What should I do with this issue? Any help?
Thanks a million!

I believe that you want to construct your table such that your images are out of row to avoid the 8k row item limit.

http://msdn2.microsoft.com/en-us/library/ms173530.aspx

Hope this helps,

John Gordon (MSFT)

rebuilding full text indexes

Aight, so I added a full text catalog and a full text index for one specific column and table in my database.

Now the issue is, whenever I rebuild it, it locks the full text index forever, making it unsuable. Now, there are only 30,000 records i need to search, so it isn't like there is this massive amount of data. What am I doing wrong to where it is locking the index and disallowing me to use the stored procedure that does the searching?

The indexes have changed in SQL Server 2005 but it places locks on the table or column during rebuilds that is the reason to schedule the now discontinued index related DBCC statements to run at night or when ever is a slow time for your users. There are some access restrictions during rebuild so, you may want to plan and use the new tuning advisor. In previous versions I know if needed you can count the IAM (index allocation mapping) pages and Extents so you can prevent related performance issues. Try the link below for the new index rebuild information. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms188388.aspx

|||

today we got a messagge like full text index is corrupt.Do u know why does this happen?

PS: we did send TRUNCATE TABLE COMMAND to the table that had the index...is it a problem?

sql

Rearrange column

Hi!
I want to rearrange a column in a table within a stored procedure.
I got this table with this sample data:
ID Text Number
1 Testing 1
2 Testing2 2
3 Testing3 3
4 Testing4 4
5 Testing5 5
I want this:
ID Text Number
1 Testing 5
2 Testing2 4
3 Testing3 3
4 Testing4 2
5 Testing5 1
The numbers of rows varies so it must be dynamical and i want 2 inparameters
(startID and endID).
I hope someone can help me.
//MagnusNumber = endID - Number +1?
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Mange" <Mange@.discussions.microsoft.com> wrote in message
news:777484C3-3018-48ED-81D8-5E9BF071DD1C@.microsoft.com...
> Hi!
> I want to rearrange a column in a table within a stored procedure.
> I got this table with this sample data:
> ID Text Number
> 1 Testing 1
> 2 Testing2 2
> 3 Testing3 3
> 4 Testing4 4
> 5 Testing5 5
> I want this:
> ID Text Number
> 1 Testing 5
> 2 Testing2 4
> 3 Testing3 3
> 4 Testing4 2
> 5 Testing5 1
> The numbers of rows varies so it must be dynamical and i want 2
> inparameters
> (startID and endID).
> I hope someone can help me.
> //Magnus
>|||When you say "rearrange a column", exactly what transformation are you
looking for? If you only specify start and end rows then what
determines the order of the other values you don't specify?
If this column is always to be dynamic then it doesn't really make
sense to have it in the table at all. Derive it in a query like this:
SELECT A.au_id, A.au_lname,
(SELECT COUNT(*)
FROM pubs.dbo.authors
WHERE au_id<=A.au_id) AS number
FROM pubs.dbo.authors AS A
ORDER BY A.au_id ;
David Portas
SQL Server MVP
--|||The meaning with the column "Number" is to display data sorted in webform.
But sometime this displayorder must be changed.
The rest of the columns must stay the same.
//Magnus
"David Portas" wrote:

> When you say "rearrange a column", exactly what transformation are you
> looking for? If you only specify start and end rows then what
> determines the order of the other values you don't specify?
> If this column is always to be dynamic then it doesn't really make
> sense to have it in the table at all. Derive it in a query like this:
> SELECT A.au_id, A.au_lname,
> (SELECT COUNT(*)
> FROM pubs.dbo.authors
> WHERE au_id<=A.au_id) AS number
> FROM pubs.dbo.authors AS A
> ORDER BY A.au_id ;
> --
> David Portas
> SQL Server MVP
> --
>|||Mange
create table #test
(
col int not null primary key,
col1 char(1)
)
insert into #test values (1,'a')
insert into #test values (2,'b')
insert into #test values (3,'c')
insert into #test values (4,'d')
select col,col1,
(select count(*) from #test t where t.col>=#test.col) from #test
"Mange" <Mange@.discussions.microsoft.com> wrote in message
news:5ABD8DA6-F591-4EFD-8C19-716042AB7010@.microsoft.com...
> The meaning with the column "Number" is to display data sorted in webform.
> But sometime this displayorder must be changed.
> The rest of the columns must stay the same.
> //Magnus
>
>
> "David Portas" wrote:
>|||>> The meaning with the column "Number" is to display data sorted in webform
. But sometime this displayorder must be changed. The rest of the columns mu
st stay the same. <<
Instead of the vague name "number", you should have used sometrhng like
"physical_display_position" so people maintaining the code would know
it is not a logical data element.
The basic principle of a tiered architecture is that display is done in
the front end and never in the back end. This a more basic programming
principle than just SQL and RDBMS.|||It doesnt matter what the intention with the column is.
Do you mean that all sorting is done in the webform ?
"--CELKO--" wrote:

> Instead of the vague name "number", you should have used sometrhng like
> "physical_display_position" so people maintaining the code would know
> it is not a logical data element.
> The basic principle of a tiered architecture is that display is done in
> the front end and never in the back end. This a more basic programming
> principle than just SQL and RDBMS.
>

Rearrange column

Hi!
I'll try again
I want to rearrange a column in a table within a stored procedure.
I got this table with this sample data:
ID Text Displayorder
1 Testing 100
2 Testing2 200
3 Testing3 300
4 Testing4 400
5 Testing5 500
The numbers of rows varies so i want 2 inparameters
(start_ID and End_ID).
Example:
start_ID, End_ID (2,4)
Will return.
ID Text Number
1 Testing 100
2 Testing2 400
3 Testing3 300
4 Testing4 200
5 Testing5 500
Can this be done?
I hope someone can help me.
//MagnusHi Magnus
Assuming that your Id columns are not contiguous then you will need to rank
them and do something like:
DECLARE @.lower int, @.upper int
SELECT @.lower = 2, @.upper = 4
SELECT [ASC].ID, [ASC].[Text],
CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
[DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
[Order],
D.id, D.[Text], D.DisplayOrder
FROM MyDisplays D ) [ASC]
JOIN
( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
D.id, D.[Text], D.DisplayOrder
FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
You can still use [id] in the case statement if necessary.
John
"Mange" wrote:

> Hi!
> I'll try again
> I want to rearrange a column in a table within a stored procedure.
> I got this table with this sample data:
> ID Text Displayorder
> 1 Testing 100
> 2 Testing2 200
> 3 Testing3 300
> 4 Testing4 400
> 5 Testing5 500
> The numbers of rows varies so i want 2 inparameters
> (start_ID and End_ID).
> Example:
> start_ID, End_ID (2,4)
> Will return.
> ID Text Number
> 1 Testing 100
> 2 Testing2 400
> 3 Testing3 300
> 4 Testing4 200
> 5 Testing5 500
> Can this be done?
> I hope someone can help me.
> //Magnus
>|||Thanks but..
That doesnt save the result into that table.
"John Bell" wrote:
> Hi Magnus
> Assuming that your Id columns are not contiguous then you will need to ran
k
> them and do something like:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> SELECT [ASC].ID, [ASC].[Text],
> CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
> [DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
> FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
> [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [ASC]
> JOIN
> ( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
> You can still use [id] in the case statement if necessary.
> John
>
> "Mange" wrote:
>|||> That doesnt save the result into that table.
No, it does not, but you can *use* it in your procedure to make it do whan
you need. :)
ML|||It doesnt work.
The result is the whole table.
"John Bell" wrote:
> Hi Magnus
> Assuming that your Id columns are not contiguous then you will need to ran
k
> them and do something like:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> SELECT [ASC].ID, [ASC].[Text],
> CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
> [DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
> FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
> [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [ASC]
> JOIN
> ( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
> You can still use [id] in the case statement if necessary.
> John
>
> "Mange" wrote:
>|||Hi
It does what you specified with the data that you gave. You have not
specified what your restriction should be but you should be able to use a
WHERE clause in each of the derived tables to do what you require.
John
"Mange" wrote:
> Thanks but..
> That doesnt save the result into that table.
>
> "John Bell" wrote:
>|||If you actually want to change the data try:
DECLARE @.lower int, @.upper int
SELECT @.lower = 2, @.upper = 4
UPDATE O
SET DisplayOrder = N.DisplayOrder
FROM MyDisplays O
JOIN MyDisplays N ON ( O.id = @.lower AND N.id = @.upper ) OR ( O.id = @.upper
AND N.id = @.lower )
John
"Mange" wrote:
> Thanks but..
> That doesnt save the result into that table.
>
> "John Bell" wrote:
>|||Hi
To be more precise.
The result is exactly like if i would have used Select * from MyDisplays
"John Bell" wrote:
> Hi
> It does what you specified with the data that you gave. You have not
> specified what your restriction should be but you should be able to use a
> WHERE clause in each of the derived tables to do what you require.
> John
> "Mange" wrote:
>|||Many Thanks John you are a star.
I'm sorry about my bad english and my poor knowledge about SQL.
Thanks again it works know.
//Magnus
"John Bell" wrote:
> If you actually want to change the data try:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> UPDATE O
> SET DisplayOrder = N.DisplayOrder
> FROM MyDisplays O
> JOIN MyDisplays N ON ( O.id = @.lower AND N.id = @.upper ) OR ( O.id = @.uppe
r
> AND N.id = @.lower )
> John
> "Mange" wrote:
>

READTEXT and return values

First question :

How can I set the return value of READTEXT to a variable of type nvarchar.

Second question :

I have a table t1 with a ntext column n1.

The ntext column has has words separated by empty space.
Each word can be assumed to be of size <= 255 characters.

How can I extract all the keywords in the ntext column to a table t2 with a column word nvarchar(255).

Assume that the text in column n1 is big enough so that it cannot be cast into a nvarchar or any other simpler type.

Any help on this is greatly appreciated.
Please do provide a sample code.

Alok.Have you referred to books online for READTEXT topoic.