'MSDASQL' headache

[bigHairy]

Hello - hope this is in the right group:

We have just started with linked servers and have successfully created
a view on SQL Server linked to a Progress database. I can query this
view happily in Query Analyzer.

I have created an ASP.NET application to display this view in a
datagrid but I get the following error:

System.Data.SqlClient.SqlException: OLE DB provider 'MSDASQL' reported
an error. at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption
exception, Boolean breakConnection) at
System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection) at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior
runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaDat a() at
System.Data.SqlClient.SqlDataReader.get_MetaData() at
System.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString) at
System.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(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(Com mandBehavior
behavior, String method) at
System.Data.SqlClient.SqlCommand.ExecuteDbDataRead er(CommandBehavior
behavior) at
System.Data.Common.DbCommand.System.Data.IDbComman d.ExecuteReader(CommandBehavior
behavior) at System.Data.Common.DbDataAdapter.FillInternal(Data Set
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) at...

The code in my ASP.NET application looks fine and works on non-linked
views on the same server. Do I need to add a command or change a
setting on SQL Server?

[Erland Sommarskog]

bigHairy (mt*********@gmail.com) writes:

We have just started with linked servers and have successfully created
a view on SQL Server linked to a Progress database. I can query this
view happily in Query Analyzer.

I have created an ASP.NET application to display this view in a
datagrid but I get the following error:

System.Data.SqlClient.SqlException: OLE DB provider 'MSDASQL' reported
an error. at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption
exception, Boolean breakConnection) at

This means that the access to the remote server failed for some reason that
we don't know. Use Profiler to see what commands emits to SQL Server, and
then run those commands from Query Analyzer, to get a slightly more
informative message.

A wild guess is that the view is not updatable from the SQL Server side,
and this is why run into problems. But that is just a guess.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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

[bigHairy]

Just in case anyone else has a similar issue, I resolved this with
authentication. The DSN used to create the linked server on SQL
connected using credentials on the Progress database. We added a new
user on SQL Server to mirror these credentials and used that in the
ASP.NET connection string. Pretty obvious I guess but it's all a
learning curve. Anyway, works great now. Thanks for your help, though -
much appreciated.