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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply