February 14, 2005 at 10:51 am
I have recently begun having problems with a single line of VB6 code. I'm using the OpenSchema (Information Schema Views) method of ADO to retrieve a list of tables in an SQL database. At some point this worked very quickly without any problems.
Just recently it now takes well over 1 minute to run. I'm running SQL 2000 sp3 and have un-install and re-installed it several times. I'm also running MDAC 2.8 (ran component checker). I also have .NET installed. Also this code runs fine on other computers, other computers can access my SQL Server with this code and it works fine. I have the same slow issue when accessing other computers SQL servers.
All issues point to my machine. I don't believe it's my SQL Server but something else. Maybe my ODBC, MDAC etc...
If anyone has some suggestions it would be greatly appreciated.
CODE & CONNECT STRING USED...
Reference in Project: Microsoft ActiveX Data Objects 2.7 Library
Connection string;
Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=myODBCsoure;Extended Properties="DSN=myODBCsoure;Description=Test;UID=sa;APP=Visual Basic;WSID=Development;DATABASE=myDatabase";Initial Catalog=myDatabase
CODE:
Dim tblSchema As adodb.Recordset
Set tblSchema = InConnection.OpenSchema(adSchemaTables) <<<< TAKES OVER 1 MINUTE!!!!
Thanks in advance
February 15, 2005 at 4:50 am
Instead of using 'OpenSchema' use the SQL statement
select name from sysobjects where xtype = 'U' or xtype = 'S'
if you want both system tables and user tables or
select name from sysobjects where xtype = 'U'
if you only want user tables
February 15, 2005 at 11:37 am
Thanks for the response, but I can't do that because I have the same code base that run under both SQL Server and MSAccess.
February 15, 2005 at 3:54 pm
Just figured it out. I had, somehow, set the registry "TraceODBCAPI" setting to ON. All my activity was being logged.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply