@@IDENTITY issues with SQL2000 on GoDaddy

Okay... GoDaddy's driving menuts!!! Anyone... please help.

I am doing an insert into a database with:

INSERT blah blah blah; SELECT @.@.IDENTITY;

On EVERY OTHER SERVER I have tried this on (local SQLExpress 2005's and my companies various SQL2000 servers, this always returns for me the row number (of autoIndex number of the row just added to the database). My autoIndex was created with the following code:

ALTER TABLE sites ADD autoIndex INT NOT NULL IDENTITY (1,1) PRIMARY KEY

The code I have tried getting the identity with is:

cmd.CommandText ="INSERT blah blah; SELECT @.@.IDENTITY;";

string autoIndex =Convert.ToString(cmd.ExecuteScalar());

cmd.Dispose();

I also tried:

// bulding of SQL INSERT here

cmd.ExecuteNonQuery();

cmd.Dispose();

cmd =

newSqlCommand(SQLDataSource_pics.SelectCommand, conn);

cmd.CommandType =

CommandType.Text;

cmd.CommandText =

"SELECT @.@.IDENTITY;";string autoIndex =Convert.ToString(cmd.ExecuteScalar());

Session.Add(

"autoIndex", autoIndex);

Most of the time, I get a value of "0" (zero) for autoIndex. Every now an then (5%) of the time I get back a correct value.

I don't know where to go from here.

Thank you.

Hayedid

Are they using MS SQL 2000 or MySQL?

Now I had a problem before doing that when I had a cluster of SQL servers. My Intermediate layer sent INSERTS to computer A, UPDATE to computer B and SELECT to computer C.

So as you can see C did not know about A until after a few seconds was received on the replication.

Al

|||

Have you thought of making it a Stored Procedure? do the insert inside the sProc, and then, inside the SProc, having Declared an Integer variable (like Set @.NewID), the last line (after the insert) would be Select @.NewID, returning the integer

Why are you converting the 'autoIndex ' to a string? Try making it an integer. If it's an identity field, it would return an integer

Then, you can do with it whatever you'd like after that.

|||

SQL2000. I am in the process of converting to MySQL to see if that resolves the problem.

I originally had autoIndex as an integer. I converted to a string simply because that the debugging procedure I am throwing the value into a Textbox to view it until I get this problem resovled.

I'll try the stored procedure.

Thanks.

|||

For anyone who's interested, I figured this one out. When I was testing this on my production server (hosted with GoDaddy), I went to the site through a masked domain. If I tested through the primary domain everything worked fine.

Not sure why this makes a difference. Any ideas? I was able to resolve the issue by forwarding my second domain without masking it.

Thank you everyone.