October 26, 2018 at 1:19 am
Hello,
Is there a workaround to accessing a memory optimized table from another db? For a select only?
DB1 -> The location of the Select Query
DB2 -> The location of the Mem Optimized Table
use DB1
select * from DB2.Mem_Opt_Tbl
I tried:
1. table hint: with (snapshot)
2. creating a synonym on DB1 for DB2.Mem_Opt_Tbl
3. adding a memory optimized filegroup to DB1
4. creating a mem optimized variable on DB1 and insert into the new mem optimized variable from DB2.Mem_Opt_Tbl, and then select from there
I always get the error:
"Msg 41317, Level 16, State 6, Line 18
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."
Is there any workaround regarding this?
Thank you!
October 26, 2018 at 1:27 am
I think you can use a Table Valued Function in the database with the MO table, but it's been a while since I looked into it. It would probably have to be a non inlined one though. I do recall that there didn't seem to be a particularly good solution to it unfortunately.
October 26, 2018 at 1:53 am
It works! It's slow, but it works.
Thank you so much for your reply!
October 26, 2018 at 2:01 am
Glad to help. If anyone has a better way I'd be interested to hear it too.
October 26, 2018 at 2:03 am
I just tried with a view as well, but it doesn't work. I guess with the function it selects everything from the table, encapsulates the whole data and then it can be safely sent with "the highest isolation level"
October 29, 2018 at 12:50 pm
according to this page:
Microsoft says to copy to a table variable first, then copy to destination. Interesting!
October 30, 2018 at 6:20 am
Yes, but that would be the situation where you want to insert data into a memory optimized table from a disk-based table from another db. My need was to read from a mwmory optimized table from another db.
October 30, 2018 at 10:52 am
ralu_k_17 - Tuesday, October 30, 2018 6:20 AMYes, but that would be the situation where you want to insert data into a memory optimized table from a disk-based table from another db. My need was to read from a mwmory optimized table from another db.
I was thinking it would work the other way too.
November 1, 2018 at 8:24 am
I did try the other way and it's the number 4 point in the initial description of the issue. It doesn't work unfortunately.
November 1, 2018 at 9:44 am
ralu_k_17 - Thursday, November 1, 2018 8:24 AMI did try the other way and it's the number 4 point in the initial description of the issue. It doesn't work unfortunately.
Probably not exactly what you want, but what I'm finding that inserting into a memory variable while the memory optimized database is current works, then I can insert into the other database from the memory variable. It does treat the "non memory optimized" table as NOT the current database so don't know if that would work for you.
use dbname_with_mem_filegroup;
create table memtest
(
key1 int not null primary key nonclustered,
data1 int
)
with (memory_optimized = on);
insert into memtest (key1, data1) select 1,1
declare @memvar table
(
key1 int,
data1 int
)
insert into @memvar (key1, data1)
select key1, data1 from memtest;
insert into the_other_regular_database.dbo.regtest (key1, data1) select key1, data1 from @memvar;
November 16, 2018 at 11:31 pm
Check this out:
https://sqlwithmanoj.com/tag/memory_optimized_data/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply