{"id":93587,"date":"2022-03-21T00:57:50","date_gmt":"2022-03-21T00:57:50","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93587"},"modified":"2022-03-31T19:47:55","modified_gmt":"2022-03-31T19:47:55","slug":"sql-server-graph-tables-cross-database-surprises","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sql-server-graph-tables-cross-database-surprises\/","title":{"rendered":"SQL Server Graph Tables – Cross Database Surprises"},"content":{"rendered":"

I was putting together some queries today, pulling data from several databases. One of these is my tables that employ graph tables in SQL Server. As I started part of the query, I used a three part name to access graph table, and used * to fetch the column names in the table (as one does when looking at a few rows from a table):<\/p>\n

SELECT *
\n<\/span>FROM<\/span> \u00a0 <\/span>GraphDBName.<\/span>IdentityGraph.<\/span>MarketingContact;<\/p>\n

This was greeted with:<\/p>\n

Msg 13924,<\/span> Level<\/span> 16,<\/span> State<\/span> 1,<\/span> Line 114
\nSelect<\/span> *<\/span> is<\/span> not<\/span> permitted because it refers to<\/span> a node<\/span> or<\/span> edge<\/span> table<\/span> ‘GraphDBName.IdentityGraph.MarketingContact’<\/span> which is<\/span> not<\/span> in<\/span> the current<\/span> database<\/span>.<\/span><\/p>\n

I next tried to let SQL Prompt give me the column names, which were output as:<\/p>\n

SELECT<\/span> MarketingContact.<\/span>$node_id<\/span>,
\n<\/span>\u00a0\u00a0 \u00a0 \u00a0 <\/span>MarketingContact.<\/span>MarketingContactId,
\n<\/span>\u00a0\u00a0 \u00a0 \u00a0 <\/span>MarketingContact.<\/span>RowCreatedTime,
\n<\/span>\u00a0\u00a0 \u00a0 \u00a0 <\/span>MarketingContact.<\/span>RowLastModifiedTime
\nFROM<\/span> \u00a0 <\/span>GraphDBName.<\/span>IdentityGraph.<\/span>MarketingContact;<\/p>\n

This returned the following:<\/p>\n

Msg 13923, Level 16, State 5, Line 124
\nPseudocolumn ‘$node_id’ is invalid because it references a node or edge table that is not in the current database.<\/p>\n

Msg 13923, Level 16, State 5, Line 124
\nPseudocolumn ‘$node_id’ is invalid because it references a node or edge table that is not in the current database.<\/p>\n

Msg 13923, Level 16, State 5, Line 124
\nPseudocolumn ‘$node_id’ is invalid because it references a node or edge table that is not in the current database.<\/p>\n

Msg 13923, Level 16, State 5, Line 124
\nPseudocolumn ‘$node_id’ is invalid because it references a node or edge table that is not in the current database.<\/p>\n

Msg 13923, Level 16, State 5, Line 124
\nPseudocolumn ‘$node_id’ is invalid because it references a node or edge table that is not in the current database.<\/p>\n

Why so emphatic that this is not the way that it required 5 error messages? Not sure. But either way, no go. It isn’t that it won’t work at all across databases. This code works fine:<\/p>\n

SELECT<\/span> TOP<\/span> 10 MarketingContactId,<\/span>\u00a0 <\/span>ContactId
\nFROM<\/span> \u00a0 <\/span>GraphDBName.<\/span>IdentityGraph.<\/span>MarketingContact,
\n<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0<\/span>GraphDBName.<\/span>IdentityGraph.<\/span>MarketingLink,
\n<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0GraphDBName.<\/span>IdentityGraph.<\/span>Contact
\nWHERE<\/span> MATCH<\/span>(<\/span>MarketingContact-(<\/span>MarketingLink)-><\/span>Contact);<\/span><\/p>\n

Just understand that if you need any of the graphDb underlying data structures, you will need to find their actual physical name and use it. I would definitely suggest never accessing these columns via any method other than the pseudocolmns for production code (because you have no way to predict the column names from dev to prod (you cannot specify the names when creating a table), but this following code does work:<\/p>\n

SELECT<\/span> TOP<\/span> 10 MarketingContactId,<\/span>\u00a0 <\/span>ContactId,\u00a0
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0[$from_id_BC51BB1CD6BE4631A9101A1510560591],<\/span> [$to_id_E50693D30F7E41A2B8B29BA31D020332]
\nFROM<\/span> \u00a0 <\/span>GraphDBName.<\/span>IdentityGraph.<\/span>MarketingContact,
\n<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0<\/span>GraphDBName.<\/span>IdentityGraph.<\/span>MarketingLink,
\n<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0GraphDBName.<\/span>IdentityGraph.<\/span>Contact
\nWHERE<\/span> MATCH<\/span>(<\/span>MarketingContact-(<\/span>MarketingLink)-><\/span>Contact);<\/span><\/p>\n

Not completely surprising, but might be a bit jarring the first time you see this (and something to know if you are using the underlying graph structures in a way that isn’t exactly desirable based on how the structures were intended to be used by their creators.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Louis Davidson explains how to get around an interesting issue when doing cross-database queries involving graph database tables.…<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[136366,4151,4183],"coauthors":[19684],"class_list":["post-93587","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-sql-graph","tag-sql-server","tag-t-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93587","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=93587"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93587\/revisions"}],"predecessor-version":[{"id":93705,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93587\/revisions\/93705"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=93587"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=93587"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=93587"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=93587"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}