using (var cmd = Connection.CreateCommand())
{
      Connection.Open();
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = "INSERT INTO AssemblyBinaries (AssemblyData) VALUES (@AssemblyData); SELECT @@IDENTITY AS AssemblyId";
      cmd.Parameters.Add(new SqlParameter("@AssemblyData", SqlDbType.Image, 2147483647, ParameterDirection.Input, true, 0, 0, "AssemblyData", DataRowVersion.Current, assemblyBinary.AssemblyData));
      using (IDataReader reader = cmd.ExecuteReader())
      {
          reader.Read();
          etc...

[*]I’m using SQL server 2005.
[*]ADO is using connection pooling. When it’s turned off, the problem stops.
[*]Multiple Active Result Sets isn’t a factor. Problem occurs even when it’s explicitly turned off, although most MS docs say it’s off by default. (There are other docs that say it’s on by default.)
[*]The problem occurs more frequently on multicore PCs than single core.
This problem occurs randomly, sometimes the calls work, and sometimes they don’t.
[*]I’ve tried turning on ADO logging (via ETW) but it didn’t show anything useful.
[*]The application is running on a VM.

quote:

---

Originally posted by Kristen
Update all drivers etc. / check on latest version?

---

quote:

---

Originally posted by russell

What version Windows and IIS are you running at web server?/

---

quote:

---

What MDAC version is installed on IIS server?

---

quote:

---

Are you properly closing and destroying your objects?

---

quote:

---

Are you executing any SET ROWCOUNT statements?

---

quote:

---

By the way, most likely the problem is at the web server, not the database server.

---

quote:

---

Originally posted by russell

there was a known issue with set rowcount in one batch "sticking" in subsequent batches when connection pooling is enabled. not sure if it was ever resolved with a patch. i'll see if i can google it out for you.

---

quote:

---

why do u say iis or the web server can't be responsible? your C# code is executing on the web server (or application server), not the database server, right?

---

quote:

---

i don't see where you're explicitly closing any connections

---

• I've checked our code, Set rowcount is only used in stored procs, (RecordSet.MaxRecords is never used) and the pattern is as Kristne describes, set rowcount X; <SQL operation>; Set rowcount 0


• The rowcount bug (rowcount not cleared on a pooled connection) doesn't match the problem I'm seeing (previous queries result set is returned)


• The rowcount bug is in SQL 2000, I'm running SQL 2005.

quote:

---

now, as for the C# service...it uses the objects installed on the server it lives on, not the SQL Server.

---

quote:

---

so the ADO version being used depends on the box hosting the service.

---

quote:

---

are you running iis and sql on the same box?

---

quote:

---

just because you called an open() method doesn't mean you called a close() method previously.

---

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                connection.Open();

quote:

---

Originally posted by russell
I'll stand by the explicitly closing connections -- this should go without saying. but I am not wholly convinced that's your problem at this point. still it is such an obvious mistake that it ought to be fixed and see if it doesn't help. that said, the .net runtime should clean that up for you.

---

quote:

---

Also, correct me if I'm wrong, but pooling is a client technology, not a SQL Server one, so again, the problem points to ADO.net, IIS, or the application code itself.

---

quote:

---

Originally posted by Kristen

Any chance that, when the problem arises, the previous user of the pooled connection had pulled all the data? i..e was closing the connection "early"? Perhaps there is another recordset, or some warning messages that have not been retrieved ("WARNING: NULLs in aggregate" or somesuch) - I'm thinking that this may cause the Pool to think the Connection still has valid data.

---

quote:

---

Originally posted by KristenRe-running the SQL that was used, manually, would show the resultset plus any warning error.

---

SELECT VersionMajor, VersionMinor, BuildNumber, DatabaseType FROM DatabaseVersion

Create Proc test
	@AssemblyData varbinary(max)
AS

SET NOCOUNT ON

INSERT INTO AssemblyBinaries (AssemblyData)
VALUES	(@AssemblyData)

SELECT @@IDENTITY AS AssemblyId
GO