Exception when use TVP in CLR and cross database queries.

Exception when use TVP in CLR and cross database queries. by Raol

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	777155
Opened:	1/22/2013 7:08:44 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When table value parameters is used from ADO.NET inside CLR procedure, error occurs if CLR procedure is called via cross database query from another database.
Please see below.
The case is strange from my viewpoint as in both functions the same TVP is used. But in one case SQL tries to instantiate it from CallerDB and in other one from the CalleeDB.

Details (expand)

English

SQL Server 2008 R2 SP1

SQL Engine

Windows Server 2008 (all editions)

English

Create databases callerdb, callee
USE CalleeDB
CREATE SCHEMA TestSchema
CREATE TYPE [TestSchema].[IdsTVP] AS TABLE(
[Id] [int] NOT NULL
)
CREATE ASSEMBLY -- attached
CREATE FUNCTION [TestSchema].[QueryTVP]()
RETURNS TABLE (
[Id] [int] NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME ...[QueryTVP]
GO
CREATE FUNCTION [TestSchema].[TVPInsideText]()
RETURNS TABLE (
[Id] [int] NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME ...[TVPInsideText]
GO
-- Switch to the CallerDB
SELECT * FROM CalleeDB.TestSchema.QueryTVP() -- (1)
SELECT * FROM CalleeDB.TestSchema.TVPInsideText() -- (2)

(1) Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "QueryTVP":
System.Data.SqlClient.SqlException: Type IdsTVP not found in database CallerDB
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlCommand.SetUpSmiRequest(SqlInternalConnectionSmi innerConnection)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderSmi(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at SqlClrLibTest.WorkWithTVP.QueryTVP()
.

(2) Works as expected. TVP is treated correctly and data are returned to the client side.

(1) all data from the @parameter are returned as query result
(2) all data from the @parameter are returned as query result

X64

File Name	Submitted By	Submitted On	File Size
Class1.cs	Raol	1/22/2013	2 KB

Hello,
We looked at this issue and the behavior has been in the product since the SQLCR beginning. At this point, we have no intention of changing the behavior or design. Our general recommendation is to keep databases self-contained for maximum application portability and there are cases like this (SQLCLR or otherwise) where you may get unexpected results.

--
Umachandar, SQL Programmability Team