October 26, 2012 at 6:41 am
Hey,
We have a Production SQLServer and a Report SQLServer (on which reporting queries are run). On Production we have a number of System Stored Procedures, that I want to copy on a daily basis to Report.
How can I do this?
Thx,
Raymond
October 26, 2012 at 6:51 am
if you've created your own procedures, and then marked them as system procs, it's easy.
1.you still script them like always (sp_helptext procname, or script them in the GUI.
2.Create them in the master database on the other server.
3. then mark each one individually as a system proc with sp_ms_marksystemobject
once marked, it can never be un-marked...you can only drop and recreate it instead.
--if you are going to put this in MASTER, and want it to be able to query
--each database's sys.tables/columns/objects, you MUST mark it as a system procedure:
--EXECUTE sp_ms_marksystemobject 'sp_GetDDLa'
Lowell
October 26, 2012 at 7:05 am
Hi Lowell,
Thanks for your answer. Unfortunately I didn't write my question all too well...
We have done all what you wrote. De SSP's on Report run flawlessly.
However, sometimes the SP's get updated on the Production Server. What I want is that those updates are copied automatically to the Report Server. Once a day, for example in a job that runs at night, is sufficient (because the databases from Production get restored on Report during the night as well).
October 26, 2012 at 7:23 am
can the two servers see each other, like on the same network or anything like that?
you could set up replication for preocedures, but i'm not sure the master database can be replicated to/from...are your procs in MASTER?
what I would do is the following:
1. on the production server, create a table( dbo.ProcedureSnapShot in my example to hold the contents of this query results:
SELECT
SCHEMA_NAME(SCHEMA_ID) as SchemaName,
name AS Procedurename,
type_desc,
Create_date,
modify_date
--INTO dbo.ProcedureSnapShot
FROM master.sys.procedures
WHERE SCHEMA_NAME(SCHEMA_ID) = 'dbo'
then i'd have a scheduled job do this however often the procs need to be checked:
SELECT
SCHEMA_NAME(procz.SCHEMA_ID) as SchemaName,
procz.name AS Procedurename,
procz.type_desc,
procz.Create_date,
procz.modify_date,
defz.definition
FROM master.sys.procedures procz
inner join master.sys.sql_modules defz
on procz.object_id = defz.object_id
LEFT OUTER JOIN dbo.ProcedureSnapShot snapz
ON SCHEMA_NAME(procz.SCHEMA_ID) = snapz.SchemaName
AND procz.name = snapz.Procedurename
WHERE snapz.Procedurename is NULL --newly created procedures
OR ( procz.Create_date <> snapz.Create_date --detect drop and recreated
OR procz.modify_date <> snapz.modify_date) --detect ALTER
that would have the DEFINITION of anything i needed to create or ALTER, based on the last snapshot fo the procs
since this is for metadata, a cursor that executed the definitino ont he report server would be fine.
then i'd drop or delete the ProcedureSnapShot table and populate it again to be ready to capture future changes again.
Lowell
October 26, 2012 at 8:26 am
However, sometimes the SP's get updated on the Production Server. What I want is that those updates are copied automatically to the Report Server. Once a day, for example in a job that runs at night, is sufficient (because the databases from Production get restored on Report during the night as well).
Sorry might be being abit naive here, but you could setup replication between the two servers and push stored procedure updates that way. You could also control the frequency of update and the update strategy through choosing the replication topology that suites you.
/>L
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
October 26, 2012 at 8:52 am
Raymond van Laake (10/26/2012)
What I want is that those updates are copied automatically to the Report Server. Once a day, for example in a job that runs at night, is sufficient (because the databases from Production get restored on Report during the night as well).
you can
1. set a DDL trigger on CREATE proc OR ALTER proc
2. catch the script
3. with the help of linked server ..insert into destination server's table
4. please a DML insert trigger there
5 . catch the every records there which is store proc's script basically
6. execuete there
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply