August 6, 2021 at 3:28 pm
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.
August 7, 2021 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 9, 2021 at 6:36 pm
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/
August 10, 2021 at 7:07 am
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.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy