Find Date of Last Restore

http://www.mssqltips.com/sqlservertip/1860/identify-when-a-sql-server-database-was-restored-the-source-and-backup-date/

SELECT [rs].[destination_database_name], 
[rs].[restore_date], 
[bs].[backup_start_date], 
[bs].[backup_finish_date], 
[bs].[database_name] as [source_database_name], 
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf 
ON [bs].[media_set_id] = [bmf].[media_set_id] 
ORDER BY [rs].[restore_date] DESC

IE7 and IE8 Serif / Letter Tail Cut Off

line-height: normal;

http://stackoverflow.com/questions/634824/ie7-is-clipping-my-text-how-do-i-adjust-its-attitude

HTML Table - Text Not Centered in Headers

At least for IE7, TH widths should be declared as %'s, not px.

Fun with Dynamic SQL - Abusing the Data Layer

I needed to do some reporting on a process from within the db, essentially output a table of values to an email for alerting purposes ... enjoy!

Period	Records	Charges
1301	55975	523110523.76
1302	58820	554884666.09
1303	0	0.00
1304	0	0.00
1305	0	0.00
1306	0	0.00
1307	0	0.00
1308	0	0.00
1309	0	0.00
1310	0	0.00
1311	0	0.00
1312	0	0.00

-------------------------------------------------------------------

ALTER PROC schema.getChargeCounts (@currentFY CHAR (2), @tOut VARCHAR (MAX) OUTPUT)
AS
BEGIN

/**************************************************************************
Joe Kelly
2012-09-17 17:43:59.300

Returns an HTML table of late charge information based up on the two digit
year entered.

DECLARE @V VARCHAR (maX) = ''
EXEC schema.getChargeCounts @currentFY = '13', @tOut = @V OUTPUT
SELECT @V

**************************************************************************/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON

DECLARE @rowCount INT = 12
, @iter INT = 1
, @Period CHAR (4) = ''
, @tStart CHAR (256) = ''
, @tEnd CHAR (8) = ''
, @tHead CHAR (512) = 'PeriodRecordsCharges'
, @tRStart CHAR (256) = ''
, @tREnd CHAR (8) = ''
, @tDStarCC CHAR (256) = ''
, @tDStartR CHAR (256) = ''
, @tDEnd CHAR (8) = ''
, @SQL NVARCHAR (MAX) = ''

DECLARE @Report TABLE (
ident INT IDENTITY (1, 1)
, Period INT
, Records BIGINT
, Charges MONEY
)

DECLARE @ReportOut TABLE (
ident INT IDENTITY (1, 1)
, Period INT
, Records BIGINT
, Charges MONEY
)

SELECT @SQL = 'SELECT lc.Period, Count (*) Records , Sum (Charge) Charges
FROM dbo.Charge_FY' + CAST (@currentFY AS CHAR (2)) + ' LC
WHERE ' + CAST (@currentFY AS CHAR (2)) + ' = LEFT (lc.Period, 2)
GROUP BY lc.Period '

INSERT @Report (
Period
, Records
, Charges
)
EXEC sp_EXECUTESQL @SQL

WHILE (@iter <= 12)
BEGIN
SELECT @Period = @currentFY + RIGHT (CAST (REPLICATE ('0', 2) AS VARCHAR (2)) + CAST (@iter AS VARCHAR (3)), 2)

INSERT @ReportOut (
Period
, Records
, Charges
)
SELECT @Period
, 0
, 0

SELECT @iter += 1
END

UPDATE RO
SET ro.Records = r.Records
, ro.Charges = r.Charges
FROM @ReportOut RO
JOIN @Report r
ON ro.period = r.period

SELECT @tOut +=
@tStart
+ @tHead

SELECT @iter = 1

WHILE (@iter <= 12)
BEGIN
SELECT @tOut +=
@tRStart
+ @tDStarCC
+ CAST (Period AS VARCHAR (64))
+ @tDEnd
+ @tDStartR
+ CAST (Records AS VARCHAR (64))
+ @tDEnd
+ @tDStartR
+ CAST (CONVERT (MONEY, Charges, 2) AS VARCHAR (64))
+ @tDEnd
+ @tREnd
FROM @ReportOut
WHERE ident = @iter

SET @iter += 1

END

SELECT @tOut += @tEnd

SELECT @tOut = REPLACE (REPLACE (REPLACE (@tOut, ' ', ''), ' ', ''), ' ', '')

