SQL - some musings
http://www.sqljunkies.com/article/92cc4817-604d-4344-8be0-4490f8ed24b6.scuk
SQL needs a lot of loving. Just leaving it to get on with life over a few
months, an you'll find logs are filling up
Prehaps a table's got full of junk?
--select count(*) from dat_decoded_diagnostics_b
--delete from dat_decoded_diagnostics_b
-- truncate table dat_decoded_diagnostics_b
Transaction logs are choking?
--alter database [mydb] set offline with rollback immediate
--dbcc shrinkfile (mydb,2)
--alter database [mydb] set online
Are you really logging that data stream that terminates in FF?
select substring(payload, len(payload)-1 , 4) as lastbit, len(payload) as length from DATASTREAM_LOG
order by length desc
Using BCP to create and export a CSV file
bcp aprs..APRS_LOCATION_TABLE out c:\APRS_LOCATION_TABLE.txt -U user -P password -S
APRS_DB_1 -n -c -t,
Using BCP to create and export a text file
bcp aprs..APRS_LOCATION_TABLE out c:\APRS_LOCATION_TABLE.txt -U user -P password -S
APRS_DB_1 -n -c
...and a suite of 4 stored procedures to import that data - with manual
rollback
- prcUploadLocation -- imports data into temporary table for cutting live
- prcSaveLocation -- creates table to save the old data and saves it
- prcUpdateLocation -- copies new data into live table
- prcRestoreLocation -- the rollback proc
CREATE procedure prcUploadLocation
as
begin
--imports data into temporary table for cutting live
set nocount on
if EXISTS (select * from INFORMATION_SCHEMA.tables where table_name = 'APRS_LOCATION_NEW'
AND TABLE_CATALOG = 'APRS_UPDATES')
DROP TABLE aprs_updates.dbo.APRS_LOCATION_NEW
SELECT * INTO aprs_updates.dbo.APRS_LOCATION_NEW
FROM aprs.dbo.APRS_LOCATION_TABLE WHERE 1=2
BULK INSERT aprs_updates.dbo.APRS_LOCATION_NEW FROM 's:\aprs\APRS_LOCATION_TABLE.txt' WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n'
)
end
GO
|
CREATE procedure prcSaveLocation
as
begin
--creates tables to save the old data and saves it
set nocount on
if EXISTS (select * from INFORMATION_SCHEMA.tables where table_name = 'APRS_LOCATION_OLD'
AND TABLE_CATALOG = 'APRS_UPDATES')
DROP TABLE APRS_LOCATION_OLD
select * INTO aprs_updates.dbo.APRS_LOCATION_OLD
from aprs.dbo.APRS_LOCATION_TABLE
end
GO
|
CREATE procedure prcUpdateLocation
as
begin
--copies new data into live table
set nocount on
truncate table aprs.dbo.APRS_LOCATION_TABLE
insert into aprs.dbo.APRS_LOCATION_TABLE
select * from aprs_updates.dbo.APRS_LOCATION_NEW
end
GO
|
CREATE procedure prcRestoreLocation
as
begin
--This is the rollback proc
set nocount on
truncate table aprs.dbo.APRS_LOCATION_TABLE
insert into aprs.dbo.APRS_LOCATION_TABLE
select * from aprs_updates.dbo.APRS_LOCATION_OLD
end
GO
|