Problem on "Select count(*)"

HI,

We have one table have problem when run "select count(*) from table". This is a sql 2000 server. it returned different count everytime when I ran the "select count(*) from table". I used DBCC to check the table and did not find any problems. I can fix it by recreate the table and reimport the data, but the problem come back after we update some data in the table monthly with a stored procedure. Any suggestion?

Thanks

Sand

Try fully qualifying the table name (database.schema.table) and using a new connection from the same PC each time you exeucte the select count(*)

You want to make sure you are selecting from the same database on the same server from the same table everytime. And a new connection to make sure you don't have rowcount or some other option interfering.

-Sue

problem inserting data in table - all rows are not returned

The procedure is inserting data into this table :
create table temp_XML
(xml_data varchar(8000))
I ran the trace and proc is running fine. But all the data is not
returned when I do select * from temp_xml.
I did some research and limited data by using a where clause.
The proc ran fine in some cases and returns all data and does not runs
fine in some where clauses. What do I need to do to return all the
rows.
But the proc runs fine in production without any limiting where clause
I have different procs like this and some run fine in development and
not in production and vice versa. The procs have successfully ran in
the development before also.
any sugesstions on how to fix this.VJ wrote:
> The procedure is inserting data into this table :
> create table temp_XML
> (xml_data varchar(8000))
> I ran the trace and proc is running fine. But all the data is not
> returned when I do select * from temp_xml.
> I did some research and limited data by using a where clause.
> The proc ran fine in some cases and returns all data and does not runs
> fine in some where clauses. What do I need to do to return all the
> rows.
> But the proc runs fine in production without any limiting where clause
> I have different procs like this and some run fine in development and
> not in production and vice versa. The procs have successfully ran in
> the development before also.
> any sugesstions on how to fix this.
Check if there is a faulty trigger on the table, especially one that
can't handle multiple row inserts. Also, are you sure you are capturing
any error message that the proc might be generating?
If you need more help, please post your code if you can.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||There are no triggers in the proc.
The proc is running fine in the other database. Something is limiting
the rows inserted in the development database now, but it was fine a
few minutes ago.
Please let me know your email address and I will send you the proc.
David Portas wrote:
> VJ wrote:
> Check if there is a faulty trigger on the table, especially one that
> can't handle multiple row inserts. Also, are you sure you are capturing
> any error message that the proc might be generating?
> If you need more help, please post your code if you can.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --