Setting up shared servers in Windows 2003.Shared servers allows multiple servers to communicate with each other through queries and stored procedires.Example: Server1 can read/write/query to Server2 and visa versa
SET xact_abort ON
GO
USE pubs
GO
BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM [server-name].pubs.dbo.authors
COMMIT TRAN
GO
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE .....
CREATE PROCEDURE dbo.SomeProcedureName
AS
SET XACT_ABORT ON
...
GO
CREATE PROCEDURE dbo.SomeProcedureName
/*
mReschke 2008-01-20
Adds a new template and binds it to a module (planID)
*/
@planID as int,
@templateName as nvarchar(50),
@templateFile as nvarchar(50),
@templateFile2 as nvarchar(50),
@templateTypeID as int,
@templateSize as nvarchar(50),
@coopID as int
AS
SET XACT_ABORT ON
DECLARE @server as nvarchar(50), @sql as nvarchar(4000) DECLARE SharedServers CURSOR FORWARD_ONLY FOR SELECT server FROM ebis_prod.dbo.SharedServerList OPEN SharedServers FETCH NEXT FROM SharedServers INTO @server BEGIN TRANSACTION
SET @sql = 'DECLARE @identity int BEGIN TRANSACTION' WHILE @@FETCH_STATUS = 0 BEGIN IF (@server <> '') BEGIN SET @sql = @sql +
'
INSERT INTO [' + @server + '].Ebis_Prod.dbo.tblWizardTemplates (templateName, templateFile, templateFile2, templateTypeID, templateSize, coopID)
VALUES ('''+@templateName+''', '''+@templateFile+''', '''+@templateFile2+''', '+CONVERT(varchar,@templateTypeID)+', '''+@templateSize+''', '+CONVERT(varchar, @coopID)+')
SET @identity = (SELECT templateID FROM ['+@server+'].Ebis_Prod.dbo.tblWizardTemplates WHERE
templateName = '''+@templateName+'''
AND templateFile = '''+@templateFile+'''
AND templateFile2 = '''+@templateFile2+''')
INSERT INTO [' + @server + '].Ebis_Prod.dbo.tblWizardModuleTemplates (planID, templateID)
VALUES ('+CONVERT(varchar, @planID)+', @identity)
'
END FETCH NEXT FROM SharedServers INTO @server END SET @sql = @sql + ' COMMIT TRANSACTION' exec (@sql) COMMIT CLOSE SharedServers DEALLOCATE SharedServers
GO
CREATE PROCEDURE dbo.someprocedure
/* mReschke 2008-01-21 Update to add planID, templateID to tblWizardTemplateWebControlOptions on insert */
@controlID integer,
@planID as int,
@templateID as int,
@optionID integer,
@optionTypeID integer,
@optionHTML ntext
AS
DECLARE @identity as int
SET XACT_ABORT ON
IF @optionID > 0
BEGIN
BEGIN DISTRIBUTED TRANSACTION
UPDATE tblWizardTemplateOptions SET
optionHTML = @optionHTML,
optionTypeID = @optionTypeID
WHERE optionID = @optionID
UPDATE [dyna-sql].Ebis_Prod.dbo.tblWizardTemplateOptions SET
optionHTML = @optionHTML,
optionTypeID = @optionTypeID
WHERE optionID = @optionID
COMMIT TRANSACTION
END
ELSE
BEGIN
SET @identity = (SELECT MAX(optionID) + 1 FROM tblWizardTemplateOptions)
if (@identity > 0)
BEGIN
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO tblWizardTemplateOptions (optionID, optionTypeID, optionHTML) VALUES (@identity, @optionTypeID, @optionHTML)
INSERT INTO [dyna-sql].Ebis_Prod.dbo.tblWizardTemplateOptions (optionID, optionTypeID, optionHTML) VALUES (@identity, @optionTypeID, @optionHTML)
INSERT tblWizardTemplateWebControlOptions (planID, templateID, webControlID, optionID)
VALUES (@planID, @templateID, @controlID, @identity)
INSERT [dyna-sql].Ebis_Prod.dbo.tblWizardTemplateWebControlOptions (planID, templateID, webControlID, optionID)
VALUES (@planID, @templateID, @controlID, @identity)
COMMIT TRANSACTION
END
END
GO
INSERT INTO [localserver1].db.dbo.table1 'HI'
SELECT @@IDENTITY
INSERT INTO [remoteserver1].db.dbo.table1 'HI'
SELECT @@IDENTITY