Spatial Data Generator

Came across this cool website, which lets you create T-SQL statements to insert geospatial data into SQL Server 2008 based on the location we select on Microsoft virtual earth
http://mikeo.co.uk/demo/sqlspatial/default.aspx
Have fun.

Attach DB and Shrink Log T-SQL Scripts

To attach a database with only a mdf file. (This will create new log file)
USE [master]
GO
CREATE DATABASE [Test] ON
( FILENAME = N'D:\Database\2008 Database\Test.mdf' )
FOR ATTACH
GO

To attach a database with mdf and ldf files.
USE [master]
GO
CREATE DATABASE [HandWashDW] ON
( FILENAME = N'D:\Database\2008 Database\HandWashDW.mdf' ),
( FILENAME = N'D:\Database\2008 Database\HandWashDW_log.ldf' )
FOR ATTACH
GO

To shrink the log file in SQL Server 2005
USE myDatabase
GO
DBCC SHRINKFILE(myDatabase_log, 1)
BACKUP LOG myDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(myDatabase_log, 1)


To shrink the logs in 2008, use the following
USE HandWashDW
GO
-- Declare variables
DECLARE @SqlStatement as nvarchar(max)
DECLARE @LogFileLogicalName as sysname

-- Alter the database to simple recovery
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY SIMPLE'
EXEC ( @SqlStatement )

-- Make sure it has been altered
SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()

-- Set the log file name variable
SELECT @LogFileLogicalName = [Name] FROM sys.database_files WHERE type = 1

-- Shrink the logfile
DBCC Shrinkfile(@LogFileLogicalName, 1)

-- Alter the database back to FULL
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY FULL'
EXEC ( @SqlStatement )

-- Make sure it has been changed back to full
SET @SqlStatement = 'SELECT [name], [recovery_model_desc] FROM ' + DB_NAME() + '.sys.databases WHERE [name] = ''' + DB_NAME() + ''''
EXEC ( @SqlStatement )

SQL Server Query Output to XML file

Use this to get the sql server query output into an XML file.
DECLARE @SQL VARCHAR(2000)
DECLARE @ExportResult INT

SET @SQL = 'bcp "SELECT * from [DBName].dbo.TableName FOR XML PATH (''OwnerStatus''), ROOT(''Root'')" queryout D:\EMS\Data.XML -c -t, -T -S SERVERNAME'

EXEC master..xp_cmdshell @SQL

EXEC @ExportResult = master..xp_cmdshell @SQL ,NO_OUTPUT

SELECT @ExportResult

The command requires that cmdshell be enabled. In sql server 2005 this is found in the surface area configuration. In 2008 this can be enabled in the SSMS by running the following.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
GO
RECONFIGURE