Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

15Apr/100
Inside Microsoft’s Data Centers

Check out this great Gizmodo article about Microsoft's Cloud computing data warehouses. Microsoft racks servers in shipping containers and can ship and install the containers anywhere in the world. This allows Microsoft to setup a Data Center quickly and efficiently. Pretty cool idea. Also reference Microsoft's Data Center blog.

18Feb/101
Move Your Mouse Using VBScript and Excel

This script uses the GetMessagePos and SetCursorPos Windows API functions to move your mouse. The script will first get the current position of the mouse by calling GetMessagePos. Next, the script will move the mouse using SetCursorPos. All this is done through an Excel macro. You can call Excel macros through the ExecuteExcel4Macro method. Needless to say, Excel must be installed locally for this script to work. I've found this script very useful for fooling screensavers.

Option Explicit
Dim Excel, GetMessagePos, x, y, Count, Position

Do While Count < 10
	Set Excel = Wscript.CreateObject("Excel.Application")

	GetMessagePos = excel.ExecuteExcel4Macro("CALL(""user32"",""GetMessagePos"",""J"")")

	x = CLng("&H" & Right(Hex(GetMessagePos), 4))
	y = CLng("&H" & Left(Hex(GetMessagePos), (Len(Hex(GetMessagePos)) - 4)))
	If Count MOD 2 = 0 Then
		Position = "- 30"
	Else
		Position = "+ 30"
	End If
	Excel.ExecuteExcel4Macro("CALL(""user32"",""SetCursorPos"",""JJJ""," & x & " " & Position & "," & y & " " & Position & ")")

	WScript.Sleep(100)
	Count = Count + 1

Loop
WScript.Echo "Program Ended"

Reference: http://msdn.microsoft.com/en-us/library/aa191494%28office.10%29.aspx

14Dec/090
Finding Duplicate Rows in SQL

I used this to solve a problem today.

// Finding duplicates in a table

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

// Find rows that occur exactly once

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

// Return all duplicate cities and how often they appear
// Works with ORACLE

select city_name, count(city_name) as cnt
from areas
group by city_name
where cnt>1

// not all SQL dbms will support the reference to the count column cnt in the where clause.
// The following will return ALL rows with counter, but sorted by number of appearances
// Your duplicates will be at the top.
// Works with MYSQL

select city_name, count(city_name) as cnt
from areas
group by city_name
order by cnt desc

// finally, no back reference to count column cnt at all-
// the following will work on all SQL dbms:
// Return all cities and how often they appear

select city_name, count(city_name) as cnt
from areas
group by city_name

// version for Micrsoft's MSSQL Server
// make use of the HAVING clause

select city_name
from areas
group by city_name
having count(*) > 1

// multiple columns in MySQL:
SELECT PL_CONTACT_FK, PL_PHONE_FK, COUNT(*) as DuplicateCount FROM PHONE_ALTERNATE GROUP BY PL_CONTACT_FK, PL_PHONE_FK HAVING DuplicateCount > 1;

10Jun/090
How to Turn on xp_cmdshell for SQL 2005

The xp_cmdshell function can be very helpful for running commands on an SQL server. However, turning on the function comes with risks. I'll discuss those risks later... If you try to run xp_cmdshell on a server where it is not turned on, you may receive this error message:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

To turn xp_cmdshell on, log in to the SQL server as sa, and run this command:

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
8Jun/090
Script to Add a New SQL Differential Backup Job With Daily Incremental Zip Archives

This script builds on the differential backup script, adding a zipped incremental backup. This script will create a zip file for each day of the week. I've used this script when I have a simple database type but need the flexibility of multiple daily differential backups. You will have to turn on xp_cmdshell to run this script.

-- This script creates a differential backup job to backup the DBName database as DBName_Differential.bak (daily at 5:00 PM). This script also creates daily incremental zip archives.

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DBName_Differential.bak', @server_name = N'SERVER\INSTANCE'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 1: Backup File', 
	@step_id=1, 
	@cmdexec_success_code=0, 
	@on_success_action=3, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'BACKUP DATABASE [PBSA] TO  DISK = N''C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak'' WITH  DIFFERENTIAL ,  DESCRIPTION = N''DBName_Differential.bak'', NOFORMAT, INIT,  NAME = N''DBName_Differential.bak'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 2: Zip Backup File', 
	@step_id=2, 
	@cmdexec_success_code=0, 
	@on_success_action=1, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'xp_cmdshell ''C:\PROGRA~1\WinZip\winzip32.exe -a -en "C:\MSSQL.1\MSSQL\Backup\Backup_zipped\DBName_Differential.bak - %date:~0,3%.zip" "C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak"''', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@start_step_id=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@description=N'', 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', 
	@notify_email_operator_name=N'', 
	@notify_netsend_operator_name=N'', 
	@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DBName_Differential.bak', @name=N'Schedule 1', 
	@enabled=1, 
	@freq_type=4, 
	@freq_interval=1, 
	@freq_subday_type=1, 
	@freq_subday_interval=0, 
	@freq_relative_interval=0, 
	@freq_recurrence_factor=1, 
	@active_start_date=20080725, 
	@active_end_date=99991231, 
	@active_start_time=193000, 
	@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
