Why does Count(*) return NULL in ODBC connection to sql server?

Question

I need to execute a simple sql query on sql server database with ODBC. The sql code is below:

insert into mylogint
EXEC(
'select count(*) from mymessage'
)

mylogint is a simple table:

create table mylogint
(value int)

But sometimes I found that table mylogint is empty, because count() returned NULL when the ODBC connected a sql server instance. I mean that the count() returned NULL rather than int, and I'm sure that mymessage isn't empty. I found that the count(*) returned NULL on some sql instances, returned int on other instances.

Do you know why count(*) returned NULL in this case? What's the possible reason? Thanks.

Answer 1

count(*) should never return NULL. It is possible for a subquery to return no rows. This could result in what seems like NULL values from the subquery, but this is really coming from the lack of rows (an unfortunate confusion in SQL).

Your query has to return one row -- that is how aggregation queries with SQL work.

You are saying that mylogint is empty. That is very different. That suggests that you are getting an error when running the query. This could be a permissions error, invalid table name, syntax error, and so on.