Code Library

VBScript
Find the default printer on a machine Submitted by eoszakiewski on 8/27/2009 12:50:00 PM

Dim printer, regkey, WSHShell
Set WSHShell = CreateObject("WScript.Shell")
regkey="HKEY_CURRENT_USERSoftwareMicrosoftWindows NTCurrentVersionWindows"
printer = WSHShell.RegRead(regkey & "Device")
WScript.Echo(printer)

Determine Free Disk space on a machine Submitted by eoszakiewski on 8/27/2009 12:51:00 PM

Const HARD_DISK = 3

strComputer = "."
strResult = strComputer & " results:" & vbCrLf
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\" & strComputer & "rootcimv2")

Set colDisks = objWMIService.ExecQuery _
    ("Select * from Win32_LogicalDisk Where DriveType = " & HARD_DISK & "")

For Each objDisk in colDisks
    strResult = strResult & objDisk.DeviceID & " has " & CInt(objDisk.FreeSpace / 1000000000) & " GB free" & vbCrLf
Next

WScript.Echo strResult

How to recreate the default web site in IIS6 Submitted by eoszakiewski on 8/27/2009 12:54:00 PM

c:
cdInetpubAdminScripts
cscript adsutil.vbs enum w3svc

Look for the folder entries at the end, which start with square brackets. You'll have w3svc/Info, w3svc/Filters, and hopefully at least one w3svc/### where the number is the interesting thing. If there are more than one then you can find the name of each with:

cscript adsutil.vbs enum w3svc/###