END

Funky IE Appearance Upon Deployment - Intranet

Fix: < meta http-equiv="X-UA-Compatible" content="IE=8" / >


Source: http://blogs.msdn.com/b/askie/archive/2009/03/23/understanding-compatibility-modes-in-internet-explorer-8.aspx

Note for intranet sites the default is 7, not 8.

Unfortunately the default compatibility mode for intranet sites is set to "On" in IE.

jquery with Master Pages and Subdirectories - Correct Path Syntax

Error:" $ is not defined"

Helpful: http://stackoverflow.com/questions/2194992/jquery-is-not-defined

Use the net tab in firebug to make sure the URI is being written out and is being loaded.

Syntax:    

[script type="text/javascript" src="../Scripts/jquery/jquery.1.8.0.dev.js" ][/script]

Verified for FF & IE.

IIS 6.0 Local User Security

A common site configuration issue (non-AD):

KCTS - Make sure the root of your FTP site is not the localuser(s) folder itself but its parent folder.

http://forums.devshed.com/ftp-help-113/iis-6-isolate-user-ftp-91708.html

VB6 DLL Web Deployment Errors

ASP_0177_:_800401f3|Server.CreateObject_Failed
ActiveX_component_can't_create_object


' "ASP_0177_:_800401f3" means that the component either has not been registered or that
' the name that was passed to call it is bad (http://forums.iis.net/t/1122303.aspx)
'
' Check that the dll has been registered and that the correct case is being used
' both in the build and the call (not sure if it is case sensitive)
'
' If component fails on deployment make sure that all class modules have
' "5 - Multiuse" selected for their "instancing" property

VB6 DLL Web Deployment

ASP_0177_:_800401f3|Server.CreateObject_Failed
ActiveX_component_can't_create_object

Hook to Debug VB6 ActiveX DLL

'Public Sub Main()
'
' 'Note that switching from an ActiveX DLL to a Standard EXE may/will change the ' class module “Instancing” property from “Multiuse” to “Private” (error: “No
' ' creatable public component detected". To get back to the ActiveX DLL change the ' class module “Instancing” property back to “Multiuse”
'
' Dim sError, vTable, WriteAccess, RowCount
' ' name of class that contains the function to invoke
'
' Dim foo As New cReports
'
' Dim sPEDate, sL5, sL4, sL3, sL2, sL1, sCC, sFrom, sTxtMonth, states(12), hFromRev, hSortBy
' Dim vReportDataarray(3), TimePeriod, sType
' Dim sUsername, oUCSF
' Dim ARows, BRows, CRows, DRows, ERows, ZRows
'
' sPEDate = "1211"
' sCC = "1742001 "
' hSortBy = "ActRevenue"
'
' ' invoke function
' sError = foo.getRPT_MEV_CC_Revenue_Detail(sPEDate, vReportDataarray, sUsername, sCC, ARows, BRows, CRows, DRows, ERows, ZRows, states, hSortBy)
'
' 'F8 – away!
'
' 'modal for a breakpoint
' MsgBox Command$, vbOK, "Command Line params"
'
'End Sub

Great Powershell Script - Directory Size Recursively

http://technet.microsoft.com/en-us/library/ff730945.aspx

$startFolder = "C:\Scripts"  $colItems = (Get-ChildItem $startFolder | Measure-Object -property length -sum) "$startFolder -- " + "{0:N2}" -f ($colItems.sum / 1MB) + " MB"  $colItems = (Get-ChildItem $startFolder -recurse | Where-Object {$_.PSIsContainer -eq $True} | Sort-Object) foreach ($i in $colItems)     {         $subFolderItems = (Get-ChildItem $i.FullName | Measure-Object -property length -sum)         $i.FullName + " -- " + "{0:N2}" -f ($subFolderItems.sum / 1MB) + " MB"     }  

Is Agent Job Running???

CREATE PROC portal.isJobRunning (@jobName VARCHAR (256))
AS
BEGIN

/**************************************************************************
Joe Kelly
2012-07-31 17:56:04.540

Determines if a job is running or not

EXEC portal.isJobRunning @jobName = 'Export%'

Template source:
http://weblogs.asp.net/johnbilliris/archive/2009/10/13/how-to-determine-if-a-sql-job-is-running.aspx

**************************************************************************/
SET NOCOUNT ON

