Intermittent SQL corruption from ExecuteScalar

[Mike Krajewski]

We are having an intermittent problem using an ExecuteScalar command on a
asp.NET page accessing SQL Server. 2 characters of the sql statement get
intermittently altered. The code looks as follows:

SqlCommand command = new SqlCommand("SELECT fkOWSStatusID FROM
vwUsersCleanups WHERE pkCleanupID = 123", connection);
object nReturn = command.ExecuteScalar();

About 10% of the time, the 'rs' in vwUsersCleanups get altered into
unrecgonizable characters. The other 90% of the time, everything works just
fine. I have put a Trace method just before the ExecuteScalar() to verify
the sql statement is correct. It always looks just fine. However, in SQL
Profiler, in the BatchStarting Event, the Select statement is corrupt. I
have applied the most recent service pack for MDAC 2.7, reinstalled the .NET
Framework (version 1.1), and deleted and recreated the view all to no avail.
This occurs on 2 of our IIS servers. All other IIS servers we try it on
works fine. This seems to eliminate any bad build problems.

[MikeL]

Is there a database collation difference? If you are using more than one
database server or database. If there is a colation difference, you will get
bad SQL statements.

"Mike Krajewski" wrote:

We are having an intermittent problem using an ExecuteScalar command on a
asp.NET page accessing SQL Server. 2 characters of the sql statement get
intermittently altered. The code looks as follows:

SqlCommand command = new SqlCommand("SELECT fkOWSStatusID FROM
vwUsersCleanups WHERE pkCleanupID = 123", connection);
object nReturn = command.ExecuteScalar();

About 10% of the time, the 'rs' in vwUsersCleanups get altered into
unrecgonizable characters. The other 90% of the time, everything works just
fine. I have put a Trace method just before the ExecuteScalar() to verify
the sql statement is correct. It always looks just fine. However, in SQL
Profiler, in the BatchStarting Event, the Select statement is corrupt. I
have applied the most recent service pack for MDAC 2.7, reinstalled the .NET
Framework (version 1.1), and deleted and recreated the view all to no avail.
This occurs on 2 of our IIS servers. All other IIS servers we try it on
works fine. This seems to eliminate any bad build problems.

[Mike Krajewski]

As far as I can tell, all default collations, and collations on the
procedures, tables, views, and columns all have SQL_Latin1_General_CP1_CI_AS.
We havn't dealt with changing collations at all. The really strange thing
is that it is so intermittent. Those 2 characters will be incorrect, but if
you refresh your browser then they will be correct the next time and the page
will show up correctly.

"MikeL" wrote:

Is there a database collation difference? If you are using more than one
database server or database. If there is a colation difference, you will get
bad SQL statements.

"Mike Krajewski" wrote:

We are having an intermittent problem using an ExecuteScalar command on a
asp.NET page accessing SQL Server. 2 characters of the sql statement get
intermittently altered. The code looks as follows:

SqlCommand command = new SqlCommand("SELECT fkOWSStatusID FROM
vwUsersCleanups WHERE pkCleanupID = 123", connection);
object nReturn = command.ExecuteScalar();

About 10% of the time, the 'rs' in vwUsersCleanups get altered into
unrecgonizable characters. The other 90% of the time, everything works just
fine. I have put a Trace method just before the ExecuteScalar() to verify
the sql statement is correct. It always looks just fine. However, in SQL
Profiler, in the BatchStarting Event, the Select statement is corrupt. I
have applied the most recent service pack for MDAC 2.7, reinstalled the .NET
Framework (version 1.1), and deleted and recreated the view all to no avail.
This occurs on 2 of our IIS servers. All other IIS servers we try it on
works fine. This seems to eliminate any bad build problems.