Error when attempting to delete rows

I have a log table with no indexes, triggers, or keys. During the course of development, I will clean out the entries by selecting all the rows (in Database Explorer) and hitting the delete key.

Occassionally, I will end up with couple rows (out of many) that won't delete. I get the following error when I attempt to delete the "affected" rows:

A problem occurred attempting to delete row 1.
Error Source: Microosft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the
unique or they alter multiple rows (2 rows).

I'm also not able to modify any of the data of the affected rows. There don't seem to be anything really special or unique about these affected rows (compared to the ones I'm able to delete). I've tried rebooting the machine and that did resolve the issue.

This is the second time I've encountered this issue. By any chance, is this a known bug with the beta (it's SQL Server 2005 June CTP)? Or is it some else I'm not aware of?

I've also tried deleting the rows using SQL Server Management, same results.

RichardCan you run SQL Profiler to check the different events executing while you delete these records? There must be something happening in the background that is causing all these.|||

Did you ever get the problem resolved? I am having the same problem.

Thanks, Bob

|||

Does the table have a primary key or unique constraint?

|||I just saw this. I'm sure it was resolved since the post was awhile ago, but that error message can occur if there is a constraint which the deletion violates. A common one might be that a field is set to "NOT NULL".

Error when attempting to delete rows

I have a log table with no indexes, triggers, or keys. During the course of development, I will clean out the entries by selecting all the rows (in Database Explorer) and hitting the delete key.

Occassionally, I will end up with couple rows (out of many) that won't delete. I get the following error when I attempt to delete the "affected" rows:

A problem occurred attempting to delete row 1.
Error Source: Microosft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the
unique or they alter multiple rows (2 rows).

I'm also not able to modify any of the data of the affected rows. There don't seem to be anything really special or unique about these affected rows (compared to the ones I'm able to delete). I've tried rebooting the machine and that did resolve the issue.

This is the second time I've encountered this issue. By any chance, is this a known bug with the beta (it's SQL Server 2005 June CTP)? Or is it some else I'm not aware of?

I've also tried deleting the rows using SQL Server Management, same results.

Richard
Can you run SQL Profiler to check the different events executing while you delete these records? There must be something happening in the background that is causing all these.|||

Did you ever get the problem resolved? I am having the same problem.

Thanks, Bob

|||

Does the table have a primary key or unique constraint?

|||I just saw this. I'm sure it was resolved since the post was awhile ago, but that error message can occur if there is a constraint which the deletion violates. A common one might be that a field is set to "NOT NULL".

Error when attempting to delete rows

I have a log table with no indexes, triggers, or keys. During the course of development, I will clean out the entries by selecting all the rows (in Database Explorer) and hitting the delete key.

Occassionally, I will end up with couple rows (out of many) that won't delete. I get the following error when I attempt to delete the "affected" rows:

A problem occurred attempting to delete row 1.
Error Source: Microosft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the
unique or they alter multiple rows (2 rows).

I'm also not able to modify any of the data of the affected rows. There don't seem to be anything really special or unique about these affected rows (compared to the ones I'm able to delete). I've tried rebooting the machine and that did resolve the issue.

This is the second time I've encountered this issue. By any chance, is this a known bug with the beta (it's SQL Server 2005 June CTP)? Or is it some else I'm not aware of?

I've also tried deleting the rows using SQL Server Management, same results.

Richard
Can you run SQL Profiler to check the different events executing while you delete these records? There must be something happening in the background that is causing all these.|||

Did you ever get the problem resolved? I am having the same problem.

Thanks, Bob

|||

Does the table have a primary key or unique constraint?

|||I just saw this. I'm sure it was resolved since the post was awhile ago, but that error message can occur if there is a constraint which the deletion violates. A common one might be that a field is set to "NOT NULL".

Error selecting text from a SQL database

Hello,
I have an application that used an access database and I am currently
migrating to SQL Server. The software is designed to use Odbc object
found in the .Net framework version 1.1. I have a simple select
statement that is driving me nuts. It works fine with the Access
databases but dies under SQL Server.
The statement is:
SELECT
settingValue
FROM
tblSettings
WHERE ( owner=@.owner and setting=@.setting )
settingValue is an ntext field and owner and setting are varchar fields.
When I try to execute the query I get an error:
ERROR [42000][Microsoft][ODBC SQL Server Driver][SQL Server]The text,
ntext, and image data types cannot be compared or sorted, except when
using IS NULL, or LIKE operator.
There are no indexes on any of the fields so I cannot figure out what
the problem is.
Mageos
Try this to see if you still get an error:
SELECT
owner,
setting,
settingValue
FROM
tblSettings
WHERE owner = @.owner
and setting=@.setting
--
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/
"mageos" <matt.dot.osborne@.cox.dot.com> wrote in message
news:vbGyd.2281$wZ2.581@.newssvr13.news.prodigy.com ...
> Hello,
> I have an application that used an access database and I am currently
> migrating to SQL Server. The software is designed to use Odbc object
> found in the .Net framework version 1.1. I have a simple select
> statement that is driving me nuts. It works fine with the Access
> databases but dies under SQL Server.
> The statement is:
> SELECT
> settingValue
> FROM
> tblSettings
> WHERE ( owner=@.owner and setting=@.setting )
> settingValue is an ntext field and owner and setting are varchar fields.
> When I try to execute the query I get an error:
> ERROR [42000][Microsoft][ODBC SQL Server Driver][SQL Server]The text,
> ntext, and image data types cannot be compared or sorted, except when
> using IS NULL, or LIKE operator.
> There are no indexes on any of the fields so I cannot figure out what
> the problem is.
> Mageos
|||> settingValue is an ntext field and owner and setting are varchar fields.
> When I try to execute the query I get an error:
> ERROR [42000][Microsoft][ODBC SQL Server Driver][SQL Server]The text,
> ntext, and image data types cannot be compared or sorted, except when
> using IS NULL, or LIKE operator.
The error tells you exactly where the problem is. You are trying to do a
compare on an ntext field. Double check your create table statement. I think
you will find that either owner or setting is not of the datatype you
expected. What method of creating the tables in SQL did you use. I find that
often when I let SQL help me out converting data from an outside source, it
comes up with insane assumptions about what the datatype needs to be.
I tried reproducing the error with the script below and it works fine for
me.
create table test(
settingvalue ntext,
owner varchar(5),
setting varchar(5)
)
insert test (settingvalue, owner, setting)
values ('jgdfkjgiugsv8ohyohfef mne hevbe vmnerv', '1', '2')
declare @.owner varchar(5)
declare @.setting varchar(5)
set @.owner = '1'
set @.setting ='2'
select * from test where owner = @.owner and setting = @.setting

