July 8, 2013 at 8:43 am
I am attempting to execute the following code via SQL agent job.
USE [dataASH]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[usp_CaptureStatusData] (
@RunFeat TINYINT = 0,
@ELSonly TINYINT = 0
)
AS
DECLARE
@LSname SYSNAME
,@SQL NVARCHAR(3000)
,@curflag TINYINT
,@ErrString VARCHAR(200)
,@Trans INT
,@ret INT
--Initialization
SELECT @curflag = 0
--Processing
SET @Trans = 0
SET @Trans = @@TRANCOUNT
IF @Trans = 0
BEGIN TRAN
DECLARE ls_cursor INSENSITIVE CURSOR
FOR
SELECT
a.Name
FROM Sys.servers a (NOLOCK)
WHERE 1=1
AND a.is_linked = 1
AND a.server_id <> 0
ORDER BY a.Name
OPEN ls_cursor
FETCH NEXT
FROM ls_cursor
INTO @LSname
SELECT @curflag = 1
WHILE @@FETCH_STATUS = 0
BEGIN
---Loading Status Data
SELECT @sql = 'DECLARE @ret INT, @pdate DATETIME
SELECT @pdate = getdate()
EXEC @ret = ' + @LSname + '.DataAdmin.dbo.usp_ASHDbStatus @pdate
IF @@ERROR <> 0
RAISERROR(''Inner Error'', 16, 1)'
--PRINT @sql
EXEC (@SQL)
IF (@@ERROR <> 0) OR (@ret <> 0)
BEGIN
SELECT @ErrString = 'Error loading ' + @LSname + ' db status.'
GOTO ERREXIT
END
FETCH NEXT
FROM ls_cursor
INTO @LSname
END
CLOSE ls_cursor
DEALLOCATE ls_cursor
SELECT @curflag = 0
IF @Trans = 0 AND @@TRANCOUNT > 0
COMMIT TRAN
RETURN (0)
ERREXIT:
IF @curflag = 1
BEGIN
CLOSE ls_cursor
DEALLOCATE ls_cursor
END
IF @Trans = 0 AND @@TRANCOUNT > 0
ROLLBACK TRAN
RAISERROR ('%s', 16, 1, @ErrString)
RETURN(1)
This is a code rewrite that uses Dynamic SQL (as you can see 🙂 so that we dont have duplicate code thru out the SP. Below is the error message I am getting:
Executed as user: ASH\ASHSQLserv. The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "LS01" was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) OLE DB provider "SQLNCLI10" for linked server "LS01" returned message "The transaction manager has disabled its support for remote/network transactions.". [SQLSTATE 01000] (Error 7412). The step failed.
If I un-comment out the PRINT, I copy that result to a new Query Window and it executes successfully. I can run a SELECT statement across the Linked Server, so I dont think it is a problem with MSDTC. I did check all of the MSDTC settings though and everything appears to be started and in good order.
Below is what I get with my PRINT statement un-commented out:
DECLARE @ret INT, @pdate DATETIME
SELECT @pdate = getdate()
EXEC @ret = LS01.DataAdmin.dbo.usp_ASHDbStatus @pdate
IF @@ERROR <> 0
RAISERROR('Inner Code Block Error', 16, 1)
How can I get the above SPROC to loop over over the Linked Servers and execute the SQL dynamically?
Any and all help will be greatly appreciated.
Thanks in Advance!
July 8, 2013 at 8:56 am
It appears that all of this dynamic SQL is running in one giant distributed transaction across however many SQL servers are in your sys.servers table. For one thing, I would question why that is necessary, it makes whatever is happening require a lot more of a footprint on your concurrency and resources that would otherwise be the case with a different technique, but might not work correctly with differing brands/versions of databases servers (or SQL Servers).
What actually is the the stored procedure trying to do at the remote server?
The probability of survival is inversely proportional to the angle of arrival.
July 8, 2013 at 9:15 am
Sturner has a good point.
You might also want to check your firewall settings[/url].
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 8, 2013 at 9:17 am
The SPROC being called by the Dynamic SQL is a gathering metadata about each database on a Linked Server. The reason for this, is that we are undertaking a big migration, which means getting rid of Linked Servers, adding new Linked Servers, etc...The goal is to make the code Dynamic so that for each change requires minimal intervention. As you could guess, I have other SPROCs that I need to make dynamic as well.
Thank You
July 8, 2013 at 9:19 am
Chris, Thanks for the reply, but why would I need to check the firewall settings, when I can run the old SPROC that hardcodes everything (it works fine) and when I use the PRINT statement, I can execute the "guts" of the dynamic SQL with no problems.
Thank You
July 8, 2013 at 10:58 am
Gathering data across different databases and/or servers does not require that everything take place in one single transaction... unless I am missing something. That is why I was interested in what the procedure is doing.
The probability of survival is inversely proportional to the angle of arrival.
July 8, 2013 at 11:02 am
GBeezy (7/8/2013)
Chris, Thanks for the reply, but why would I need to check the firewall settings, when I can run the old SPROC that hardcodes everything (it works fine) and when I use the PRINT statement, I can execute the "guts" of the dynamic SQL with no problems.
Are you actually running all of the generated SQL all together in a single transaction as your original code is doing or just executing one iteration of the cursor?
The probability of survival is inversely proportional to the angle of arrival.
July 8, 2013 at 2:17 pm
The intention is to loop over each Linked Server, run the transaction for that Linked Server, and then fetch the next Linked Server. That being said, I have gotten this to work. By changing Server Option (within the Linked Server properties) "Remote Proc Transaction Promotion" to false, the SPROC was able to complete successfully. I still want to test this solution and see how it affects other internal processes that use the Linked Server.
Here are some links to some articles regarding the above mentioned server option:
http://technet.microsoft.com/en-us/library/ms178532.aspx
Thanks Again for all the help, I do appreciate it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply