January 31, 2012 at 11:33 am
yes...O.K to accept the loss of data if it is greater than 245 chars
January 31, 2012 at 11:34 am
Thanks everyone for your response..I told the team it is not possible to do it from the DB end.
January 31, 2012 at 11:35 am
GilaMonster (1/31/2012)
SQL Kiwi (1/31/2012)
GilaMonster (1/31/2012)
jcb (1/31/2012)
Can You rely on a trigger to safe truncate the data to the column?No, because types are checked before triggers fire.
Yes, if you use a view 😉 ...
True, but probably not an option in this case seeing as they're looking for a quick fix and locating all the places that it can happen for a fix isn't apparently allowed.
I have seen cases where the base table is renamed, as the view named as the base table...well you know the pattern. That said, my reply was intended for interest and completeness, rather than as a practical suggestion.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 31, 2012 at 11:37 am
SQLKnowItAll (1/31/2012)
Personally... I would tell them no. We don't truncate data at the database level with SQL Server. Tell them it is not possible and that they simply have to truncate it at the application layer.
Ah! The famous DBA "can't do" attitude :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 31, 2012 at 11:44 am
SQL Kiwi (1/31/2012)
SQLKnowItAll (1/31/2012)
Personally... I would tell them no. We don't truncate data at the database level with SQL Server. Tell them it is not possible and that they simply have to truncate it at the application layer.Ah! The famous DBA "can't do" attitude :laugh:
😎 As long as you say it with a positive attitude and maybe a little "I really wish I could, but I just can't figure out how to allow bad data into the database." That will usually get the stakeholders to rethink the request.
Jared
CE - Microsoft
January 31, 2012 at 11:56 am
"can't do" attitude is bad for everyone and works both sides.
Just go direct to the point: Deactivating this messagens ill deactivate also usefull messages and you cannot guarantee correctness = "it ill bring more problems".
At the other hand the app must send valid data to the BD. = "sorry if the app is bad writen by lazy developers"
If you manager/boss/team/director is not dumb they ill listen to you.
(if they are dumb I'm so sorry for you)
January 31, 2012 at 12:14 pm
SQLKnowItAll (1/31/2012)
As long as you say it with a positive attitude and maybe a little "I really wish I could, but I just can't figure out how to allow bad data into the database." That will usually get the stakeholders to rethink the request.
Being serious for a second, if the business requirement is that over-length strings should be silently truncated - and remember it always worked this way with the old database engine - and if there is no time to fix the UI application right away, should the DBA really say it isn't possible, or should he/she just get on with whatever was needed to not get in the way of the business?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 31, 2012 at 12:14 pm
jcb (1/31/2012)
"can't do" attitude is bad for everyone and works both sides.Just go direct to the point: Deactivating this messagens ill deactivate also usefull messages and you cannot guarantee correctness = "it ill bring more problems".
At the other hand the app must send valid data to the BD. = "sorry if the app is bad writen by lazy developers"
If you manager/boss/team/director is not dumb they ill listen to you.
(if they are dumb I'm so sorry for you)
Communication skills are also very important when looking to make a cogent argument 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 31, 2012 at 1:02 pm
Agreed, if there really isn't time (it happens, but is many times used as an excuse for poor development) it should be truncated at the database layer. However, I have been told many times by development teams that there is no time because they want to hit their bonus timeline. My concern is that the stakeholders will potentially blame me if I took part in a poor decision.
I suppose if the stakeholders said that it NEEDS to truncate data at the database level, I would simply do it. It just seems that, in this case, the company is migrating the application from an existing and functional application. So unless you know that the hardware is going to fail on a specific date or something like a license is going to expire (not likely in this case), I would push to do it right the first time.
Of course it is all situational, but I don't think that denying a request on the grounds that it can't be done is necessarily a bad thing. Something can ALWAYS be done, but many times it is at the expense of something else. Can lead be turned into gold? Sure, by removing 3 protons. 🙂 However, I don't know how to do that and many people died trying other methods. So if someone asks me if lead can be turned into gold, I don't have a problem saying no.
To sell this specific case, I like to use airport security as an analogy with my stakeholders... The metal detectors are the application, checking to see if anything is wrong with the data and then trying to fix it if they can. However, if something gets through that shouldn't there are security guards (database engine) there to stop entry for those few cases (no political or airport jokes please). If we took away the metal detectors and just had the security guards doing all of the work, they would not be able to prevent as many bad things from coming through and the line would certainly be slower and longer.
Jared
CE - Microsoft
January 31, 2012 at 1:43 pm
SQL Kiwi (1/31/2012)
SQLKnowItAll (1/31/2012)
As long as you say it with a positive attitude and maybe a little "I really wish I could, but I just can't figure out how to allow bad data into the database." That will usually get the stakeholders to rethink the request.Being serious for a second, if the business requirement is that over-length strings should be silently truncated - and remember it always worked this way with the old database engine - and if there is no time to fix the UI application right away, should the DBA really say it isn't possible, or should he/she just get on with whatever was needed to not get in the way of the business?
DBA does not owns the data, business owns the data.
I'll make sure business understands what is going to happen, probably counting how many rows will get data truncated then I'll be sure I get a official mandate to do it - at that point in time I'll be glad to help with the process.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 31, 2012 at 1:53 pm
PaulB-TheOneAndOnly (1/31/2012)
SQL Kiwi (1/31/2012)
SQLKnowItAll (1/31/2012)
As long as you say it with a positive attitude and maybe a little "I really wish I could, but I just can't figure out how to allow bad data into the database." That will usually get the stakeholders to rethink the request.Being serious for a second, if the business requirement is that over-length strings should be silently truncated - and remember it always worked this way with the old database engine - and if there is no time to fix the UI application right away, should the DBA really say it isn't possible, or should he/she just get on with whatever was needed to not get in the way of the business?
DBA does not owns the data, business owns the data.
I'll make sure business understands what is going to happen, probably counting how many rows will get data truncated then I'll be sure I get a official mandate to do it - at that point in time I'll be glad to help with the process.
That's all fine and good, but the data that is being entered by the application is too large for the field in the database. Its not a matter of what the business is accepting for data, it is who is validating it. If an SQL script can truncate it via INSERT SUBSTRING(column, 1, 254) or LEFT(column, 254) in the application code that is fine. However, to have the engine itself do it and simply truncate the data automatically is just plain bad design. Scrub the data, then insert it. Don't just insert it and let the engine do what it wants with it...
Jared
CE - Microsoft
March 22, 2016 at 4:36 am
Hi,
Did you got the resolution for the same ?
I am also working on the project migrating Sybase to SQL and getting similar issue. If possible can you please share the resolution.
In addition, if you have any document which says the issues you faced and the mitigation for them would really help me as I am newbie to Sybase.
Thanks is advance.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply