distal-attribute
distal-attribute
distal-attribute
distal-attribute

MSSQL Error

User, date Message
Written by aabadi
3 years ago
Category: Creating a connection
2 posts since Wed, 06 Apr 11
Since you've starting with the MSSQL connection support Ive tried to start using it.
It is working, but in my case every time connect to the database I receive the following error message:
"SQL Error(208): El nombre de objecto 'sys.sysprocesses' no es valido.
(In english... the object name 'sys.sysprocesses' is not valid)

Eventhough that it let me in and run my queries, but Im not able to see the tables, and views.

Thanks in advance
Ariel
Written by ansgar
3 years ago
4987 posts since Fri, 07 Apr 06
Server version? How to get the current thread id on your server?

I think many of these system views are available only on MSSQL 2005 or even 2008. I'm quite unfamiliar with SQL Server so please point me in the right direction if you have an alternative.
Written by gabelde
3 years ago
3 posts since Thu, 16 Jun 11
I've got the same error - with the SQL Server Management Studio 2005 it works... as exptected. :D

SQL Error (208): Ung├╝ltiger Objektname 'sys.sysprocesses'.



MSSQL
Version: 8.00.2039
Name: SQL Server 2000 Service Pack 4 (SP4)
Written by ansgar
3 years ago
4987 posts since Fri, 07 Apr 06
Some idea how to get the thread id on a 2005 server?
Written by gabelde
3 years ago
3 posts since Thu, 16 Jun 11
Just to ensure, it is a SQL Server 2000 SP4, but i manage it with the SQL 2005 Management Studio

In this SQL2000 Server the table is called dbo.sysprocesses but it should have a spid and kpid (windows thread).


MSDN Reference on sys.sysprocesses:
http://msdn.microsoft.com/de-de/library/ms179881(v=SQL.90).aspx
Written by ansgar
3 years ago
4987 posts since Fri, 07 Apr 06
So the query should look like this on old servers, right?

SELECT spid FROM dbo.sysprocesses ...

Written by gabelde
3 years ago
3 posts since Thu, 16 Jun 11
Yep, sorry I could have checked this already. But it's friday so my helpfulness is pretty limited. wink

This works like a charm:

SELECT DATEDIFF(SECOND, "login_time", CURRENT_TIMESTAMP) FROM "dbo"."sysprocesses" WHERE "spid"=1;

Written by ansgar
3 years ago
4987 posts since Fri, 07 Apr 06
Super, that definitely helps out. Will put that as a version-specific query into the connection layer.
Written by ansgar
3 years ago
4987 posts since Fri, 07 Apr 06
I noticed that dbo.sysprocesses is also available on a 2008 server, while I'm unsure if I recall right that was deprecated for some reason. For now r3888 always uses dbo instead of sys here.
Written by roxwal
3 years ago
3 posts since Fri, 26 Nov 10
for sql2000 can change:

SELECT "name" FROM "sys"."databases" ORDER BY "name";
/* SQL Error (208): Invalid object name 'sys.databases'. */

in

SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
ORDER BY CATALOG_NAME


tks W:-}
Written by ansgar
3 years ago
4987 posts since Fri, 07 Apr 06
Hm, on a 2008 server this returns 13 times "master". Is that normal and does it return only appropriate rows on a 2k server?
Written by roxwal
3 years ago
3 posts since Fri, 26 Nov 10
in my "ms sql 2000" have one line for DB
you can use so

SELECT distinct CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
ORDER BY CATALOG_NAME

some detail:
http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-list-the-databases-on-my-server.html

W:-}
 

Please login to leave a reply, or register at first.