(Putting in the ###s that you saw in the list.) Check out the ServerComment property to know its name.  Find a numbered web in the list from above that you can use as a template to copy from.


Now that you know an ID to use as a source, use these commands to copy this to a new web with ID 1:

cscript adsutil.vbs create_vserv W3SVC/1
cscript adsutil.vbs copy W3SVC/### W3SVC/1
cscript adsutil.vbs set w3svc/1/ServerComment "Default Web Site"

See last login for all users on screen and in report Submitted by eoszakiewski on 8/27/2009 12:56:00 PM

Option Explicit

Const ForAppending = 8

Dim objRootDSE, adoConnection, adoCommand, strQuery
Dim adoRecordset, strDNSDomain, objShell, lngBiasKey
Dim lngBias, k, strDN, dtmDate, objDate
Dim strBase, strFilter, strAttributes, lngHigh, lngLow

' Obtain local Time Zone bias from machine registry.
Set objShell = CreateObject("Wscript.Shell")
lngBiasKey = objShell.RegRead("HKLMSystemCurrentControlSetControl" _
    & "TimeZoneInformationActiveTimeBias")
If (UCase(TypeName(lngBiasKey)) = "LONG") Then
    lngBias = lngBiasKey
ElseIf (UCase(TypeName(lngBiasKey)) = "VARIANT()") Then
    lngBias = 0
    For k = 0 To UBound(lngBiasKey)
        lngBias = lngBias + (lngBiasKey(k) * 256^k)
    Next
End If
Set objShell = Nothing

' Determine DNS domain from RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
Set objRootDSE = Nothing

' Use ADO to search Active Directory.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = adoConnection

' Search entire domain.
strBase = ""

' Filter on all user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"

' Comma delimited list of attribute values to retrieve.
strAttributes = "distinguishedName,lastLogonTimeStamp"

' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"

' Run the query.
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 60
adoCommand.Properties("Cache Results") = False
Set adoRecordset = adoCommand.Execute

Dim objFSO, objTextFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("c:results.txt", ForAppending, True)

' Enumerate resulting recordset.
Do Until adoRecordset.EOF
   ' Retrieve attribute values for the user.
    strDN = adoRecordset.Fields("distinguishedName").Value
    ' Convert Integer8 value to date/time in current time zone.
    On Error Resume Next
    Set objDate = adoRecordset.Fields("lastLogonTimeStamp").Value
    If (Err.Number <> 0) Then
        On Error GoTo 0
        dtmDate = #1/1/1601#
    Else
        On Error GoTo 0
        lngHigh = objDate.HighPart
        lngLow = objDate.LowPart
        If (lngLow < 0) Then
            lngHigh = lngHigh + 1
        End If
        If (lngHigh = 0) And (lngLow = 0 ) Then
            dtmDate = #1/1/1601#
        Else
            dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _
                + lngLow)/600000000 - lngBias)/1440
        End If
    End If
    ' Display values for the user.
    If (dtmDate = #1/1/1601#) Then
        'Wscript.Echo strDN & ";Never"
	objTextFile.WriteLine(strDN & " Never")
    Else
        'Wscript.Echo strDN & ";" & dtmDate
	objTextFile.WriteLine(strDN & ";" & dtmDate)
    End If
    adoRecordset.MoveNext
Loop

' Clean up.
adoRecordset.Close
adoConnection.Close
Set adoConnection = Nothing
Set adoCommand = Nothing
Set adoRecordset = Nothing
Set objDate = Nothing
objTextFile.Close
WScript.Echo("Done")

Remote Shutdown Submitted by eoszakiewski on 8/27/2009 6:05:00 PM

function Ping(byval strName)
dim objFSO, objShell, objTempFile, objTS
dim sCommand, sReadLine
dim bReturn
set objShell = WScript.CreateObject("Wscript.Shell")
set objFSO = CreateObject("Scripting.FileSystemObject")
bReturn = false
sCommand = "cmd /c ping.exe -n 3 -w 1000 " & strName & " > C:temp.txt"
objShell.run sCommand, 0, true
set objTempFile = objFSO.GetFile("C:temp.txt")
set objTS = objTempFile.OpenAsTextStream(1)
do while objTs.AtEndOfStream <> true
sReadLine = objTs.ReadLine
if instr(lcase(sReadLine), "reply from") > 0 then
bReturn = true
exit do
end if
loop
objTS.close
objTempFile.delete
set objTS = nothing
set objTempFile = nothing
set objShell = nothing
set objFSO = nothing
Ping = bReturn
end function

ComputerName=InputBox("Enter the Machine name of the computer" & vbCRLF & "you wish to Shutdown / Reboot / Logoff", "Remote Shutdown / Reboot / Logoff", "ComputerName")
If (ComputerName = "") Then Wscript.Quit
ComputerName=UCase(ComputerName)
bPingtest = ping(Computername)
If bPingtest = FALSE Then
y = msgbox ("'" & ComputerName & "' is not accessible!" & vbCRLF & "It may be offline or turned off." & vbCRLF & "Check the name for a typo." & vbCRLF, vbCritical, ComputerName & " NOT RESPONDING")
Wscript.Quit
end IF
Action=InputBox("Select Action to perform on " & ComputerName & vbCRLF & vbCRLF & " 1 - Logoff" & vbCRLF & " 2 - Force Logoff ( NO SAVE )" & vbCRLF & " 3 - Powerdown" & vbCRLF & " 4 - Force Powerdown ( NO SAVE )" & vbCRLF & " 5 - Reboot" & vbCRLF & " 6 - Force Reboot ( NO SAVE )" & vbCRLF & vbCRLF & "NOTE:" & vbCRLF & " Using Force will close windows" & vbCRLF & " without saving changes!", "Select action to perform on " & ComputerName, "")
If (Action = "") Then Wscript.Quit
If (INSTR("1234567",Action)=0) OR (Len(Action)>1) then
y = msgbox("Unacceptable input passed -- '" & Action & "'", vbOKOnly + vbCritical, "That was SOME bad input!")
Wscript.Quit
end if
flag = 0
Select Case Action
Case 1 'Logoff
x = 0
strAction = "Logoff sent to " & ComputerName
flag = 1
Case 2 'Force Logoff 
x = 4
strAction = "Force Logoff sent to " & ComputerName
flag = 1
Case 3 'Powerdown
x = 8
strAction = "Powerdown sent to " & ComputerName
flag = 1
Case 4 'Force Powerdown
x = 12
strAction = "Force Powerdown sent to " & ComputerName
flag = 1
Case 5 'Reboot
x = 2
strAction = "Reboot sent to " & ComputerName
flag = 1
Case 6 'Force Reboot
x = 6
strAction = "Force Reboot sent to " & ComputerName
flag = 1
Case 7 'Test dialog boxes
y = msgbox("Test complete", vbOKOnly + vbInformation, "Dialog Box Test Complete")
flag = 0
Case Else 'Default -- should never happen
y = msgbox("Error occurred in passing parameters." & vbCRLF & " Passed '" & Action & "'", vbOKOnly + vbCritical, "PARAMETER ERROR")
flag = 0
End Select
if flag then
Set OpSysSet=GetObject("winmgmts:{(Debug,RemoteShutdown)}//" & ComputerName & "/root/cimv2").ExecQuery( "Select * from Win32_OperatingSystem where Primary=true")
for each OpSys in OpSysSet
OpSys.Win32Shutdown(x)
y = msgbox(strAction,vbOKOnly + vbInformation,"Mission Accomplished")
next
end If
set OpSys = nothing
set OpSysSet = nothing

C#
Left Right and Mid in ASP.Net Submitted by eoszakiewski on 8/27/2009 11:02:00 AM

public static string Left(string param, int length)
{
 string result = param.Substring(0, length);
 return result;
}

public static string Right(string param, int length)
{
 string result = param.Substring(param.Length - length, length);
 return result;
}

public static string Mid(string param,int startIndex, int length)
{
 string result = param.Substring(startIndex, length);
 return result;
}

public static string Mid(string param,int startIndex)
{
 string result = param.Substring(startIndex);
 return result;
}

Handle button click in GridView row in ASP.Net Submitted by eoszakiewski on 8/27/2009 11:05:00 AM

GridViewRow row = (GridViewRow)((Button)sender).NamingContainer;
int r = row.RowIndex;

Get the response of a MessageBox Submitted by eoszakiewski on 8/27/2009 12:43:00 PM

DialogResult dlgResult = MessageBox.Show("Do you want to continue?", "Continue?", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dlgResult == DialogResult.Yes)
{
  // Yes, continue
}
else if (dlgResult == DialogResult.No)
{
  // No, stop
}

Return a single value from a SQL query to a variable in ASP.Net Submitted by eoszakiewski on 8/27/2009 12:45:00 PM

SqlConnection con = new SqlConnection(strConnection);
SqlCommand cmd = new SqlCommand("", con);
Object returnValue;
con.Open();
returnValue = cmd.ExecuteScalar();
con.Close();

Query AD properties Submitted by eoszakiewski on 1/25/2010 1:01:00 PM

using System.DirectoryServices; // May need to add reference to the same library

            System.DirectoryServices.DirectoryEntry entry = new System.DirectoryServices.DirectoryEntry("LDAP://DC=domain,DC=com");
            System.DirectoryServices.DirectorySearcher srchr= new System.DirectoryServices.DirectorySearcher(entry);
            srchr.Filter = ("");
            srchr.PropertiesToLoad.Add("EmployeeID");
            foreach (System.DirectoryServices.SearchResult result in srchr.FindAll())
            {
                username = result.GetDirectoryEntry().Name;
                userid = (string)result.Properties["EmployeeID"][0];
            }

Regex for Date Submitted by eoszakiewski on 2/9/2011 1:39:00 PM

Date format mm-dd-yyyy:
^(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)dd$

Misc RegEx Submitted by eoszakiewski on 2/27/2013 12:47:00 PM

DateTime:
(d{2})/(d{2})/(d{4}) (d{2}):(d{2}):(d{2})

SmallDateTime:
^(([1-9])|(0[1-9])|(1[0-2]))/(([0-9])|([0-2][0-9])|(3[0-1]))/(([1][9][0-9][0-9])|([2][0][0-7][0-9]))$

Money:
^-?(?$?s*-?s*(?(((d{1,3}((,d{3})*|d*))?(.d{1,4})?)|((d{1,3}((,d{3})*|d*))(.d{0,4})?)))?$

Int:
d{1,10}

T-SQL
See all tables where a column name exists - SQL 2000 Submitted by eoszakiewski on 8/26/2009 9:45:00 PM

select so.name
from syscolumns sc
inner join sysobjects so
on (so.id = sc.id)
where sc.name like '%%'

How to determine which version of SQL Server is running Submitted by mperillo on 8/27/2009 11:23:00 AM

SELECT 
  SERVERPROPERTY('productversion') 'Product Version'
, SERVERPROPERTY ('productlevel') 'Product Level'
, SERVERPROPERTY ('edition') 'Edition'

Select random records from SQL Submitted by eoszakiewski on 8/27/2009 12:57:00 PM

SELECT   TOP 5 d.definition, t.term
   FROM   tblDefinitions d
	join tblTerms t on (d.pkid = t.pkid)
   ORDER BY NEWID()

Check for existence of a temp table Submitted by eoszakiewski on 9/10/2009 1:25:00 PM

IF object_id('tempdb..#temp_table_name') IS NOT NULL
   DROP TABLE #temp_table_name

See all tables where a column name exists - SQL 2005 Submitted by eoszakiewski on 9/14/2009 12:36:00 PM

select so.name, sc.name
from syscolumns sc
inner join sys.objects so
on (so.object_id = sc.id)
where sc.name like '%%'

Nth weekday of a month Submitted by eoszakiewski on 10/21/2009 11:18:00 AM

DECLARE @date datetime
DECLARE @weekday int
DECLARE @day datetime
DECLARE @number int
DECLARE @WhichOne int

SELECT @WhichOne = 2 -- Set this to the week you want to grab
SELECT @number = 1
SELECT @weekday = 0
SELECT @date = getDate() -- Change to dateadd(month, 1, getDate()) for next month, etc.

WHILE @weekday <> 4 -- Change this value to be the day of the week you're looking for
BEGIN
SELECT @day = (CAST(STR(MONTH(@date)) + '/' + STR(@number) + '/'+ STR(YEAR(@date)) AS DATETIME))
SELECT @weekday = DATEPART(weekday, @day)
SELECT @number = @number + 1
END

SELECT DATEADD(d, (@WhichOne - 1) * 7, @day)

Find all objects where text exists in SQL 2000 Submitted by eoszakiewski on 10/29/2009 1:44:00 PM

SELECT name, type 
FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id
WHERE text like '%%'
ORDER BY name

Find all objects where text exists in SQL 2005+ Submitted by eoszakiewski on 07/19/2013 12:06:00 PM

SELECT name, type 
FROM sys.objects so INNER JOIN syscomments sc ON so.object_id = sc.id
WHERE text like '%%'
ORDER BY name

Find duplicate records in SQL Submitted by eoszakiewski on 1/8/2010 12:32:00 PM

select 
from 
group by 
having count(*) > 1

Bulk Insert from .CSV file Submitted by eoszakiewski on 1/28/2010 1:26:00 PM

BULK INSERT 
FROM 'c:file.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = 'n' )

Calculate HYF from Date Submitted by eoszakiewski on 2/23/2010 2:06:00 PM

-- Converts date as nvarchar(10) to hundred year format
-- Suitable for AS/400 applications or Excel calculations

DateDiff(day, Convert(datetime, '01/01/1900'), Convert(datetime, )) + 1

See all column names in an object Submitted by eoszakiewski on 3/8/2010 1:21:00 PM

SELECT table_name=sysobjects.name,
		 column_name=syscolumns.name,
         datatype=systypes.name,
         length=syscolumns.length
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.name = '
' ORDER BY sysobjects.name,syscolumns.colid

Top n rows per group Submitted by eoszakiewski on 8/4/2011 2:44:00 PM

select *
from
(
    Select 
      b.field1
      , b.field2
      , field3
      ,rank() over (partition by b.field1 order by field3 desc) as field_Rank
    from table b
    where b.field1 > 
    group by b.field1, b.field2
) 
  
where field_Rank <= 10

Create a duplicate table on the fly Submitted by eoszakiewski on 10/26/2011 9:55:00 AM

/* Using an existing table in a database, to make a duplicate table run the following */

select top 0 * into 
from 

See all tables in a DB2 database Submitted by eoszakiewski on 12/27/2011 8:27:00 AM

select dbname, name from sysibm.tables order by dbname

Read contents of long running query causing blocking Submitted by eoszakiewski on 3/3/2012 5:19:00 PM

/* When a long running query is causing blocking in SQL, it's nice to know the */
/* details of the query, to give you an idea of who ran it and what they were */
/* trying to do before killing it.  Here's a query that tells you the query */
/* contents of the first blocked spid in SQL.  If there are more than one, */
/* you'll need to kill one before it'll pick up another. */

declare @spid int, @cnt int
set @cnt = (select count(*) from master..sysprocesses where blocked <> 0)
if @cnt > 0
begin
set @spid = (select top 1 spid from master..sysprocesses where blocked <> 0)
dbcc inputbuffer (@spid)
end
else
begin
print 'No blocked records found'
end

Server Monitor Submitted by eoszakiewski on 5/7/2012 2:04:00 PM

SELECT
  HOST_NAME  AS [System Name]
, program_name AS [Application Name]
, DB_NAME(database_id) AS [DATABASE Name]
, USER_NAME(USER_ID) AS [USER Name]
, connection_id AS [CONNECTION ID]
, sys.dm_exec_requests.session_id AS [CURRENT SESSION ID]
, blocking_session_id AS [Blocking SESSION ID]
, start_time AS [Request START TIME]
, sys.dm_exec_requests.status AS [Status]
, command AS [Command Type]
, (SELECT TEXT FROM sys.dm_exec_sql_text(sql_handle)) AS [Query TEXT]
, wait_type AS [Waiting Type]
, wait_time AS [Waiting Duration]
, wait_resource AS [Waiting FOR Resource]
, sys.dm_exec_requests.transaction_id AS [TRANSACTION ID]
, percent_complete AS [PERCENT Completed]
, estimated_completion_time AS [Estimated COMPLETION TIME (in mili sec)]
, sys.dm_exec_requests.cpu_time AS [CPU TIME used (in mili sec)]
, (memory_usage * 8) AS [Memory USAGE (in KB)]
, sys.dm_exec_requests.total_elapsed_time AS [Elapsed TIME (in mili sec)]
FROM sys.dm_exec_requests
INNER join sys.dm_exec_sessions 
ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id
WHERE	(blocking_session_id <> 0					--blocked process
		or sys.dm_exec_requests.cpu_time > 50000	--CPU intensive
		or (memory_usage * 8) >	25000				--Memory intensive
		)

Top 10 slowest SSRS reports Submitted by eoszakiewski on 5/10/2012 1:02:00 PM

SELECT TOP 10 C.Path, C.Name, Count(*) AS ReportsRun,
   CONVERT(VARCHAR,DATEADD(ms,AVG((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)), 0), 108)
                                            AS AverageProcessingTime,
   CONVERT(VARCHAR,DATEADD(ms,Max((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)), 0), 108)
                                            AS MaximumProcessingTime,
   CONVERT(VARCHAR,DATEADD(ms,Min((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)), 0), 108)
                                            AS MinimumProcessingTime
 FROM reportserver..ExecutionLog EL
   INNER JOIN reportserver..Catalog C ON EL.ReportID = C.ItemID
 WHERE EL.TimeStart>Datediff(d,GetDate(),-28)
 GROUP BY C.Path,C.Name
 ORDER BY AVG((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)) DESC 

Top 20 most recently run SSRS reports Submitted by eoszakiewski on 5/10/2012 1:03:00 PM

SELECT TOP 20 C.Path, C.Name, EL.UserName, EL.Status, EL.TimeStart,
     EL.[RowCount], EL.ByteCount,
    (EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)/1000 AS TotalSeconds,
     CONVERT(VARCHAR,DATEADD(ms,EL.TimeDataRetrieval, 0), 108) as TimeDataRetrieval
   , CONVERT(VARCHAR,DATEADD(ms,EL.TimeProcessing, 0), 108) as TimeProcessing
   , CONVERT(VARCHAR,DATEADD(ms,EL.TimeRendering, 0), 108) as TimeRendering
   FROM reportserver..ExecutionLog EL
    INNER JOIN reportserver..Catalog C ON EL.ReportID = C.ItemID
   ORDER BY TimeStart DESC 

Most active SSRS users Submitted by eoszakiewski on 5/10/2012 1:03:00 PM

SELECT TOP 10 EL.UserName, Count(*) AS ReportsRun,
   Count(DISTINCT C.[Path]) AS DistinctReportsRun
  FROM reportserver..ExecutionLog EL
    INNER JOIN reportserver..Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.TimeStart>Datediff(d,GetDate(),-28)
  GROUP BY EL.UserName
  ORDER BY Count(*) DESC 

Top 10 most popular reports Submitted by eoszakiewski on 5/10/2012 1:04:00 PM

SELECT TOP 10 C.Path, C.Name, Count(*) AS ReportsRun,
  CONVERT(VARCHAR,DATEADD(ms,AVG((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)), 0), 108)
                                            AS AverageProcessingTime,
   CONVERT(VARCHAR,DATEADD(ms,Max((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)), 0), 108)
                                            AS MaximumProcessingTime,
   CONVERT(VARCHAR,DATEADD(ms,Min((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)), 0), 108)
                                            AS MinimumProcessingTime
  FROM reportserver..ExecutionLog EL
   INNER JOIN reportserver..Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.TimeStart>Datediff(d,GetDate(),-28)
  GROUP BY C.Path, C.Name
  ORDER BY Count(*) DESC 

Top 20 failed SSRS reports Submitted by eoszakiewski on 5/10/2012 1:04:00 PM

SELECT TOP 20 C.Path, C.Name, EL.UserName, EL.Status, EL.TimeStart,
   EL.[RowCount], EL.ByteCount,
   (EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)/1000 AS TotalSeconds,
   CONVERT(VARCHAR,DATEADD(ms,EL.TimeDataRetrieval, 0), 108) as TimeDataRetrieval
   , CONVERT(VARCHAR,DATEADD(ms,EL.TimeProcessing, 0), 108) as TimeProcessing
   , CONVERT(VARCHAR,DATEADD(ms,EL.TimeRendering, 0), 108) as TimeRendering
  FROM reportserver..ExecutionLog EL
   INNER JOIN reportserver..Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.Status <> 'rsSuccess'
  ORDER BY TimeStart DESC 

First and Last day of the month Submitted by eoszakiewski on 8/14/2012 11:26:00 AM

DECLARE @FirstDayOfMonth datetime, @LastDayOfMonth datetime
/* First day of last month */
SET @FirstDayOfMonth = (select convert(datetime, convert(nvarchar(2), month(dateadd(month, -1, getdate()))) + '/01/' + convert(nvarchar(4), year(dateadd(month, -1, getdate())))))
/* Last day of last month */
SET @LastDayOfMonth = (select dateadd(day, -1, convert(datetime, convert(nvarchar(2), month(getdate())) + '/01/' + convert(nvarchar(4), year(getdate())))))

Total days in current month Submitted by eoszakiewski on 8/24/2012 12:45:00 PM

select DAY(DATEADD(DAY, - 1, DATEADD(MONTH, 1, DATEADD(DAY, - (DAY(getdate())) + 1, getdate()))))

Get all stored procedures in SQL 2000 Submitted by eoszakiewski on 3/19/2013 12:15:22 PM

select * from sysobjects where type = 'P'

PowerShell
Move a SP2010 web inside a site collection Submitted by eoszakiewski on 8/4/2011 2:28:00 PM

cls
# Prompt the user for the source project name URL fragment
#
$input = Read-Host "Enter the website (ex: http://portal/site)"
#
# Set variables
#
$sourceURL = $input
$web = Get-SPWeb $sourceURL
$web = $sourceWeb.Name
$targetURL = "http://portal/archive/"+$sourceName
$title = $web.Title
#
# Let's begin
#
Write-Host "Extracting source URL"
#
# Export the source site to the desktop
#
Export-SPWeb $sourceURL -Path "C:userspublicdocumentstemp.cmp" -Force -IncludeUserSecurity -IncludeVersions all -NoFileCompression -NoLogFile
#
# In PowerShell, we need to create the website first, then import it.  This is a by-design security feature
# built into PowerShell.
#
Write-Host "Migrating site to target URL"
New-SPWeb $targetURL -Template "STS#0"
#
# Update the target title
#
$web = Get-SPWeb $targetURL
$web.Title = $title
$web.Update()
#
# Import the same site, overwriting the existing one we just made
#
Import-SPWeb $targetURL -Path "C:userspublicdocumentstemp.cmp" -UpdateVersions Overwrite -Force -IncludeUserSecurity -NoFileCompression -NoLogFile
Write-Host "Removing source site"
#
# Remove the source site
#
Remove-SPWeb $sourceURL -Confirm:$false
Write-Host "Completed"

Check all non-Completed workflows in a SharePoint list Submitted by eoszakiewski on 9/6/2012 11:29:00 AM

# Usage = c:>./.ps1 > outputfile.txt
# Edit all items in <> brackets
#
$web = Get-SPWeb ;
$web.AllowUnsafeUpdates = $true;

$list = $web.Lists[];
foreach($item in $list.Items) {
foreach($wf in $item.Workflows) {
if($wf.InternalState -ne "Completed") {
$x = $wf.ItemName.TrimEnd() + "`t" + $item[].tostring() + "`t" + $item[].tostring();
write-output $x;
}}} $web.Dispose();

HTML report of selected servers/computers hard drive space Submitted by eoszakiewski on 1/23/2014 12:44:00 PM

#requires -version 2.0 
#set $Path argument below
#set path to list of machines you want to check in Get-Content below
 
Param ( 
$computers = (Get-Content  "") 
) 
 
$Title="Hard Drive Report" 
$Path=""

#embed a stylesheet in the html header 
$head = @" 
 
$Title 

"@ #define an array for html fragments $fragments=@() #get the drive data $data=Get-WmiObject -Class Win32_logicaldisk -filter "drivetype=3" -computer $computers #group data by computername $groups=$Data | Group-Object -Property SystemName #this is the graph character [string]$g=[char]9608 #create html fragments for each computer #iterate through each group object ForEach ($computer in $groups) { $fragments+="

$($computer.Name)

" #define a collection of drives from the group object $Drives=$computer.group #create an html fragment $html=$drives | Select @{Name="Drive";Expression={$_.DeviceID}}, @{Name="SizeGB";Expression={$_.Size/1GB -as [int]}}, @{Name="UsedGB";Expression={"{0:N2}" -f (($_.Size - $_.Freespace)/1GB) }}, @{Name="FreeGB";Expression={"{0:N2}" -f ($_.FreeSpace/1GB) }}, @{Name="Usage";Expression={ $UsedPer= (($_.Size - $_.Freespace)/$_.Size)*100 $UsedGraph=$g * ($UsedPer/2) $FreeGraph=$g* ((100-$UsedPer)/2) #I'm using place holders for the < and > characters "xopenFont color=Redxclose{0}xopen/FontxclosexopenFont Color=Greenxclose{1}xopen/fontxclose" -f $usedGraph,$FreeGraph }} | ConvertTo-Html -Fragment #replace the tag place holders. It is a hack but it works. $html=$html -replace "xopen","<" $html=$html -replace "xclose",">" #add to fragments $Fragments+=$html #insert a return between each computer $fragments+="
" } #foreach computer #add a footer $footer=("
Report run {0} by {1}{2}" -f (Get-Date -displayhint date),$env:userdomain,$env:username) $fragments+=$footer #write the result to a file ConvertTo-Html -head $head -body $fragments | Out-File $Path

SharePoint
Format Date on DateTime field using XSLT Submitted by eoszakiewski on 4/16/2014 12:44:00 PM