I have a function in VB to check if the given table exists in SQL Server 7 Database

1.Using ADOX catalog as follows:
 Dim CatlogObj New ADOX.CatalogADOX.Catalog
 For i = 0 To CatObj.Tables.Count - 1
 'check if name present in the
 If UCase(CatlogObj.Tables(i).Name) = UCase(TableName) Then
     TableExists = True
     Exit Function
 End If

I found that using this function for database of 24,000 tables,the method returns false though the physical table is present.Table is getting created and after 45-60 secs the code checks if table exists.
This happens only when running 2 jobs in parallel and both are creating different tables in DB

Is it because somehow the catalog is not getting refreshed view of database or something like that.


I have another approach to do the same as follows:
2.Querying "sysobjects" table
  SELECT COUNT (NAME) AS CNT FROM SYSOBJECTS OBJ WHERE OBJ.NAME='CUSTOMER'
 and then if recodset has >0 records return true;

But I am not sure if "sysobjects" will be refreshed with that entry when function call is made.

Any suggestions are welcome

Thanks
Meghana

Hi,

Yes you can use the second approach. Just add to the query:

SELECT COUNT (NAME) AS CNT FROM SYSOBJECTS OBJ WHERE OBJ.NAME='CUSTOMER' AND XTYPE = 'U'
Hope this helps,

Lalit
Life Means More...;)