Adding OpenQuery to Local machine
Nobody has claimed this yet.
- 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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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