ASP - Reading Linked Server Stored Procedure

[wjunk]

Hi,

basically i've ben searching forums and google with no success with my problem....

I've got a stored procedure which returns a select query on a linked server. This works fine if i call within SQL and Query Analyser but if i try and execue to return data to a ASP recordset i get the ridiculous

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

Am i missing something really basic like a simple setting or is it just not possible.

Heres my ASP code:

Dim adoCon
Dim rsTList
Dim strSQL

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "DSN=extranet;"

strSQL = "EXEC sp_test"

Set rs = adoCon.Execute(strSQL)

do while not rs.eof

response.write(rs.fields(3) & "<br>")

rs.movenext

loop

rs.close()
adoCon.close()


Heres my Stored Procedure:

SELECT top 2 * FROM OPENQUERY(MY_LINKED_SERVER, ' SELECT * FROM mytable') Rowset_1

any ideas?

cheers

[jhardman]

Best guess: it is a permissions problem. Make sure iUser_serverName has permission to access that database.

Jared

[ArvindK]

Hi,
Did you resolve this problem? am running into same. looks like permission issues, but i checked all permissions and everything looks fine.

any hints?

Hi,

basically i've ben searching forums and google with no success with my problem....

I've got a stored procedure which returns a select query on a linked server. This works fine if i call within SQL and Query Analyser but if i try and execue to return data to a ASP recordset i get the ridiculous

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

Am i missing something really basic like a simple setting or is it just not possible.

Heres my ASP code:

Dim adoCon
Dim rsTList
Dim strSQL

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "DSN=extranet;"

strSQL = "EXEC sp_test"

Set rs = adoCon.Execute(strSQL)

do while not rs.eof

response.write(rs.fields(3) & "<br>")

rs.movenext

loop

rs.close()
adoCon.close()


Heres my Stored Procedure:

SELECT top 2 * FROM OPENQUERY(MY_LINKED_SERVER, ' SELECT * FROM mytable') Rowset_1

any ideas?

cheers