Showing posts with label regards. Show all posts
Showing posts with label regards. Show all posts

Wednesday, February 15, 2012

execute DBCC on linked server

Hi,

How do you execute 'DBCC' statements or 'EXEC ...' against a linked server (SQL 2000 sp4) ?

Regards,

A.E

EXEC [linkedserver].master.dbo.sp_executesql N'DBCC USEROPTIONS'

|||

Thanks Mark, your suggestion works fine but now I have another problem when I do the following for example:

INSERT [sometable] EXEC [LinkedServer].pubs.dbo.sp_executesql N'EXEC sp_helpfile'

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
The transaction active in this session has been committed or aborted by another session.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver]Distributed transaction error]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].

I followed the instructions in article 839279 but still get the error, any ideas?. I don't need DTC transaction support, is there a way to get the results without involving MSDTC ?

Regards,

A.E

|||


If you've tried everything in

http://support.microsoft.com/kb/839279

then there's not a lot more I can add.

BTW, the last time I saw this, it was fixed simply by specifying
SET XACT_ABORT ON before the EXEC.