sql azure db error logging

• vsamantha35

  SSChampion

  Points: 11529

  More actions

  July 19, 2018 at 6:45 am

  #414402

  Hi All,

  Need some help on tracking and viewing sql server related errors on SQL Azure PaaS db.
  Is there are dmv on sql azure to get all sql server errors for a database? I tried using below dmv in the context of master database but i dont see any sql error.

  SELECT * FROM sys.event_log

  Basically, today , one request has come saying there is some error in Azure SQL Analytics: But they didnt provide any much info.
  Error
  The multi-part identifier 'S.ITEMTYPEID' could not be bound.

  I have a free sql azure subscription and wanted to repro the error and wanted to check where the error is getting logged.

  open ssms, connected to sql azure db and executed below queries to repro the error.
  CREATE TABLE [dbo].[DEPT](
      [DEPTNO] [int] NOT NULL,
      [DNAME] [varchar](14) NULL,
      [LOC] [varchar](13) NULL,
  CONSTRAINT [PK_DEPT] PRIMARY KEY CLUSTERED
  (
      [DEPTNO] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  GO

  INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (10, N'ACCOUNTING', N'NEW YORK')
  GO
  INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (20, N'RESEARCH', N'DALLAS')
  GO
  INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (30, N'SALES', N'CHICAGO')
  GO
  INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (40, N'OPERATIONS', N'BOSTON')
  GO
  INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (50, N'HR', N'HYDERABAD')
  GO
  INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (60, N'MARKETTING', N'NEW DELHI')
  GO

  --- similuation of the error
  select
   a.deptno,
   b.dname,
   loc
  from dept a
  /*
  Msg 4104, Level 16, State 1, Line 104
  The multi-part identifier "b.dname" could not be bound.
  */

  Now, i want to know , suppose if want to configure sql error logging for such types errors on a free subscription what are the steps to be taken.
  They are saying we see this in Azure SQL Log Analytics. How can i set this up and see the sql errors on sql azure free subscription...

  Once i know how the logging work, next step is to trace from which sql stored proc/statement this error is getting generated and want to fix it accordingly.

  Thanks,

  Sam