Can't connect MS SQL 7

[expired user #6596]'s profile image [expired user #6596] posted 12 years ago in General Permalink
Hi,

Using HeidiSQL 7.0.0.4197 I tried to connect MS SQL for the first time but can't on MSSQL 7, "sys.databases" don't exists on MSSQL 7 and HeidiSQL can't process connection.

HeidiSQL connect successfully to MSSQL 8 :

/* Entering session "IPCC" */
/* Connecting to 10.0.0.8 via Microsoft SQL Server (TCP/IP, experimental), username sa, using password: Yes ... */
SELECT @@SPID;
/* Connected. Thread-ID: 54 */
SELECT DATEDIFF(SECOND, "login_time", CURRENT_TIMESTAMP) FROM "master"."dbo"."sysprocesses" WHERE "spid"=1;
SELECT @@VERSION;
SELECT "name" FROM "master".."sysdatabases" ORDER BY "name";

but not MSSQL 7 :

/* Connecting to 10.0.0.7 via Microsoft SQL Server (TCP/IP, experimental), username sa, using password: Yes ... */
SELECT @@SPID;
/* Connected. Thread-ID: 23 */
SELECT DATEDIFF(SECOND, "login_time", CURRENT_TIMESTAMP) FROM "master"."dbo"."sysprocesses" WHERE "spid"=1;
SELECT @@VERSION;
SELECT "name" FROM "sys"."databases" ORDER BY "name";
/* SQL Error (208): Invalid object name 'sys.databases'. */

regards.
jfalch's profile image jfalch posted 11 years ago Permalink
SELECT "name" FROM "sys"."databases" ORDER BY "name";
will fetch the names of the databases defined within the server on MSSQL 8+. Do you know what SQL statement produces a similar list of database names in MSSQL 7 ?
[expired user #6596]'s profile image [expired user #6596] posted 11 years ago Permalink
SELECT name FROM master..sysdatabases

double dot ".." is important.
jfalch's profile image jfalch posted 11 years ago Permalink
and what does SELECT @@VERSION return with that server ?
[expired user #6596]'s profile image [expired user #6596] posted 11 years ago Permalink
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
May 29 2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
MSDE on Windows NT 5.0 (Build 2195: Service Pack 4)
ansgar's profile image ansgar posted 11 years ago Permalink
Oh, thanks to jfalch for asking exactly the questions as I would have done if I hadn't been so lazy :)

Ah, is that right - each and every version of MS SQL seems to bring its own approach of fetching database names? Well...

dodfr, can you confirm that HeidiSQL reports "MS SQL 1094" in the status bar? The current logic expects and searches for a 4 digit string in the result of SELECT @@VERSION. I guess I need to fix that first before I can add another exception for MS SQL 7 when reading database names.
jfalch's profile image jfalch posted 11 years ago Permalink
MS major release policy: "A foolish consistency is the hobgoblin of little minds" (RWE) ...
[expired user #6596]'s profile image [expired user #6596] posted 11 years ago Permalink
I confirm MSSQL 1094
Code modification/commit from ansgar.becker, 11 years ago, revision 7.0.0.4200
Get away from using "2008", "2000" version numbers for MSSQL, to add compatibility to old MS SQL servers, e.g. 7.0 had no year-styled version. Instead, use 700 for 7.0, 800 for 2000, and so on. Use these more exact version numbers to fix a 7.0 issue in TAdoDBConnection.GetAllDatabases. See http://www.heidisql.com/forum.php?t=11230.
ansgar's profile image ansgar posted 11 years ago Permalink
Fixed in Heidi r4200. HeidiSQL should report now "MS SQL 7.0" in your status bar. Also, v2000 is now displayed as "MS SQL 8.0", and so on.

See also http://support.microsoft.com/kb/321185 for shedding some light on year => version numbers.
[expired user #6596]'s profile image [expired user #6596] posted 11 years ago Permalink
Tested ... OK !

Tnx.

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