Re: errors calling linked oleDB server stored procedures

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: gerry (germ_at_hotmail.com)
Date: 04/27/04 

Date: Tue, 27 Apr 2004 09:33:16 -0400

Thanks for the reply.

I find it hard to believe that this is so - in fact the same documentation
you cite refutes this assertion , please refer to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_3164.asp
which clearly states that OpenQuery sends an unrestricted 'blind' command
string to the remote server and expects nothing back other than a valid
oleDB rowset - exactly the same as any other oleDB aware software is
apparently able to do.

The document you specified states what can and can't be done with a linked
server using T-Sql directly.
It says that linked server stored procedures can only be executed using
T-Sql against SQL server linked servers.
OpenQuery uses pass through SQL to execute the remote procedure - NOT T-Sql.
ie. you cannot use "exec [NonSqlSrv].sp()" to execute a stored procedure
against a linked non-sql server server - and this makes perfect sense.
However , the whole point in OpenQuery is to invoke & execute a command on
the remote server - Sql Server has no knowledge of what command is being
'passed through' as long as a valid oleDB dataset is returned - which it is
, at least by every other applications standards.

My guess is that this is some type of configuration problem or Sql Servers
reliance upon some oleDB functionality the the provider does not fully
support or just a plain bug in SqlServer. I am going to try this same
scenario against a linked VFP database via the vfpoledb provider (
definitely a MS product ) to see if i get the same results.

gerry

""Michael, Cheng [MSFT]"" <v-mingqc@online.microsoft.com> wrote in message
news:n7s9$g4KEHA.308@cpmsftngxa10.phx.gbl...
> Hi Gerry,
>
> From your descriptions, I know that you could run OPENQUERY successfully
> when excute select statement, however, call a Linked Server stored
> procedures will encounter an error. Have I understand your issue? If there
> is anything I miss, please feel free to let me know.
>
> First of all, I am not very familiar with IBM.UniOLEDB, it's not an
product
> of Microsoft, is it?
>
> Secondly, I am so sorry to that, You cannot execute remote stored
> procedures against non-SQL Servers from SQL Server, which is to say that
> OPENQUERY only support to excute stored procedures on SQL Server in Linked
> Server.
>
> Also, you could find this in
>
> External Data and Transact-SQL
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8
> _qd_12_3164.asp
> see the last one "Stored procedures are supported only against SQL Server
> data sources. "
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
>
> Michael Cheng
> Microsoft Online Support
> ***********************************************************
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks.
>
>
>

Relevant Pages

  • Re: User defined types command parameter types
    ... > the server telling it what it wants done. ... > aren't going to need or want SQL CLR. ... >>avoided if Visual Studio was more tightly integrated with SQL server. ... >>like to see better integration of stored procedures and application code. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Temp files in Stored Procedures
    ... you do need to use SELECT INTO, try to schedule it when your SQL Server is ... server to its knees. ... >> The ideal method is to avoid using temp tables within stored procedures. ...
    (microsoft.public.sqlserver.programming)
  • Re: Dynamic SQL generation is not supported against a SelectCommand that does not return any base ta
    ... When you execute Update (for the first ... UpdateCommand etc. See my article on the CommandBuilder and why it should be ... SQL Server or MS Access. ... Code works fine with MS SQL Server. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: User defined types command parameter types
    ... the server telling it what it wants done. ... SQLS 2005 hosting the CLR isn't going to make any difference in this ... >avoided if Visual Studio was more tightly integrated with SQL server. ... >like to see better integration of stored procedures and application code. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: E-Mail Nachricht an Operator
    ... Man kann ja mit dem SQL Server ... SQL Server-Agent / Operatoren ... EXECUTE msdb.dbo.sysmail_add_account_sp ...
    (microsoft.public.de.sqlserver)