Hidden records?

Hi all,
Using vb6 ado connection I can see the record
e.g. SELECT * FROM table where id=1234
I will see the record
but
Using the same query on Enterprise Manager
the same query results to nothing.
Also
SELECT COUNT(*) AS Expr1
FROM table
Result:
vb6 query:1726 records
Ent. Manager view: 18 records
My questions is why Ent. Manager cannot see all the records
I am using SQL Server 2000 standard edition
The database itselt is nearly 21gb.
Any help or advice appreciated
Regards,
AllanAllan
Are you sure you connected to the same database?
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> Hi all,
> Using vb6 ado connection I can see the record
> e.g. SELECT * FROM table where id=1234
> I will see the record
> but
> Using the same query on Enterprise Manager
> the same query results to nothing.
> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
> My questions is why Ent. Manager cannot see all the records
> I am using SQL Server 2000 standard edition
> The database itselt is nearly 21gb.
> Any help or advice appreciated
> Regards,
> Allan
>|||> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
My guess is that these are different server/databases with the same table.
Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> Hi all,
> Using vb6 ado connection I can see the record
> e.g. SELECT * FROM table where id=1234
> I will see the record
> but
> Using the same query on Enterprise Manager
> the same query results to nothing.
> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
> My questions is why Ent. Manager cannot see all the records
> I am using SQL Server 2000 standard edition
> The database itselt is nearly 21gb.
> Any help or advice appreciated
> Regards,
> Allan
>|||Uri,
Yes. We only have one database. I connect via ip address and database name
"Uri Dimant" wrote:
> Allan
> Are you sure you connected to the same database?
> "Allan" <Allan@.discussions.microsoft.com> wrote in message
> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> > Hi all,
> >
> > Using vb6 ado connection I can see the record
> > e.g. SELECT * FROM table where id=1234
> > I will see the record
> >
> > but
> > Using the same query on Enterprise Manager
> > the same query results to nothing.
> >
> > Also
> > SELECT COUNT(*) AS Expr1
> > FROM table
> >
> > Result:
> > vb6 query:1726 records
> > Ent. Manager view: 18 records
> >
> > My questions is why Ent. Manager cannot see all the records
> >
> > I am using SQL Server 2000 standard edition
> > The database itselt is nearly 21gb.
> >
> > Any help or advice appreciated
> >
> > Regards,
> > Allan
> >
>
>|||Also, after verifying the same servername/databasename, make sure you use the same table by
owner-qualifying the name, like
SELECT COUNT(*) FROM dbo.table
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:2D08ED0C-D379-4DE6-97E1-3A97037EA0AE@.microsoft.com...
>> Also
>> SELECT COUNT(*) AS Expr1
>> FROM table
>> Result:
>> vb6 query:1726 records
>> Ent. Manager view: 18 records
> My guess is that these are different server/databases with the same table. Try verifying
> connection context with SELECT @.@.SERVERNAME, DB_NAME().
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan" <Allan@.discussions.microsoft.com> wrote in message
> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
>> Hi all,
>> Using vb6 ado connection I can see the record
>> e.g. SELECT * FROM table where id=1234
>> I will see the record
>> but
>> Using the same query on Enterprise Manager
>> the same query results to nothing.
>> Also
>> SELECT COUNT(*) AS Expr1
>> FROM table
>> Result:
>> vb6 query:1726 records
>> Ent. Manager view: 18 records
>> My questions is why Ent. Manager cannot see all the records
>> I am using SQL Server 2000 standard edition
>> The database itselt is nearly 21gb.
>> Any help or advice appreciated
>> Regards,
>> Allan
>|||Hi Dan
Same reply as Uri
We only have one database. I connect via ip address and database name
I wish that was the case and the problem is solved
Will the size of the database (21 gb) makes any difference to the standard
edition of SQL server?
How about data corruption?
Thank you for your interest
"Dan Guzman" wrote:
> > Also
> > SELECT COUNT(*) AS Expr1
> > FROM table
> >
> > Result:
> > vb6 query:1726 records
> > Ent. Manager view: 18 records
> My guess is that these are different server/databases with the same table.
> Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan" <Allan@.discussions.microsoft.com> wrote in message
> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> > Hi all,
> >
> > Using vb6 ado connection I can see the record
> > e.g. SELECT * FROM table where id=1234
> > I will see the record
> >
> > but
> > Using the same query on Enterprise Manager
> > the same query results to nothing.
> >
> > Also
> > SELECT COUNT(*) AS Expr1
> > FROM table
> >
> > Result:
> > vb6 query:1726 records
> > Ent. Manager view: 18 records
> >
> > My questions is why Ent. Manager cannot see all the records
> >
> > I am using SQL Server 2000 standard edition
> > The database itselt is nearly 21gb.
> >
> > Any help or advice appreciated
> >
> > Regards,
> > Allan
> >
>|||"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:FE6482BA-6070-40EE-AC99-ABDA230A42BD@.microsoft.com...
> Hi Dan
> Same reply as Uri
> We only have one database. I connect via ip address and database name
> I wish that was the case and the problem is solved
> Will the size of the database (21 gb) makes any difference to the standard
> edition of SQL server?
Not at all.
> How about data corruption?
Highly unlikely that a query one place would return one set of data and the
same query from another place would return different data.
So sounds like something is missing in your description (like a table with
the same name was accidentally created in the MASTER DB and some records
inserted and when you connect the 2nd way you're hitting master, not your
database and seeing the wrong table.
As other have said, to be 100% sure, do a select * from
<dbname>.<owner>.tablename.
> Thank you for your interest
> "Dan Guzman" wrote:
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> My guess is that these are different server/databases with the same
>> table.
>> Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Allan" <Allan@.discussions.microsoft.com> wrote in message
>> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
>> > Hi all,
>> >
>> > Using vb6 ado connection I can see the record
>> > e.g. SELECT * FROM table where id=1234
>> > I will see the record
>> >
>> > but
>> > Using the same query on Enterprise Manager
>> > the same query results to nothing.
>> >
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> >
>> > My questions is why Ent. Manager cannot see all the records
>> >
>> > I am using SQL Server 2000 standard edition
>> > The database itselt is nearly 21gb.
>> >
>> > Any help or advice appreciated
>> >
>> > Regards,
>> > Allan
>> >
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||I think Tibor's idea that you have different tables in different schema is
likely. The Best Practice is to always schema-qualify table names. Not
only does this avoid ambiguous references, it helps improve performance too.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:FE6482BA-6070-40EE-AC99-ABDA230A42BD@.microsoft.com...
> Hi Dan
> Same reply as Uri
> We only have one database. I connect via ip address and database name
> I wish that was the case and the problem is solved
> Will the size of the database (21 gb) makes any difference to the standard
> edition of SQL server?
> How about data corruption?
> Thank you for your interest
> "Dan Guzman" wrote:
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> My guess is that these are different server/databases with the same
>> table.
>> Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Allan" <Allan@.discussions.microsoft.com> wrote in message
>> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
>> > Hi all,
>> >
>> > Using vb6 ado connection I can see the record
>> > e.g. SELECT * FROM table where id=1234
>> > I will see the record
>> >
>> > but
>> > Using the same query on Enterprise Manager
>> > the same query results to nothing.
>> >
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> >
>> > My questions is why Ent. Manager cannot see all the records
>> >
>> > I am using SQL Server 2000 standard edition
>> > The database itselt is nearly 21gb.
>> >
>> > Any help or advice appreciated
>> >
>> > Regards,
>> > Allan
>> >|||To Uri,Dan,Greg and Tibor,
Thank for all your help. It was decided to restore from last nights backup
and redo todays activities.
After restore queries from vb6 and Ent. Manager equalled
I am printing your advice for future reference
Regards,
Allan
"Allan" wrote:
> Hi all,
> Using vb6 ado connection I can see the record
> e.g. SELECT * FROM table where id=1234
> I will see the record
> but
> Using the same query on Enterprise Manager
> the same query results to nothing.
> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
> My questions is why Ent. Manager cannot see all the records
> I am using SQL Server 2000 standard edition
> The database itselt is nearly 21gb.
> Any help or advice appreciated
> Regards,
> Allan
>|||> Not only does this avoid ambiguous references, it helps improve
> performance too.
Is the performance gain documented any where?
/Sjang|||On Sep 3, 10:46 am, "Henrik Davidsen" <n...@.none.dk> wrote:
> > Not only does this avoid ambiguous references, it helps improve
> > performance too.
> Is the performance gain documented any where?
> /Sjang
You can see for yourself easily:
DECLARE @.i INT, @.SQL NVARCHAR(300)
SELECT @.i = 0
WHILE @.i < 10000 BEGIN
SET @.i = @.i + 1
SET @.SQL = 'CREATE PROCEDURE dbo.DummyProc' + CAST(@.i AS
NVARCHAR(10)) + ' AS RETURN 0'
EXEC sp_executesql @.SQL
END
GO
DECLARE @.i INT, @.j INT, @.d DATETIME, @.SQL NVARCHAR(300), @.SQL_to_run
NVARCHAR(300)
SELECT @.i = 0, @.j = 0, @.d = getdate()
WHILE @.j < 3 BEGIN
SET @.j = @.j + 1
IF @.j = 1 BEGIN
SET @.SQL = 'EXEC DummyProc'
END ELSE BEGIN
SET @.SQL = 'EXEC dbo.DummyProc'
END
WHILE @.i < 10000 BEGIN
SET @.i = @.i + 1
SET @.SQL_to_run = @.SQL + CAST(@.i AS NVARCHAR(10))
--PRINT @.SQL_to_run
IF @.j <3 BEGIN
EXEC sp_executesql @.SQL_to_run
END
END
SELECT DATEDIFF(ms, @.d, GEtdate()) as ms
SELECT @.i = 0, @.d = getdate()
END
GO
DECLARE @.i INT, @.SQL NVARCHAR(300)
SELECT @.i = 0
WHILE @.i < 10000 BEGIN
SET @.i = @.i + 1
SET @.SQL = 'DROP PROCEDURE dbo.DummyProc' + CAST(@.i AS
NVARCHAR(10))
EXEC sp_executesql @.SQL
END
GO
-- without dbo.
ms
--
3860
(1 row(s) affected)
-- with dbo
ms
--
640
(1 row(s) affected)
-- the overhead of running the loop
ms
--
46|||On Mon, 3 Sep 2007 17:46:30 +0200, "Henrik Davidsen" <none@.none.dk>
wrote:
>> Not only does this avoid ambiguous references, it helps improve
>> performance too.
>Is the performance gain documented any where?
Don't have a link handy, but it's mentioned in various white papers,
also just saw it mentioned in Kalen's "Storage Engine 2005" book, near
the middle, I'm too lazy to look up the page!
Per Kalen, in SQL2005 apparently it helps even a little more, if you
don't specify then there is more that SQL2005 does before it decides
you meant dbo. all along.
OK it's a small effect, but on a high-transaction system it might get
you a few TPS, and also there may be something of an increase in the
use of namespaces generally with 2005, so it might be a good practice
just on that basis.
Josh|||You *could* have had an open transaction in snapshot mode on 2005, ...
but it does sound more like corruption. Did you have any crashes
recently? Did you try running DBCC CHECKDB? Was this on a production
system? It's really a table and not a view with some kind of weird
user-sensitive parameters on it? Were you using the same login in
both cases? Was it repeatable - you could log off and log back in
again in both situations and see the same conflicting results?
I know it's moot now, but nobody likes that sort of anomaly.
J.
On Mon, 3 Sep 2007 07:00:00 -0700, Allan
<Allan@.discussions.microsoft.com> wrote:
>Hi all,
>Using vb6 ado connection I can see the record
>e.g. SELECT * FROM table where id=1234
>I will see the record
>but
>Using the same query on Enterprise Manager
>the same query results to nothing.
>Also
>SELECT COUNT(*) AS Expr1
>FROM table
>Result:
>vb6 query:1726 records
>Ent. Manager view: 18 records
>My questions is why Ent. Manager cannot see all the records
>I am using SQL Server 2000 standard edition
>The database itselt is nearly 21gb.
>Any help or advice appreciated
>Regards,
>Allan|||Hi J,
"JXStern" wrote:
> You *could* have had an open transaction in snapshot mode on 2005, ...
We are using SQL Server 2000 standard edition
> but it does sound more like corruption. Did you have any crashes
> recently?
No crashes
Did you try running DBCC CHECKDB?
No.
Was this on a production system?
We are very small company. Production and test system is the same.
It's really a table and not a view with some kind of weird user-sensitive
parameters on it?
Yes it is a table. The query was taken (copied and pasted) directly from vb6
source code that hasn't been changed for 3 years
Were you using the same login in both cases?
There is only one login "sa"
Was it repeatable - you could log off and log back in again in both
situations and see the same conflicting results?
We restarted the SQL server 2000. Same result.
Restarted Windows Server 2003 standard edition. Same result
> I know it's moot now, but nobody likes that sort of anomaly.
> J.
>
> On Mon, 3 Sep 2007 07:00:00 -0700, Allan
> <Allan@.discussions.microsoft.com> wrote:
> >Hi all,
> >
> >Using vb6 ado connection I can see the record
> >e.g. SELECT * FROM table where id=1234
> >I will see the record
> >
> >but
> >Using the same query on Enterprise Manager
> >the same query results to nothing.
> >
> >Also
> >SELECT COUNT(*) AS Expr1
> >FROM table
> >
> >Result:
> >vb6 query:1726 records
> >Ent. Manager view: 18 records
> >
> >My questions is why Ent. Manager cannot see all the records
> >
> >I am using SQL Server 2000 standard edition
> >The database itselt is nearly 21gb.
> >
> >Any help or advice appreciated
> >
> >Regards,
> >Allan
>