主页 / dba / 问题 / 27250
Accepted
Biju jose
Asked: 2012-10-20 06:57:56 +0800 CST

如何使用 xp_fixeddrives 创建存储过程？

  • 772

我需要一个存储过程来检查 2 个链接服务器中的可用空间。（服务器是手动链接的）。我想出了这样的查询并使用 sql server 2008

ALTER PROCEDURE freespace
AS
BEGIN
    CREATE Table #freespace(drive VARCHAR(50),freedrivespace VARCHAR(50));
    DECLARE @servername VARCHAR(50);
    DECLARE @sql VARCHAR(100);
    DECLARE @space  CURSOR;
    SET @space= CURSOR FOR
    SELECT Name FROM SERVERNAME;
    OPEN @space;
    FETCH NEXT
    FROM @space INTO @servername;
    WHILE @@FETCH_STATUS=0
    BEGIN


    SET @sql = '['+@servername+']...xp_fixeddrives';
    INSERT INTO #freespace
    EXEC(@sql);
    FETCH NEXT FROM @space INTO @servername;
    END
    CLOSE @space;
    DEALLOCATE @space;
END

该servername表由链接服务器的服务器名称组成，例如 server1、server2。

当我运行此查询时，会显示如下错误：

Msg 7202, Level 11, State 2, Line 1
Could not find server 'server1' in sys.servers. Verify that the correct server name
was specified. If necessary, execute the stored procedure sp_addlinkedserver to add
the server to sys.servers.

 (0 row(s) affected)

OLE DB provider "SQLNCLI10" for linked server "server1" returned message "No
transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked
server "server1" was unable to begin a distributed transaction.

我认为问题出在动态 SQL 上。我已尝试对此查询进行许多更改，但仍然出现此错误。

更新

现在我需要将服务器名也包含在表中，如果我创建了一个额外的列“服务器名”并使用此查询插入值

SET @sql = insert into #freespace values(@servername,['+@servername+']...xp_fixeddrives');

EXEC(@sql);

但它显示一个错误，请帮助

sql-server stored-procedures

1 个回答

  1. Best Answer

    我已经在我的测试服务器上重新创建了您的场景，该服务器有 2 个手动创建的链接服务器。

    CREATE TABLE SERVERNAME
(
    [Name] nvarchar(255)
);

INSERT INTO SERVERNAME ([Name]) SELECT name from SYS.SERVERS;    
GO

CREATE PROCEDURE freespace
AS
BEGIN
    CREATE TABLE #freespace
    (
        drive VARCHAR(50)
        , freedrivespace VARCHAR(50)
    );
    DECLARE @servername VARCHAR(50);
    DECLARE @sql VARCHAR(100);
    DECLARE curspace CURSOR LOCAL FORWARD_ONLY FOR
    SELECT [Name] FROM SERVERNAME;
    OPEN curspace;
    FETCH NEXT FROM curspace INTO @servername;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = '[' + @servername + ']...xp_fixeddrives';
        INSERT INTO #freespace
        EXEC @sql;
        FETCH NEXT FROM curspace INTO @servername;
    END
    CLOSE curspace;
    DEALLOCATE curspace;
    SELECT * FROM #freespace;
END
GO

EXEC FREESPACE;

    我的服务器的结果是：

    drive   freedrivespace
C       75305
I       277458
K       861878
C       24378
D       90630
F       7575392
G       972578
H       768495
I       50952
J       7150657
C       13799
D       25162
F       6745607
G       982818
H       580167
I       50881
J       7224593

    由于未显示服务器的名称，因此结果并不完全可用，但它确实有效。这告诉我问题不在于您的动态 SQL；问题在于您的链接服务器配置。

    您是否能够手动运行一个简单的查询，例如：

    SELECT * FROM [server1].master.sys.tables;

    这应该返回结果，或者很可能是您在上面看到的相同错误。

    • 4

相关问题