Archive for the ‘SQL Scripts’ category

Log diskspace

September 18th, 2009

If you want to log the usage of diskspace on a computer it’s not that hard.. you just need some place to store the data and a script that runs at given intervals.

I have a table in my SQL Server.

CREATE TABLE Diskspace(
	index bigint IDENTITY(1,1) NOT NULL,
	TimeStamp datetime NOT NULL,
	FreePercent decimal(18, 3) NOT NULL,
	DriveLetter char(1) NOT NULL,
 CONSTRAINT PK__Diskspace__49C3F6B7 PRIMARY KEY CLUSTERED
(
	index ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
) ON PRIMARY

Then I have a VBScript that runs every five minutes (via Eventghost). You need to change databasename, user and password.

Set oDb = createobject("ADODB.Connection")
oDb.ConnectionString = "DRIVER={SQL Server Native Client 10.0};SERVER=localhost;DATABASE=TheDatabaseName;UID=MyUserName;PWD=SomePassword; OPTION=3"
oDb.Open

Set oWmiService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
Set oDisks = oWmiService.ExecQuery("Select * from Win32_LogicalDisk Where DriveType = 3")

For Each oDisk In oDisks
    sPercent = Replace(Round((oDisk.FreeSpace / oDisk.Size)*100, 1), ",", ".")
    sDisk = uCase(Replace(oDisk.DeviceID, ":", ""))

	sSql = "INSERT INTO diskspace (TimeStamp, FreePercent, DriveLetter) VALUES (GETDATE(), '" & sPercent & "', '" & sDisk & "')"
	WScript.Echo sSql
	oDb.Execute(sSql)
Next

oDb.Close

As easy as that…

No comments »

Posted in SQL Scripts, VBScripts

Tags:

Last temperature reading

September 13th, 2009

If you want to create a webpage or something similar you might want to show off the latest temperature reading.

This script will give you last reading in every table, if you want to exclude / include specific tables look at the “AND Name NOT LIKE” lines.

IF EXISTS(SELECT TABLE_NAME FROM TEMPDB.INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME LIKE '#TemperatureLast%')
   DROP TABLE #TemperatureLast
GO

CREATE TABLE #TemperatureLast (
   MSureName SYSNAME NOT NULL,
   TimeStamp DATETIME NOT NULL,
   Temperature DECIMAL(18, 3) NOT NULL,
)

DECLARE
   @query VARCHAR(2000),
   @currTable SYSNAME

DECLARE tablesCurr CURSOR
	FOR
	SELECT Name as TableName
		FROM  sysobjects
		WHERE xtype = 'U'
			AND Name NOT LIKE '%space%'
			-- AND Name NOT LIKE '%something%'
			-- AND Name LIKE 'Temperature%'
		ORDER BY TableName
	FOR READ ONLY

OPEN tablesCurr
   FETCH NEXT FROM tablesCurr INTO @currTable
   WHILE (@@FETCH_STATUS <> -1) BEGIN

		SET @Query = 'SELECT TOP 1 ''' + @currTable + ''', TimeStamp, Temperature FROM ' + @currTable + ' ORDER BY TimeStamp DESC'
		INSERT #TemperatureLast EXEC (@query)

      FETCH NEXT FROM tablesCurr INTO @currTable
   END
CLOSE tablesCurr
DEALLOCATE tablesCurr

SELECT * FROM #TemperatureLast
DROP TABLE #TemperatureLast

No comments »

Posted in SQL Scripts

Tags:

Spring and autumn

September 13th, 2009

According to SMHI autumn is when the average temperature of the day is falling and is between 0 and 10 degrees. And spring is when it’s rising and between 0 and 10 degrees.

Reference: http://www.smhi.se/cmp/jsp/polopoly.jsp?d=5938&l=sv

This “little” script will give you a resulting table with average temperature on UtomhusNorr calculated with the last five days.

IF EXISTS(SELECT TABLE_NAME FROM TEMPDB.INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME LIKE '#TemperatureAvg%')
   DROP TABLE #TemperatureAvg
GO

CREATE TABLE #TemperatureAvg (
   TimeStamp DATETIME NOT NULL,
   Temperature DECIMAL(18, 3) NOT NULL,
)

DECLARE
   @query VARCHAR(2000),
   @currDate DATETIME

DECLARE dateCurr CURSOR
   FOR
   SELECT DISTINCT CAST(CONVERT(CHAR(10),TimeStamp,20) AS DATETIME) as TempDates
   FROM UtomhusNorr
   WHERE TimeStamp &gt; DATEADD(month, -1, GETDATE())
   ORDER BY TempDates DESC
   FOR READ ONLY

OPEN dateCurr
   FETCH NEXT FROM dateCurr INTO @currDate
   WHILE (@@FETCH_STATUS &lt;&gt; -1) BEGIN
      SET @Query = 'SELECT MAX(TimeStamp) AS TimeStamp, AVG(Temperature) as Temperature FROM UtomhusNorr WHERE TimeStamp &gt; DATEADD(day, -5, ''' + CONVERT(NVARCHAR(32), @currDate) + ''') AND TimeStamp &lt; ''' + CONVERT(NVARCHAR(32), @currDate) + ''''
      INSERT #TemperatureAvg EXEC (@query)

      FETCH NEXT FROM dateCurr INTO @currDate
   END
CLOSE dateCurr
DEALLOCATE dateCurr

SELECT TimeStamp, Temperature AS AvgTemp5daysBack FROM #TemperatureAvg
DROP TABLE #TemperatureAvg

No comments »

Posted in SQL Scripts

Tags:

Migrate data from MySQL to SQL Server

September 13th, 2009

Since I work with MS SQL Server (and have done for the last 10 years) I converted the MySQL-databases in MSure to MS SQL.
This script will copy 4 tables, easy to add more.
Remember to change the ConnectionString’s to your settings. (server, database, user and password)

Set oMySQL = createobject("ADODB.Connection")
oMySQL.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=msure;UID=msure;PWD=abc123; OPTION=3"
oMySQL.Open

Set oSql = createobject("ADODB.Connection")
oSql.ConnectionString = "DRIVER={SQL Server Native Client 10.0};SERVER=localhost;DATABASE=msure;UID=MSure;PWD=abc123; OPTION=3"
oSql.Open

CopyTable "UtomhusNorr"
CopyTable "UtomhusOster"
CopyTable "UtomhusSoder"
CopyTable "UtomhusVaster"

sub CopyTable(sTable)
  set oSource = oMySQL.Execute("SELECT * FROM " & sTable)
  do while not oSource.Eof

    sSql = "INSERT INTO " & sTable & " (TimeStamp, Temperature) VALUES ('" & oSource("TimeStamp") & "', '" & Replace(oSource("Temperature"),",", ".") & "')"

    wscript.echo sSql
    oSql.Execute sSql

    oSource.MoveNext
  Loop
End Sub

oMySQL.Close
oSql.Close

No comments »

Posted in SQL Scripts, VBScripts

Tags: