Mohamed Nasr Mohamed Nasr - 1 month ago 20
SQL Question

SQL AZURE : An error occurred while executing GlobalQuery operation: Large object column support is limited to only nvarchar(max) data type

Sql azure query: after creating external table i run select query to get data from external table but this error occures!
i removed all columns with datatype = nvarchar(max) but also the problem have not been solved yet!

Code to create external table:

CREATE External TABLE [dbo].[tbl_threads_controlPanel_v](
[thread_id] [varchar](6) NOT NULL,
[thread_desc_criteria] [varchar](300) NOT NULL,
[thread_desc_formula] [varchar](300) NOT NULL,
[thread_type] [char](1) NOT NULL,
[detectType] [char](1) NOT NULL,
[detailed_qry] [nvarchar](300) NULL,
[bottomup_qry] [nvarchar](300) NULL,
[period_desc] [char](1) NULL,
[period_value] [int] NULL,
[period_value_range] [varchar](50) NULL,
--[cond_attribute] [nvarchar](max) NULL,
[cond_min_max_limit] [varchar](30) NULL,
[cond_desc] [varchar](60) NULL,
[active] [char](1) NULL,
[mature] [char](1) NULL,
[pkg_run] [char](1) NULL,
[thread_index] [int] NULL,
[thread_weight] [numeric](12, 11) NULL,
[thread_noti_type] [char](1) NULL,
[notif_id] [varchar](9) NULL,
[amt_type] [nchar](5) NULL--,
--[report_Columns] [nvarchar](max) NULL,
--[OS_Columns] [nvarchar](max) NULL
)
with(DATA_SOURCE = MyElasticDBQueryDataSrc3)


And this is the select query:

select * from dbo.[tbl_threads_controlPanel_v]


Please help ..

Thanks in advance.

UPDATE 2:
This is original tbl_threads definition
USE [DB_IFDPS_ControlPanel]
GO

/****** Object: Table [dbo].[tbl_threads] Script Date: 10/26/2016 8:51:09 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_threads](
[thread_id] [varchar](6) NOT NULL,
[thread_desc_criteria] [varchar](300) NOT NULL,
[thread_desc_formula] [varchar](300) NOT NULL,
[thread_type] [char](1) NOT NULL,
[detectType] [char](1) NOT NULL,
[detailed_qry] [text] NULL,
[bottomup_qry] [text] NULL,
[period_desc] [char](1) NULL,
[period_value] [int] NULL,
[period_value_range] [varchar](50) NULL,
[cond_attribute] [nvarchar](max) NULL,
[cond_min_max_limit] [varchar](30) NULL,
[cond_desc] [varchar](60) NULL,
[active] [char](1) NULL,
[mature] [char](1) NULL,
[pkg_run] [char](1) NULL,
[thread_index] [int] NULL,
[thread_weight] [numeric](12, 11) NULL,
[thread_noti_type] [char](1) NULL,
[notif_id] [varchar](9) NULL,
[amt_type] [nchar](5) NULL,
[report_Columns] [nvarchar](max) NULL,
[OS_Columns] [nvarchar](max) NULL,
CONSTRAINT [PK_tbl_threads] PRIMARY KEY CLUSTERED
(
[thread_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Answer

It looks like this is not the whole story. The problem seems to be not with the varchar(max) columns but with a column that is defined somewhere else as a LOB but in the external table defined otherwise than varchar(max)


The issue is most likely with the text columns

CREATE External TABLE [dbo].[tbl_threads_controlPanel_v](
...
[detailed_qry] [nvarchar](300) NULL,
[bottomup_qry] [nvarchar](300) NULL,
...

CREATE TABLE [dbo].[tbl_threads]
...
[detailed_qry] [text] NULL,
[bottomup_qry] [text] NULL,   
...

P.s.

https://msdn.microsoft.com/en-us/library/ms187993.aspx

IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.