Transport Level Error has occured

[Shilpa]

I am getting the following error.
A transport-level error has occurred when sending the request to the
server. (provider: Shared Memory Provider, error: 0 - The system cannot
open the file.)

On performing the same operation again, it happens fine without any
error.
Please help.

Regards,
Shilpa

[Erland Sommarskog]

Re: Transport Level Error has occured

Shilpa (shilpa.nagavar a@unisys.com) writes:[color=blue]
> I am getting the following error.
> A transport-level error has occurred when sending the request to the
> server. (provider: Shared Memory Provider, error: 0 - The system cannot
> open the file.)
>
> On performing the same operation again, it happens fine without any
> error.[/color]

That's just the way it works. If Mgmt Studio looses contact with SQL
Server, because the network goes down and up, or SQL Server is restarted,
you will get this message. Next time you try, SSMS reconnects automatically.
Query Analyzer handles this situation more gracefully, as it reconnects
directly when it detects that the connection is broken.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Shilpa]

Re: Transport Level Error has occured

Hi Erland,
I am using SQL Server 2000. So, it does not hvae a management studio.
Could you please explain now?

Regards,
Shilpa

[Erland Sommarskog]

Re: Transport Level Error has occured

Shilpa (shilpa.nagavar a@unisys.com) writes:[color=blue]
> I am using SQL Server 2000. So, it does not hvae a management studio.
> Could you please explain now?[/color]

It would help if you had explained exactly in which context you get this
error.

As long as I don't, I will have to assume that you are running a .Net
application that uses SqlClient to connect to SQL 2000. And then as I said
in my previous post, the network connection drops or SQL Server is restared
and you get this error.

That's all I can say with the information you have to provided so far.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Shilpa]

Re: Transport Level Error has occured

Yes, you are right.
I am using a .Net application

Here are the sequence of actions
1) Drop all connections to a database by using the ALTER DATABASE
command
2) Drop the database
3) Recreate the database and populate it with some data
4) Select a value from a table using a where condition (no joins).
Command Object is used to perform this operation and fill a datset
through a data adapter.
All these happen through the .Net application. Before step 4, a
connection is successfully established and the status says 'Open'.

Can you get some clues from the above and suggest me a solution?

Regards,
Shilpa

Erland Sommarskog wrote:[color=blue]
> Shilpa (shilpa.nagavar a@unisys.com) writes:[color=green]
> > I am using SQL Server 2000. So, it does not hvae a management studio.
> > Could you please explain now?[/color]
>
> It would help if you had explained exactly in which context you get this
> error.
>
> As long as I don't, I will have to assume that you are running a .Net
> application that uses SqlClient to connect to SQL 2000. And then as I said
> in my previous post, the network connection drops or SQL Server is restared
> and you get this error.
>
> That's all I can say with the information you have to provided so far.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

[Erland Sommarskog]

Re: Transport Level Error has occured

Shilpa (shilpa.nagavar a@unisys.com) writes:[color=blue]
> I am using a .Net application
>
> Here are the sequence of actions
> 1) Drop all connections to a database by using the ALTER DATABASE
> command
> 2) Drop the database
> 3) Recreate the database and populate it with some data
> 4) Select a value from a table using a where condition (no joins).
> Command Object is used to perform this operation and fill a datset
> through a data adapter.
> All these happen through the .Net application. Before step 4, a
> connection is successfully established and the status says 'Open'.
>
> Can you get some clues from the above and suggest me a solution?[/color]

And after step 4, you get the transport-level error?

I guess the problem is the connection pool. Recall that when you close
a connection from your .Net app, the connection to SQL Server is not
really closed. Instead ADO .Net lingers to it, for another 60 seconds,
and if the applicaiton opens a new connection during this time, it will
reuse the connection.

When you issue ALTER DATABASE WITH ROLLBACK IMMEDIATE, SQL Server will
terminate these connections. However, this is not something that ADO .Net
can detect. So it gives you a connection which it thinks is good, but
which isn't.

There are a couple of ways to approach this. One is to configure the
connection pool, so that all connections are dropped when you drop the
database. Another is to change the connection string, each time you
drop the database, as different connection strings gives different pools.

I would suggest that the best way, though, is simply to set up an exception
handler, so that you simply trap this error, and then try to reconnect.

For the fine details on how to implement any of the above, I would suggest
that microsoft.publi c.dotnet.framew ork.adonet is a better venue, as the
issue involved here is more one with SqlClient rather than SQL Server.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx