dcsimg
Results 1 to 16 of 16

Thread: why do I use SQL Server Management Studio to update a row of a table in SQL Server ?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2008
    Posts
    421

    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: Name:  SQLServer01.jpg
Views: 78
Size:  39.2 KB

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,886

    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.

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,092

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    Quote Originally Posted by jmcilhinney View Post
    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.....
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  4. #4
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,405

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    Sigh...
    Please remember next time...elections matter!

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2008
    Posts
    421

    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

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,886

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    Quote Originally Posted by dong View Post
    I do not think it is "truncated" as you said
    I haven't watched your video but I'm confident that you're wrong.

    Name:  Truncated.jpg
Views: 44
Size:  33.0 KB

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2008
    Posts
    421

    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;

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,886

    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.

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2008
    Posts
    421

    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
    Name:  SQLServer02.jpg
Views: 34
Size:  44.9 KB
    Name:  SQLServer03.jpg
Views: 33
Size:  24.1 KB

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,462

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,680

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    Quote Originally Posted by dong View Post
    I asked how to fix the edit directly on the Table, still using through the SQL statement I know, you see my attached attachments
    Name:  SQLServer02.jpg
Views: 34
Size:  44.9 KB
    Name:  SQLServer03.jpg
Views: 33
Size:  24.1 KB
    Look at your SQL statement again... and then read this part next, again...


    Quote Originally Posted by jmcilhinney View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,462

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2008
    Posts
    421

    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 ?

  14. #14
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,886

    Re: why do I use SQL Server Management Studio to update a row of a table in SQL Serve

    Quote Originally Posted by FunkyDexter View Post
    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.

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,092

    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....
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  16. #16
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,462

    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; Yesterday at 03:22 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - 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
  •  



Featured


Click Here to Expand Forum to Full Width