Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need a stored procedure to check the free space in 2 linked servers .(the servers are linked manually) .I have comed up with a query like this and using 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

The servername table consist of servername of linked servers like server1, server2.

When I run this query an error like this is displayed:

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.

I think the problem is with the dynamic SQL. I've tried many changes to this query, but still I get this error.

Update

now I need to include the servername also into the table for that if i created an extra column 'servername' and used this query for inserting values

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

EXEC(@sql);

But it shows an error ,please help

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

I've recreated your scenario on my test server that has 2 linked servers that were created manually.

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;

The results for my server are:

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

Granted the results are not exactly usable since the name of the server is not shown, however it does work. This tells me the problem is not in your dynamic SQL; the problem lies with your linked server configuration.

Are you able to manually run a simple query such as:

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

This should either return results, or most likely the same error you are seeing above.

share|improve this answer
    
When i run the query above mentioned it says cannot find the server .But when i run the stored procedure manually giving the servername it returns the results . –  Biju jose Oct 20 '12 at 6:22
    
It worked Vernon ,the problem was i didn't mentioned the instance name.And about the servername ,how we include the servername in #freetable using the @servername varible.I am now working on it .Please help –  Biju jose Oct 20 '12 at 9:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.