7Jun/090
Script to Add a New SQL Differential Backup Job

I know you've been waiting for this... This script will add a differential backup job to Microsoft SQL Server 2005. Use this script in conjunction with a full backup.

-- This script creates a differential backup job to backup the DBName database as DBName_Differential.bak (daily at 5:00 PM)

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DBName_Differential.bak', @server_name = N'SERVER\INSTANCE'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 1: Backup File', 
	@step_id=1, 
	@cmdexec_success_code=0, 
	@on_success_action=3, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'BACKUP DATABASE [DBName] TO  DISK = N''C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak'' WITH DIFFERENTIAL, DESCRIPTION = N''DBName_Differential.bak'', NOFORMAT, INIT,  NAME = N''DBName_Differential.bak'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 2: Zip Backup File', 
	@step_id=2, 
	@cmdexec_success_code=0, 
	@on_success_action=1, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'CmdExec', 
	@command=N'"C:\Program Files\WinZip\winzip32.exe" -a -en "C:\MSSQL.1\MSSQL\Backup\Backup_zipped\DBName_Differential.zip" "C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak"', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@start_step_id=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@description=N'', 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', 
	@notify_email_operator_name=N'', 
	@notify_netsend_operator_name=N'', 
	@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DBName_Differential.bak', @name=N'Schedule 1', 
	@enabled=1, 
	@freq_type=4, 
	@freq_interval=1, 
	@freq_subday_type=1, 
	@freq_subday_interval=0, 
	@freq_relative_interval=0, 
	@freq_recurrence_factor=1, 
	@active_start_date=20080725, 
	@active_end_date=99991231, 
	@active_start_time=170000, 
	@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
6Jun/090
Script to Add a New SQL Backup Job

This script is for the times when I like to automate the task of adding database backup jobs to Microsoft SQL Servers. I'll sometimes add a dozen jobs at once. This script saves time and keeps things consistent. The script adds two tasks and a schedule:

1. Database backup
2. Zip backup file

Using WinZip (you'll need a license) to zip the backup file makes it easier to move the file to an offsite location. This script also makes it easy to add or remove steps. Enjoy...

-- This script creates a backup job to backup the DBName database as DBName_Full.bak (daily at 10:00 PM)

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'DBName_Full.bak', 
	@enabled=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DBName_Full.bak', @server_name = N'SERVER\INSTANCE'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Full.bak', @step_name=N'Step 1: Backup File', 
	@step_id=1, 
	@cmdexec_success_code=0, 
	@on_success_action=3, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'BACKUP DATABASE [DBName] TO  DISK = N''C:\MSSQL.1\MSSQL\Backup\DBName_Full.bak'' WITH  DESCRIPTION = N''DBName_Full.bak'', NOFORMAT, INIT,  NAME = N''DBName_Full.bak'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Full.bak', @step_name=N'Step 2: Zip Backup File', 
	@step_id=2, 
	@cmdexec_success_code=0, 
	@on_success_action=1, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'CmdExec', 
	@command=N'"C:\Program Files\WinZip\winzip32.exe" -a -en "C:\MSSQL.1\MSSQL\Backup\Backup_zipped\DBName_Full.zip" "C:\MSSQL.1\MSSQL\Backup\DBName_Full.bak"', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DBName_Full.bak', 
	@enabled=1, 
	@start_step_id=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@description=N'', 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', 
	@notify_email_operator_name=N'', 
	@notify_netsend_operator_name=N'', 
	@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DBName_Full.bak', @name=N'Schedule 1', 
	@enabled=1, 
	@freq_type=4, 
	@freq_interval=1, 
	@freq_subday_type=1, 
	@freq_subday_interval=0, 
	@freq_relative_interval=0, 
	@freq_recurrence_factor=1, 
	@active_start_date=20080725, 
	@active_end_date=99991231, 
	@active_start_time=220000, 
	@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO