Query over Linked Server timing out

I'm having some confusing problems when making a query involving a
linked server. I'm really at a loss as to what the problem could be.
This is happening with multiple views. Here's the code for one of them:
CREATE VIEW dbo.vwStates
AS
SELECT Code, Name, CountryTerritoryCode
FROM BSTBETA.ProdDB.dbo.StateProvince StateProvince_1
The name of the linked server is BSTBETA. I am able to query the view
in Enterprise Manager and Query Analyer, with no issues, and it takes
less than a second to run. The SQL I run to test this view is simply:
select * from vwStates
Howerver, when trying to query the view from an ASP.Net web application
(using the same username/password I used in Query Analyzer with the
same simple query given above), this exception is thrown:
Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bjec
t
stateObj)
at
System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject
stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult
asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32
bytesExpected)
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader
ds, RunBehavior runBehavior, String resetOptionsString)
at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method)
at
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
behavior)
at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBeh
avior
behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable)
at Pipeline2006.DataAccess.getDataSet(String strSQL, String
strTableName) in
\\Eadev2\E\inetpub\Pipeline2006\Pipeline
2006_DM\Common\Classes\DataAccess.vb
:line
19
The server that runs the web server and the SQL Server which is trying
to connect to BSTBETA is running Windows 2000 and SQL Server 2000. The
web app is running ASP.Net 2.0. The BSTBETA server is running Windows
2003 with SQL Server 2000.
I'm sure there's a bunch more information that might be useful, so if
anyone's got some ideas on how to troubleshoot this issue, just let me
know what might be significant.
Please help!Hi
You don't say what the login is for the ASP connection and the QA one? Have
you configured sp_addlinkedsrvlogin? Can you use the query defined in the
view directly?
John
"moskie@.gmail.com" wrote:

> I'm having some confusing problems when making a query involving a
> linked server. I'm really at a loss as to what the problem could be.
> This is happening with multiple views. Here's the code for one of them:
> CREATE VIEW dbo.vwStates
> AS
> SELECT Code, Name, CountryTerritoryCode
> FROM BSTBETA.ProdDB.dbo.StateProvince StateProvince_1
>
> The name of the linked server is BSTBETA. I am able to query the view
> in Enterprise Manager and Query Analyer, with no issues, and it takes
> less than a second to run. The SQL I run to test this view is simply:
> select * from vwStates
> Howerver, when trying to query the view from an ASP.Net web application
> (using the same username/password I used in Query Analyzer with the
> same simple query given above), this exception is thrown:
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bj
ect
> stateObj)
> at
> System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObje
ct
> stateObj, UInt32 error)
> at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult
> asyncResult, TdsParserStateObject stateObj)
> at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32
> bytesExpected)
> at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
> at System.Data.SqlClient.TdsParserStateObject.ReadByte()
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream,
> BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
> at System.Data.SqlClient.SqlDataReader.get_MetaData()
> at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader
> ds, RunBehavior runBehavior, String resetOptionsString)
> at
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
> async)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
> method, DbAsyncResult result)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
> method)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior, String method)
> at
> System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
> behavior)
> at
> System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandB
ehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
> srcTable, IDbCommand command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
> srcTable)
> at Pipeline2006.DataAccess.getDataSet(String strSQL, String
> strTableName) in
> \\Eadev2\E\inetpub\Pipeline2006\Pipeline
2006_DM\Common\Classes\DataAccess.
vb:line
> 19
>
> The server that runs the web server and the SQL Server which is trying
> to connect to BSTBETA is running Windows 2000 and SQL Server 2000. The
> web app is running ASP.Net 2.0. The BSTBETA server is running Windows
> 2003 with SQL Server 2000.
> I'm sure there's a bunch more information that might be useful, so if
> anyone's got some ideas on how to troubleshoot this issue, just let me
> know what might be significant.
> Please help!
>|||Hi
You don't say what the login is for the ASP connection and the QA one? Have
you configured sp_addlinkedsrvlogin? Can you use the query defined in the
view directly?
John
"moskie@.gmail.com" wrote:

> I'm having some confusing problems when making a query involving a
> linked server. I'm really at a loss as to what the problem could be.
> This is happening with multiple views. Here's the code for one of them:
> CREATE VIEW dbo.vwStates
> AS
> SELECT Code, Name, CountryTerritoryCode
> FROM BSTBETA.ProdDB.dbo.StateProvince StateProvince_1
>
> The name of the linked server is BSTBETA. I am able to query the view
> in Enterprise Manager and Query Analyer, with no issues, and it takes
> less than a second to run. The SQL I run to test this view is simply:
> select * from vwStates
> Howerver, when trying to query the view from an ASP.Net web application
> (using the same username/password I used in Query Analyzer with the
> same simple query given above), this exception is thrown:
> Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bj
ect
> stateObj)
> at
> System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObje
ct
> stateObj, UInt32 error)
> at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult
> asyncResult, TdsParserStateObject stateObj)
> at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32
> bytesExpected)
> at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
> at System.Data.SqlClient.TdsParserStateObject.ReadByte()
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream,
> BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
> at System.Data.SqlClient.SqlDataReader.get_MetaData()
> at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader
> ds, RunBehavior runBehavior, String resetOptionsString)
> at
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
> async)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
> method, DbAsyncResult result)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
> method)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior, String method)
> at
> System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
> behavior)
> at
> System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandB
ehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
> srcTable, IDbCommand command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
> srcTable)
> at Pipeline2006.DataAccess.getDataSet(String strSQL, String
> strTableName) in
> \\Eadev2\E\inetpub\Pipeline2006\Pipeline
2006_DM\Common\Classes\DataAccess.
vb:line
> 19
>
> The server that runs the web server and the SQL Server which is trying
> to connect to BSTBETA is running Windows 2000 and SQL Server 2000. The
> web app is running ASP.Net 2.0. The BSTBETA server is running Windows
> 2003 with SQL Server 2000.
> I'm sure there's a bunch more information that might be useful, so if
> anyone's got some ideas on how to troubleshoot this issue, just let me
> know what might be significant.
> Please help!
>