1

I did search for exception 208 (invalid object name) identified by SQL Profiler and found many hints regarding "Deferred Name Resolution". Nevertheless I still did not find an asnwer related to TVP (table valued parameter) and SQL Server 2019.

I´m going to migrate an application from SQL Server 2016 to SQL Server 2019 and I´m wondering why Profiler is starting throwing of hundrets of exceptions per hour with same code. It turned out that all of them are Exception 208 (invalid object name).

Steps for reproduction: Set compatbility level of database to 140 (2017); SSMS as well as Profiler runs without any exception.

Set compatbility level of database to 150 (2019); SSMS runs fine but SQL Profiler throws exception 208 (invalid object name).

Is there any way to get rid of this? If I´m looking for any unexpected exceptions in database I get blind due to that many useless exceptions.

--ALTER DATABASE [...] SET COMPATIBILITY_LEVEL = 140;
ALTER DATABASE [...] SET COMPATIBILITY_LEVEL = 150;
GO

CREATE TYPE dbo.LocationTableType AS TABLE ( LocationName VARCHAR(20) );
GO

DECLARE @LocationTVP AS dbo.LocationTableType;
SELECT * FROM @LocationTVP; -- Throws the exception in profiler

--INSERT INTO @LocationTVP (LocationName)   SELECT 'MyLocation'; -- Throws the exception in profiler
GO

DROP TYPE dbo.LocationTableType;
GO

Either INSERT or SELECT statement is throwing an exception. Could anyone let me know how to turn this off in SQL-Server 2019 to be able to further use of SQL profiler.

SQL Tom
  • 11
  • 1
  • Profiler has been deprecated for some time; you should really be using extended events now. – Thom A Jun 14 '22 at 09:28
  • Do you get this error when running this in SSMS, or when passing a TVP from client code such as C#? – Charlieface Jun 14 '22 at 13:25
  • This error only occurs in SQL profiler. So neither in SSMS nor in C#! Thanks @Larnu for the reminder. You are right. It´s time to say good bye to profiler. I checked this behaviour in extended events and it doesn´t occur. – SQL Tom Jun 15 '22 at 09:26
  • 1
    I will say that SQL Profiler has another issue with TVPs: it shows the parameter [as separate `INSERT` statements](https://stackoverflow.com/a/56744520/14868997) (even though intenally it uses the fast Bulk Copy mechanism) so if you have a big TVP it freezes up the UI, which in turn causes the server to slow down. XEvents Profiler is far better in many respects – Charlieface Jun 15 '22 at 09:37

0 Answers0