SQL Server does not return all columns of a table

Question

I have an old application (built with Visual C++ 6.0) using MFC and RecordSets to access SQL Server 2016 using SQL ODBC driver. The code uses a lot of MFC functions to dynamically retrieve informations about the table to access. While opening a ColumnSet the following steps are done.

...
SQLColumns(...);
AllocAndCacheFieldInfo();
AllocRowset();
MoveNext();
m_bBOF = m_bEOF;
...

After MoveNext() the value of m_bEOF is true. But it should be false for the following code to run properly where the table columns are retrieved.

This (old) code is running well with a MS Access database and with a Postgres database. So I think my code should be fine. Now I would think about configuring the database or the ODBC driver. But: Using MS Access to link to a SQL database table using the same data source works fine. All tables are listed successfully. That means the database is working fine and the data source is working fine, too.

And now I am a little confused. What do I have to do that my code is working fine? I checked the MSDN sites for the MFC functions but did not find any hint to update the code.

Do you guys have any help for me? I would really appreciate it. If you need any more information I will post it.

Kind regards

Answer 1

I used the SQL profiler to find out any differences and I got these results.

my code:

exec [MyDatabase].[sys].sp_columns_100 N'MyTable',N'sys',N'MyDatabase',NULL,@fUsePattern=1

MS Access:

exec [sys].sp_columns_100 N'MyTable',N'dbo',NULL,NULL,@fUsePattern=1

So I changed the parameters for SQLColumns() to only provide the table name and no longer database and owner.

old code:

SQLColumns(m_hstmt,
           (UCHAR FAR *)(const char*)m_strQualifierParam,
           SQL_NTS,
           (UCHAR FAR *)(const char*)m_strOwnerParam,
           SQL_NTS,
           (UCHAR FAR *)(const char*)m_strTableNameParam,
           SQL_NTS,
           NULL,
           SQL_NTS)

new code:

SQLColumns(m_hstmt,
           (UCHAR FAR *)NULL,
           SQL_NTS,
           (UCHAR FAR *)NULL,
           SQL_NTS,
           (UCHAR FAR *)(const char*)m_strTableNameParam,
           SQL_NTS,
           NULL,
           SQL_NTS)

Now the profiler result looks like that when using my new code:

exec [sys].sp_columns_100 N'MyTable',NULL,NULL,NULL,@fUsePattern=1

And it works. It also stills works for MS Access and PostgreSQL.