cant call extended sp through open query using linked sql for sql server 2012

  • i'm trying to get the drive space from a prod box querying from a monitoring box using openquerry and linked sql

    here is the error. it only happens when i try this to my sql server 2012 standard boxes

    Msg 11519, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1

    The metadata could not be determined because statement 'exec master.dbo.xp_FixedDrives' invokes an extended stored procedure.

    anyone know why

  • crickets....i just don't understand why this would be available in all other versions but 2012.

    Now i have to code a linked server call directly if my process is trying to connect to a sql server 2012 and run a extended proc call. pain in the butt. Sql Server 2012 i shake my fist in the air at u in dismay.

  • I assume you've seen the openquery documentation here (which indicates the same restriction back to 2000)

    So you know can still do the following?

    EXEC [linked_server].[db].[schema].[xProc]

    You can also create a local synonym for that proc if your complaint about making linked server calls directly was about having to use the four-part name.

    Or maybe I'm missing the difficult part...



    Dan Guzman - Not the MVP (7/22/2010)
    All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

  • heres more detail.

    source sql server:

    This is the server i'm makeing the open query call from....

    Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

    Feb 10 2012 19:39:15

    Copyright (c) Microsoft Corporation

    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    command:

    SELECT *

    FROM OPENQUERY([100XXX01], 'set fmtonly off exec master.dbo.xp_FixedDrives')

    DEST:

    This is the server version the [100XXX01] linked server is pointing to.

    Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

    Oct 19 2012 13:38:57

    Copyright (c) Microsoft Corporation

    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    This is the error i get:

    Msg 11519, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1

    The metadata could not be determined because statement 'exec master.dbo.xp_FixedDrives' invokes an extended stored procedure.

    When i run the same command w/ linked server pointing to the below sql version i dont get any errors

    Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)

    Sep 16 2010 19:43:16

    Copyright (c) 1988-2008 Microsoft Corporation

    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

    Lastly, i realize that i can use a 4 part query. That' sreally not the point. The point is the process that's calling this is quite a bit more complex and is going to take a lot of cycles and time to change to dynamically get the version and then branch off and do 4 part query loading results into a table. etc. This process will not run agains sql server 2012 versions until the dev work and testing is complete. I just dont understand why they would make the 2012 product less useful as they have in this instance.

  • This is a super old thread, but I wonder if this helps:
    https://blogs.msdn.microsoft.com/sqlagent/2012/07/12/workaround-sql-server-2012-openrowset-on-sp_help_job-throws-the-metadata-could-not-be-determined/

    I'm running into this issue myself trying to get result from sp_help_job.

    SELECT @JobStatus = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC MSDB.dbo.sp_help_job @job_name = ''MyJob'', @job_aspect = ''JOB'' ')

    Rob

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply