Hi All,

I am trying to grab disk free space information from all the SQL server instances and dump into central server database.

I am using openrowset for all the versions and it works well for SQL 2005, 2008 and 2008 R2.

But when it comes to SQL 2012, it throws following error.

Msg 11519, Level 16, State 1, Server , 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.

My script is as following.

USE

[DBAdmin]

GO

/****** Object:  StoredProcedure [dbo].[usp_DiskSpace_Information_2012_to_2014]    Script Date: 6/29/2015 4:25:59 PM ******/

SET

ANSI_NULLSON

GO

SET

QUOTED_IDENTIFIERON

GO

ALTER

PROC[dbo].[usp_DiskSpace_Information_2012_to_2014]

AS

SET

NOCOUNTON

BEGIN

declare@srvr varchar(100)

declare@SQL varchar(max)

declare@QueryFlag varchar(1)

declareServerName cursorforselectServerName,QueryFlag fromDBAdmin.dbo.ServerList whereVersionin('2012','2014')

and  ([Accessible] IN('Y'))

--(StatusFlag NOT IN ('Decommissioned') or QueryFlag NOT IN ('N'))

openServerName

fetchnextfromServerName into@srvr,@QueryFlag

while@@fetch_status=0

begin

if@QueryFlag ='L'      -- Local domain server

begin

SET@SQL='insert into DBAdmin.dbo.DiskSpace_Information

SELECT '''

+@srvr+''',*

FROM OPENROWSET(''SQLNCLI'', ''Server='

+@srvr+';Trusted_Connection=yes;'',

''exec master.dbo.xp_fixeddrives

'')'

EXEC (@SQL)

end

fetchnextfromServerName into@srvr,@QueryFlag

end

closeServerName

deallocateServerName

END

------------------------------------------------------------------

I also tried to use

SET FMTONLY OFF but it is still same.

Can you please let me know how to solve the issue?

I have 150 instances so can not create linked server for all the server.

Thanks for your help in advance.

Regards,

Naman

The trick with SET FMTONLY OFF does not work when both servers are running SQL 2012 or later. And it never was a good idea.

You need to add the clause WITH RESULT SETS to the call to xp_fixeddrives:

EXEC xp_fixeddrives WITH RESULT SETS ((Drive char(1),
                                      [MB Free] int))

However, this only works with the remote server runs SQL 2012 or later.

In all honesty, you are much better off implementing this as a PowerShell script (or some other client-side language you are comfortabel with).

Hi Erland,

I have tried with your suggestion and it works okay now.

At this stage, I am interested only in free space so it is enough to collect data.

Actually I am trying to create trend by collecting daily free space data into data warehouse. So for time being that would work.

Thanks a lot for your help.

Regards,

Naman

Hi Erland,

I am facing same kind of issue when running following query and getting below error.

Incorrect syntax near 'RESULT'.
OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Deferred prepare could not be completed.".

USE

[DBAdmin]

GO

/****** Object:  StoredProcedure [dbo].[usp_Database_Security_Information]    Script Date: 8/07/2015 10:04:44 AM ******/

SET

ANSI_NULLSON

GO

SET

QUOTED_IDENTIFIERON

GO

ALTER

PROC[dbo].[usp_Database_Security_Information]

AS

SET

NOCOUNTON

BEGIN

TRUNCATETABLEDBAdmin.dbo.Database_Security_Information

declare@srvr varchar(100)

declare@SQL varchar(max)

declare@QueryFlag varchar(1)

declareServerName cursorforselectServerName,QueryFlag fromDBAdmin.dbo.ServerList where  [Accessible] IN('Y')  --and StatusFlag NOT IN ('Decommissioned') and QueryFlag NOT IN ('N')

-- Version in ('2012','2014')

openServerName

fetchnextfromServerName into@srvr,@QueryFlag

while@@fetch_status=0

begin

if@QueryFlag ='L'      -- Local domain server

begin

SET@SQL='insert into DBAdmin.dbo.Database_Security_Information

SELECT '''

+@srvr+''',A.*

FROM OPENROWSET(''SQLNCLI'', ''Server='

+@srvr+';Trusted_Connection=yes;'',

''

SET FMTONLY OFF

exec sp_MSforeachdb

''''USE [?];select

''''''''?'''''''' AS DBName,cast(p.name as nvarchar(128)) as Name, p.default_schema_name as DefaultSchema, p.type_desc as Type,

case when l.hasdbaccess = 1 then ''''''''YES'''''''' else '''''''''''''''' end as DBaccess,

CASE S.RoleName WHEN ''''''''db_owner'''''''' THEN ''''''''Yes'''''''' ELSE '''''''''''''''' END AS db_owner,

CASE S.RoleName WHEN ''''''''db_accessadmin'''''''' THEN ''''''''Yes'''''''' ELSE '''''''''''''''' END AS db_accessadmin ,

CASE S.RoleName WHEN ''''''''db_securityadmin'''''''' THEN ''''''''Yes'''''''' ELSE '''''''''''''''' END AS db_securityadmin,

CASE S.RoleName WHEN ''''''''db_ddladmin'''''''' THEN ''''''''Yes'''''''' ELSE '''''''''''''''' END AS db_ddladmin,

CASE S.RoleName WHEN ''''''''db_datareader'''''''' THEN ''''''''Yes'''''''' ELSE '''''''''''''''' END AS db_datareader,

CASE S.RoleName WHEN ''''''''db_datawriter'''''''' THEN ''''''''Yes'''''''' ELSE '''''''''''''''' END AS db_datawriter,

CASE S.RoleName WHEN ''''''''db_denydatareader'''''''' THEN ''''''''Yes'''''''' ELSE '''''''''''''''' END AS db_denydatareader,

CASE S.RoleName WHEN ''''''''db_denydatawriter'''''''' THEN ''''''''Yes'''''''' ELSE '''''''''''''''' END AS db_denydatawriter,

--b.permission_name AS Permission,b.state_desc AS PermissionState,

case when l.islogin = 1 then ''''''''YES'''''''' else '''''''''''''''' end as Login,

case when l.issqluser = 1 then ''''''''YES'''''''' else '''''''''''''''' end as SQLuser,

case when l.issqlrole = 1 then ''''''''YES'''''''' else '''''''''''''''' end as SQLrole,

case when l.isaliased = 1 then ''''''''YES'''''''' else '''''''''''''''' end as Aliased,

case when l.isapprole = 1 then ''''''''YES'''''''' else '''''''''''''''' end as APProle,

case when l.isntname = 1 then ''''''''YES'''''''' else '''''''''''''''' end as NTname,

case when l.isntgroup = 1 then ''''''''YES'''''''' else '''''''''''''''' end as NTgroup,

case when l.isntuser = 1 then ''''''''YES'''''''' else '''''''''''''''' end as NTuser,

p.create_date,p.modify_date

from ?.sys.database_principals p

--join ?.sys.database_permissions b on p.principal_id=b.grantee_principal_id

left join ?.sys.sysusers l on p.name=l.name

left join (

select b.name as USERName, c.name as RoleName

from ?.sys.sysmembers a  join ?.sys.sysusers b on a.memberuid = b.uid

join ?.sys.sysusers c on a.groupuid = c.uid 

)S on p.Name=S.USERName

''''

WITH RESULT SETS (([ServerName] [varchar](100) ,

[DBName] [varchar](100) ,

[Name] [nvarchar](200) ,

[DefaultSchema] [sysname] ,

[Type] [nvarchar](60) ,

[DBaccess] [varchar](3)  ,

[db_owner] [varchar](3)  ,

[db_accessadmin] [varchar](3)  ,

[db_securityadmin] [varchar](3)  ,

[db_ddladmin] [varchar](3)  ,

[db_datareader] [varchar](3)  ,

[db_datawriter] [varchar](3)  ,

[db_denydatareader] [varchar](3)  ,

[db_denydatawriter] [varchar](3)  ,

[Login] [varchar](3)  ,

[SQLuser] [varchar](3)  ,

[SQLrole] [varchar](3)  ,

[Aliased] [varchar](3)  ,

[APProle] [varchar](3)  ,

[NTname] [varchar](3)  ,

[NTgroup] [varchar](3)  ,

[NTuser] [varchar](3)  ,

[create_date] [datetime]  ,

[modify_date] [datetime]))

'') AS A;

'

EXEC (@SQL)

end

fetchnextfromServerName into@srvr,@QueryFlag

end

closeServerName

deallocateServerName

END

Can you please help me out asap?

Thanks a lot in advance.

Regards,

Naman