Cannot Enumerate SQL 2000 Tables using SMO

24 Aug 2006 2:51 PM

Jamie Carper

I am new to SMO. I was using SQL DMO prior to this. I am now working with
Visual Studio 2005 and using SMO (the .NET equivalent to SQL DMO) to access a
SQL 2000 server.

I have been able to Enumerate all instances of Servers and I have been able
to enumerate all instances of databases within each server. I have been even
able to connect to a database. However when I attempt to enumerate the tables
within the database I receive an error:

EnumeratorException was unhandled "Failed to retrieve data for this request".

I get this error even when all I am asking for is a simple count of the
tables.

e.g. 1

Dim ActiveTable as Table
For Each ActiveTable in MyDatabase.Tables
....
Next ActiveTable

e.g. 2

MsgBox CStr(MyDatabase.Tables.Count)

Can anyone shed any light on the subject? How can I debug this when the
error message is so vauge? Any input would be appreciated.

24 Aug 2006 5:10 PM

Jamie Carper

Found my problem or at least a workaround.

The table collection was failing whenever I used attempted a SQL login. When
I used a Windows Authentication login I was able to enumerate thru the tables.

So the code was simple:

Dim MyServer as New Server ' Defaults to my local instance
Dim MyDatabase as Database
Dim ActiveTable as Table

MyServer.ConnectionContext.LoginSecure = "True" ' This is the default setting

MyDatabase = MyServer.Databases("MyDatabaseName")

For Each ActiveTable In MyDatabase.Tables
....
Next ActiveTable

Show quote

"Jamie Carper" wrote:

> I am new to SMO. I was using SQL DMO prior to this. I am now working with
> Visual Studio 2005 and using SMO (the .NET equivalent to SQL DMO) to access a
> SQL 2000 server.
>
> I have been able to Enumerate all instances of Servers and I have been able
> to enumerate all instances of databases within each server. I have been even
> able to connect to a database. However when I attempt to enumerate the tables
> within the database I receive an error:
>
> EnumeratorException was unhandled "Failed to retrieve data for this request".
>
> I get this error even when all I am asking for is a simple count of the
> tables.
>
> e.g. 1
>
> Dim ActiveTable as Table
> For Each ActiveTable in MyDatabase.Tables
> ...
> Next ActiveTable
>
> e.g. 2
>
> MsgBox CStr(MyDatabase.Tables.Count)
>
> Can anyone shed any light on the subject? How can I debug this when the
> error message is so vauge? Any input would be appreciated.
>