MS SQL Server - Stored Procedure vs. Query Analyzer

Author

Stored Procedure vs. Query Analyzer

Steve

2005-10-28, 11:23 am

I need to create a Stored Procedure that will run a query to fill a temporary
table. I have run the query successfully from within Query Analyzer to prove
that it works, however when I try to use the query from within a Stored
Procedure, I get an error.

Essentially, the query runs OpenQuery against a LinkedServer. The
LinkedServer is based on a DSN. Using different drivers to create the DSN
doesn't seem to help as the errors are all pretty much the same. References
in the error messages are something like "Driver not capable." or "Optional
feature not implemented.". Is there something I missed in creating my Stored
Procedure? Cutting it down to just the Select query it looks like this:

CREATE PROCEDURE "RunF18T"

AS

select * from OpenQuery(F18T,'sele
ct * from OBS')

/* SET NOCOUNT ON */
RETURN

Where F18T is the LinkedServer and OBS is a table in the database.

Query Analyzer - Runs fine.
Stored Procedure - NG.

Any pointers?

Thanks,
Steve

GlennThomas5

2005-10-28, 8:23 pm

whats the error you get in the SP? is it invalid pointer?

Steve

2005-10-28, 8:23 pm

No, it's not invalid pointer. When I use the Pervasive ODBC driver to create
the DSN the error comes back with "Driver not capable". When I use the
DataDirect driver to create the DSN the error says "Optional feature not
implemented".

Either one of these configurations will run successfuly from the Query
Analyzer, just not from a SP.

I think I'm going to be bald soon from tearing my hair out trying get to the
bottom of this.

Any ideas?

Steve

"GlennThomas5" wrote:

> whats the error you get in the SP? is it invalid pointer?
>
>

Sponsored Links