There is currently an issue with Linked Servers when querying and filtering by a guid (uniqueidentifier). If you query a Synapse SQL pool from your on-prem SQL server, for example, and attempt to filter the results using a uniqueidentifier column, you will get the following error message:
Msg 103010, Level 16, State 1, Line 1
Parse error at line: 1, column: 408: Incorrect syntax near ‘guid’.
Msg 110811, Level 16, State 1, Line 1
110811;The request to execute a prepared statement failed because the prepared statement handle 1 does not refer to a prepared statement.
OLE DB provider “MSOLEDBSQL” for linked server “[Linked Server Name]” returned message “Unspecified error”.
Msg 7421, Level 16, State 2, Line 10
Cannot fetch the rowset from OLE DB provider “MSOLEDBSQL” for linked server “[Linked Server Name]”.
The simple answer is, you cannot query this way with a Linked Server. Microsoft has offered no insight into when this may be fixed. The reference to ‘guid’ is odd as it appears even when there is no syntax that spells it out. However, there are ways to get around this issue if you need to perform this query
Current solutions:
- Create a view – this may be slower and you may need to create it in the first place but you can filter on the same columns with no issues.
- CAST / CONVERT – you can convert the datatypes of the column or filter, such as below. Tip: It is more optimal to convert the right side
- WHERE CONVERT(varchar, [guid column]) NOT LIKE ‘00000000-0000-0000-0000-000000000000’0
- WHERE [guid column] NOT LIKE CONVERT(uniqueidentifier, ‘00000000-0000-0000-0000-000000000000’)
Note: experiment with the operator as depending on your cast/convert, LIKE may work whilst = will not, and visa versa. (Interchange between like, =, not like, <>, in). Also, this limitation and problem example applies to dedicated Synapse SQL pools, we are not sure of the behaviour for serverless.
Now, the long answer for those that are interested. SQL uses PolyBase to query Linked Servers to optimise and boost performance. So once you create the SQL pool, T-SQL queries become PolyBase which is where the limitation with uniqueidentifiers to occur. You can find out more here: uniqueidentifier (Transact-SQL) – SQL Server | Microsoft Learn
