SQL Server

Function Main() dim pkg dim conTextFile dim conSQLDest dim stpEnterLoop dim stpFinished dim intLocation1 dim intLocation2 intLocation1 = instrrev(DTSGlobalVariables("gv_FileFullName").Value, "\") intLocation2 = instr(DTSGlobalVariables("gv_FileFullName").Value, ".") DTSGlobalVariables("gv_FileTableName") = Mid(DTSGlobalVariables("gv_FileFullName").Value, intLocation1+1, intLocation2-intLocation1-1) msgbox "FileTableName: " & DTSGlobalVariables("gv_FileTableName").Value set pkg = DTSGlobalVariables.Parent set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1") set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5") set conTextFile = pkg.Connections("Text File (Source)") set conSQLDest = pkg.Connections("SQLServerDestination") ' We want to continue with the loop only of there are more ' than 1 text file in the directory. If the function ShouldILoop ' returns true then we disable the step that takes us out of the package ' and continue processing if ShouldILoop = True then stpEnterLoop.DisableStep = False stpFinished.DisableStep = True conTextFile.DataSource = DTSGlobalVariables("gv_FileFullName").Value conSQLDest.DataSource = "[XTend_Load].[dbo].[" & DTSGlobalVariables("gv_FileTableName").Value &"_JU]" Msgbox "Destination: " & conSQLDest.DataSource stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting else stpEnterLoop.DisableStep =True stpFinished.DisableStep = False stpFinished.ExecutionStatus = DTSStepExecStat_Waiting End if Main = DTSTaskExecResult_Success End Function

I've been asked by my manager to come up with a script to run before and after an upgrade of SQL server to verify that basic statements within SQL work before and after an upgrade. Here's what I came up with. Anyone else ever do something like this?

/*
SQL Standard Health Check
DBA : Amy Boyd
Date: 10/5/2006

This is a script that can be run prior to and after the upgrade of
SQL server components to verify basic functionality.

*/

set nocount on
select 'Server Name: ' + @@servername + ' at version: ' + @@version

use admin -- or any other test database you might have
select 'Testing a select of a few sysobjects from admin db...'
select * from sysobjects where id < 5
if @@error = 0
begin
select 'Select of sysobjects successful'
select ''
end

select 'Testing table creation'
create table test_upgrade ( message varchar(100))
if @@error = 0
begin
select 'Table creation successful'
select ''
end

select 'Testing Insert into Table'
insert into test_upgrade values ('Table Insert Works')
if @@error = 0
begin
select 'Insert successful'
select ''
end

select 'Testing Update of Table'
update test_upgrade set message ='Table Update Works'
if @@error = 0
begin
select 'Update of table Successful'
select ''
end

select 'Testing Delete from Table'
delete from test_upgrade
if @@error = 0
begin
select 'Delete from table successful'
select ''
end

select 'Testing Drop table command'
drop table test_upgrade
if @@error = 0
begin
select 'Drop of table successful'
select ''
end

use msdb
select 'Testing a select of a few sysobjects from msdb db...'
select * from sysobjects where id < 5
if @@error = 0
begin
select 'Select of sysobjects successful'
select ''
end

use master
select 'Testing a select of a few sysobjects from master db...'
select * from sysobjects where id < 5
if @@error = 0
begin
select 'Select of sysobjects successful'
select ''
end

select 'Retreiving list of databases from ' + @@servername
select * from sysdatabases
select ''

if @@error = 0
begin
select 'Getting disk space stats for ' + @@servername
exec xp_fixeddrives
select ''
end

select 'Basic SQL Operations are working. Do not forget to test start/stop of SQL Server and Agent.'
select ''

if @@error > 0
select 'There was an error executing the health check please verify output for resolution.'