Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSupgrade_heterogeneous_subscriber_connectinfo

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSupgrade_heterogeneous_subscriber_connectinfo
AS
BEGIN
	DECLARE @retcode					int,
			@agent_id				int,
			@subscriber_provider			sysname,
			@subscriber_datasrc			nvarchar(4000),
			@subscriber_location			nvarchar(4000),
			@subscriber_provider_string		nvarchar(4000),
			@subscriber_catalog			sysname

	BEGIN TRANSACTION tr_upgrade_connectinfo
	SAVE TRANSACTION tr_upgrade_connectinfo

	-- PUSH DISTRIBUTION AGENTS
	IF OBJECT_ID(N'MSdistribution_agents', N'U') IS NOT NULL
	BEGIN
		 -- Here we loop through each heterogeneous distribution agent to update the
		 -- MSdistribution_agents table with connect information

		DECLARE cursorDistributionAgents CURSOR LOCAL FAST_FORWARD FOR
			SELECT msda.id,
					syss1.provider,
					syss1.data_source,
					syss1.location,
					syss1.provider_string,
					syss1.catalog
				FROM MSdistribution_agents msda
					JOIN sys.servers syss1
						ON syss1.server_id = msda.publisher_id
					JOIN sys.servers syss2
						ON syss2.server_id = msda.subscriber_id  AND
						   syss2.is_nonsql_subscriber = 1
				WHERE 	    msda.subscriber_provider IS NULL
					AND msda.subscriber_datasrc IS NULL
					AND msda.subscriber_location IS NULL
					AND msda.subscriber_provider_string IS NULL
					AND msda.subscriber_catalog IS NULL

		OPEN cursorDistributionAgents

		FETCH cursorDistributionAgents into @agent_id, @subscriber_provider, @subscriber_datasrc,
						 @subscriber_location, @subscriber_provider_string, @subscriber_catalog
		WHILE @@FETCH_STATUS <> -1
		BEGIN
			-- Update meta data
			UPDATE MSdistribution_agents
				SET 	subscriber_provider = @subscriber_provider,
					subscriber_datasrc = @subscriber_datasrc,
					subscriber_location = @subscriber_location,
					subscriber_provider_string = @subscriber_provider_string,
					subscriber_catalog = @subscriber_catalog
				WHERE id = @agent_id

			FETCH cursorDistributionAgents into @agent_id, @subscriber_provider, @subscriber_datasrc,
						 @subscriber_location, @subscriber_provider_string, @subscriber_catalog
		END
		
		CLOSE cursorDistributionAgents
		DEALLOCATE cursorDistributionAgents
	END

	COMMIT TRANSACTION tr_upgrade_connectinfo

	RETURN 0
UNDO:
	ROLLBACK TRANSACTION tr_upgrade_connectinfo
	COMMIT TRANSACTION

	RETURN 1
END

 
Last revision 2008RTM
See also

  sp_vupgrade_distdb (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash