Ok, I give up (again). I have a report that breaks on company number.
I want to display
="Details for Company " & Fields!Company.Value
in the table header (or in a textbox within the body above the table).
However, I only get the value of the first company number in the dataset,
even when it breaks on company. How is this done?
Thank you very much,
Marc MillerAdd a table group with a grouping expression of =Fields!Company.Value
In the table group header your expression will work ="Details for Company "
& Fields!Company.Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:Oa7l51UgEHA.2416@.TK2MSFTNGP10.phx.gbl...
> Ok, I give up (again). I have a report that breaks on company number.
> I want to display
> ="Details for Company " & Fields!Company.Value
> in the table header (or in a textbox within the body above the table).
> However, I only get the value of the first company number in the dataset,
> even when it breaks on company. How is this done?
> Thank you very much,
> Marc Miller
>
Showing posts with label value. Show all posts
Showing posts with label value. Show all posts
Tuesday, March 27, 2012
cant get around @@TEXTSIZE
Hello all,
Having a problem getting around @.@.TEXTSIZE. Shared hosting, SQL Server 2000. @.@.TEXTSIZE Value appears to be 64000, or anyway that's what I get in a query from ColdFusion. Which would explain why some of my very long values in my text column (up to 125K) are being truncated on SELECT.
But when I crank up Management Studio and query for @.@.TEXTSIZE I get the full 2GB max. So why does a SELECT in Management Studio of a row with one of my very long values still return truncated results?
I know that my full content is indeed in the database; READTEXT Research.HTMLContent @.ptrval 124484 100 gets me the last 100 characters of the full content as expected.
Sticking SET TEXTSIZEs into my queries (in ColdFusion app or Management Studio) has no effect. Are there other settings that can behave this way that I should be looking into?
Any thoughts appreciated; I've got a busted production application and an unhappy client.Found the answer, which I'll post here for the benefit of future Googlers--
It wasn't a SQL Server issue, but a ColdFusion one. There is a ColdFusion setting, in ColdFusion Administrator under Edit Datasource, called "Enable retrieval of long text." I'm not sure whether it's on or off by default, but in my case it was off. In which case, retrieval of SELECTS of text is limited to a data size specified on the same screen, labeled something like "Long text buffer limit."
Behind the scenes it does appear that ColdFusion is manipulating @.@.TEXTSIZE with this long text buffer limit, since a SELECT @.@.TEXTSIZE sent via a ColdFusion query returns the limit value.
Having a problem getting around @.@.TEXTSIZE. Shared hosting, SQL Server 2000. @.@.TEXTSIZE Value appears to be 64000, or anyway that's what I get in a query from ColdFusion. Which would explain why some of my very long values in my text column (up to 125K) are being truncated on SELECT.
But when I crank up Management Studio and query for @.@.TEXTSIZE I get the full 2GB max. So why does a SELECT in Management Studio of a row with one of my very long values still return truncated results?
I know that my full content is indeed in the database; READTEXT Research.HTMLContent @.ptrval 124484 100 gets me the last 100 characters of the full content as expected.
Sticking SET TEXTSIZEs into my queries (in ColdFusion app or Management Studio) has no effect. Are there other settings that can behave this way that I should be looking into?
Any thoughts appreciated; I've got a busted production application and an unhappy client.Found the answer, which I'll post here for the benefit of future Googlers--
It wasn't a SQL Server issue, but a ColdFusion one. There is a ColdFusion setting, in ColdFusion Administrator under Edit Datasource, called "Enable retrieval of long text." I'm not sure whether it's on or off by default, but in my case it was off. In which case, retrieval of SELECTS of text is limited to a data size specified on the same screen, labeled something like "Long text buffer limit."
Behind the scenes it does appear that ColdFusion is manipulating @.@.TEXTSIZE with this long text buffer limit, since a SELECT @.@.TEXTSIZE sent via a ColdFusion query returns the limit value.
Thursday, March 22, 2012
Cant figure out this query
I have a table with multiple records.
Some have the same value in the 'subkey' field.
I want to select all the records from the table that have their highest MAINKEY.
So say there were 4 records in the table that has 3 fields (id, subkey and mainkey)
Each record has a unique id field but the subkeys are the same for the first two and the sub keys are the same for the last two while the Mainkey can be different.
So the tables looks sort of lLike this:
ID SK MK
1 10 2
2 10 3
3 25 2
4 25 3
I want to query and select one record for each subkey, but I want it to be record that has the highest mainkey. In this case, it would be records with ID 2 and 4.
I can not figure this out. :eek:
Any help would be GREATLY appreciated.This works...
SELECT [ID]
FROM yourtable T1
WHERE EXISTS (
SELECT SK, MAX(MK) AS MK
FROM yourtable T2
WHERE T1.SK=T2.SK
GROUP BY SK
HAVING T1.MK=MAX(T2.MK))|||select a.id, a.sk, a.mk from yourtable a
where a.mk in(select max(b.mk) from yourtable b
where a.sk = b.sk)|||Simpler even:
select a.sk, max(a.mk) as MK from yourtable a
group by a.sk
Some have the same value in the 'subkey' field.
I want to select all the records from the table that have their highest MAINKEY.
So say there were 4 records in the table that has 3 fields (id, subkey and mainkey)
Each record has a unique id field but the subkeys are the same for the first two and the sub keys are the same for the last two while the Mainkey can be different.
So the tables looks sort of lLike this:
ID SK MK
1 10 2
2 10 3
3 25 2
4 25 3
I want to query and select one record for each subkey, but I want it to be record that has the highest mainkey. In this case, it would be records with ID 2 and 4.
I can not figure this out. :eek:
Any help would be GREATLY appreciated.This works...
SELECT [ID]
FROM yourtable T1
WHERE EXISTS (
SELECT SK, MAX(MK) AS MK
FROM yourtable T2
WHERE T1.SK=T2.SK
GROUP BY SK
HAVING T1.MK=MAX(T2.MK))|||select a.id, a.sk, a.mk from yourtable a
where a.mk in(select max(b.mk) from yourtable b
where a.sk = b.sk)|||Simpler even:
select a.sk, max(a.mk) as MK from yourtable a
group by a.sk
Can't figure out how to pass a comma delimeted value?
I am trying to pass a comma delimited value from an input box "attempting to
allow for multple entries within a string" and have the results returned from
the report. The stored procedure that I created will allow me to get what I
want but when I try to make the report make use of the sproc from within a
parameter I get nothing returned if I enter multiple values seperated by a
comma into the input box. If I put in just a single entry, it works.
Again, when I run the stored procedure within the database it works with
multiple values, when I run and test within the data view in the report it
runs and accepts multiple entries when prompted. Only when I run the report
does it fail?
Here is the sproc...
USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[usp_TestRprtArray]
@.subscriberarray varchar(500)
as
declare @.sql varchar(8000)
set @.subscriberarray = "'" + replace(@.subscriberarray,',',"','") + "'"
set @.sql = "select SUBSCRIBER, OID, CODE, OFFERCODE, EFFECTIVE_DATE,
EXPIRATION_DATE from subscription where (subscriber in (" + @.subscriberarray
+ "))"
exec (@.sql)
--
Anthony E. Castro - MCDBAIf the comma seperated value is passed from thr UI screen of a web
page.. try url encoding the page...
acmcdba68 wrote:
> I am trying to pass a comma delimited value from an input box "attempting to
> allow for multple entries within a string" and have the results returned from
> the report. The stored procedure that I created will allow me to get what I
> want but when I try to make the report make use of the sproc from within a
> parameter I get nothing returned if I enter multiple values seperated by a
> comma into the input box. If I put in just a single entry, it works.
> Again, when I run the stored procedure within the database it works with
> multiple values, when I run and test within the data view in the report it
> runs and accepts multiple entries when prompted. Only when I run the report
> does it fail?
> Here is the sproc...
> USE [mydatabase]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> CREATE PROCEDURE [dbo].[usp_TestRprtArray]
> @.subscriberarray varchar(500)
> as
> declare @.sql varchar(8000)
> set @.subscriberarray = "'" + replace(@.subscriberarray,',',"','") + "'"
> set @.sql = "select SUBSCRIBER, OID, CODE, OFFERCODE, EFFECTIVE_DATE,
> EXPIRATION_DATE from subscription where (subscriber in (" + @.subscriberarray
> + "))"
> exec (@.sql)
> --
> Anthony E. Castro - MCDBA|||try putting in a textbox in your report that shows what parameter RS is
sending your sproc. that can be helpfule in seeing just what is passing in
the back end. Are you using a Multi-value parameter box? that format is
usually '<value>,<value>,...'
What works for me is to loop through the string, parsing out each value and
inserting the results into a temp table that can then be sorted, filtered,
etc as you like.
"acmcdba68" wrote:
> I am trying to pass a comma delimited value from an input box "attempting to
> allow for multple entries within a string" and have the results returned from
> the report. The stored procedure that I created will allow me to get what I
> want but when I try to make the report make use of the sproc from within a
> parameter I get nothing returned if I enter multiple values seperated by a
> comma into the input box. If I put in just a single entry, it works.
> Again, when I run the stored procedure within the database it works with
> multiple values, when I run and test within the data view in the report it
> runs and accepts multiple entries when prompted. Only when I run the report
> does it fail?
> Here is the sproc...
> USE [mydatabase]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> CREATE PROCEDURE [dbo].[usp_TestRprtArray]
> @.subscriberarray varchar(500)
> as
> declare @.sql varchar(8000)
> set @.subscriberarray = "'" + replace(@.subscriberarray,',',"','") + "'"
> set @.sql = "select SUBSCRIBER, OID, CODE, OFFERCODE, EFFECTIVE_DATE,
> EXPIRATION_DATE from subscription where (subscriber in (" + @.subscriberarray
> + "))"
> exec (@.sql)
> --
> Anthony E. Castro - MCDBA
allow for multple entries within a string" and have the results returned from
the report. The stored procedure that I created will allow me to get what I
want but when I try to make the report make use of the sproc from within a
parameter I get nothing returned if I enter multiple values seperated by a
comma into the input box. If I put in just a single entry, it works.
Again, when I run the stored procedure within the database it works with
multiple values, when I run and test within the data view in the report it
runs and accepts multiple entries when prompted. Only when I run the report
does it fail?
Here is the sproc...
USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[usp_TestRprtArray]
@.subscriberarray varchar(500)
as
declare @.sql varchar(8000)
set @.subscriberarray = "'" + replace(@.subscriberarray,',',"','") + "'"
set @.sql = "select SUBSCRIBER, OID, CODE, OFFERCODE, EFFECTIVE_DATE,
EXPIRATION_DATE from subscription where (subscriber in (" + @.subscriberarray
+ "))"
exec (@.sql)
--
Anthony E. Castro - MCDBAIf the comma seperated value is passed from thr UI screen of a web
page.. try url encoding the page...
acmcdba68 wrote:
> I am trying to pass a comma delimited value from an input box "attempting to
> allow for multple entries within a string" and have the results returned from
> the report. The stored procedure that I created will allow me to get what I
> want but when I try to make the report make use of the sproc from within a
> parameter I get nothing returned if I enter multiple values seperated by a
> comma into the input box. If I put in just a single entry, it works.
> Again, when I run the stored procedure within the database it works with
> multiple values, when I run and test within the data view in the report it
> runs and accepts multiple entries when prompted. Only when I run the report
> does it fail?
> Here is the sproc...
> USE [mydatabase]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> CREATE PROCEDURE [dbo].[usp_TestRprtArray]
> @.subscriberarray varchar(500)
> as
> declare @.sql varchar(8000)
> set @.subscriberarray = "'" + replace(@.subscriberarray,',',"','") + "'"
> set @.sql = "select SUBSCRIBER, OID, CODE, OFFERCODE, EFFECTIVE_DATE,
> EXPIRATION_DATE from subscription where (subscriber in (" + @.subscriberarray
> + "))"
> exec (@.sql)
> --
> Anthony E. Castro - MCDBA|||try putting in a textbox in your report that shows what parameter RS is
sending your sproc. that can be helpfule in seeing just what is passing in
the back end. Are you using a Multi-value parameter box? that format is
usually '<value>,<value>,...'
What works for me is to loop through the string, parsing out each value and
inserting the results into a temp table that can then be sorted, filtered,
etc as you like.
"acmcdba68" wrote:
> I am trying to pass a comma delimited value from an input box "attempting to
> allow for multple entries within a string" and have the results returned from
> the report. The stored procedure that I created will allow me to get what I
> want but when I try to make the report make use of the sproc from within a
> parameter I get nothing returned if I enter multiple values seperated by a
> comma into the input box. If I put in just a single entry, it works.
> Again, when I run the stored procedure within the database it works with
> multiple values, when I run and test within the data view in the report it
> runs and accepts multiple entries when prompted. Only when I run the report
> does it fail?
> Here is the sproc...
> USE [mydatabase]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> CREATE PROCEDURE [dbo].[usp_TestRprtArray]
> @.subscriberarray varchar(500)
> as
> declare @.sql varchar(8000)
> set @.subscriberarray = "'" + replace(@.subscriberarray,',',"','") + "'"
> set @.sql = "select SUBSCRIBER, OID, CODE, OFFERCODE, EFFECTIVE_DATE,
> EXPIRATION_DATE from subscription where (subscriber in (" + @.subscriberarray
> + "))"
> exec (@.sql)
> --
> Anthony E. Castro - MCDBA
Tuesday, March 20, 2012
Can't enter data
I keep getting the error message "the value you entered is not consistent
with the data type or length of the column" I verified that I am entering the
correct datatype and length. Any ideas why it is not allowing me to enter
data.
Thanks...Hi,
Can you please post the table structure and the data you are trying to
insert.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:8D43843F-621B-4BCD-A855-917F592DB1D5@.microsoft.com...
> I keep getting the error message "the value you entered is not consistent
> with the data type or length of the column" I verified that I am entering
the
> correct datatype and length. Any ideas why it is not allowing me to enter
> data.
> Thanks...
with the data type or length of the column" I verified that I am entering the
correct datatype and length. Any ideas why it is not allowing me to enter
data.
Thanks...Hi,
Can you please post the table structure and the data you are trying to
insert.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:8D43843F-621B-4BCD-A855-917F592DB1D5@.microsoft.com...
> I keep getting the error message "the value you entered is not consistent
> with the data type or length of the column" I verified that I am entering
the
> correct datatype and length. Any ideas why it is not allowing me to enter
> data.
> Thanks...
Can't enter data
I keep getting the error message "the value you entered is not consistent
with the data type or length of the column" I verified that I am entering the
correct datatype and length. Any ideas why it is not allowing me to enter
data.
Thanks...
Hi,
Can you please post the table structure and the data you are trying to
insert.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:8D43843F-621B-4BCD-A855-917F592DB1D5@.microsoft.com...
> I keep getting the error message "the value you entered is not consistent
> with the data type or length of the column" I verified that I am entering
the
> correct datatype and length. Any ideas why it is not allowing me to enter
> data.
> Thanks...
with the data type or length of the column" I verified that I am entering the
correct datatype and length. Any ideas why it is not allowing me to enter
data.
Thanks...
Hi,
Can you please post the table structure and the data you are trying to
insert.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:8D43843F-621B-4BCD-A855-917F592DB1D5@.microsoft.com...
> I keep getting the error message "the value you entered is not consistent
> with the data type or length of the column" I verified that I am entering
the
> correct datatype and length. Any ideas why it is not allowing me to enter
> data.
> Thanks...
Can't enter data
I keep getting the error message "the value you entered is not consistent
with the data type or length of the column" I verified that I am entering th
e
correct datatype and length. Any ideas why it is not allowing me to enter
data.
Thanks...Hi,
Can you please post the table structure and the data you are trying to
insert.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:8D43843F-621B-4BCD-A855-917F592DB1D5@.microsoft.com...
> I keep getting the error message "the value you entered is not consistent
> with the data type or length of the column" I verified that I am entering
the
> correct datatype and length. Any ideas why it is not allowing me to enter
> data.
> Thanks...
with the data type or length of the column" I verified that I am entering th
e
correct datatype and length. Any ideas why it is not allowing me to enter
data.
Thanks...Hi,
Can you please post the table structure and the data you are trying to
insert.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:8D43843F-621B-4BCD-A855-917F592DB1D5@.microsoft.com...
> I keep getting the error message "the value you entered is not consistent
> with the data type or length of the column" I verified that I am entering
the
> correct datatype and length. Any ideas why it is not allowing me to enter
> data.
> Thanks...
Can't Edit Table
Hello, I'm an undergrad who is using SQL Server 2005 for the first time. I have a table Components where there are three columns, ID, Name and Value, where ID and name are the composite key.
I have entered some data into it from the user interface and it is entered just fine. But when I try to edit data in Value column it can't be done. This error message comes...
"No Row was updated
The Data in Row 7 was not committed
Error source: Microsoft.VisualStudio.DataTools
Error Message: The Row value(s) updated or deleted either do not make the row unique or they alter multiple rows(3 rows).
Correct the errors and retry or press Esc to cancel change(s)."
The way I get it, does it say that it is being bound to VS.NET environment? I use it in my VB . NET programme. But I close every time that I open a connection. I'm positive about that. Then I tried, restarting the application, nothing happened! And then I closed Visual Studio .Net and even then nothing happened. And I'm getting very frustrated....
The funny thing is that, when I write a query using the SQL Pane of the SQL Server 2005 then, everything works perfectly. And I don't understand what this is...
I really appreciate if anyone can shed some light into me.
Thank You,
Prasad.
hi,
It seems that you are violation a primary key constraints,
If your using visual studio.net 2003 and later
a schema of the table is stored in the dataset for 2003
or other sqlclient objects of vsnet 2005.
A primary key constraints can be implemeted on these data layers(dataset.. and the likes)
And on the database.
what you can do.
1. Refresh the dataset schema (datatables.. etc) and other ado.net implementation
2. verify that your data is not violting pk on the database tables
hope it helps
|||
I am also getting the same message. I have been using 2000 for some time and this error does not make sense.
My table has no primary key or any relationship to another table. I created the table in the SSMSE and added records using ADO in VB6. I can not delete records and get the following message: "A Problem occurred attempting to delete row X. Error Source: Microsoft.VisualStudio.DataTools. Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(Y rows). Correct the errors and attempt to delete the row again or press ESC to cancel the changes(s)."
SQL will give me a very similar message if I try to edit a field in any of these rows.
Some rows SQL will allow me to edit or delete.
Any Ideas?
Peter
|||Apparently this only effects SSMSE as a DELETE query issued from inside VB6 using an ODBC connection works flawlessly. This is the same DELETE query I issued using the Query designer in SSMSE.
Seems like a bug in SSMSE.
Peter
|||Suppose you have two identical rows in your table, where column X has the value 7. The query DELETE FROM yourTable WHERE X = 7 will delete both of these rows, and there will be no error. Now suppose you open the table in SSMS and try to delete just one of these rows (or edit just one of them). You will get an error, because there no DELETE (or update) statement is possible to modify just one of two identical rows.Even without identical rows you may get this error when there is no primary key on the table. The position of a row in the visual editor is not an intrinsic property of the data in the table, and the only modifications you can make to a table are those that can be written as queries with conditions that depend on the column values in the table. A task like "delete the third row" does not correspond to a delete query unless some set of column values unambiguously describes the third row (in other words, unless there is a key). The row number as the table appears in the visual editor is not a table column, so it can't be the sole property used to identify the row to be deleted.
Steve Kass
Drew University|||
I did write a query:
DELETE FROM SalesData WHERE Sequence = 5;
This should have deleted any rows where the Sequence Column is equal to 5. This query did not work in SSMSE when the rows where the Sequence was equal to 5 are identical. My understanding is any rows with a Sequence of 5 should be deleted. This works from VB6 and will not work in the query designer under SSMSE.
Maybe there is some interpretation going on when I select the 7th row in the SSMSE GUI and it tries to delete the rows one at a time and cannot do this since the rows are not unique. The SSMSE query designer should work the same as a Query from VB6 shouldn't it?
SalesData:
Seq Name ID
5 Tom A
5 Tom A
6 Bob B
6 Bob C
These do not work from within SSMSE using wither the GUI or the Query designer and DO work from VB6:
DELETE FROM SalesData WHERE Seq = 5;
DELETE FROM SalesData WHERE Seq = 5 AND ID = 'A';
DELETE FROM SalesData;
These work as expected:
DELETE FROM SalesData WHERE Seq = 6;
DELETE FROM SalesData WHERE Seq = 6 AND ID = 'B';
Peter House
|||Hi there
got the same problem. Did you manage to solve it?
regards
Michael J
|||Michael,
I did solve the problem - sort of. Apparently this is an issue with the SQL Management Studio environment. I could do the same exact query from within a VB program or MS Access Query and they would work correctly while the query would not work in the Management Studio.
I have not experienced this problem in a while and use the Management Studio Interface a lot with both the Express and Standard versions.
Peter
|||Peter
I first experienced it now and I have had ss05 for 5 month and used the feature a lot. But as you say. One can do without it and hope that it resolves.....
Thanks for your reply!
Michael
Can't Edit Table
Hello, I'm an undergrad who is using SQL Server 2005 for the first time. I have a table Components where there are three columns, ID, Name and Value, where ID and name are the composite key.
I have entered some data into it from the user interface and it is entered just fine. But when I try to edit data in Value column it can't be done. This error message comes...
"No Row was updated
The Data in Row 7 was not committed
Error source: Microsoft.VisualStudio.DataTools
Error Message: The Row value(s) updated or deleted either do not make the row unique or they alter multiple rows(3 rows).
Correct the errors and retry or press Esc to cancel change(s)."
The way I get it, does it say that it is being bound to VS.NET environment? I use it in my VB . NET programme. But I close every time that I open a connection. I'm positive about that. Then I tried, restarting the application, nothing happened! And then I closed Visual Studio .Net and even then nothing happened. And I'm getting very frustrated....
The funny thing is that, when I write a query using the SQL Pane of the SQL Server 2005 then, everything works perfectly. And I don't understand what this is...
I really appreciate if anyone can shed some light into me.
Thank You,
Prasad.
hi,
It seems that you are violation a primary key constraints,
If your using visual studio.net 2003 and later
a schema of the table is stored in the dataset for 2003
or other sqlclient objects of vsnet 2005.
A primary key constraints can be implemeted on these data layers(dataset.. and the likes)
And on the database.
what you can do.
1. Refresh the dataset schema (datatables.. etc) and other ado.net implementation
2. verify that your data is not violting pk on the database tables
hope it helps
|||
I am also getting the same message. I have been using 2000 for some time and this error does not make sense.
My table has no primary key or any relationship to another table. I created the table in the SSMSE and added records using ADO in VB6. I can not delete records and get the following message: "A Problem occurred attempting to delete row X. Error Source: Microsoft.VisualStudio.DataTools. Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(Y rows). Correct the errors and attempt to delete the row again or press ESC to cancel the changes(s)."
SQL will give me a very similar message if I try to edit a field in any of these rows.
Some rows SQL will allow me to edit or delete.
Any Ideas?
Peter
|||Apparently this only effects SSMSE as a DELETE query issued from inside VB6 using an ODBC connection works flawlessly. This is the same DELETE query I issued using the Query designer in SSMSE.
Seems like a bug in SSMSE.
Peter
|||Suppose you have two identical rows in your table, where column X has the value 7. The query DELETE FROM yourTable WHERE X = 7 will delete both of these rows, and there will be no error. Now suppose you open the table in SSMS and try to delete just one of these rows (or edit just one of them). You will get an error, because there no DELETE (or update) statement is possible to modify just one of two identical rows.Even without identical rows you may get this error when there is no primary key on the table. The position of a row in the visual editor is not an intrinsic property of the data in the table, and the only modifications you can make to a table are those that can be written as queries with conditions that depend on the column values in the table. A task like "delete the third row" does not correspond to a delete query unless some set of column values unambiguously describes the third row (in other words, unless there is a key). The row number as the table appears in the visual editor is not a table column, so it can't be the sole property used to identify the row to be deleted.
Steve Kass
Drew University
|||
I did write a query:
DELETE FROM SalesData WHERE Sequence = 5;
This should have deleted any rows where the Sequence Column is equal to 5. This query did not work in SSMSE when the rows where the Sequence was equal to 5 are identical. My understanding is any rows with a Sequence of 5 should be deleted. This works from VB6 and will not work in the query designer under SSMSE.
Maybe there is some interpretation going on when I select the 7th row in the SSMSE GUI and it tries to delete the rows one at a time and cannot do this since the rows are not unique. The SSMSE query designer should work the same as a Query from VB6 shouldn't it?
SalesData:
Seq Name ID
5 Tom A
5 Tom A
6 Bob B
6 Bob C
These do not work from within SSMSE using wither the GUI or the Query designer and DO work from VB6:
DELETE FROM SalesData WHERE Seq = 5;
DELETE FROM SalesData WHERE Seq = 5 AND ID = 'A';
DELETE FROM SalesData;
These work as expected:
DELETE FROM SalesData WHERE Seq = 6;
DELETE FROM SalesData WHERE Seq = 6 AND ID = 'B';
Peter House
|||Hi there
got the same problem. Did you manage to solve it?
regards
Michael J
|||Michael,
I did solve the problem - sort of. Apparently this is an issue with the SQL Management Studio environment. I could do the same exact query from within a VB program or MS Access Query and they would work correctly while the query would not work in the Management Studio.
I have not experienced this problem in a while and use the Management Studio Interface a lot with both the Express and Standard versions.
Peter
|||Peter
I first experienced it now and I have had ss05 for 5 month and used the feature a lot. But as you say. One can do without it and hope that it resolves.....
Thanks for your reply!
Michael
Subscribe to:
Posts (Atom)