Mohamed Nasr Mohamed Nasr - 1 year ago 118
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download