DTC, Distributed Query Error.
-
Hello, I am running a distributed query from one server. I have all the linkedservers setup correctly and I can query these linked server without error. I have this job which is run once a day to collect data file sizes into a table and I get this error.
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Here is my code, HELP ANYONE.
BEGIN
DECLARE @l_SerCursor_Csr CURSOR,
@l_DBCursor_Csr CURSOR,
@l_SQLStmt_Vch VARCHAR(300),
@l_ServerName_Vch VARCHAR(100),
@l_DBName_Vch VARCHAR(100)
CREATE TABLE #DATABASES
(
[DB_ID] INT IDENTITY(1,1),
[DB_NAME] VARCHAR(100)
 
CREATE TABLE #DATAFILEINFO
(
[ID] INT IDENTITY(1,1),
SRV_NAME VARCHAR(50),
[DB_NAME] VARCHAR(50),
FILEID INT,
GROUPID INT,
INT,
[MAXSIZE] INT,
GROWTH INT,
STATUS INT,
PERF INT,
[NAME] VARCHAR(50),
[FILENAME] VARCHAR(200)
 
SET @l_SerCursor_Csr = CURSOR FOR
SELECT SRVNAME
FROM MASTER.DBO.SYSSERVERS
OPEN @l_SerCursor_Csr
FETCH NEXT FROM @l_SerCursor_Csr INTO @l_ServerName_Vch
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #DATABASES EXEC('SELECT [NAME] FROM [' + @l_ServerName_Vch + '].MASTER.DBO.SYSDATABASES')
SET @l_DBCursor_Csr = CURSOR FOR
SELECT [DB_NAME]
FROM #DATABASES
OPEN @l_DBCursor_Csr
FETCH NEXT FROM @l_DBCursor_Csr INTO @l_DBName_Vch
WHILE @@FETCH_STATUS = 0
BEGIN
SET @l_SQLStmt_Vch = 'SELECT ''' + @l_ServerName_Vch + ''', ''' + @l_DBName_Vch + ''', FILEID, GROUPID, SIZE, MAXSIZE, GROWTH, STATUS, PERF, NAME, FILENAME FROM [' + @l_ServerName_Vch + '].' + @l_DBName_Vch + '.DBO.SYSFILES'
INSERT #DATAFILEINFO
EXEC(@l_SQLStmt_Vch)
FETCH NEXT FROM @l_DBCursor_Csr INTO @l_DBName_Vch
END
FETCH NEXT FROM @l_SerCursor_Csr INTO @l_ServerName_Vch
TRUNCATE TABLE #DATABASES
END
END
-
This was removed by the editor as SPAM
-
I just got this error and saw Technet references to a setting under Windows 2003 server which disables Network access to the DTC.
In my case though, I am trying to make a view - my query runs fine, it is just when I try to save the query that I get this error.
Did you work out what caused your error ?
The systems fine with no users loggged in. Can we keep it that way ?br>
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply