Using Stored Procedure w/ linked server table

[wrbodine]

Hi,

We are creating a new Crystal Report (8.5) that uses a dsn, with a stored procedure from SQL Server 2000. The stored procedure uses a linked server in the select statement, with a fully qualified name in the join, something like

Select LTABLE.field1, local.field2 from local join RemoteServer.DBName.DBO.LTABLE LTABLE on local.IDfield = LTABLE.IDfield

When we try and select this stored procedure in the report, we get the following error:

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='RemoteServer', TableName="DBName"."DBO"."LTABLE"].

Any ideas how to resolve this, so we can use the stored procedure?

Thanks much,
Ray

ps - the SP runs fine in Query Analyzer

 

[synapsevampire]

Try changing your connectivity to CR SQL Server direct as opposed to OLE DB.

And consider posting the actual SP as opposed to "something like"

-k

 

[wrbodine]

Thanks much k,

That helped me figure out what was going on; when I tried a different way of connecting (using OLE DB) I found one of the tables didn't have proper permissions for the SQL account we were using....

Thanx again