SQL server timeout 2000 from C# .NET

I have run into a strange problem using SQL Server 2000 and two linked server. For two years now our solution has run without a hitch, but suddenly yesterday a query synchronizing data from one of the databases to the other started timing out.

I connect to a server in the production network, which is linked to a server containing orders I need data from.

The query contains a few joins, but basically this summarizes what is done:

INSERT INTO ProductionDataCache
   (column1, column2, ...)
   SELECT tab1.column1, tab1.column2, tab2.column1, tab3.column1 ...
       FROM linkedserver.database.dbo.Table1 AS tab1
       JOIN linkedserver.database.dbo.Table2 AS tab2 ON (...)
       JOIN linkedserver.database.dbo.Tabl32 AS tab3 ON (...)
       ...
       WHERE tab1.productionOrderId = @id
       ORDER BY ...

Obviously my first attempt to fix the problem was to increase the timeout limit from the original 5 minutes. But when I arrived at 30 minutes and still got a timeout, I started to suspect something else was going on. A query just does not go from executing in less than 5 minutes to over 30 minutes over night.

I outputted the SQL query (which was originally in the C# code) to my logs, and decided to execute the query in the Query Analyzer directly on the database server. To my big surprise, the query executed correctly in less than 10 seconds.

So I isolated the SQL execution in a simple test program, and observed the same query time out both on the server originally running this solution AND when running it locally on the database server. Also I have tried to create a Stored Procedure and execute this from the program, but this also times out. Running it in Query Analyzer works fine in less than a few seconds.

It seems that the problem only occurs when I execute this query from the C# program. Has anyone seen such behavior before, and found a solution for it?

UPDATE: I have now used SQL Profiler on the server. The obvious difference is that when executing the query from the .NET program, it shows up in the log as "exec sp_executesql N'INSERT INTO ...'", but when executing from Query Analyzer it occurs as a normal query in the log.

Further I tried to connect the SQL Query Analyzer using the same SQL user as the program, and this triggered the problem in Query Analyzer as well. So it seems the problem only occurs when connecting via TCP/IP using a sql user.

There are a few things to investigate; the first is SET options; these can make a huge difference to some queries. Look at the SET options in the trace, and repeat these when you are testing in Management Studio (or Query Analyzer). Note that you'd have to look at an actual profile session to really see these (not just what your code logs).

The next thing I'd look at is transactions; what transaction model are you using in your C#? Any? None? What isolation? Serializable? Perhaps distributed? It could be there is some obscure lock being held on the table.

Of course, if locking is an issue you might see something via sp_who / sp_who2 while the query is running.