CODEWITHSUNDEEP
sql-serversql-server-2019sql-server-profiler
SQL Tom
SQL Tom

Reputation: 11

How to get rid of SQL Profiler exception 208 using table valued parameter (TVP)

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.

Upvotes: 1

Views: 117

Answers (0)

Related Questions