Connect and share data between our 2 databases

This may be a simple solution, but I am unable to figure out how to share data between our 2 sql server 2005 databases. What we need to do is have a stored procedure on our main database create tables, drop tables and truncate tables on our 2nd database. The stored procedure would also need to collect data from the main database and populate specific tables on the 2nd database through a single insert statement. This SP would need to be executed from a page on our website. I am able to successfully configure this on our localized development system, but can't seem to figure out what needs to be done on DASP. I am pretty sure we wouldn't want to create remote servers or anything and as long as there is permissible data read/write roles established for the same user on both databases I don't understand why it wouldn't work?

Thank you so much for any insight!
- Roger

 

Ok, that's exactly along the lines of what I was thinking we could do. Here is the stored procedure we tested on our local development system. To incorporate this into our DASP schema we would need to add the server name as well as the database name right? Also, how do we add additional users with the same credentials to both databases. From what I see in the "Add Additional SQL Users" it has a warning saying that users must be globally unique. Are you saying we can add the same user with like credentials to both our databases?

Thanks.

Stored Procedure (specific db and column info extracted)
------------------------------------------------------
CREATE PROCEDURE spv_RptMgrBaseRemote
(
/* Input parms descriptions */
/* ss = Session ID */
/* PageNum = Selected Page to return */
/* PageSize = Number of rows per page (how many records to return from dataset) */
/* addon = Where clause and Order by criteria */
/* nw = Switch denoting whether or not to create new dataset where: */
/* 1 = Force population of new dataset */
/* 0 = Use existing dataset */
@ss varchar(50) = '1',
@PageNum int = 1,
@PageSize int = 34,
@addon varchar(400) = '',
@nw int = 0
)
AS
DECLARE @iStart int, @iEnd int
DECLARE @err int, @SQLSTRING varchar(4000)
DECLARE @tt varchar(116)
DECLARE @doCrt int, @doUpd int

SET NOCOUNT ON
SET @iStart = (((@PageNum - 1) * @PageSize) + 1)
SET @iEnd = @PageNum*@PageSize

/*Unique Temp Table name for session*/
SET @tt = 'cmtmp'+@ss
SET @doCrt = 0
SET @doUpd = 0

/*Check if the cm temp table already exists.*/
IF EXISTS (SELECT [name] FROM {DESTINATION_DB}.[dbo].sysobjects WHERE [name] = @tt AND xtype = 'U')
BEGIN
IF @nw = 1
BEGIN
SET @doUpd = 1
SET @SQLSTRING = 'USE {DESTINATION_DB} Truncate Table ' + @tt
EXEC(@SQLSTRING)
END
END
ELSE
SET @doCrt = 1

/*create new session CM temp table*/
IF @doCrt = 1
BEGIN
/* tCnt, doCrt and doUpd are columns included for simple reference by calling application */
SET @doUpd = 1
SET @SQLSTRING =
'USE {DESTINATION_DB}
CREATE TABLE [dbo].['+@tt+'] (
r_id int identity(1,1) primary key,
{additional data columns needed from source tables},
tcnt int,
docrt int,
doupd int
)'

EXEC(@SQLSTRING)
END

IF @doUpd = 1
BEGIN
/*Insert records from criteria in order specified*/
SET @SQLSTRING = 'USE {LOCALSOURCE_DB}
INSERT INTO {DESTINATION_DB}.[dbo].' + @tt + '(
{Column List})
SELECT {Column List}
FROM {LOCALSOURCE_DB Tables} ' + @addon
EXEC(@SQLSTRING)

/*Get recordset count*/
SET @SQLSTRING = 'UPDATE {DESTINATION_DB}.[dbo].' + @tt + ' SET tcnt = (SELECT count(r_id) as cnt FROM {DESTINATION_DB}.[dbo].' + @tt + ')'
EXEC(@SQLSTRING)

END

/*Included switches as debug reference for calling app*/
SET @SQLSTRING = 'UPDATE {DESTINATION_DB}.[dbo].' + @tt + ' SET docrt = ' + CAST(@doCrt AS VARCHAR(1)) + ', doupd = ' + CAST(@doUpd AS VARCHAR(1))
EXEC(@SQLSTRING)

/* Select and return page range with only the number of records requested in page size */
SET @SQLSTRING = 'SELECT * from {DESTINATION_DB}.[dbo].'+@tt+' where
r_id between '+CAST(@iStart AS VARCHAR(50))+' and '+CAST(@iEnd AS VARCHAR(50))
EXEC(@SQLSTRING)
RETURN

 

PERFECT!
That worked like a charm. I had to experiment a bit with SQL Server 2005 Management console, but once I figured out exactly what needed to happen and the settings with the login roles, it came together perfectly!

Thank you so much Bruce!

 

Cool, glad it worked for you.