T-SQL unable to CAST text to a number – Byte order mark – Dodgy character at start

Scenario

Imported some data from Excel in form “156 x 54 x 16mm”. Parsed the text to get the 156. However this would not convert to a numeric.

Error converting data type nvarchar to numeric

After some exploration I discovered that the cursor required to steps through before the number. So a character was there that SQL Server could not display. To find this I used:

SELECT Code, UNICODE(LEFT(DimensionsTextImport, 1)) AS UnicodeTest FROM Product.<tablename>

And discovered text which would not convert had Unicode 65279

Solution

Create a new column and fill this with the data from the first column after a CASE statement.

UPDATE Product.Dimensions
SET DimensionsText = CASE
WHEN UNICODE(LEFT(DimensionsTextImport, 1)) = 65279 THEN SUBSTRING(DimensionsTextImport, 2, 50)
ELSE DimensionsTextImport END

With reference to:

http://stackoverflow.com/questions/6441208/unable-to-replace-char63-by-sql-query

http://www.fileformat.info/info/unicode/char/feff/index.htm

http://en.wikipedia.org/wiki/Byte_Order_Mark

 

 

Advertisements

Continue Statement (Visual Basic) (T-SQL) “Control-of-Flow Language”

My SQL 70-461 study reminded me about the T-SQL CONTINUE statement to cause execution to jump back to the beginning of the loop. Re. Querying Microsoft SQL Server 2012 – MS Press – p 479

http://msdn.microsoft.com/en-us/library/ms178642.aspx – SQL Server

I keep needing this in VB.Net and for some reason it is missing from my VB.Net vocabulary. Not any longer…

http://msdn.microsoft.com/en-us/library/801hyx6f(v=vs.100).aspx  – VB.Net

Continue transfers control immediately to the next iteration of a loop.

Cannot use adobe reader to view pdf in your browser. Reader will now exit. Please exit your browser and try again.

PROBLEM:

  • Windows 7 64-bit
  • Internet Explorer 9
  • Adobe Reader 8.3.1

When opening a pdf in i.e

“Cannot use adobe reader to view pdf in your browser. Reader will now exit. Please exit your browser and try again.”

With reference to:

http://forums.adobe.com/thread/511010

Uninstall version 8.3.1 and install latest. For me this was 11.x.x

Crystal Reports Error: The request could not be submitted for background processing

If there is a database picture this may need to be re-imported