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

Better idea: generate the dynamic SQL from within VB first, then test that. — Dai
– Dai, Commented Feb 25, 2022 at 14:38
do you mean capture the SQL as it's assigned to the variable during debugging? — DK2014
– DK2014, Commented Feb 25, 2022 at 14:41
If you are using the same exact query in Visual Basic, then I'm surprised it would run at all. I would have expected it to treat your variable names as parameters since they're all prefixed with an @ sign. — David
– David, Commented Feb 25, 2022 at 14:43
@David Yes, that is correct. The syntax "@foobar" in T-SQL isn't just for PROCEDURE and FUNCTION parameters, but for all variables, including DECLARE-locals - and it's perfectly fine to use a multi-statement batch for .CommandText, you'll need to use SqlDataReader directly with .NextResult() for subsequent resultsets (I'm not a fan of EtcDataAdapter though: people overuse it imo when EtcDataReader is often better, but oh well). — Dai
– Dai, Commented Feb 25, 2022 at 14:48
And you're clearly connect to SQL Server (only database with the FOR XML syntax), so why are you using the OleDb provider instead of the SqlClient provider? — Joel Coehoorn
– Joel Coehoorn, Commented Feb 25, 2022 at 15:30

Xavier Junqué · Accepted Answer · 2022-03-01 08:57:42Z

2

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

edited Mar 1, 2022 at 8:57

answered Feb 25, 2022 at 20:41

Xavier Junqué

4845 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DK2014 Over a year ago

Many thanks Xavier for testing and providing the code. This works great as I am getting the table populated with values. Just later in the evening after reviewing in one of the comments, I changed the code to using SQLDataAdapter, and the code's now working as before. Thanks again...

Xavier Junqué Over a year ago

Just to comment that my SQL Server edition is 64 bits, while MS Access (Office) is 32 bits.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related