|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Modifying Data Directly through Studio ManagerHELP!!!! I've been using SQL for years, and I support a product built on top
of SQL Server. Many of my customers are starting to upgrade to SQL 2005, but the ones that are not "SQL inclined" are having problems modifying data. They are used to having simple tables that can have rows modified by simply opening Enterprise Manager (in SQL 7 and 2000), returning the appropriate rows, entering the new data, and saving the record. From what I've seen in 2005, you can't do that. I understand that Query can be written, but this is for people that are much better with an "Access" environment, if you will. I've seen a lot of posts on this on other sites, but no solutions. Fellow MCSE's, I'm calling on you to help!!! Thank you, Isaac Shloss, MCSE I think that what you propose, allowing end users, using SSMS, to alter data
using an administrative tool, is absolutely idiotic!! You are living very dangerously... That said, in SSMS, in the Object Explorer, expand down to the table, right click on the table and select [Open Table]. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Isaac" <Is***@discussions.microsoft.com> wrote in message news:4989F805-11F9-4669-965E-F3B9610D6A29@microsoft.com... > HELP!!!! I've been using SQL for years, and I support a product built on > top > of SQL Server. Many of my customers are starting to upgrade to SQL 2005, > but > the ones that are not "SQL inclined" are having problems modifying data. > They are used to having simple tables that can have rows modified by > simply > opening Enterprise Manager (in SQL 7 and 2000), returning the appropriate > rows, entering the new data, and saving the record. From what I've seen > in > 2005, you can't do that. > > I understand that Query can be written, but this is for people that are > much > better with an "Access" environment, if you will. I've seen a lot of > posts > on this on other sites, but no solutions. Fellow MCSE's, I'm calling on > you > to help!!! > > Thank you, > > Isaac Shloss, MCSE Not idiotic, necessary. We won't refuse a sale because a customer is not
large enough to afford a DBA. A basic IT person could handle the Enterprise Manager, and our customers are entitled to do whatever they wish with their data. I see the "Open Table" option. Have you tried to use it? I make a change, click the pen to the left, and I get a red exclamation mark. If I execute the query, the change is lost. If I hit enter (instead of clicking the pen), it errors out stating that the data has changed since the last refresh. I've seen people comment on turned on the query pane, but all that does is shows me the select statement. If my customers could do TSQL, I never would have made this post. Show quote "Isaac" wrote: > HELP!!!! I've been using SQL for years, and I support a product built on top > of SQL Server. Many of my customers are starting to upgrade to SQL 2005, but > the ones that are not "SQL inclined" are having problems modifying data. > They are used to having simple tables that can have rows modified by simply > opening Enterprise Manager (in SQL 7 and 2000), returning the appropriate > rows, entering the new data, and saving the record. From what I've seen in > 2005, you can't do that. > > I understand that Query can be written, but this is for people that are much > better with an "Access" environment, if you will. I've seen a lot of posts > on this on other sites, but no solutions. Fellow MCSE's, I'm calling on you > to help!!! > > Thank you, > > Isaac Shloss, MCSE Because business decision choose to allow this doesn't mean that it isn't an
troublesome decision, and in my opinion, one that is truly lunacy. It seems like a 'Sales Add-on' (read: additional revenue) to provide an appropriate 'tool' to allow your customers to manage their data. Yes, I've used it for editing. Edit a field, click or tab to the next field. If an error occurs, correct the nature of the error (datalength, datatype, etc.) -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Isaac" <Is***@discussions.microsoft.com> wrote in message news:385F1137-8205-4600-96C5-FE885D779512@microsoft.com... > Not idiotic, necessary. We won't refuse a sale because a customer is not > large enough to afford a DBA. A basic IT person could handle the > Enterprise > Manager, and our customers are entitled to do whatever they wish with > their > data. > > I see the "Open Table" option. Have you tried to use it? I make a change, > click the pen to the left, and I get a red exclamation mark. If I execute > the query, the change is lost. If I hit enter (instead of clicking the > pen), > it errors out stating that the data has changed since the last refresh. > > I've seen people comment on turned on the query pane, but all that does is > shows me the select statement. If my customers could do TSQL, I never > would > have made this post. > > "Isaac" wrote: > >> HELP!!!! I've been using SQL for years, and I support a product built on >> top >> of SQL Server. Many of my customers are starting to upgrade to SQL 2005, >> but >> the ones that are not "SQL inclined" are having problems modifying data. >> They are used to having simple tables that can have rows modified by >> simply >> opening Enterprise Manager (in SQL 7 and 2000), returning the appropriate >> rows, entering the new data, and saving the record. From what I've seen >> in >> 2005, you can't do that. >> >> I understand that Query can be written, but this is for people that are >> much >> better with an "Access" environment, if you will. I've seen a lot of >> posts >> on this on other sites, but no solutions. Fellow MCSE's, I'm calling on >> you >> to help!!! >> >> Thank you, >> >> Isaac Shloss, MCSE Since this isn't the forum for business philosophy, I'll let that part go.
My real concern is the technology side of this. That being said, I wish it was that easy, and I thought that is how it is supposed to work. However, if I do as you say ("Edit a field, click or tab to the next field."), I get an error. It's not one about data integrety per say, but more a misnomer that "The updated row has changed or been deleted since data was last retrieved." I'm the only one in the table, or in the server for that matter. That leads me to believe that I'm either missing something or, more likely, there is a bug in the interface. I am running SP1 with all hot fixes made available through Microsoft Update. Show quote "Arnie Rowland" wrote: > Because business decision choose to allow this doesn't mean that it isn't an > troublesome decision, and in my opinion, one that is truly lunacy. It seems > like a 'Sales Add-on' (read: additional revenue) to provide an appropriate > 'tool' to allow your customers to manage their data. > > Yes, I've used it for editing. Edit a field, click or tab to the next field. > If an error occurs, correct the nature of the error (datalength, datatype, > etc.) > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Isaac" <Is***@discussions.microsoft.com> wrote in message > news:385F1137-8205-4600-96C5-FE885D779512@microsoft.com... > > Not idiotic, necessary. We won't refuse a sale because a customer is not > > large enough to afford a DBA. A basic IT person could handle the > > Enterprise > > Manager, and our customers are entitled to do whatever they wish with > > their > > data. > > > > I see the "Open Table" option. Have you tried to use it? I make a change, > > click the pen to the left, and I get a red exclamation mark. If I execute > > the query, the change is lost. If I hit enter (instead of clicking the > > pen), > > it errors out stating that the data has changed since the last refresh. > > > > I've seen people comment on turned on the query pane, but all that does is > > shows me the select statement. If my customers could do TSQL, I never > > would > > have made this post. > > > > "Isaac" wrote: > > > >> HELP!!!! I've been using SQL for years, and I support a product built on > >> top > >> of SQL Server. Many of my customers are starting to upgrade to SQL 2005, > >> but > >> the ones that are not "SQL inclined" are having problems modifying data. > >> They are used to having simple tables that can have rows modified by > >> simply > >> opening Enterprise Manager (in SQL 7 and 2000), returning the appropriate > >> rows, entering the new data, and saving the record. From what I've seen > >> in > >> 2005, you can't do that. > >> > >> I understand that Query can be written, but this is for people that are > >> much > >> better with an "Access" environment, if you will. I've seen a lot of > >> posts > >> on this on other sites, but no solutions. Fellow MCSE's, I'm calling on > >> you > >> to help!!! > >> > >> Thank you, > >> > >> Isaac Shloss, MCSE > > > You're right, I should have dropped it too.
I'm using version 9.00.2047 (SP1). I have no difficulty in editing and saving edits to table data. Let's explore some other reported issues with the same message. Is there a text datatype field in the table? Are you using SSMS to remotely manage a server? -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Isaac" <Is***@discussions.microsoft.com> wrote in message news:6F579129-E3F8-4E89-A481-A74CDEA17102@microsoft.com... > Since this isn't the forum for business philosophy, I'll let that part go. > My real concern is the technology side of this. > > That being said, I wish it was that easy, and I thought that is how it is > supposed to work. However, if I do as you say ("Edit a field, click or > tab > to the next field."), I get an error. It's not one about data integrety > per > say, but more a misnomer that "The updated row has changed or been deleted > since data was last retrieved." > > I'm the only one in the table, or in the server for that matter. That > leads > me to believe that I'm either missing something or, more likely, there is > a > bug in the interface. I am running SP1 with all hot fixes made available > through Microsoft Update. > > "Arnie Rowland" wrote: > >> Because business decision choose to allow this doesn't mean that it isn't >> an >> troublesome decision, and in my opinion, one that is truly lunacy. It >> seems >> like a 'Sales Add-on' (read: additional revenue) to provide an >> appropriate >> 'tool' to allow your customers to manage their data. >> >> Yes, I've used it for editing. Edit a field, click or tab to the next >> field. >> If an error occurs, correct the nature of the error (datalength, >> datatype, >> etc.) >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "Isaac" <Is***@discussions.microsoft.com> wrote in message >> news:385F1137-8205-4600-96C5-FE885D779512@microsoft.com... >> > Not idiotic, necessary. We won't refuse a sale because a customer is >> > not >> > large enough to afford a DBA. A basic IT person could handle the >> > Enterprise >> > Manager, and our customers are entitled to do whatever they wish with >> > their >> > data. >> > >> > I see the "Open Table" option. Have you tried to use it? I make a >> > change, >> > click the pen to the left, and I get a red exclamation mark. If I >> > execute >> > the query, the change is lost. If I hit enter (instead of clicking the >> > pen), >> > it errors out stating that the data has changed since the last refresh. >> > >> > I've seen people comment on turned on the query pane, but all that does >> > is >> > shows me the select statement. If my customers could do TSQL, I never >> > would >> > have made this post. >> > >> > "Isaac" wrote: >> > >> >> HELP!!!! I've been using SQL for years, and I support a product built >> >> on >> >> top >> >> of SQL Server. Many of my customers are starting to upgrade to SQL >> >> 2005, >> >> but >> >> the ones that are not "SQL inclined" are having problems modifying >> >> data. >> >> They are used to having simple tables that can have rows modified by >> >> simply >> >> opening Enterprise Manager (in SQL 7 and 2000), returning the >> >> appropriate >> >> rows, entering the new data, and saving the record. From what I've >> >> seen >> >> in >> >> 2005, you can't do that. >> >> >> >> I understand that Query can be written, but this is for people that >> >> are >> >> much >> >> better with an "Access" environment, if you will. I've seen a lot of >> >> posts >> >> on this on other sites, but no solutions. Fellow MCSE's, I'm calling >> >> on >> >> you >> >> to help!!! >> >> >> >> Thank you, >> >> >> >> Isaac Shloss, MCSE >> >> >> Not text, but varchar. Enterprise manager (in SQL 2000) never had a problem
modifying varchar, int, decimal, float, etc. Yes, I am using SSMS to remotely manage the server. Also, just to rule out security, I've tested this while being logged in as "sa". No difference. Show quote "Arnie Rowland" wrote: > You're right, I should have dropped it too. > > I'm using version 9.00.2047 (SP1). I have no difficulty in editing and > saving edits to table data. > > Let's explore some other reported issues with the same message. > > Is there a text datatype field in the table? > > Are you using SSMS to remotely manage a server? > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Isaac" <Is***@discussions.microsoft.com> wrote in message > news:6F579129-E3F8-4E89-A481-A74CDEA17102@microsoft.com... > > Since this isn't the forum for business philosophy, I'll let that part go. > > My real concern is the technology side of this. > > > > That being said, I wish it was that easy, and I thought that is how it is > > supposed to work. However, if I do as you say ("Edit a field, click or > > tab > > to the next field."), I get an error. It's not one about data integrety > > per > > say, but more a misnomer that "The updated row has changed or been deleted > > since data was last retrieved." > > > > I'm the only one in the table, or in the server for that matter. That > > leads > > me to believe that I'm either missing something or, more likely, there is > > a > > bug in the interface. I am running SP1 with all hot fixes made available > > through Microsoft Update. > > > > "Arnie Rowland" wrote: > > > >> Because business decision choose to allow this doesn't mean that it isn't > >> an > >> troublesome decision, and in my opinion, one that is truly lunacy. It > >> seems > >> like a 'Sales Add-on' (read: additional revenue) to provide an > >> appropriate > >> 'tool' to allow your customers to manage their data. > >> > >> Yes, I've used it for editing. Edit a field, click or tab to the next > >> field. > >> If an error occurs, correct the nature of the error (datalength, > >> datatype, > >> etc.) > >> > >> -- > >> Arnie Rowland, Ph.D. > >> Westwood Consulting, Inc > >> > >> Most good judgment comes from experience. > >> Most experience comes from bad judgment. > >> - Anonymous > >> > >> > >> "Isaac" <Is***@discussions.microsoft.com> wrote in message > >> news:385F1137-8205-4600-96C5-FE885D779512@microsoft.com... > >> > Not idiotic, necessary. We won't refuse a sale because a customer is > >> > not > >> > large enough to afford a DBA. A basic IT person could handle the > >> > Enterprise > >> > Manager, and our customers are entitled to do whatever they wish with > >> > their > >> > data. > >> > > >> > I see the "Open Table" option. Have you tried to use it? I make a > >> > change, > >> > click the pen to the left, and I get a red exclamation mark. If I > >> > execute > >> > the query, the change is lost. If I hit enter (instead of clicking the > >> > pen), > >> > it errors out stating that the data has changed since the last refresh. > >> > > >> > I've seen people comment on turned on the query pane, but all that does > >> > is > >> > shows me the select statement. If my customers could do TSQL, I never > >> > would > >> > have made this post. > >> > > >> > "Isaac" wrote: > >> > > >> >> HELP!!!! I've been using SQL for years, and I support a product built > >> >> on > >> >> top > >> >> of SQL Server. Many of my customers are starting to upgrade to SQL > >> >> 2005, > >> >> but > >> >> the ones that are not "SQL inclined" are having problems modifying > >> >> data. > >> >> They are used to having simple tables that can have rows modified by > >> >> simply > >> >> opening Enterprise Manager (in SQL 7 and 2000), returning the > >> >> appropriate > >> >> rows, entering the new data, and saving the record. From what I've > >> >> seen > >> >> in > >> >> 2005, you can't do that. > >> >> > >> >> I understand that Query can be written, but this is for people that > >> >> are > >> >> much > >> >> better with an "Access" environment, if you will. I've seen a lot of > >> >> posts > >> >> on this on other sites, but no solutions. Fellow MCSE's, I'm calling > >> >> on > >> >> you > >> >> to help!!! > >> >> > >> >> Thank you, > >> >> > >> >> Isaac Shloss, MCSE > >> > >> > >> > > > Isaac (Is***@discussions.microsoft.com) writes:
> Since this isn't the forum for business philosophy, I'll let that part go. Use SQL Server Profiler, to see what Mgmt Studio actually emits to > My real concern is the technology side of this. > > That being said, I wish it was that easy, and I thought that is how it > is supposed to work. However, if I do as you say ("Edit a field, click > or tab to the next field."), I get an error. It's not one about data > integrety per say, but more a misnomer that "The updated row has changed > or been deleted since data was last retrieved." > > I'm the only one in the table, or in the server for that matter. That > leads me to believe that I'm either missing something or, more likely, > there is a bug in the interface. I am running SP1 with all hot fixes > made available through Microsoft Update. SQL Server. If that does not help, please post the CREATE TABLE statement for your table, and some sample data that causes the problem. The sample data should be in INSERT statements. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I just built a simple, 1 column table. The field name was called "Stuff",
and I made it a varchar(50). I then did an open table, typed "hello" into the field that said "null". I got the error. I traced it with Profiler, but I got nothing useful. Show quote "Erland Sommarskog" wrote: > Isaac (Is***@discussions.microsoft.com) writes: > > Since this isn't the forum for business philosophy, I'll let that part go. > > My real concern is the technology side of this. > > > > That being said, I wish it was that easy, and I thought that is how it > > is supposed to work. However, if I do as you say ("Edit a field, click > > or tab to the next field."), I get an error. It's not one about data > > integrety per say, but more a misnomer that "The updated row has changed > > or been deleted since data was last retrieved." > > > > I'm the only one in the table, or in the server for that matter. That > > leads me to believe that I'm either missing something or, more likely, > > there is a bug in the interface. I am running SP1 with all hot fixes > > made available through Microsoft Update. > > Use SQL Server Profiler, to see what Mgmt Studio actually emits to > SQL Server. > > If that does not help, please post the CREATE TABLE statement for > your table, and some sample data that causes the problem. The sample > data should be in INSERT statements. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Isaac (Is***@discussions.microsoft.com) writes:
> I just built a simple, 1 column table. The field name was called "Stuff", Maybe it wasn't useful to you, but it might have been useful to me.> and I made it a varchar(50). I then did an open table, typed "hello" into > the field that said "null". I got the error. > > I traced it with Profiler, but I got nothing useful. Anyway, I was not able to reproduce the problem. I created a table, create table Stuff(Stuff varchar(50) NULL) did Open Table on it, and I was able to insert a value, and even two. I could even update them. However, if I set both cells to NULL, and tried to update, I got an error, but that is to be expected, as there is no key in this table. And, really, tables without primary keys are not very useful, least of all from things like Open Table. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland,
I'm getting the same results that Isaac is getting. USE [Budget_CFY_Maint] GO /****** Object: Table [dbo].[_stuff] Script Date: 08/18/2006 08:17:05 ******/ CREATE TABLE [dbo].[_stuff]( [id] [int] IDENTITY(1,1) NOT NULL, [stuff] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK__stuff] PRIMARY KEY CLUSTERED ( [id] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO this is what Profiler is seeing /*------------------------ SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION exec sp_executesql N'INSERT INTO _stuff(stuff) VALUES (@stuff)', N'@stuff nvarchar(5)', @stuff = N'chuck' IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION ------------------------*/ I've gone into options and set the isolation level to repeatable read and still see this iso level in profiler. SSMS also won't let me change the data in a row either. Your thoughts are appreciated Chuck Show quote "Erland Sommarskog" wrote: > Isaac (Is***@discussions.microsoft.com) writes: > > I just built a simple, 1 column table. The field name was called "Stuff", > > and I made it a varchar(50). I then did an open table, typed "hello" into > > the field that said "null". I got the error. > > > > I traced it with Profiler, but I got nothing useful. > > Maybe it wasn't useful to you, but it might have been useful to me. > > Anyway, I was not able to reproduce the problem. I created a table, > > create table Stuff(Stuff varchar(50) NULL) > > did Open Table on it, and I was able to insert a value, and even two. > I could even update them. However, if I set both cells to NULL, and > tried to update, I got an error, but that is to be expected, as there > is no key in this table. > > And, really, tables without primary keys are not very useful, least of > all from things like Open Table. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Erland,
I am having the same problem. From what I can determine it only happens when trying to change data on a 2000 instance. No problems when the database has been created on a 2005 instance. Is there something we are missing or something we need to change in order to make changes to an SQL 2000 table. ChuckCraig wrote: Show quote > Erland, > I'm getting the same results that Isaac is getting. > USE [Budget_CFY_Maint] > GO > /****** Object: Table [dbo].[_stuff] Script Date: 08/18/2006 08:17:05 > ******/ > CREATE TABLE [dbo].[_stuff]( > [id] [int] IDENTITY(1,1) NOT NULL, > [stuff] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, > CONSTRAINT [PK__stuff] PRIMARY KEY CLUSTERED > ( > [id] ASC > ) ON [PRIMARY] > ) ON [PRIMARY] > > GO > > this is what Profiler is seeing > /*------------------------ > SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION > exec sp_executesql N'INSERT INTO _stuff(stuff) VALUES (@stuff)', N'@stuff > nvarchar(5)', @stuff = N'chuck' > IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION > ------------------------*/ > > I've gone into options and set the isolation level to repeatable read and > still see this iso level in profiler. > > SSMS also won't let me change the data in a row either. > > Your thoughts are appreciated > Chuck > > "Erland Sommarskog" wrote: > > > Isaac (Is***@discussions.microsoft.com) writes: > > > I just built a simple, 1 column table. The field name was called "Stuff", > > > and I made it a varchar(50). I then did an open table, typed "hello" into > > > the field that said "null". I got the error. > > > > > > I traced it with Profiler, but I got nothing useful. > > > > Maybe it wasn't useful to you, but it might have been useful to me. > > > > Anyway, I was not able to reproduce the problem. I created a table, > > > > create table Stuff(Stuff varchar(50) NULL) > > > > did Open Table on it, and I was able to insert a value, and even two. > > I could even update them. However, if I set both cells to NULL, and > > tried to update, I got an error, but that is to be expected, as there > > is no key in this table. > > > > And, really, tables without primary keys are not very useful, least of > > all from things like Open Table. > > > > -- > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > Books Online for SQL Server 2005 at > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > > Books Online for SQL Server 2000 at > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > I feel bad saying this, but I'm glad it's not just me!
I tried your theory about it being related to SQL 2000, because I've had this problem both when managing SQL 2000 and 2005. I thought maybe it had to do with a database that was originally created in 2000. I created a test database in SQL2005, gave it a test table, but I still couldn't do it. Show quote "willuw***@msn.com" wrote: > Erland, > I am having the same problem. From what I can determine it only > happens when trying to change data on a 2000 instance. No problems > when the database has been created on a 2005 instance. Is there > something we are missing or something we need to change in order to > make > changes to an SQL 2000 table. > > > ChuckCraig wrote: > > Erland, > > I'm getting the same results that Isaac is getting. > > USE [Budget_CFY_Maint] > > GO > > /****** Object: Table [dbo].[_stuff] Script Date: 08/18/2006 08:17:05 > > ******/ > > CREATE TABLE [dbo].[_stuff]( > > [id] [int] IDENTITY(1,1) NOT NULL, > > [stuff] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, > > CONSTRAINT [PK__stuff] PRIMARY KEY CLUSTERED > > ( > > [id] ASC > > ) ON [PRIMARY] > > ) ON [PRIMARY] > > > > GO > > > > this is what Profiler is seeing > > /*------------------------ > > SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION > > exec sp_executesql N'INSERT INTO _stuff(stuff) VALUES (@stuff)', N'@stuff > > nvarchar(5)', @stuff = N'chuck' > > IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION > > ------------------------*/ > > > > I've gone into options and set the isolation level to repeatable read and > > still see this iso level in profiler. > > > > SSMS also won't let me change the data in a row either. > > > > Your thoughts are appreciated > > Chuck > > > > "Erland Sommarskog" wrote: > > > > > Isaac (Is***@discussions.microsoft.com) writes: > > > > I just built a simple, 1 column table. The field name was called "Stuff", > > > > and I made it a varchar(50). I then did an open table, typed "hello" into > > > > the field that said "null". I got the error. > > > > > > > > I traced it with Profiler, but I got nothing useful. > > > > > > Maybe it wasn't useful to you, but it might have been useful to me. > > > > > > Anyway, I was not able to reproduce the problem. I created a table, > > > > > > create table Stuff(Stuff varchar(50) NULL) > > > > > > did Open Table on it, and I was able to insert a value, and even two. > > > I could even update them. However, if I set both cells to NULL, and > > > tried to update, I got an error, but that is to be expected, as there > > > is no key in this table. > > > > > > And, really, tables without primary keys are not very useful, least of > > > all from things like Open Table. > > > > > > -- > > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > > > Books Online for SQL Server 2005 at > > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > > > Books Online for SQL Server 2000 at > > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > > > > ChuckCraig (ChuckCr***@discussions.microsoft.com) writes:
Show quote > Erland, Could you add all events under the Errors and Warnings category? When I> I'm getting the same results that Isaac is getting. > USE [Budget_CFY_Maint] > GO > /****** Object: Table [dbo].[_stuff] Script Date: 08/18/2006 08:17:05 > ******/ > CREATE TABLE [dbo].[_stuff]( > [id] [int] IDENTITY(1,1) NOT NULL, > [stuff] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, > CONSTRAINT [PK__stuff] PRIMARY KEY CLUSTERED > ( > [id] ASC > ) ON [PRIMARY] > ) ON [PRIMARY] > > GO > > this is what Profiler is seeing > /*------------------------ > SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION > exec sp_executesql N'INSERT INTO _stuff(stuff) VALUES (@stuff)', N'@stuff > nvarchar(5)', @stuff = N'chuck' > IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION > ------------------------*/ run in Profiler I see the SET TRANSACTION ISOLATION LEVEL and the exec sp_executesql, but then there is an COMMIT TRANSACTION. And you have installed SP1 of SQL 2005, haven't you? The same question goes to Isaac and willuw***@msn.com as well. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I have SP1 installed.
Show quote "Erland Sommarskog" wrote: > ChuckCraig (ChuckCr***@discussions.microsoft.com) writes: > > Erland, > > I'm getting the same results that Isaac is getting. > > USE [Budget_CFY_Maint] > > GO > > /****** Object: Table [dbo].[_stuff] Script Date: 08/18/2006 08:17:05 > > ******/ > > CREATE TABLE [dbo].[_stuff]( > > [id] [int] IDENTITY(1,1) NOT NULL, > > [stuff] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, > > CONSTRAINT [PK__stuff] PRIMARY KEY CLUSTERED > > ( > > [id] ASC > > ) ON [PRIMARY] > > ) ON [PRIMARY] > > > > GO > > > > this is what Profiler is seeing > > /*------------------------ > > SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION > > exec sp_executesql N'INSERT INTO _stuff(stuff) VALUES (@stuff)', N'@stuff > > nvarchar(5)', @stuff = N'chuck' > > IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION > > ------------------------*/ > > Could you add all events under the Errors and Warnings category? When I > run in Profiler I see the SET TRANSACTION ISOLATION LEVEL and the > exec sp_executesql, but then there is an COMMIT TRANSACTION. > > And you have installed SP1 of SQL 2005, haven't you? The same question > goes to Isaac and willuw***@msn.com as well. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Yes, I too have SP1 installed
Isaac wrote: Show quote > I have SP1 installed. > > "Erland Sommarskog" wrote: > > > ChuckCraig (ChuckCr***@discussions.microsoft.com) writes: > > > Erland, > > > I'm getting the same results that Isaac is getting. > > > USE [Budget_CFY_Maint] > > > GO > > > /****** Object: Table [dbo].[_stuff] Script Date: 08/18/2006 08:17:05 > > > ******/ > > > CREATE TABLE [dbo].[_stuff]( > > > [id] [int] IDENTITY(1,1) NOT NULL, > > > [stuff] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, > > > CONSTRAINT [PK__stuff] PRIMARY KEY CLUSTERED > > > ( > > > [id] ASC > > > ) ON [PRIMARY] > > > ) ON [PRIMARY] > > > > > > GO > > > > > > this is what Profiler is seeing > > > /*------------------------ > > > SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION > > > exec sp_executesql N'INSERT INTO _stuff(stuff) VALUES (@stuff)', N'@stuff > > > nvarchar(5)', @stuff = N'chuck' > > > IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION > > > ------------------------*/ > > > > Could you add all events under the Errors and Warnings category? When I > > run in Profiler I see the SET TRANSACTION ISOLATION LEVEL and the > > exec sp_executesql, but then there is an COMMIT TRANSACTION. > > > > And you have installed SP1 of SQL 2005, haven't you? The same question > > goes to Isaac and willuw***@msn.com as well. > > > > > > -- > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > Books Online for SQL Server 2005 at > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > > Books Online for SQL Server 2000 at > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > On Mon, 7 Aug 2006 18:12:02 -0700, Isaac
<Is***@discussions.microsoft.com> wrote: >That being said, I wish it was that easy, and I thought that is how it is I am also having this problem! Has anyone come up with a solution?>supposed to work. However, if I do as you say ("Edit a field, click or tab >to the next field."), I get an error. It's not one about data integrety per >say, but more a misnomer that "The updated row has changed or been deleted >since data was last retrieved." > >I'm the only one in the table, or in the server for that matter. That leads >me to believe that I'm either missing something or, more likely, there is a >bug in the interface. I am running SP1 with all hot fixes made available >through Microsoft Update. Sorry, but no. I've seen lots of people on the internet complain about this,
but no one seems to have an answer. Maybe it'll work in SP2? Show quote "Bob Shruncle" wrote: > On Mon, 7 Aug 2006 18:12:02 -0700, Isaac > <Is***@discussions.microsoft.com> wrote: > > >That being said, I wish it was that easy, and I thought that is how it is > >supposed to work. However, if I do as you say ("Edit a field, click or tab > >to the next field."), I get an error. It's not one about data integrety per > >say, but more a misnomer that "The updated row has changed or been deleted > >since data was last retrieved." > > > >I'm the only one in the table, or in the server for that matter. That leads > >me to believe that I'm either missing something or, more likely, there is a > >bug in the interface. I am running SP1 with all hot fixes made available > >through Microsoft Update. > > I am also having this problem! Has anyone come up with a solution? > > Bob Shruncle (b**@shruncle.com) writes:
Show quote > On Mon, 7 Aug 2006 18:12:02 -0700, Isaac I've asked for instructions to reproduce the problem, but what I've been><Is***@discussions.microsoft.com> wrote: > >>That being said, I wish it was that easy, and I thought that is how it >>is supposed to work. However, if I do as you say ("Edit a field, click >>or tab to the next field."), I get an error. It's not one about data >>integrety per say, but more a misnomer that "The updated row has changed >>or been deleted since data was last retrieved." >> >>I'm the only one in the table, or in the server for that matter. That >>leads me to believe that I'm either missing something or, more likely, >>there is a bug in the interface. I am running SP1 with all hot fixes >>made available through Microsoft Update. > > I am also having this problem! Has anyone come up with a solution? told so far, I have not been successful. And without a repro scenario, it's difficult to analyse what is going on. That is, I don't get it on my machine. Then again, I only run Open Table if there are questions about it on the newsgroups. If I need to change data, I use SQL. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics