'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.Sql Client.SqlExcep tion: OLE DB provider 'MSDASQL' reported
an error. at System.Data.Sql Client.SqlConne ction.OnError(S qlException
exception, Boolean breakConnection ) at
System.Data.Sql Client.SqlInter nalConnection.O nError(SqlExcep tion
exception, Boolean breakConnection ) at
System.Data.Sql Client.TdsParse r.ThrowExceptio nAndWarning(Tds ParserStateObje ct
stateObj) at System.Data.Sql Client.TdsParse r.Run(RunBehavi or
runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleR esultSet bulkCopyHandler , TdsParserStateO bject stateObj)
at System.Data.Sql Client.SqlDataR eader.ConsumeMe taData() at
System.Data.Sql Client.SqlDataR eader.get_MetaD ata() at
System.Data.Sql Client.SqlComma nd.FinishExecut eReader(SqlData Reader ds,
RunBehavior runBehavior, String resetOptionsStr ing) at
System.Data.Sql Client.SqlComma nd.RunExecuteRe aderTds(Command Behavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async) at
System.Data.Sql Client.SqlComma nd.RunExecuteRe ader(CommandBeh avior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result) at
System.Data.Sql Client.SqlComma nd.RunExecuteRe ader(CommandBeh avior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method) at
System.Data.Sql Client.SqlComma nd.ExecuteReade r(CommandBehavi or
behavior, String method) at
System.Data.Sql Client.SqlComma nd.ExecuteDbDat aReader(Command Behavior
behavior) at
System.Data.Com mon.DbCommand.S ystem.Data.IDbC ommand.ExecuteR eader(CommandBe havior
behavior) at System.Data.Com mon.DbDataAdapt er.FillInternal (DataSet
dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords,
String srcTable, IDbCommand command, CommandBehavior behavior) at
System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at
System.Data.Com mon.DbDataAdapt er.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]

Re: 'MSDASQL' headache

bigHairy (mthomas1973@gm ail.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.Sql Client.SqlExcep tion: OLE DB provider 'MSDASQL' reported
an error. at System.Data.Sql Client.SqlConne ction.OnError(S qlException
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, 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

[bigHairy]

Re: 'MSDASQL' headache

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.