September 9, 2013 at 3:40 am
Hi,
How can I query a remote server using server functions and save that data to a local table?
I have a simple proc that collects server information from a linkedserver.
EXEC usp_MyProc 'Param' gives the desired results.
INSERT <Tbl> EXEC usp_MyProc 'Param' gives an error. See below in the code.
IF OBJECT_ID('TempDB..#TmpDBs') IS NULL
BEGIN
CREATE TABLE #TmpDBs
(
SvrNameVarchar(128) NOT NULL,
DBNAmeVarchar(128) NOT NULL,
ComPatLvlTinyInt NOT NULL
)
END
GO
CREATE PROC usp_TmpDBs
(@Svr Varchar(128))
AS
BEGIN
-- Testing parameter
--DECLARE@svrVarchar(128)
--SELECT@svr= 'Svr002'
-- Variables
DECLARE@SQLVarchar(MAX),
@SQLSPVarchar(MAX)
--BEGIN TRAN
DELETE#TmpDBs WHERE SvrName = @svr
--Code 1:
SET @SQL = '
SELECT@@SERVERNAME SvrName, D.Name, D.[Compatibility_Level]
FROM['+@Svr+'].[master].sys.Databases D'
-- Both of these SET lines return the same error. See below for the error.
SET @SQLSP = 'EXEC ('''+@SQL+''') AT '+@Svr
--SET @SQLSP = 'EXEC ['+@Svr+'].[master].[dbo].[sp_ExecuteSQL] @Statement = N'''+@SQL + ''''
EXEC (@SQLSP)
-- Code 2:
/* This code works fine but cannot use functions such as @@VERSION or SERVERPROPERTY('ProductVersion') etc..
SET @SQL = 'INSERT #TmpDBs
SELECT '''+@Svr+''' SvrName, D.Name, D.[Compatibility_Level]
FROM['+@Svr+'].[master].sys.Databases D'
EXEC (@SQL)
*/
--COMMIT TRAN
END
EXEC usp_TmpDBs @svr = 'Svr002' -- Returns the desired results, when not using BEGIN / COMMIT TRAN
INSERT #TmpDBs EXEC usp_TmpDBs @svr = 'Svr002' -- gives error :
/*
OLE DB provider "SQLNCLI11" for linked server "Svr002" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "Svr002" was unable to begin a distributed transaction.
*/
/* After turning on DTC on Svr002.
OLE DB provider "SQLNCLI" for linked server "Svr002" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "Svr002" was unable to begin a distributed transaction.
*/
I have tried the following links to no avail.
the-partner-transaction-manager-has-disabled-its-support-for-remotenetwork-transactions
how-to-configure-dtc-on-windows-2008[/url]
how-to-configure-dtc-on-windows-2003[/url]
Local machines:
Windows 7 SQL + 2012 Express
Windows 2003 Enterprise + SQL 2005 Enterprise
Remote machine
Windows 2008 R2 Enterprise + SQL 2008 R2 Enterprise
September 9, 2013 at 4:04 pm
So your problem is really about how to get DTC to work. And that is not trivial if the conditions are not the rights ones. In a domain with trust between the servers, there is rarely any problems. In a workgroup - I've been fighting that battle myself. I think was successful last time by have the same name for the service accounts on both machines and also the same password.
Then again, you can use sp_serveroption to turn off "remote proc transaction promotion" for the server.
And then yet again, you don't need to use INSERT EXEC, look at this article on my web site for alternatives: http://www.sommarskog.se/share_data.html.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 10, 2013 at 3:04 am
Thanks Erland!!
sp_ServerOption is exactly what I need.
EXEC sp_serveroption '<LinkedServer>', 'remote proc transaction promotion', 'false'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply