Yesterday I had a chat with one of my friend who was having problem with his SQL Query.
\nIll just list down his Peculiar problem and the way we solved it in this blog post. It would be a good learning to all.<\/p>\n
The problem he had:<\/strong> All the columns being varchar, why was this error occuring? Following are the steps we followed to find the reason for this error:<\/strong> Analysed the input value which was giving the error. But the output will be 0 for ‘128M99’. So there is some part of code somewhere in the database which is causing this issue by validating weather the value is numeric.<\/p>\n So we went ahead and searched for all the indexed views in the database. Below is the piece of code in this indexed view So this is the code that is giving the above error. Why was this indexed view code running when he was executing the insert script on tablex?<\/p>\n Learnings form this post: Yesterday I had a chat with one of my friend who was having problem with his SQL Query. Ill just list down his Peculiar problem and the way we solved it in this blog post. It would be a good learning to all. The problem he had: – There was an insert script for a…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[128,186,3],"tags":[623,624,625,626,627,628],"class_list":["post-102","post","type-post","status-publish","format-standard","hentry","category-sql-errors","category-sql-server-issues","category-sqlserverpedia-syndication","tag-conversion-error-even-though-there-is-no-conversion-happening-in-the-insert-update-script","tag-error-converting-varchar-to-bigint","tag-level-16","tag-line-1","tag-msg-8114","tag-state-5"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/102","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=102"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/102\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=102"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=102"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=102"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n– There was an insert script for a table(say it tablex).
\n– All the columns in this table were of type varchar(100).
\n– This insert script was running fine for most of the insert values, but was failing when one of the column has value ‘123E99’.<\/strong>
\n – The error the script was giving was:
\n
\n Msg 8114, Level 16, State 5, Line 1
\n Error converting data type varchar to bigint.
\n <\/font><\/p>\n
\nWhy is this datatype conversion happening?
\nIs this conversion implicitly happening or it explicitly done?
\nWhy was this error occuring only for value ‘123E99’ but not for value ‘128M99’?<\/p>\n
\nChecked the table definition to see if there were any triggers on the table.
\n There were no triggers on this table<\/p>\n
\n‘123E99’ – This value has E as part of it.
\nWhen the below query is executed it gives 1 as output.
\n[sql]
\nSELECT isnumeric(‘123E99’);
\n[\/sql]<\/p>\n
\nThis means sql server understands ‘123E99’ as a numeric value but not ‘128M99’.<\/p>\n
\nWe found one that is using this table.<\/p>\n
\n[sql]
\nCASE WHEN ISNUMERIC(tablex.column2) = 1
\nTHEN CONVERT(BIGINT,tablex.column2)
\n[\/sql]<\/p>\n
\nThe ISNUMERIC is giving true but conversion to big int is failing as the value is larger that that of bigint.
\nModifying this piece of code solved the issue. \ud83d\ude42<\/p>\n
\nWhen you face such peculiar issues of conversion etc.. <\/p>\n\n