I have a strange problem that when using PHP
to connect to a MSSQL
database using freetds
the query returns no data.
I have two SQL
queries:
SELECT TOP 100 *
FROM [TBL_Contact] AS C
LEFT JOIN [TBL_Address] AS A
ON (C.[ContactID] = A.[ContactID])
;
and
SELECT TOP 100
N.[NoteID],
N.[NoteTypeID],
N.[IsPrivate],
CAST(N.[NoteText] AS TEXT) AS [NoteText],
N.[ManageUserID],
N.[DisplayDate],
N.[CreateUserID],
N.[CreateDateE],
N.[EditUserID],
N.[EditDate]
FROM [TBL_Note] AS N
LEFT JOIN [TBL_Contact_Note] AS C
ON (C.[NoteID] = N.[NoteID])
;
When run directly on the MSSQL
Server they both work as expected and return data. However when running them via PHP by wrapping them in a mssql_query()
function call.
$result = mssql_query('
SELECT TOP 100 *
FROM [TBL_Contact] AS C
LEFT JOIN [TBL_Address] AS A
ON (C.[ContactID] = A.[ContactID])
;
');
$result1 = mssql_query('
SELECT TOP 100
N.[NoteID],
N.[NoteTypeID],
N.[IsPrivate],
CAST(N.[NoteText] AS TEXT) AS [NoteText],
N.[ManageUserID],
N.[DisplayDate],
N.[CreateUserID],
N.[CreateDateE],
N.[EditUserID],
N.[EditDate]
FROM [TBL_Note] AS N
LEFT JOIN [TBL_Contact_Note] AS C
ON (C.[NoteID] = N.[NoteID])
;
');
Then only the first one returns data.
I have experimented by removing fields from the query and have found that it is the smalldatetime
field N.[DisplayDate]
that is causing the issue. When I run the query with that field removed then the mssql_query()
call works.