-
Feb 10th, 2020, 02:03 AM
#1
Thread Starter
Fanatic Member
why do I use SQL Server Management Studio to update a row of a table in SQL Server ?
why do I use SQL Server Management Studio to update a row of a table in SQL Server 2005 but it's not row was update ?
A few days ago me to use SQL Server Management Studio to update a table in Microsoft SQL Server 2005. In Object Explorer, you right-click the name of the table, and then I click Open Table. I update a row of the table normal but now I do it again. I may receive one of the following error messages unexpectedly in the Microsoft SQL Server Management Studio dialog box:
No row was updated
The data in row 61 was not committed
The statement has bĂȘn terminated
Correct the errors and retry or press ESC to cancel the change(s).
You see the attached image file:
-
Feb 10th, 2020, 02:28 AM
#2
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
You shouldn't be posting questions on forums before you even bothered to use a search engine. If you'd have simply copied the error message into Google or Bing then you'd have got your answer. Too much trouble?
"Truncated" means cut short, so the error message is telling you that you entered some data that would have to be cut short in order to save, i.e. is too long for the column you're trying to save it to. If you're using SSMS, you ought to know how wide your columns are.
-
Feb 10th, 2020, 04:59 AM
#3
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
Originally Posted by jmcilhinney
If you're using SSMS, you ought to know how wide your columns are.
Or they shouldn't give him access to SSMS in any case if he doesn't know such basic things.....
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Feb 10th, 2020, 12:08 PM
#4
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
Please remember next time...elections matter!
-
Feb 12th, 2020, 02:48 AM
#5
Thread Starter
Fanatic Member
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
I have searched on google but no results, I do not think it is "truncated" as you said but a few days ago I edited directly from the normal table, if I use SQL statements to update data is normal, but if I fix it directly from the table, the above error will appear, see my attached video http://www.mediafire.com/file/cd14ia...teSQL.swf/file
-
Feb 12th, 2020, 03:04 AM
#6
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
Originally Posted by dong
I do not think it is "truncated" as you said
I haven't watched your video but I'm confident that you're wrong.
-
Feb 13th, 2020, 09:08 PM
#7
Thread Starter
Fanatic Member
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
My data type is datetime, I revised the posting date, in your opinion, how do I fix this error ?
Code:
UPDATE Article
SET PostDate = '2019/03/28 02:43:08'
WHERE ArticleID = 107;
-
Feb 14th, 2020, 01:15 AM
#8
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
If you want to use literal text to represent a date in T-SQL then use 'yyyy-MM-dd' format, not 'yyyy/MM/dd'. If you use slashes then I'm guessing that it would try to interpret it as 'dd/MM/yyyy' or 'MM/dd/yyyy'. The truncation that it is referring to is likely to a 2-digit day or month from the 4-digit substring in your code.
-
Feb 14th, 2020, 03:20 AM
#9
Thread Starter
Fanatic Member
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
I asked how to fix the edit directly on the Table, still using through the SQL statement I know, you see my attached attachments
-
Feb 17th, 2020, 04:32 AM
#10
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
When inserting/updating dates via sql, best practice is to explicitly convert the string to a date using a format. The formats can be found here. That said, assuming the default format as JM has suggested is basically OK and lots of us do it as a short cut. You really should do one or the other though as anything else is likely to produce incorrect results.
As for chaning it using SSMS, that error message is telling you that some piece of string data on the row is being truncated. It's not checked until you leave the row, though, so it could be any field on the row, not necessarily the date field you've just updated. In fact, if that field is genuinely set to a date, it's definitely not the problem.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Feb 18th, 2020, 08:23 AM
#11
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
Originally Posted by dong
I asked how to fix the edit directly on the Table, still using through the SQL statement I know, you see my attached attachments
Look at your SQL statement again... and then read this part next, again...
Originally Posted by jmcilhinney
If you want to use literal text to represent a date in T-SQL then use 'yyyy-MM-dd' format, not 'yyyy/MM/dd'. If you use slashes then I'm guessing that it would try to interpret it as 'dd/MM/yyyy' or 'MM/dd/yyyy'. The truncation that it is referring to is likely to a 2-digit day or month from the 4-digit substring in your code.
Now, read it again, pay attention to part I've highlighted...
And then compare that to you SQL statement, again.
-tg
-
Feb 19th, 2020, 03:23 AM
#12
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
I think the date issue is a distraction. Dong absolutely should be taking good advice on how to express dates, but it's not the cause of that error.
The error is being thrown because a string or binary field on that row is being overflowed.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Feb 19th, 2020, 11:49 PM
#13
Thread Starter
Fanatic Member
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
In my opinion this error is due to SQL Server Management Studio software, do you think so ?
-
Feb 20th, 2020, 12:08 AM
#14
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
Originally Posted by FunkyDexter
I think the date issue is a distraction. Dong absolutely should be taking good advice on how to express dates, but it's not the cause of that error.
The error is being thrown because a string or binary field on that row is being overflowed.
Sounds plausible. A sensible person would actually test that specifically, rather than insist that it is a bug in a piece of software used by millions and ask whether we would agree when everything in this thread indicates that we don't.
-
Feb 20th, 2020, 01:51 AM
#15
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
Errr....? The OP does know, that, basically, the underlying Datatype of a DateTime is actually a double?
As i said many times: i've no experience whatsoever in MS-SQL, but he's changing the value directly in the table.
Has he tried to enter, say "456789.2356"?
Nevermind that it eludes me completely, why someone would want to change something directly in the table, and not with an SQL-Statement.
And since he said, that it works with SQL, it's probably because the engine is correctly "translating" the value to its underlying datatype.
Maybe he's trying to avoid some auditing mechanism, which shows who has changed what....
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Feb 20th, 2020, 03:18 AM
#16
Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve
In my opinion this error is due to SQL Server Management Studio software, do you think so ?
No, I don't agree. SSMS is doing exactly what it should. It's telling you that you have made an error and it's not going to let you.
SSMS does not commit changes you make as you leave each cell, it commits them as you leave the row. So when you leave the row every cell in the is checked. You have entered too much data into one of the string or binary cells so SSMS would have to truncate it in order to accept it. That would involve data loss so it is refusing to accept it and telling you why: "String or Binary Data would be truncated". You need to work out which cell is affected but it won't be a date column because that's neither string nor binary.
The only other possible explanation is that your PostDate column isn't a DateTime but is actually a string (varchar) column and the value you're entering is too long. This, of course, would actually be exactly the same underlying error as above. It seems unlikely because the formatting in your screenshots is perfect but we can't be 100% just from the screenshot. I suggest that you check the type on that column if only to eliminate it as a possibility.
Errr....? The OP does know...
That wouldn't be an issue in SSMS. SqlServer itself will allow you to handle datetimes as double but Management Studio converts them in the presentation layer. So when you entered 456789.2356, it would try to convert that to a date using a known format and fail. So you can use those tricks directly with SQL, but not the UI.
@JM, I always try to be the nice guy but sometimes, just sometimes, I feel the lure of the darkside
Last edited by FunkyDexter; Feb 20th, 2020 at 03:22 AM.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|