Archivi tag: sql server

ADO 2.8 and Compatibility mode

If you have old code, that access Sql Server through MS-ADO 2.8, you can have some trouble if connect to DB on Sql Server 2016 with compatibility mode do 2016 (130). You can experience some random error on update:

Row cannot be located for updating. Some values may have been changed since it was last read.

The error is absolutely random. On the same table one row update go, the next no.

The only solution is to set compatibility level to 2014 (120) or 2012 (110).

Microsoft® SQL Server ® 2012 High-Performance T-SQL Using Window Functions

Una ottima guida all’uso efficiente delle window function dell’SQL. La guida è aggiornata alla versione 2012 di SQL server, ma buona parte delle indicazioni sono utili anche per SQL Server 2005 e SQL Server 2008. Infatti la prima introduzione delle window function in SQL Server risale alla versione 2005.

Microsoft® SQL Server® 2012 High-Performance T-SQL Using Window Functions

Limits of EXEC and SP_EXECUTESQL

One thing that is not clear from MSDN documentation is the limits of 8000 byte for query input of EXEC and SP_ECECUTESQL. If you execute this query:

declare @query1 as varchar(max)
 set @query1= replicate(' ',7951)+ ' select top 1 object_id,name from sys.all_columns'
 exec (@query1)
declare @query2 as nvarchar(max)
 set @query2= replicate(N' ',3951)+ N' select top 1 object_id,name from sys.all_columns'
 EXECUTE sp_executesql @query2
results to
object_id name
 ----------- ----------
 1307151702 ABI
(1 row(s) affected)
object_id name
 ----------- ----------
 1307151702 ABI
(1 row(s) affected)

you get some row. The first query is composed by 8000-len(‘ select top 1 object_id,name from sys.all_columns’) blanck charcters followed by simple select. In the second query there are 3951 blank unicode characters.

If you execute:

declare @query1 as varchar(max)
 set @query1= replicate(' ',7952)+ ' select top 1 object_id,name from sys.all_columns'
 exec (@query1)
declare @query2 as nvarchar(max)
 set @query2= replicate(N' ',3952)+ N' select top 1 object_id,name from sys.all_columns'
 EXECUTE sp_executesql @query2
you get
Msg 208, Level 16, State 1, Line 1
 Invalid object name 'sys.all_column'.
 Msg 208, Level 16, State 1, Line 1
 Invalid object name 'sys.all_column'.

One more black character is enough to owerflow input buffer, and query truncation happen.