microsoft / microsoft/DacFx

Adding OpenQuery to Local machine

Open
#650 4 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

area: build sdk bug
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

- SqlPackage or DacFx Version: 170.0.94.3
- .NET Framework (Windows-only) or .NET Core: Net Core 9
- Environment (local platform and source/target platforms): Windows 11, Visual Studio 2022 Preview Version 17.14.4 Preview 1.0

**Steps to Reproduce:**

1. I have an openquery to the local machine like select * from openquery([tcl-processarc],'')
2. On build, there is an error for [tcl-processarc] being an unresolved reference

**Did this occur in prior versions? If not - which version(s) did it work in?**
I tried the previous preview versions of the Version 2 SDK but same issue

(DacFx/SqlPackage)

Although I have the Master and Msdb databases as references, a view I have still throws an error about the master database:
```tsql
SELECT
DISTINCT
number AS NumberOfDays,
CAST(number AS nvarchar(10)) + N' days' AS NumberOfDaysDescription
FROM master.dbo.spt_values

WHERE number BETWEEN 0 AND 2050
UNION

SELECT
-1 AS NumberOfDays,
'Undefined' AS NumberOfDaysDescription
```
I decided to try an openquery to the local server, calling a cte to return the same data:
```tsql
select NumberOfDays,NumberOfDaysDescription from openquery([tcl-processarc],'
WITH cte
AS (
SELECT 0 AS RowNum

UNION ALL

SELECT RowNum + 1
FROM cte
WHERE RowNum <= 2050
)
SELECT rowNum AS NumberofDays
,CAST(RowNum AS NVARCHAR(10)) + N'' days'' AS NumberOfDaysDescription
FROM cte
UNION ALL

SELECT - 1 AS NumberOfDays
,''Undefined'' AS NumberOfDaysDescription
OPTION (MAXRECURSION 0)
' )
```
I'm getting an unresolved reference to [tcl-processarc]. I have other openquery to a few external linked servers so I will need to figure out how to make the reference but I didn't see anything in the Issues nor online about a method.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing issue #650 with the shown OPENQUERY against the local linked server and the existing master and msdb references. Compare how the other external linked servers are represented during the DacFx/SqlPackage build. Done means the intended linked-server reference behavior is established and the build no longer reports [tcl-processarc] as unresolved, or the limitation is clearly documented.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
30/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.