error selecting null columns

I want to run a query that selects rows from the table where a datetime column has null values;

select*from OrderswhereIsNull(dClosedDate,'Null')='Null'

However i get this error:

Conversion failed when converting datetime from character string.

Any help appreciated

SELECT * FROM Orders WHERE ISNULL(dClosedDate, '1900-01-01') = '1900-01-01'

|||

thx...i tried dClosedDate is NULL and it worked too.

Error selecting from a database

I have a database running on MSDE it was working fine. Recently when I try to select from the database I am getting the following error after a few records have been returned. All of the tables are giving this problem. Not sure what to do.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.
Connection Broken
Have you installed SP or changed password? Can you connect to the database
locally using Windows or SQL authentication? Does it fail when you run the
queries locally?
Thanks,
Amy
| Thread-Topic: Error selecting from a database
| thread-index: AcQcwIQNBPeO2TYQSyWHTFoBm4eMiw==
| X-Tomcat-NG: microsoft.public.sqlserver.msde
| From: =?Utf-8?B?V2FycmVu?= <anonymous@.discussions.microsoft.com>
| Subject: Error selecting from a database
| Date: Wed, 7 Apr 2004 09:51:04 -0700
| Lines: 6
| Message-ID: <B1588E26-CFBE-441B-A743-E607EB44679D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.msde
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.msde:13485
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.sqlserver.msde
|
| I have a database running on MSDE it was working fine. Recently when I
try to select from the database I am getting the following error after a
few records have been returned. All of the tables are giving this problem.
Not sure what to do.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
your network documentation.
Connection Broken
|
|||Thanks Amy
I don't have a problem connecting to the database locally or remotely. I also haven't installed or change
anything recently but for some reason or another it will not run a simple select query.

Error selecting data due to floating pt exception

Got a good one! or a bad one depending if the boss is breathing down the neck.

I am selecting from a table. When certain columns are selected, sql server generates the following message:

[Microsoft][ODBC SQL Server Driver]Numeric value out of range

To be more specific, if i select the id column of the table I get perhaps 4 rows.

select id from table
1
2
3
4

If I select the id column plus the column causing problems, then I only see the rows that don't generate the message, plus i get the above listed message

select id, columnThatIsFloat from table
1

In fact there are 4 columns in the table that are generating these messages. All of which are of type Float.

If I try to convert the column I get the following error:
Server: Msg 3628, Level 16, State 1, Line 1
A floating point exception occurred in the user process. Current transaction is canceled.

What could cause this. Bad data? How could it have been inputted? Is this a bug with SQL 2k? Why me?

SQL information:
Microsoft SQL Server 2000 - 8.00.384 (Intel X86) May 23 2001 00:02:52 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)Are you aggregating any of the columns ? Have you run dbcc checktable or dbreindex ?|||Which service pack do you have installed for sql server ?|||I am doing no aggregation. It truely is as simple as

select colA, colB from table

SQL information:
Microsoft SQL Server 2000 - 8.00.384 (Intel X86) May 23 2001 00:02:52 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Originally posted by rnealejr
Which service pack do you have installed for sql server ?|||The problem with posting SQL Server 2000 - 8.00.384 it does not tell you what service pack sql server has installed. In your case you have sp1 installed. I would recommend that you update your service pack.

Also, have you run the dbcc commands ?|||Hello,

I am not aggregating columns (see the previous message) and I have run dbcc checktable with the following result:

DBCC results for 'TABLENAME'.
There are 5218 rows in 62 pages for object 'TABLENAME'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I do not know what "dbreindex" is.

Thanks
Josh

Originally posted by rnealejr
Are you aggregating any of the columns ? Have you run dbcc checktable or dbreindex ?|||There is a bug with the indexing wizard that is corrected when you update the service pack that causes similar issues that you are experiencing. dbcc dbreindex rebuilds an index for a table.|||I've now upgraded to sp3a. I was looking at the service pack for the Win2K server. My oversight. Unfortunately I am still having the exact same problem.

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition
on Windows NT 5.0 (Build 2195: Service Pack 4)

i did run "dbcc checktable" and "dbcc dbreindex"

If you have any more ideas I would greatly appreciate.

Thanks alot

Originally posted by rnealejr
The problem with posting SQL Server 2000 - 8.00.384 it does not tell you what service pack sql server has installed. In your case you have sp1 installed. I would recommend that you update your service pack.

Also, have you run the dbcc commands ?