question

OmarPea-3072 avatar image
0 Votes"
OmarPea-3072 asked ErlandSommarskog answered

Bug with Msg 137 when using table variable and XPath

Hi;

I've just found what I think is a bug in the syntax validator algorithm for error 137;

The code below reproduces the escenario:

 create database test137
 go
    
 use test137
 go
    
 create type dbo.testtype as table (col1 int null)
 go
    
 create function dbo.testfn (@minormax bit, @testable dbo.testtype readonly) returns int as 
 begin
  return 1
 end
    
 go
    
 declare @myxml xml = '<root some="0" />'
 declare @mytable dbo.testtype;
    
 insert @mytable values (19), (5), (22), (8848);
    
 select dbo.testfn(t.minmax, @mytable) -- works good
 from @myxml.nodes('root') r(x)
 cross apply ( select minmax = r.x.value('@some', 'bit') ) t;
    
 go
    
 declare @myxml xml = '<root some="0" />'
 declare @mytable dbo.testtype;
    
 insert @mytable values (19), (5), (22), (8848);
    
 select dbo.testfn(r.x.value('@some', 'bit'), @mytable) -- fail, undocumented reason.
 from @myxml.nodes('root') r(x);
    
 go
 drop function dbo.testfn
 drop type testtype
    
 go
    
    
 use master
 go
 drop database test137
 go

Notice that the line commented with "works good" do the same query than the other commented with "fails".

I'm running this sql versión: Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)



sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

You are using the RTM version of SQL 2014. You need to update to a current service pack and CU and retest.

https://docs.microsoft.com/en-US/troubleshoot/sql/general/determine-version-edition-update-level#sql-server-2014

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

That does indeed look like a bug. As Tom points, you are running the RTM version of SQL 2014, and you should absolutely apply Service Pack 3 and the last CU for SQL 2014 SP3, which is CU4.

However, that will not resolve the issue. I was able to repro the problem on the most recent build of SQL 2019.

The best place to submit bugs for SQL Server is here:
https://feedback.azure.com/forums/908035-sql-server

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.