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

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