SQL 2005 does not work with ADO query on SYS.sysprocesses after Update installed from Microsoft

SQL Server Data Access

Bio2


str = "select nt_username, hostname, nt_domain, loginame, login_time, program_name " &
"from master.sys.sysprocesses where spid=@@SPID"

-This is the Query to display the username in the application.

-The client is MS-Access 2003, MDAC 2.8

-It was and is Ok both on SQL 2005 and SQL 2000. The qury runs under Query Analyzer 2000 or Management Studio 2005 without problem

-After Downloading the updates from Microsot Website. The embedded code returns no result set:

Data provider or other service returned an E_FAIL status

If anybody has any idea, I will be thankful.

Top

Re: SQL 2005 does not work with ADO query on SYS.sysprocesses after Update installed from Microsoft

Anton Klimov - MSFT


What is the connection string you are using





Top

Re: SQL 2005 does not work with ADO query on SYS.sysprocesses after Update installed from Microsoft

Bio2

strConnect = "PROVIDER=SQLOLEDB.1;Integrated Security=SSPI;INITIAL CATALOG=" & dbname & ";DATA SOURCE=" & srvname



Top

Re: SQL 2005 does not work with ADO query on SYS.sysprocesses after Update installed from Microsoft

Anton Klimov - MSFT

Yes, this is a problem with the backward compatibility.
The workarounds are:
1) modify your query to:
"select cast (nt_username as nchar(128)), hostname, cast (nt_domain as nchar(128)), loginame, program_name ...
2) use SQLNCLI instead of SQLOLEDB.




Top

Re: SQL 2005 does not work with ADO query on SYS.sysprocesses after Update installed from Microsoft

Bio2

Well , this is not a connection problem as you address. If I use SQLNCLI then the whole connection fails at all and not works. ( I just replaced SQLNCLI instead of SQLOLEDB.1)

it is a query execution problem and it happened after updating of SQL- Server 2005. so it was working fine under 2005 before updates.

The Current connection string works fine on both 2000 and 2005, My development PC has 2005 and MSDE 2005

The same code will be comiled and operate under SQL-2000 on the user side.

A resume next statement solves the problem at this time for the code and user name have to be entered manually.

As I mentioned, The query works under Mangement Studio 2005 so there is no Syntax problem.




Top

Re: SQL 2005 does not work with ADO query on SYS.sysprocesses after Update installed from Microsoft

Bio2

What I finaly found :

The problem is caused by where clause in ADO query String : WHERE SPID = @@SPID

The @@SPID does not work under ADO or OLEDB where clause So I solved the problem this way :

With Two recordset one for @@SPID and the other for query without where clause


- rs1: Select @@SPID as P1")
- rs2: SELECT DISTINCT nt_username , hostname, nt_domain , loginame, SPID FROM master.sys.sysprocesses
- After Openning recordsets
rs2.Find "SPID = " & rs1("P1")


Top

Re: SQL 2005 does not work with ADO query on SYS.sysprocesses after Update installed from Microsoft

Anton Klimov - MSFT

This is pretty strange because I tried this query in ADO and it worked fine for me.
Did the modified query with the casts which I suggested fail for you, or have you just decided not to give it a try
It is also not clear why SQLNCLI would fail, unless it is not installed on your machine at all.





Top

Re: SQL 2005 does not work with ADO query on SYS.sysprocesses after Update installed from Microsoft

William W1

I can duplicate this problem exactly, and have a VBS script with which to demonstrate. It works perfectly against a SQL2000sp4 installation, but fails (0 rows) against a SQL2005sp2 (no hotfixes) installation.

---- snip ----
option explicit
dim oConn, oRS, a1, a2
set oConn = createobject("adodb.connection")
oConn.Open "Provider=SQLOLEDB;Integrated Security=SSPI;Application Name=SysProcessesTest;Initial Catalog=master;Data Source=" & wscript.arguments(0)
oConn.CursorLocation = 3

set oRS = createobject("adodb.recordset")
oRS.Open "select * from master.dbo.sysprocesses", oConn, 0

a1 = 0
a2 = ""
do while not oRS.EOF
a1 = a1 + 1
if a2 = "" then a2 = a2 & oRS("spid") else a2 = a2 & ", " & oRS("spid")
oRS.movenext
loop

wscript.echo a1 & " processes found" & vbcrlf & a2
---- snip ----

It is possible to get rows from the sysprocesses table using ADO, but it seems you have to use a server-side static cursor. It seems really weird to me that the DB engine can even be sensitive to such things.






Top