October 30, 2013 at 6:45 am
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
October 31, 2013 at 11:06 am
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.
October 31, 2013 at 3:21 pm
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.'
November 1, 2013 at 11:31 am
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.
September 13, 2017 at 2:18 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy