What Port is Named Instance of SQL Server Listening On?

exec xp_readerrorlog 1 , 1 , N'listen' It works on 2008 and lower, is said to work on 2012. exec xp_readerrorlog [log #, 1 is current] , [1 – sql server log, 2 – agent log, 3 – full text log] , [search string] http://mssqlwiki.com/2011/02/13/beyond-xp_readerrorlog/

Solution to Apostrophes in Email Addresses (.Net)

It should work - http://social.msdn.microsoft.com/Forums/en-US/netfxnetcom/thread/84dd5b66-f68d-4b0c-823f-25aa7c7b4d6b/ i.e. create MailAddress("myFirstName.O'Reilly@mywebsite.com"), and then use that to create a MailMessage, and pass to SmtpClient.Send.

HTML5 Resources

HTML5 video - Wikipedia, the free encyclopedia
http://en.wikipedia.org/wiki/HTML5_video

Slides, A Slideshow Plugin for jQuery
http://slidesjs.com/


We love HTML5 games - Ludei
 ludei.com

CSS3 Keyframe Animation Demo
 impressivewebs.com

Sass - Syntactically Awesome Stylesheets
 sass-lang.com

Sencha Fastbook
 vimeo.com

This four-minute video gives you a quick overview of Sencha Fastbook, and shows you a side-by-side comparison of how well our HTML5 app performs against both the native iOS and the native Android Facebook apps (versions 5.2 and 1.9.12 respectively, the latest available when we made this video on December 10th).
Douglas Crockford's Javascript
 crockford.com

stack.jpg
 dropbox.com

CSS3 2D Transforms
 w3schools.com

Touchy-stuff by vainrobot
 github.com

jQuery Mobile | jQuery Mobile
 jquerymobile.com

Understanding CSS Filter Effects - HTML5 Rocks
 html5rocks.com

Filter Effects - SVG 1.1 - 20030114
 w3.org
Draggable
 jqueryui.com

HTML5 Rocks - Native HTML5 Drag and Drop
 html5rocks.com

html5rocks com
 html5rocks.com

jQuery Tutorial for Beginners: Nothing But the Goods | Impressive Webs Toronto
 impressivewebs.com

JavaScript Tutorial
 w3schools.com

http://code.jquery.com/jquery-1.9.1.min.js
 jquery.com

.click() – jQuery API
 jquery.com

 delicious.com
 delicious.com

Solving Performance for 2D & 3D Web Apps: Finally! - Marakana
 marakana.com

 An Introduction To CSS3 Keyframe Animations - Smashing Coding
 smashingmagazine.com

SVG-edit
 googlecode.com

Swelen JavaScript Beauty - Online JS Beautifier With Colors
 swelen.com

Google Font Directory
 google.com

Normalize.css: Make browsers render all elements more consistently.
 github.com

HTML5 Reset Stylesheet
 html5doctor.com

The W3C Markup Validation Service
 w3.org

Sublime Text - Download
 sublimetext.com

https://marakana.com/static/courseware/html5/blocky_blog_image.png
 marakana.com

W3Schools Online Web Tutorials
 w3schools.com

Dart : Structured web programming
 dartlang.org

AngularJS — Superheroic JavaScript MVC Framework
 angularjs.org

Backbone.js
 backbonejs.org

PhoneGap API Documentation
 phonegap.com

PhoneGap Build
 phonegap.com

Firefox at Mobile World Congress 2013 — mozilla.org
 mozilla.org

Earth Hour | WWF France
 earthhour.fr

http://three.js/
 three.js

Vows « Asynchronous BDD for Node
 vowsjs.org

HTML datalist Tag
 w3schools.com

JavaScript UI widgets | Kendo UI
 kendoui.com

John Resig - HTML5 Shiv
 ejohn.org

Angry Birds Volcano
 angrybirds.com

HexGL, the HTML5 futuristic racing game.
 bkcore.com
webghl game

Sencha Fastbook on Vimeo
2 months ago
 vimeo.com
facebook vs snecha

Soul Reaper - HTML5 Scroll Book
 soul-reaper.com

HTML5 Cross Browser Polyfills · Modernizr/Modernizr Wiki · GitHub
 github.com
Modernizr is a JavaScript library that detects HTML5 and CSS3 features in the user’s browser.

PhoneGap
 phonegap.com

Modernizr
 modernizr.com

Kendo UI - The Art of Web Development
 kendoui.com
Everything developers need to build html5/js sites and mobile apps. jQuery based toolset that includes rich UI widgets, powerful data source, dynamic data visualizations, and blazing fast micro-templates, all backed by industry leading professional support.

HTML5 Rocks - A resource for open web HTML5 developers
 html5rocks.com

Ultimate CSS Gradient Generator - ColorZilla.com
 colorzilla.com   css
A powerful Photoshop-like CSS gradient editor

(Re-Copy) Why Javascript Returns Zero For parseInt('08') and parseInt('09')?

http://www.ventanazul.com/webzine/articles/issues-parseint-javascript Why Javascript Returns Zero For parseInt('08') and parseInt('09')? Writing a simple Javascript function to create a Date object from a 'yyyy-mm-dd' string I found a small, but important, issue with the parseInt function, used to transform strings to integers. Both parseInt('08') and parseInt('09') return zero because the function tries to determine the correct base for the numerical system used. In Javascript numbers starting with zero are considered octal and there's no 08 or 09 in octal, hence the problem. To fix this just add the second parameter for parseInt, the base to be used for the conversion. The correct calls should be parseInt('08', 10) and parseInt('09', 10). Another of those little details, uh? Posted to Articles, javascript, tips on Wed, 2008-07-02 17:03

SSIS - DTS - Agent Permissions

http://technet.microsoft.com/en-us/library/ms188283.aspx SQL Server Agent Fixed Database Roles SQL Server 2012 Other Versions 3 out of 8 rated this helpful - Rate this topic SQL Server 2005 introduced the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are: SQLAgentUserRole SQLAgentReaderRole SQLAgentOperatorRole When users who are not members of one of these roles are connected to SQL Server in SQL Server Management Studio, the SQL Server Agent node in Object Explorer is not visible. A user must be a member of one of these fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent. Permissions of SQL Server Agent Fixed Database Roles The SQL Server Agent database role permissions are concentric in relation to one another -- more privileged roles inherit the permissions of less privileged roles on SQL Server Agent objects (including alerts, operators, jobs, schedules, and proxies). For example, if members of least-privileged SQLAgentUserRole have been granted access to proxy_A, members of both SQLAgentReaderRole and SQLAgentOperatorRole automatically have access to this proxy even though access to proxy_A has not been explicitly granted to them. This may have security implications, which are discussed in the following sections about each role. SQLAgentUserRole Permissions SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. They cannot use multiserver jobs (master and target server jobs), and they cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole members can view a list of available proxies only in the Job Step Properties dialog box of SQL Server Management Studio. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of SQLAgentUserRole. Security noteSecurity Note Consider the security implications before granting proxy access to members of the SQL Server Agentdatabaseroles. The SQLAgentReaderRole and the SQLAgentOperatorRole are automatically members of the SQLAgentUserRole. This means that members of SQLAgentReaderRole and SQLAgentOperatorRole have access to all SQL Server Agent proxies that have been granted to the SQLAgentUserRole and can use those proxies. The following table summarizes SQLAgentUserRole permissions on SQL Server Agent objects. 1 Cannot change job ownership. 2 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio. 3 List of proxies only available in the Job Step Properties dialog box of Management Studio. 4 Members of SQLAgentUserRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history for any other jobs. SQLAgentReaderRole Permissions SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole. Security noteSecurity Note Consider the security implications before granting proxy access to members of the SQL Server Agentdatabaseroles. Members of SQLAgentReaderRole are automatically members of the SQLAgentUserRole. This means that members of SQLAgentReaderRole have access to all SQL Server Agent proxies that have been granted to SQLAgentUserRole and can use those proxies. 1 Cannot change job ownership. 2 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio. 3 List of proxies only available in the Job Step Properties dialog box of Management Studio. 4 Members of SQLAgentReaderRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history for any other jobs. SQLAgentOperatorRole Permissions SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server. SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server. They can also enable or disable all local jobs and schedules on the server. To enable or disable local jobs or schedules, members of this role must use the stored procedures sp_update_job and sp_update_schedule. Only the parameters that specify the job or schedule name or identifier and the @enabled parameter can be specified by members of SQLAgentOperatorRole. If they specify any other parameters, execution of these stored procedures fails. SQLAgentOperatorRole members cannot change job ownership to gain access to jobs that they do not already own. The Jobs, Alerts, Operators, and Proxies nodes in SQL Server Management Studio Object Explorer are visible to members of SQLAgentOperatorRole. Only the Error Logs node is not visible to members of this role. Security noteSecurity Note Consider the security implications before granting proxy access to members of the SQL Server Agentdatabaseroles. Members of SQLAgentOperatorRole are automatically members of SQLAgentUserRole and SQLAgentReaderRole. This means that members of SQLAgentOperatorRole have access to all SQL Server Agent proxies that have been granted to either SQLAgentUserRole or SQLAgentReaderRole and can use those proxies. 1 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio. 2 Cannot change job ownership. 3SQLAgentOperatorRole members can enable or disable local jobs they do not own by using the stored procedure sp_update_job and specifying values for the @enabled and the @job_id (or @job_name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail. 4SQLAgentOperatorRole members can enable or disable schedules they do not own by using the stored procedure sp_update_schedule and specifying values for the @enabled and the @schedule_id (or @name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail. Assigning Users Multiple Roles Members of the sysadmin fixed server role have access to all SQL Server Agent functionality. If a user is not a member of the sysadmin role, but is a member of more than one SQL Server Agent fixed database role, it is important to remember the concentric permissions model of these roles. Because more privileged roles always contain all the permissions of less privileged roles, a user who is a member of more than one role automatically has the permissions associated with the most privileged role that the user is a member of.

All Variables Named After Food

http://jacquesmattheij.com/The+worst+program+I+ever+worked+on Not trying to boost content, just love this tale and do not want to loose it... ------------------------------------------------------------------------------------------- The Worst Program I Ever Worked On Mar 16th, 2011 Most contract jobs fade pretty quickly in memory after the work is done, but some you remember for the rest of your life. This is one of the latter variety. This happened long ago, at a (fair sized) company that shall remain nameless. The software was a chunk of code that had been maintained by a single guy that had been fired recently and was a core component of a commercial system. So far nothing unusual, companies tend to find out that they have a piece of critical knowledge in one head all the time, usually if something happens to or with such a person there is a mild panic, some days of frantic reading and then life continues. Not so in this case. Some bug had popped up in this program and the guy assigned to fix it had come back alternatively laughing and crying babbling nonsense about ‘pizzas calling hamburgers and passing booze’. The programmer that had written this code had his own sense of humor and his own ideas about the term ‘job security’. We’ve all heard the tall stories about the accounting package that wipes all records if there is no payment to some off-shore bankaccount 48 hours after terminating some tech guy, but such tricks are relatively easy to deal with - assuming that most of those stories are true, which I have a hard time believing, and I’ve never seen any of those in real life. The software this guy left behind did not have any logic bombs or other nasty tricks in it, it compiled just fine, and besides that one bug it seemed to work fine as well. Imagine this though: every function and variable name in the program was named after food. Pizza’s, tomatoes, pickles, various kinds of cheese, fruits, vegetables, drinks and so on, for page after endless page. The only place where the names made any immediate sense was ‘main’ and any C stdlib calls. So I got handed the thankless job of working very hard to get the program back to a state where it could be maintained. It was a really nifty form of encryption, and only with a key in hand would the salad of code make any sense. Little by little I converted the program back by naming the functions and the variables with more sensible names, and as the work progressed it got easier and easier. Working backwards from a known function and source code is a lot easier than reverse assembly of unknown code (because then you first have to separate code/data and you have to figure out what the high level representation was, here I had the high level representation in plaintext in front of me), so it wasn’t that the job was impossible or even particularly hard, it was just tedious. Once a function or variable had been identified to have some probable meaning a new name was made and a search-and-replace took care of the actual renaming. Another problem was that it was bad code, in fact, the various spaghetti twists in the code did more to obfuscate the meaning of it than the lack of meaningful symbols, so once I had all the functions and variables renamed back to something that made sense I re-wrote a good bit of the code to make it easier to understand and work more efficient. I never did find out if he had a non-obfuscated version of the code that he ran through a ‘blender’ script that obfuscated the original code by stripping out any and all comments and replacing all symbols by nonsensical ones. I find it hard to get in to the mind of someone that does something like that to begin with but I find it even harder to imagine that he wrote code like that directly, that would have actually been an amazing feat. Of course, if in your head you think that they can’t fire you because your program makes no sense (or that they might hire you back) then you’re delusional so whatever goal this guy had with his trick it failed in a terrible way (I can’t imagine him giving his former employer as a reference either) but it made for an amusing couple of weeks, and a very happy customer.

Bulk Insert

UtilSQL.DML.DBExecNonQueryText("TRUNCATE TABLE foo;", "bar"); using (SqlDataReader portal = UtilSQL.DML.DBExecReader("EXEC myproc", "mate")) { using (SqlConnection conn = UtilSQL.DML.GetSQLConnString("bar")) { conn.Open(); /// Note: for SqlBulkCopy to work the columns must be identically named /// and typed and all fields in the record must be copied or else you /// get a useless error about "LocaleID" /// /// Turns out that using SQLBulkCopy in the middle tier is faster than an /// insert using linked servers even if the data is prepped in a temp table /// first SqlBulkCopy bulk = new SqlBulkCopy(conn); bulk.DestinationTableName = "destTable"; // UID Username LastName FirstName Email isAdmin Inactive CostCenter Descr Bud_Read Bud_Write Facility SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping("UID", "UID"); SqlBulkCopyColumnMapping mapping2 = new SqlBulkCopyColumnMapping("Username", "Username"); SqlBulkCopyColumnMapping mapping3 = new SqlBulkCopyColumnMapping("LastName", "LastName"); SqlBulkCopyColumnMapping mapping4 = new SqlBulkCopyColumnMapping("FirstName", "FirstName"); SqlBulkCopyColumnMapping mapping5 = new SqlBulkCopyColumnMapping("Email", "Email"); SqlBulkCopyColumnMapping mapping6 = new SqlBulkCopyColumnMapping("isAdmin", "isAdmin"); SqlBulkCopyColumnMapping mapping7 = new SqlBulkCopyColumnMapping("Inactive", "Inactive"); SqlBulkCopyColumnMapping mapping8 = new SqlBulkCopyColumnMapping("CostCenter", "CostCenter"); SqlBulkCopyColumnMapping mapping9 = new SqlBulkCopyColumnMapping("Descr", "Descr"); SqlBulkCopyColumnMapping mapping10 = new SqlBulkCopyColumnMapping("Bud_Read", "Bud_Read"); SqlBulkCopyColumnMapping mapping11 = new SqlBulkCopyColumnMapping("Bud_Write", "Bud_Write"); SqlBulkCopyColumnMapping mapping12 = new SqlBulkCopyColumnMapping("Facility", "Facility"); bulk.ColumnMappings.Add(mapping1); bulk.ColumnMappings.Add(mapping2); bulk.ColumnMappings.Add(mapping3); bulk.ColumnMappings.Add(mapping4); bulk.ColumnMappings.Add(mapping5); bulk.ColumnMappings.Add(mapping6); bulk.ColumnMappings.Add(mapping7); bulk.ColumnMappings.Add(mapping8); bulk.ColumnMappings.Add(mapping9); bulk.ColumnMappings.Add(mapping10); bulk.ColumnMappings.Add(mapping11); bulk.ColumnMappings.Add(mapping12); bulk.WriteToServer(portal); bulk.Close(); conn.Close(); recCountPortal = UtilSQL.DML.DBExecScalarInt64("SELECT COUNT (*) FROM destTable", "bar"); } } this.lblPortal.Text = recCountPortal.ToString() + " records from portal.";

Blank Browser Edit Page

data:text/html, < html contenteditable > no space next to angle brackets

SQL Server Find Table Fragmentation

SELECT db.Name , OBJECT_NAME(s.OBJECT_ID) , s.avg_fragmentation_in_percent Perc FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) s JOIN MASTER.DBO.SYSDATABASES db ON s.database_id = db.dbID WHERE s.database_id = 7 ORDER BY Perc Desc

SQL for Validate Email

http://stackoverflow.com/questions/229824/tsql-email-validation-without-regex SELECT email FROM #Email where email like '%[^a-z,0-9,@,.]%' or email not like '%_@_%_.__%'

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.*/