Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

cant call extended sp through open query using linked sql for sql server 2012 Expand / Collapse
Author
Message
Posted Wednesday, October 30, 2013 6:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:25 AM
Points: 152, Visits: 375
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
Post #1509745
Posted Thursday, October 31, 2013 11:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:25 AM
Points: 152, Visits: 375
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.
Post #1510343
Posted Thursday, October 31, 2013 3:21 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:51 PM
Points: 880, Visits: 607
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.'
Post #1510436
Posted Friday, November 1, 2013 11:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:25 AM
Points: 152, Visits: 375
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.
Post #1510728
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse