How to: Show Line Number in SQL Server Management Studio

Have you ever met the following scenarios: 1) when you write a very complex ad-hoc query in SQL Server Management Studio (SSMS), you need to count the total columns that have been derived. 2) You even need to refer to the line that appears in the error message. If yes, you need to enable the option of showing line numbers in SSMS. In fact it is very simple. It can be done in a minute. You just need to know to click the right button. Be cautious that it is different for SQL Server 2008 (2008R2) and SQL Server 2012.

Solution

Step 1: In SSMS, select Tools tab then Options as shown below

Step 2: In Option Popup Windows, expand “Text Editor”

a. For SQL Server 2008 and SQL Server 2008R2, you need to choose “All Languages”. Under General page, check the box for Line Number.

b. For SQL Server 2012, you need to choose “Transact-SQL”. Under General page, check the box for Line Number.

Hope this tip will help you improve your coding efficiency. If you just want to jump to some specific line in SSMS quickly, you can use CTRL+G hot key.

How to: Remove Table Format from Microsoft Excel 2013

When we work with PivotTables, sometimes we don’t want a spreadsheet to be a table any more. How to remove table format from it and convert it to a normal range? Below will show the way to achieve it in Excel 2013.

Step 1: Remove any table style

Select any cell in your targeted table till Design Tab appears under TABLE TOOLS. Choose the clear style as shown:

Step 2: Convert to a normal range

After converting a table to a normal range, you can delete the table without deleting represented data.

TRY_CONVERT, TRY_CAST to Find Failed Data Conversion

Problem: Whenever you have similar error messages during data loading by SSIS, it indicates that you have issues between data type conversion. How to find those outliners / failed conversion out?

• [OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".
• [OLE DB Destination [2]] Error: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[NPI] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data".

Solution: Using TRY_CONVERT or TRY_CAST Functions in SQL Server 2012

TRY_CONVERT and TRY_CAST Functions introduced in SQL SERVER 2012 is very handy tools to dig out those failed data conversion or casting. If the conversion/cast is successful then it will return the value of the specified data type; Else it will return a NULL value. For example, for the following conversion, when using CONVERT function, it will give error message like “Error converting data type varchar to bigint”.

SELECT CONVERT(bigint, '8906UP')

while using TRY_CONVERT function, it will give NULL as a result.

SELECT TRY_CONVERT(bigint, '8906UP')

So the quick way to locate those outliner in NPI column is to run the query shown below:

SELECT NPI FROM Provider
WHERE try_convert(bigint, NPI) IS  NULL