The OLE DB provider "MSDASQL" for linked server " " supplied inconsistent metadata for a column.The column " " (compile-time ordinal 2) of object " " was reported to have a "DBCOLUMNFLAGS_ISLONG" of 0...
Applies To: SQL 2012 and above. (Requires supportability towards Multi-Subnet Failover)
Understanding of the Issue:
Let us consider a situation where we want to create a Linked Server to pull data from SQL Server Always On (using the Listener name) onto our local SQL Server (or another SQL Server) and along with it also leverage the option of having the “MultiSubnetFailOver” property being set. How can we achieve this?
To achieve the above requirement, we would have to create an ODBC DSN using SQL Native Client Provider 11.0 to connect to the SQL Server Always On using the listener name and check the checkbox saying “MultiSubnetFailOver” during the setup of the ODBC DSN (see below image) and then create a Linked Server to connect to that ODBC DSN.
NOTE: The SQL OLE DB Providers doesn’t support “MultiSubnetFailover” property. Refer article: https://msdn.microsoft.com/en-us/library/gg471494(v=sql.120).aspx
Now, let us consider an instance where the source table is something like this:
CREATE TABLE [dbo].[testtable](
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [nvarchar](max) NULL
And data in it is something as below:
If we try and pull this data from the above configuration using a query like:
select * from openquery(mylinkedserver, ' select id, value from master.dbo.testtable’)
We would receive the below error:
The OLE DB provider "MSDASQL" for linked server "mylinkedserver" supplied inconsistent metadata for a column. The column "value" (compile-time ordinal 2) of object ""master"."dbo"."testtable"" was reported to have a "DBCOLUMNFLAGS_ISLONG" of 0 at compile time and 128 at run time.
Cause:
This issue is occurring because we’re trying to pull in a column which has a column size being set to “Max”. This becomes an issue here as we’re switching providers from MSDASQL to SQLNCLI and there is an upper limit on the number of characters which can be converted for any row data of certain datatype. See the resolution section for further information.
Resolution:
How can we ascertain that the data conversion upper-limit on the number of the characters is the cause and identify the limit on the number of characters allowed?
If we change the above OPEN QUERY to cast the nvarchar(max) to nvarchar(10000):
select * from openquery(mylinkedserver, ' select id, cast(value as nvarchar(10000)) from master.dbo.testtable’)
We would hit the below error:
OLE DB provider "MSDASQL" for linked server "mylinkedserver" returned message "[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.".
OLE DB provider "MSDASQL" for linked server "mylinkedserver" returned message "[Microsoft][ODBC SQL Server Driver][SQL Server]The size (10000) given to the convert specification 'nvarchar' exceeds the maximum allowed for any data type (4000).".
The above error clearly help us in ascertaining that the limit for nvarchar is 4000 characters for the data conversion to occur. Similarly, the limit for all data types can be found.
What can we do to overcome the issue?
We can try below options:
(a) If we have the option to change the source table, change it as such that it should not have nvarchar(max) datatype based columns. We can have nvarchar(size) based datatype column, where size can have a max value of 4000. Similarly, it holds true for other datatypes with their respective limits.
(b) If we do not have the option to change the source table, then we would have to cast the incoming rows to 4000 for nvarchar “[cast(value as nvarchar (4000)]” in the openquery statement which would help in trimming down data after 4000 characters and allow the source data to get converted successfully as below:
select * from openquery(mylinkedserver, ' select id, cast(value as nvarchar(4000)) from master.dbo.testtable’)
(c) If neither (a) nor (b) is possible, we can pull data into successive chunks of characters of length 4000 and then concatenate them to a variable and then use it like print on the query output windows or use it somewhere else as per the requirement. We can try the below query (and change accordingly as per the requirement) to achieve this:
SQL Script:
--Need it for successive run, if you're running it for the first time then comment it.
drop table #temp_table
-- Create temporary table where we would be pushing all the data.
create table #temp_table(
ID int,
Value nvarchar(max))
--Declare and select what would we be using as a variable.
DECLARE @oneid int
DECLARE the_cursor CURSOR FAST_FORWARD
FOR select * from openquery(testbradtest, 'select id from testdatabase.dbo.table1 order by (id)')
OPEN the_cursor
FETCH NEXT FROM the_cursor INTO @oneid
--Loopthourgh all the rows one by one.
WHILE @@FETCH_STATUS = 0
BEGIN
declare @length int, @sqlqueryforlength nvarchar(max)
--Find length of the column in consideration
set @sqlqueryforlength = 'select @val1=lengths from openquery(testbradtest, ''select len(value) as lengths from testdatabase.dbo.table1 where id =' + cast((@oneid) as NVARCHAR) + ''')'
exec sp_executesql @sqlqueryforlength ,N'@val1 NVARCHAR(max) output', @val1=@length OUTPUT
declare @currentvalue nvarchar(4000) --Current set of 4000 characters.
declare @finalvalue nvarchar(max) -- Final row value which is a replica of what is there is in the source table (more than 4000 characters).
declare @startindex int, @sqlquery nvarchar(max)
select @startindex = 0
select @finalvalue = NULL
--Loop until we cover all the characters in the row value taking 4000 at a time.
while (@length >0)
BEGIN
-- Until the length or left over is more than 4000 characters.
if @length >4000
BEGIN
set @length = @length - 4000
set @sqlquery = 'select @val1=value1 from openquery(testbradtest,''select cast((substring(value,'+ cast((@startindex+1) as NVARCHAR) + ',' + cast((@startindex +4000) as NVARCHAR) + ')) as NVARCHAR(4000)) as value1 from testdatabase.dbo.table1 where id =' + cast((@oneid) as NVARCHAR) + ''')'
exec sp_executesql @sqlquery ,N'@val1 NVARCHAR(max) output', @val1=@currentvalue OUTPUT
set @finalvalue = COALESCE(@finalvalue, '') + @currentvalue
END --If.
-- Dealing with last set of characters.
else
begin
set @sqlquery = 'select @val1=value1 from openquery(testbradtest, ''select cast((substring(value,' + cast((@startindex+1) as NVARCHAR) +',' + cast((@startindex + @length) as NVARCHAR) +')) as NVARCHAR(4000)) as value1 from testdatabase.dbo.table1 where id = ' + cast((@oneid) as NVARCHAR) + ''')'
exec sp_executesql @sqlquery ,N'@val1 NVARCHAR(max) output', @val1=@currentvalue OUTPUT
set @finalvalue = COALESCE(@finalvalue, '') + @currentvalue
BREAK
end --ELSE.
--Increment the starting index.
set @startindex= @startindex + 4000
END
--insert data into temporary table
insert into #temp_table (Id, Value)
select @oneid, @finalvalue
--Fetch next row value.
FETCH NEXT FROM the_cursor INTO @oneid
END
CLOSE the_cursor
DEALLOCATE the_cursor
--Select data from the temp table
select * from #temp_table
HTH!
Author: Deepak Lakhotia - SQL Server BI-ONE Developer team, Microsoft
Reviewer: Krishnakumar Rukmangathan- Technical Advisor, SQL Server BI-ONE Developer team, Microsoft