Data Explorer and Sorting
I use Data Explorer (DX) to browse a SQL Server 2000 database in a project. If I don’t select Sort Objects in the SQL Connection Properties dialog, then I get a list of all the SPROC in random order when I expand the stored procedure node.
If I select Sort Objects, then I get an empty list!
Northwind does not have this problem. Only my database has this problem. So I dig into XSource and narrow down the problem to this line of code in SQLDatabaseMgmt.OnGetStoredProcedures() defined in datamgmt_sql.prg
TEXT TO cSQL TEXTMERGE NOSHOW PRETEXT 7
SELECT * FROM
[<<THIS.DatabaseName>>].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
<<IIF(THIS.SortObjects, "ORDER BY [ROUTINE_NAME]", "")>>
ENDTEXT
After text merged, the sql becomes:
SELECT * FROM [Database].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY [ROUTINE_NAME]
which returns the following error:
Cannot sort a row of size 8295, which is greater than the allowable maximum of 8094.
When I change it to select a subset of the fields instead of all fields:
SELECT Routine_Name, Routine_Schema FROM [Database].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY [ROUTINE_NAME]
It works. So I change the code and recompile the DataExplorer to get it works (Method OnGetFunctions of the same class has the same issue as well).
It didn't work before for my database but works for Northwind may be because my database is originally created in Taiwan locale which makes its INFORMATION_SCHEMA.ROUTINES records having a row size > 8094.