Particular query causes ADO Recordset to fail with E_FAIL

i'm trying to run (two) queries against SQL Server 2005 using ADO:

SELECT
   sp.nt_username AS NTUsername,
   sp.hostname AS HostName
FROM sys.dm_tran_locks tl
   INNER JOIN master.dbo.sysprocesses sp
   ON tl.request_session_id = sp.spid
WHERE tl.resource_type = N'APPLICATION'
AND tl.resource_database_id = (
       SELECT dbid
       FROM master.dbo.sysprocesses
       WHERE spid = @@spid)

and the SQL Server 2000 compatible version:

SELECT
   sp.nt_username AS NTUsername,
   sp.hostname AS HostName
FROM master.dbo.syslockinfo sli
   INNER JOIN master.dbo.sysprocesses sp
   ON sli.req_spid = sp.spid
WHERE rsc_type = 10 --10=Appliction
AND rsc_dbid = (
   SELECT dbid
   FROM master.dbo.sysprocesses
   WHERE spid = @@spid)

The query executes fine:

IRecordset rs =new Recordset();
rs.CursorLocation = adUseClient; //the default for a Recordset is adUseServer (Connection.Execute's default is adUseClient)
rs.CursorType := adOpenForwardOnly; //the default
rs.Open(szQuery, conn.ConnectionObject,
       adOpenForwardOnly, //CursorType
       adLockReadOnly, //LockType
       adCmdText);

But then checking the status of BOF or EOF:

if (rs.EOF) then

triggers an exception:

• HRESULT: 0x80004005 (Unspecified error)
• Error message: "Data provider or other service returned an E_FAIL status"

What is about this query that causes ADO to fail so miserably?

---

Note: There really is a matching row:

The provider i'm using in the connection string is Provider=SQLOLEDB.

Bonus Chatter:

sysprocesses

  nt_username nchar(128)
  hostname    nchar(128)

and querying for both - but one at a time - works, e.g.:

SELECT
   sp.nt_username AS NTUsername

and

SELECT
   sp.hostname AS HostName

work. Querying for both fail.

i found the problem. It's a known issue with SQL Server 2005 and ADO:

Even though nt_username and hostname already are nchar(128), you need to manually cast them to nchar(128)

SELECT
   CAST(sp.nt_username AS nchar(128)) AS NTUsername,
   CAST(sp.hostname AS nchar(128)) AS HostName
FROM sys.dm_tran_locks tl
   INNER JOIN master.dbo.sysprocesses sp
   ON tl.request_session_id = sp.spid
WHERE tl.resource_type = N'APPLICATION'
AND tl.resource_database_id = (
       SELECT dbid
       FROM master.dbo.sysprocesses
       WHERE spid = @@spid)

There is also a known bug with SQL Server 2005 and ADO and the use of @@spid in a query; you cannot do it. Fortunately i can, in this case, use DB_ID():

SELECT
   CAST(sp.nt_username AS nchar(128)) AS NTUsername,
   CAST(sp.hostname AS nchar(128)) AS HostName
FROM sys.dm_tran_locks tl
   INNER JOIN master.dbo.sysprocesses sp
   ON tl.request_session_id = sp.spid
WHERE tl.resource_type = N'APPLICATION'
AND tl.resource_database_id = DB_ID()

Bada-bing.

SQL Server 2005 bug.

It might still exist in SQL Server 2008, SQL Server 2008 R2, SQL Server 2012