DECLARE @xp_results TABLE (
job_id UNIQUEIDENTIFIER NOT NULL
, last_run_date INT NOT NULL
, last_run_time INT NOT NULL
, next_run_date INT NOT NULL
, next_run_time INT NOT NULL
, next_run_schedule_id INT NOT NULL
, requested_to_run INT NOT NULL -- BOOL
, request_source INT NOT NULL
, request_source_id sysname COLLATE database_default NULL
, running INT NOT NULL -- BOOL
, current_step INT NOT NULL
, current_retry_attempt INT NOT NULL
, job_state INT NOT NULL
)

INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'

SELECT xpr.running
, sjv.name
FROM @xp_results xpr
JOIN MSDB.dbo.sysjobs_view sjv
ON xpr.job_id = sjv.job_id
WHERE sjv.name LIKE @jobName

END

Flight Recorder OLAP CUBE Trace

Default 5MB log on OLAP installations:

http://msdn.microsoft.com/en-us/library/ms174827.aspx (search for FlightRecorder)

http://support.microsoft.com/kb/2128005

FlightRecorder

 

The events and columns captured by the Flight Recorder are controlled by a trace definition file.  The advanced server property named Log\FlightRecorder\TraceDefinitionFile controls the definition file used by the Fligth Recorder.  By default, the property is blank, which means the Flight Recorder defaults to using the trace file definition stored in the file flightrecordertracedef.xml, which is stored in the ..\OLAP\bin directory wherever SSAS is installed.

 http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/9bcc369d-5f23-41fc-916d-448e20182430

List of Tables in Access DB

Source???

SELECT MSysObjects.Name AS table_name FROM MSysObjects WHERE (((Left([Name],1))<>"~")          AND ((Left([Name],4))<>"MSys")          AND ((MSysObjects.Type) In (1,4,6))) order by MSysObjects.Name 

Debugging SSCode - UCSFMC

C# as a scripting language, WebForms with no code behind, no use of app_code, a single common page inheritance for errors and the use of external include files for calling external (from the page) methods - how to debug.

a.) Remove tag referring to .\SSCode\xx.cs

b.) Take contents (functions) of xx.cs and create a file in App_Code with the calling page's name and a class wrapper with the file name

c.) In xx.aspx change the page directive references to the new class and file name.

Classic ASP Errors on Large Result Set with No Information

Check the IIS log.

I this case the IIS buffer had to be increased.

Example log message: 2012-07-02 19:46:09 W3SVC1522741043 127.0.0.1 POST /Reports/Reports_Var_CC_Rev.asp |0|ASP_0251_:_80004005|Response_Buffer_Limit_Exceeded 655 UCSFMC\kellyjoe 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+8.0;+Windows+NT+5.2;+Trident/4.0;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.30;+.NET+CLR+3.0.04506.648;+.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729;+.NET4.0C;+.NET4.0E;+InfoPath.3) 500 0 0

http://support.microsoft.com/kb/925764

runas syntax

C:\Users\epsi>runas /user:domain\user "D:\Program Files (x86)\Microsoft SQL
Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

Displaying Table Sizes using T-SQL

For each table get table size

http://blog.securahosting.com/technical/databases/displaying-table-sizes-using-t-sql/

