DZone Snippets: Spearmahj's Code Snippets

Financial Year Calculation

spearmahj (j) — Mon, 26 Feb 2007 05:24:37 GMT

select (case when
datepart(mm,reqDate) between 7 and 12 then
datename(yy,reqDate) + '-' + datename(yy,dateadd(yy,1,reqDate))
else
datename(yy,dateadd(yy,-1,reqDate)) + '-' + datename(yy,reqDate)
end),
reqDate
from request
order by 1, 2

Vbscript Array sort in alphabetical order

spearmahj (j) — Wed, 03 Jan 2007 09:59:03 GMT

// sort vbscript array



<%

dim arrSortOut(8)

arrSortOut(0)="xCount"

arrSortOut(1)="zExec"

arrSortOut(2)="yFinance"

arrSortOut(3)="HR"

arrSortOut(4)="IT "

arrSortOut(5)="!aaaLegal"

arrSortOut(6)="Liberman"

arrSortOut(7)="Martha"

arrSortOut(8)="Regis"



for x=0 to 8

response.write arrSortOut(x)&"
"

next



response.write "
"



for i = UBound(arrSortOut) - 1 To 0 Step -1

    for j= 0 to i

        if arrSortOut(j)>arrSortOut(j+1) then

            temp=arrSortOut(j+1)

            arrSortOut(j+1)=arrSortOut(j)

            arrSortOut(j)=temp

        end if

    next

next 





for x=0 to 8

response.write arrSortOut(x)&"
"

next



%>

CSS - Show Application Icon next link

spearmahj (j) — Tue, 02 Jan 2007 05:45:14 GMT

// This CSS code will show the appropriate icon next to a HREF link















email

pop up

word 

excel

website

pdf

pdf2

pdf3

Write all ASP page variables

spearmahj (j) — Tue, 02 Jan 2007 04:38:18 GMT

// description of your code here



<%

  Response.Write "Server Variables" & "

"

For Each strName in Request.ServerVariables

  Response.Write strName & " - " & Request.ServerVariables(strName) & "
"

Next



  Response.Write "Session Variables" & "

"

For Each strName in Session.Contents

  Response.Write strName & " - " & Session.Contents(strName) & "
"

Next



  Response.Write "Form Variables" & "

"

For Each strName in request.form

  Response.Write strName & " - " & request.form(strName) & "
"

Next



  Response.Write "String Variables" & "

"

For Each strName in request.querystring

  Response.Write strName & " - " & request.querystring(strName) & "
"

Next

%>

Clipboard functionality

spearmahj (j) — Tue, 02 Jan 2007 04:31:18 GMT

// description of your code here



 



copy this text

Simple FSO directory file listing

spearmahj (j) — Tue, 02 Jan 2007 04:28:41 GMT

// simple browsing tool



<%



dim aryURL, URLvar

aryURL = Split(Request.ServerVariables("SCRIPT_NAME"), "/", -1, 1)

URLvar = aryURL(ubound(aryURL))



if request.querystring("sPP")<>"" then 

	

	sPP=request.querystring("sPP") & "\"

	ParentVar="Parent Directory

" & vbCrLF

else

	sPP = "\\serverlocation\"

end if



	

	Set fso = CreateObject("Scripting.FileSystemObject")

	Set f = fso.GetFolder(sPP)  

	Set fc = f.Files 

	Set ff = f.SubFolders 

	For Each f1 in ff	

	  HeaderVar=HeaderVar& "" & f1.name & "  | " & vbCrLF

	Next  

	For Each f1 in fc

	  SubHeaderVar=SubHeaderVar& "" & f1.name & " 
" & vbCrLF

	Next  

	Set ff = nothing

	Set fso = nothing

	Set f = nothing

	Set fc = nothing





response.write ParentVar

if HeaderVar<>"" then response.write "Folders" & vbCrLF & "

 | " & vbCrLF & HeaderVar & "

" & vbCrLF

if SubHeaderVar<>"" then response.write "Files" & vbCrLF & "

" & vbCrLF & SubHeaderVar & vbCrLF



%>

SQL server ASP database test

spearmahj (j) — Tue, 02 Jan 2007 03:55:42 GMT

// basic sql server database asp code test



<%

str_ConnectionString = "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=database;User Id=user;Password=pwd;"

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open str_ConnectionString



set rs = Server.CreateObject("ADODB.recordset")

strSQL="select * from tablename"



rs.Open strSQL, oConn



do until rs.EOF

	for each x in rs.Fields

		response.write x.value & "|"

	next

	rs.MoveNext

	response.write "
"

loop	



rs.Close



oConn.Close

set oConn = Nothing

%>

sql server backup script for all user databases

spearmahj (j) — Tue, 02 Jan 2007 03:35:50 GMT

// This code will create new backup locations on disk on a daily / weekly / yearly basis



-- Declare variables



declare @count int, @max int, @day varchar(10), @path varchar(1000), @dbname varchar(55)

declare @device varchar(255), @physical varchar(1255), @lastbackup smalldatetime



-- Set count to start at database ID +1 

-- master	1

-- tempdb	2

-- model		3

-- msdb		4

-- pubs		5

-- Northwind	6



set @count = 7



-- Set maximum no of databases on server



--set @max = (select count(*) from master..sysdatabases) +1

set @max = 12



-- Set backup path



set @path='D:\SQL\backups\'



-- Get unique cycle number

-- For weekly cycle - datepart(dw,getdate())

-- For monthly cycle - datepart(dd,getdate())

-- For yearly cycle - datepart(dy,getdate())



set @day=datepart(dw,getdate())



-- While the starting number of databases is smaller than the maximum number

-- of databases:

-- 1.	Check if there is already a backup device made for this

-- day of the cycle. If there isn't create the device.

-- 2.	See if a backup has already been done for this day of the cycle. If 

-- it hasn't do a full backup, otherwise do a differential backup.

-- 3.	Repeat for next database.



while (@count < @max)

begin

	

	set @dbname = (select [name] from master..sysdatabases where dbid=@count)

	set @device=@dbname + '_' + @day

	

	if (select count(*) from master..sysdevices where name = @dbname + '_' + @day)=0 begin

		set @physical=@path+@device + '.bak'

		EXEC sp_addumpdevice 'disk', @device, @physical

	end

	

	begin

	

		set @lastbackup=(select max(backup_finish_date) 

		from msdb.dbo.backupmediafamily, msdb.dbo.backupset

		where msdb.dbo.backupset.media_set_id=msdb.dbo.backupmediafamily.media_set_id

		and logical_device_name=@device

		)

		

		if datepart(dd, @lastbackup)!=datepart(dd,getdate()) begin

			BACKUP DATABASE @dbname TO @device WITH INIT

		end

		else begin

			BACKUP DATABASE @dbname TO @device WITH DIFFERENTIAL

		end 

	

	end  

	

	set @count = @count + 1  



continue 

break



end