Re: How to Interrogate SQL Server Tables for Specific Values - Here"s

Thanks! I'll check it out. To answer your question about why one
would need such a routine. I have a situation where I was getting an
error message "The path cannot be found" when trying to open SQL
Server 2000. Researing the error I found that that the error message
was usually associated with ISS. I don't have ISS loaded. Anyway, in
the interest of time I uninstalled/reinstalled SQL Server. After
bringing it back in session the user said that some of his data is
missing. He provided me with two accounts to search for. One that he
can find through his application and one that he cannot -- that he
knows was entered.

This is a very poor designed database which creates tables daily and
does not archive anything. As a result it has grown in excess of 65+
Gig in size. It literally has thousands of tables and the user could
not tell me the approximate date when the missing values were added.
That's when I thought of the MS Access routine that I have used in the
past that works great with MS Access (small DBs). I tried linking to
the SQL database and running it, but the workstation didn't have
enough horsepower to do anything with it.

I will try your routine. Will it work with SQL Server 2000? This
looks great! Thanks for your help.

RBolling

Re: How to Interrogate SQL Server Tables for Specific Values - Here"s how to do it in MS Access

robboll (robboll@hotmail.com) writes:
> This is a very poor designed database which creates tables daily and
> does not archive anything. As a result it has grown in excess of 65+
> Gig in size. It literally has thousands of tables and the user could
> not tell me the approximate date when the missing values were added.
> That's when I thought of the MS Access routine that I have used in the
> past that works great with MS Access (small DBs). I tried linking to
> the SQL database and running it, but the workstation didn't have
> enough horsepower to do anything with it.

As I almost said. "One tends to think that if you need to [do] this, your
database design might [not] be what it ought to be."

(Some words were missing.)

> I will try your routine. Will it work with SQL Server 2000? This
> looks great! Thanks for your help.

No, it will not work on SQL 2000 at all, as it uses several features
that were added in SQL 2005. In SQL 2000, you would need to run nested
cursors over the system tables.

You should always state which version of SQL Server you are using.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx