SQL server and azure troubleshooting - common issues

 SQL server and azure troubleshooting - common issues

1) Msg 245, Level 16, State 1, Line 2

Conversion failed when converting the varchar value '000005EW84' to data type int.

Resolution: this issue happens when there is character in the column that you are converting to integer. Use a case statement like below to remove those records that have a column. In the example below policy_number is the column that has characters.

case when isnumeric(policy_number) = 1 then cast(policy_number as int) else 0 end as policy_num

2) Cannot insert explicit value for identity column in table 'Mytable_Dim' when IDENTITY_INSERT is set to OFF.

Resolution: You're inserting values for OperationId that is an identity column.

You can turn on identity insert on the table like this so that you can specify your own identity values.

SET IDENTITY_INSERT Table1 ON

INSERT INTO Table1
/*Note the column list is REQUIRED here, not optional*/
            (Mydata_SID,
             MyDate_Desc)
VALUES      (20,
             ''My data")

SET IDENTITY_INSERT Table1 OFF

3)Msg 515, Level 16, State 2, Line 45

Cannot insert the value NULL into column 'Party1_SID', table 'dbo.Party_Dim'; column does not allow nulls. INSERT fails.

Resolution: The column Party1_Sid does not accept null values.  Insert a value of -1 to the column whenever the value is null. Isnull() function can be used to test if a column value is null.

4) Msg 120, Level 15, State 1, Line 45 The select list for the INSERT statement contains fewer items than the insert list.

Resolution:The number of SELECT values must match the number of INSERT columns.
Match the number of columns on select and insert statements..

5) Msg 264, Level 16, State 1, Line 45

The column name 'Party1_SID' is specified more than once in the SET clause or column list of an INSERT.

Resolution: Remove any duplication of columns in the query. A column cannot be assigned more than one value in the same clause. Modify the clause to make sure that a column is updated only once. If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code.

6) Arithmetic overflow error converting numeric to data type numeric.

Resolution: Increase the numeric precision. If there are datatype such as  numeric(11,2) you might have to make it high so that all the values can fit into the datatype. For example, make it numeric(20,2).

7) Msg 1033, Level 15, State 1, Procedure test_vw, Line 56
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

 Resolution: Remove order by from the view statement.