A transport-level error has occurred when sending the request to the server


I was facing a problem with one of the PowerShell function that I wrote  using System.Data.SqlClient.SQLConnection, that with the first run it works, but with subsequent runs it fails with error

Exception calling “ExecuteNonQuery” with “0” argument(s): “A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 1 – I/O Error detected in read/write operation)”

And in other machines , the error was with TCP/IP and Named Pipes too.

Well, as I did not changed anything in my environment  my first step was not to look at the SQL Protocols or TCP chimney..etc. Should be other issue .

For some reason, there is a connectivity problem with a previously opened session in SQL Server, then to fix  it, you just need to clear the connection pool :

My code was :

   1: $connection=new-object System.Data.SqlClient.SQLConnection

   2: $connection.ConnectionString="Server='{0}';Database='{1}';Uid='{2}';PWD = '{3}'" -f $var1 ,$var2,$var3,$var4

   3: $connection.Open()

   4: $command=new-object system.Data.SqlClient.SqlCommand($query,$connection)

Added : [System.Data.SqlClient.SqlConnection]::ClearAllPools()

   1: [System.Data.SqlClient.SqlConnection]::ClearAllPools()

   2: $connection=new-object System.Data.SqlClient.SQLConnection

   3: $connection.ConnectionString="Server='{0}';Database='{1}';Uid='{2}';PWD = '{3}'" -f $var1 ,$var2,$var3,$var4

   4: $connection.Open()

   5: $command=new-object system.Data.SqlClient.SqlCommand($query,$connection)

And solved Alegre

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

1 Response to A transport-level error has occurred when sending the request to the server

  1. sellers says:

    Help great powershell guru..
    i am using powershell 2.0. I close sql connection with close() method but get error when trying to establish a new connection:

    System.Management.Automation.MethodInvocationException: Exception calling “WriteToServer” with “1” argument(s): “Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.” —> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

    In my case, the max pool size was reached. As well, statement [System.Data.SqlClient.SqlConnection]::ClearAllPools() is before the Open() method. I can put database in in single user mode with rollback immediate and then put it back to mult_user to “kill” connections. Is there anything I can do from powershell to release pooled (ghost) connections?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s