Month: March 2008

Getting size estimates for tables in SQL SERVER 2000

Posted on by tech0x20 

-- SQL Server tables and row counts

declare tables_cursor cursor for 
select name from sysobjects where xtype='U'
declare @table_name sysname

open tables_cursor
fetch next from tables_cursor INTO @table_name

while @@fetch_status = 0
begin
    exec sp_spaceused @table_name
    fetch next from tables_cursor INTO @table_name
end
close tables_cursor
deallocate tables_cursor

Restoredb.bat

Posted on by tech0x20

Using osql and trusted connection for SQL Server 2000.


@echo off

if {%2}=={} (
   call :Usage
   goto :EOF
)
net stop mssqlserver
net start mssqlserver
osql -E -d master -Q "restore database 
%1 from disk='%2' with replace"

goto :EOF
::Subroutine area 

:Usage
   @echo Syntax: call restoredb.bat DatabaseName
FullDatabaseBackupPath

goto :EOF

“Playtime” project: Porting Didiwiki to C#.NET

Posted on by tech0x20

I’m hoping to find time to throw at least a quick port of DidiWiki to C#.NET together.

Reference 1: Creating your own Web Server using C# – This article is from 2001, so there might be a significantly better way of doing things.

Update: This CodeGuru page looks like a more original version of the same page.

Reference 2: Didiwiki Source Code that I’ve saved off. Original maintainer is no longer maintaining.

Importing a runningahead log.xml into SQL Server 2005 using OPENXML

Posted on by tech0x20

I don’t currently have a work-based use for 2005, so I thought I’d use the only readily available collection of data that I have at my disposal… my running log. This is an export file from RunningAhead.com. I don’t think I’m completely loading the XML data yet, and I don’t have database tables to load to yet, either. However, this is my implementation of some OPENXML examples.


DECLARE @hdoc int
DECLARE @xmlDoc XML
SET @xmlDoc = (
  SELECT * FROM OPENROWSET (
    BULK ‘C:projectsrunningaheadlog.1.321C02log.xml’, SINGLE_CLOB
  ) AS xmlData
)
SELECT @xmlDoc

EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlDoc

SELECT * FROM
OPENXML(@hdoc, ‘/RunningAHEADLog/CourseCollection/Course’, 2)
WITH
(
        ID varchar(32),
        Name varchar(255) ,
        Surface varchar(255),
        City varchar(255),
        State char(3),
        Notes varchar(255)
)

SELECT * FROM
OPENXML(@hdoc, ‘/RunningAHEADLog/EquipmentCollection/Shoe’, 2)
WITH
(

        ID varchar(32),
        Make varchar(255) ,
        Model varchar(255),
        Serial char(20),
        InitDistance decimal(7,2),
        PurchaseDate datetime,
        Retired char(3),
        Size char(10),
        SizeSystem char(40)
)

SELECT * FROM
OPENXML(@hdoc, ‘/RunningAHEADLog/EventCollection/Run’, 2)
WITH
(
        ID char(32),
        Date datetime,
        Time datetime,
        Type char(15),
        Weight decimal(4,1),
        WeightUnit char(12),
        AvgHR tinyint,
        MaxHR tinyint,
        Quality tinyint,
        Effort tinyint,
        CourseID char(32),
        Distance decimal(8,2),
        DistanceUnit char(12),
        Duration char(20)
)

EXEC sp_xml_removedocument @hdoc