Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Job - Scheduled update table in another databaes nightly - please review and comment on script / process Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 4:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 16, 2014 11:16 AM
Points: 123, Visits: 345
Please review Newbie's code and plan: Comments welcome
SQL 2008 - Database name FOO FOO has 2 databases DBT and GIS Only Table in GIS is GISDaily
Database GIS has SQL Server Security for the GIS dept username/Password set to DBO
The GISDaily will be set to a scheduled task to run. The GIS_Info is a Table (or view) that will be modified (e.g. new fields) from time-to-time
Each night - the GISDaily will be destroyed and rebuilt. The GIS Database will have GISDaily re-built each night.

USE DBT;
GO
IF OBJECT_ID('dbo.GIS.GISDaily', 'U') IS NOT NULL
DROP TABLE dbo.GIS.GISDaily;
GO
ALTER DATABASE GIS SET RECOVERY BULK_LOGGED;
GO
SELECT * INTO GIS.dbo.GISDaily
FROM GIS_Info
GO
ALTER DATABASE GIS SET RECOVERY Simple;
GO

-- the idea is to rebuild GISDaily from a view in DBT. The GIS dept will provide changes in the DBT view.
-- The UN/PW for the SQL server is only used for the GIS DB. So, the rebuilt table will be available to them.
Post #1372989
Posted Wednesday, October 17, 2012 2:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:07 AM
Points: 868, Visits: 2,775
Firstly, it should be GIS.dbo.GISDaily and not dbo.GIS.GISDaily.
Secondly you can leave the database in simple recovery.

USE DBT;
GO

IF OBJECT_ID('GIS.dbo.GISDaily', 'U') IS NOT NULL
DROP TABLE GIS.dbo.GISDaily;
GO

SELECT *
INTO GIS.dbo.GISDaily
FROM GIS_Info
GO





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1373668
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse