Início / dba / Perguntas / 27250
Accepted
Biju jose
Asked: 2012-10-20 06:57:56 +0800 CST

Como posso criar um procedimento armazenado usando xp_fixeddrives?

  • 772

Eu preciso de um procedimento armazenado para verificar o espaço livre em 2 servidores vinculados. (os servidores são vinculados manualmente). Eu criei uma consulta como esta e usando o 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

A servernametabela consiste em servername de servidores vinculados como server1, server2.

Quando executo esta consulta, um erro como este é exibido:

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.

Acho que o problema é com o SQL dinâmico. Eu tentei muitas alterações nesta consulta, mas ainda recebo esse erro.

Atualizar

agora eu preciso incluir o servername também na tabela para isso se eu criar uma coluna extra 'servername' e usar esta consulta para inserir valores

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

EXEC(@sql);

Mas dá um erro, por favor me ajude

sql-server stored-procedures

1 respostas

  1. Best Answer

    Eu recriei seu cenário no meu servidor de teste que possui 2 servidores vinculados que foram criados manualmente.

    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;

    Os resultados para o meu servidor são:

    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

    Concedido, os resultados não são exatamente utilizáveis, pois o nome do servidor não é mostrado, mas funciona. Isso me diz que o problema não está no seu SQL dinâmico; o problema está na configuração do servidor vinculado.

    Você é capaz de executar manualmente uma consulta simples, como:

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

    Isso deve retornar resultados ou provavelmente o mesmo erro que você está vendo acima.

    • 4

relate perguntas