SELECT LEFT(OBJECT_NAME(id), 30) AS [Table],
CAST( CAST(reserved * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS 'Allocated (MB)',
CAST(CAST(dpages * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS 'Used (MB)',
CAST(CAST((reserved - dpages) * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS 'Unused (MB)',
rowcnt AS 'Row Count (approx.)'
FROM sysindexes WHERE indid IN (0, 1) AND OBJECT_NAME(id) NOT LIKE 'sys%' AND OBJECT_NAME(id) NOT LIKE 'dt%'
ORDER BY reserved DESC, LEFT(OBJECT_NAME(id), 30)

SQL Database Mail "NOTE: Failed to notify 'operator' via email."

Credit: http://leopard-solutions.com/blog/post/2011/06/15/SQL-Database-Mail-NOTE-Failed-to-notify-operator-via-email.aspx

If you have set up your SQL Database Mail Operators and SMTP details correctly, but keep getting a message like this: "NOTE: Failed to notify 'operator' via email.", you may not have enabled the Alert System In the SQL Server Agent.

Here's how to enable the Mail System:

Right click "SQL Server Agent" in the Object Explorer and click "Properties"

Go to Alert System, and check the "Enable mail profile" box and select the correct Mail system and Mail profile.

Tip to Prevent Scientific Notation in T-SQL Output

SELECT CONVERT(DECIMAL(24, 12), CONVERT(FLOAT, '1.08E+05'));

http://stackoverflow.com/questions/7473081/convert-scientific-notation-to-float-when-using-openrowset-to-import-a-csv-file



Excel compare columns

Sub Compare2Cols()

' Principals

' For x = 1 To 32 Step 4

' For y = 2 To 106

' Cost Centers

' For x = 1 To 83 Step 4

' For y = 2 To 78

' Levels

' For x = 5 To 8 Step 2

' For y = 2 To 32

For x = 5 To 8 Step 2

For y = 2 To 32

'For show ...

Cells(y, x).Select

If Cells(y, x) <> Cells(y, x + 1) Then

With Cells(y, x).Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

.PatternTintAndShade = 0

End With

With Cells(y, x + 1).Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

.PatternTintAndShade = 0

End With

Else

With Cells(y, x).Interior

.Pattern = xlNone

.TintAndShade = 0

.PatternTintAndShade = 0

End With

With Cells(y, x + 1).Interior

.Pattern = xlNone

.TintAndShade = 0

.PatternTintAndShade = 0

End With

End If

Next y

Next x

Cells(y, x).Select

End Sub

Search Every Column in Every Table

-- Update to exclude image and other blob types

SELECT 'SELECT [' + COLUMN_NAME + '] ''[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'' FROM [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WHERE CAST ([' + COLUMN_NAME + '] AS VARCHAR (MAX)) like ''%86110%''' FROM INFORMATION_SCHEMA.Columns c

TSQL Script to identify unused indexes

http://beyondrelational.com/modules/30/scripts/485/scripts/11430/tsql-script-to-identify-unused-indexes.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2012Mar

WITH cte AS (

02.SELECT

03.'['+ c.name + '].[' + o.name + ']' AS TableName,

04.i.name AS IndexName,

05.i.index_id AS IndexID,

06.user_seeks + user_scans + user_lookups AS Reads,

07.user_updates AS Writes,

08.(

09.SELECT SUM(p.rows)

10.FROM sys.partitions p

11.WHERE p.index_id = s.index_id

12.AND s.object_id = p.object_id

13.) AS TotalRows,

14.CASE

15.WHEN s.user_updates < 1 THEN 100

16.ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups)

17./ s.user_updates

18.END AS ReadsPerWrite,

19.'DROP INDEX ' + QUOTENAME(i.name)

20.+ ' ON ' + QUOTENAME(c.name)

21.+ '.' + QUOTENAME(OBJECT_NAME(s.object_id))

22.AS 'DropSQL'

23.FROM sys.dm_db_index_usage_stats s

24.INNER JOIN sys.indexes i ON i.index_id = s.index_id

25.AND s.object_id = i.object_id

26.INNER JOIN sys.objects o on s.object_id = o.object_id

27.INNER JOIN sys.schemas c on o.schema_id = c.schema_id

28.WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1

29.AND s.database_id = DB_ID()

30.AND i.type_desc = 'nonclustered'

31.AND i.is_primary_key = 0

32.AND i.is_unique_constraint = 0

33.AND

34.(

35.SELECT SUM(p.rows)

36.FROM sys.partitions p

37.WHERE p.index_id = s.index_id

38.AND s.object_id = p.object_id

39.) > 10000

40.)

41.SELECT * FROM cte

42./*

43.TableName Name ID Reads Writes Rows ReadsPerWrite DropSQL

44.---------- ----- -- ------- ------ ----- ------------- -------

45.[dbo].[t1] IDX_1 20 1227972 996081 78599 1.23280335635 DROP ..

46.[dbo].[t2] IDX_2 21 51359 33912 78599 1.51447865062 DROP ..

47.[dbo].[t3] IDX_3 22 235280 159808 78599 1.47226672006 DROP ..

48.*/

Find Non-numeric Rows in Column

CREATE TABLE #foo (

ident BIGINT IDENTITY (0, 1)

, someString VARCHAR (128) DEFAULT '')

INSERT #foo ( someString )

SELECT '4546546'

UNION

SELECT '456654.475'

UNION

SELECT 'afafsdf'

UNION

SELECT '78998'

UNION

SELECT '87979z'

SELECT ident

, someString

, PATINDEX ('%[^0-9]%', someString)

FROM #foo

WHERE PATINDEX ('%[^0-9]%', someString) != 0