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
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.
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