Memory Optimized Tables, UDF and Cross DB access

Question

Post reply

Memory Optimized Tables, UDF and Cross DB access

frf

Old Hand

Points: 328

August 6, 2021 at 3:28 pm

#3917537

Hi everyone.

I have a database call DBMemOpt with a memory optimized table tTableMemOpt

I create a table value function in DBMemOpt in this way:

USE DBMemOpt 
GO
CREATE OR ALTER FUNCTION [dbo].[fTest_MemOpt]
(@param as int
)
RETURNS @fooTable TABLE
(
idintNOT NULL,
foointNOT NULL
)
AS
BEGIN    

    INSERT INTO @fooTable 
        SELECT 
            id, foo
        FROM 
            dbo.tTableMemOpt
        WHERE
            id = @param

RETURN 
END

I have no problem to access from other database to my function ,like this:

USE DBNOTMemOpt
GO

SELECT * FROM DBMemOpt.dbo.fTest_MemOpt(1)

But if I try to do this other query:

USE DBNOTMemOpt
GO

SELECT A.* 
FROM 
    DBMemOpt.dbo.fTest_MemOpt(1) A
    INNER JOIN
    DBNOTMemOpt.dbo.tOtherTable B on A.id = B.id

I received this error:

Msg 41317, Level 16, State 5, Line LineNumber
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.

I try to insert first the result of the function y a temp table but same error.

Any idea.

Thanks in advance.

This topic was modified 3 years, 3 months ago by frf.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Michael L John One Orange Chip Points: 27054 More actions · Answer 2

Did you check out this thread?

https://www.sqlservercentral.com/forums/topic/memory-optimized-tables-cross-db-access

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

frf Old Hand Points: 328 More actions · Answer 3

Hi Michael.

Yes I read it, but I don't want to insert data from a memory optimized table into another one, I just try to use the memory optimized table or whatever resource in a JOIN clause.

I can access to the memory optimized table with a table value function:

USE DBNOTMemOpt
GO

SELECT * FROM DBMemOpt.dbo.fTest_MemOpt(1)

But the problem appear when I try to do the JOIN.

I would not like to insert first in a variable table in DBMemOpt database, and after this insert into another table in DBNOT MemOpt and finally make the JOIN clause. Too many inserts and I guess poor performance.

Any other idea?

Thanks a lot.