T-SQL query runs in SQL Server Management Studio but not working in VB. NET application

Question

I had this query running fine in my .NET app. I recently changed this to only query databases that are online. It works fine in SSMS and return the databases that contain the specified table with rows.

However when I use this back in the VB.NET app, the query returns 0 rows. Wondering if anyone can advise?

 SET QUOTED_IDENTIFIER ON  

 DECLARE @temptbl TABLE 
                  (
                      DBName varchar(50) PRIMARY KEY NOT NULL, 
                      EntryCOUNT integer
                  ) 

 DECLARE @TableName NVARCHAR(50)     

 SELECT @TableName = 'dbo.PRJMGTLocation'     
 DECLARE @SQL NVARCHAR(MAX)   
 
 SELECT @SQL = STUFF((SELECT CHAR(13) + 'SELECT ''' + DB.name + ''', COUNT(1) FROM ' + DB.name + '.' + @TableName                  
                      FROM 
                          (SELECT TOP(1000) name FROM sys.databases  WHERE state = 0) DB                   
                      WHERE OBJECT_ID(DB.name + '.' + @TableName) IS NOT NULL                     
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')  
                
INSERT INTO @temptbl (DBName , EntryCOUNT)                 
    EXEC sys.sp_executesql @SQL   

SELECT * 
FROM @temptbl t 
WHERE EntryCOUNT >= 1 

The .NET code is as shown below. I am passing the above SQL statement as query2 variable:

Using cmdlocal1 As New OleDbCommand(query2, SQLSVRConn)
                                                        
    Using adapter As New OleDbDataAdapter(cmdlocal1)

        SQLSVRConn.Open()
        adapter.Fill(SqlDBTable)

        If SqlDBTable.Rows.Count > 0 Then
            SvrDBCBbox.DataSource = SqlDBTable

            SvrDBCBbox.ValueMember = "DBName"
            SvrDBCBbox.DisplayMember = "DBName"

        End If

        SQLSVRConn.Close()
    End Using
End Using

Answer 1

I found the same problem as you: query in SQL Server gets the result, but with OleDBDataAdapter it is empty. As a workaround the code below gets results for me:

Dim query2 As String = My.Resources.SQL
Dim sqlDBTable As New DataTable
Using SQLSVRConn As New OleDbConnection(strConn)
    Dim command As New OleDbCommand(query2, SQLSVRConn)
    SQLSVRConn.Open()
            Using reader As OleDbDataReader = command.ExecuteReader()
                Dim cols As Int32
                If reader.Read Then
                    cols = reader.FieldCount
                    For i As Int32 = 0 To cols - 1
                        sqlDBTable.Columns.Add()
                    Next
                    Dim row As DataRow = sqlDBTable.Rows.Add
                    For i As Int32 = 0 To cols - 1
                        row.Item(i) = reader(i)
                    Next
                End If
                While reader.Read()
                    Dim row As DataRow = sqlDBTable.Rows.Add
                    For i As Int32 = 0 To cols - 1
                        row.Item(i) = reader(i)
                    Next
                End While
            End Using
End Using