Microsoft Tidbits - Revision history

Chip: Added recursive sql gaps and islands thingy

2014-10-19T04:40:40Z

Added recursive sql gaps and islands thingy

@@ Line 73: / Line 73: @@
 order by object_id, index_name</pre>
 ==Reasons Microsoft Vista Sucks==
 <UL><LI>"search in files" is gone.  Yes, people THINK it's there, but it's not.  I can't get the search to find a simple string in an .aspx file (which I have ridiculously associated with Notepad, just in case Vista is only looking for files it knows how to read).  I've changed a boatload of settings, and read some other pages that seem to indicate that people got this working, but following their instruction does nothing.  Is it the file type?  WTF?</LI>

Admin: added "or"

2013-09-24T20:07:58Z

added "or"

← Older revision		Revision as of 20:07, 24 September 2013
Line 48:		Line 48:
	===Cute query to FLOOR (round to midnight the morning beginning) a date in Transact SQL===		===Cute query to FLOOR (round to midnight the morning beginning) a date in Transact SQL===
	<pre>SELECT DATEADD(day,0,FLOOR(CONVERT(float, getDate())));</pre>		<pre>SELECT DATEADD(day,0,FLOOR(CONVERT(float, getDate())));</pre>
			or
	<pre>SELECT CAST(GETDATE() AS DATE) -- Also works</pre>		<pre>SELECT CAST(GETDATE() AS DATE) -- Also works</pre>

Admin: Added Partition query

2013-09-24T20:07:29Z

Added Partition query

@@ Line 49: / Line 49: @@
 <pre>SELECT DATEADD(day,0,FLOOR(CONVERT(float, getDate())));</pre>
 <pre>SELECT CAST(GETDATE() AS DATE) -- Also works</pre>
 ==Reasons Microsoft Vista Sucks==

Admin: Added SQL Server Section

2013-09-24T19:19:27Z

Added SQL Server Section

← Older revision		Revision as of 19:19, 24 September 2013
Line 1:		Line 1:
			==SQL Server Tidbits==
			===Running Queries===
			<pre>
			SELECT sqltext.TEXT,
			req.session_id,
			req.status,
			req.command,
			req.cpu_time,
			req.total_elapsed_time
			FROM sys.dm_exec_requests req
			CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext;

			select row_count,* from sys.dm_exec_requests r join sys.dm_os_tasks t on r.session_id = t.session_id where r.session_id = 70 order by task_state;
			select sum(context_switches_count) from sys.dm_exec_requests r join sys.dm_os_tasks t on r.session_id = t.session_id where r.session_id = 70;
			</pre>
			===List Tables in a schema===
			<pre>SELECT NAME FROM SYS.all_objects WHERE schema_id IN (SELECT SCHEMA_ID FROM SYS.schemas WHERE NAME = 'ICD10');

			select [name]
			from dbo.sysobjects
			where OBJECTPROPERTY(id, N'IsUserTable') = 1 -- restrict to normal user tables
			</pre>
			===Determine space used by an object===
			<pre>EXEC sp_spaceused N'Facts.F_MEMBER_ENROLLMENT';</pre>
			===Awesome undocumented procedure to loop through tables (one exists for databases too)===
			<pre>exec sp_MSforeachtable 'EXEC sp_spaceused [?]';</pre>
			===Size of individual indexes===
			<pre>exec sp_MSIndexSpace N'Dimensions.D_CLAIM_LINE_ITEM_T1';</pre>
			===Rename or Move Objects===
			<pre>exec sp_rename 'Dimensions.D_PROVIDER_T1.TAXONOMY_CODE','PRIMARY_TAXONOMY_CODE', N'COLUMN'
			exec tempdb..sp_rename '#MY_TEMP_TABLE.COLUMN_OLD','COLUMN_NEW', N'COLUMN'

			-- Move (rename) a table into a different schema (with generation):

			alter schema HEDIS_2014 transfer dbo.AAB;

			select 'alter schema HEDIS_2014 transfer dbo.' + name + ';'
			from sys.all_objects
			where schema_id = 1 and type = 'U'</pre>
			===Rebuild Indexes===
			<pre>ALTER INDEX ALL ON Dimensions.D_CLAIM_LINE_ITEM_T1
			REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON);</pre>
			===Rebuild statistics===
			<pre>UPDATE STATISTICS Dimensions.D_CLAIM_T1;

			IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N'[Facts].[F_CLAIM_LINE_ITEM]') AND name = N'_stat_DCT1_DMT2_DMT1')
			CREATE STATISTICS [_stat_DCT1_DMT2_DMT1] ON [Facts].[F_CLAIM_LINE_ITEM]([CLAIM_T1_SKEY], [MEMBER_T2_SKEY], [MEMBER_T1_SKEY])</pre>
			===Cute query to FLOOR (round to midnight the morning beginning) a date in Transact SQL===
			<pre>SELECT DATEADD(day,0,FLOOR(CONVERT(float, getDate())));</pre>
			<pre>SELECT CAST(GETDATE() AS DATE) -- Also works</pre>

	==Reasons Microsoft Vista Sucks==		==Reasons Microsoft Vista Sucks==
	<UL><LI>"search in files" is gone. Yes, people THINK it's there, but it's not. I can't get the search to find a simple string in an .aspx file (which I have ridiculously associated with Notepad, just in case Vista is only looking for files it knows how to read). I've changed a boatload of settings, and read some other pages that seem to indicate that people got this working, but following their instruction does nothing. Is it the file type? WTF?</LI>		<UL><LI>"search in files" is gone. Yes, people THINK it's there, but it's not. I can't get the search to find a simple string in an .aspx file (which I have ridiculously associated with Notepad, just in case Vista is only looking for files it knows how to read). I've changed a boatload of settings, and read some other pages that seem to indicate that people got this working, but following their instruction does nothing. Is it the file type? WTF?</LI>

Chip at 17:24, 27 January 2009

2009-01-27T17:24:14Z

← Older revision		Revision as of 17:24, 27 January 2009
Line 14:		Line 14:
	</UL>		</UL>
	Of course, it could be something else. ANY name change to a dependant query or table could cause grief -- they matriculate properly about half the time, judging from the last 12 hours. Changing the "Total" type of a field in a query for example will rename a field MaxOfxxxx without asking, and in some cases without propagating to other queries that reference the field. It's enough to drive one mad!		Of course, it could be something else. ANY name change to a dependant query or table could cause grief -- they matriculate properly about half the time, judging from the last 12 hours. Changing the "Total" type of a field in a query for example will rename a field MaxOfxxxx without asking, and in some cases without propagating to other queries that reference the field. It's enough to drive one mad!

			==Tips and Tricks for Windows XP Professional==
			*NB: This used to be available at: http://www.microsoft.com/technet/prodtechnol/winxppro/tips/default.mspx but it's apparently gone, so I copied it here.

			===Working with Files===
			<ul>
			<li>To rename several files at once, select all of them in any folder view, or in the Windows Explorer, rename the FIRST one in the list, and the rest will follow.
			</li><li>To turn off filenames, hold down the Shift key when you click to open a folder in Windows Explorer or when you switch into thumbnail view. This will turn of the file names, giving more space for the thumbnails. Doing it again turns them back on.
			</li><li>Try grouping your files. In the top menu bar select View – Arrange icons by… - Type, and then View – Arrange icons by… - Show in groups.
			</li><li>You can change the size and quality of the shell's thumbnails. Start regedit, expand HKEY_CURRENT_USER (only for you) or HKEY_LOCAL_MACHINE (for all the users in this computer) and in Software – Microsoft – Windows – CurrentVersion – Explorer, add or modify the following entries:
			</li><li>ThumbnailSize – Double Word, decimal value between 32 and 256
			</li><li>ThumbnailQuality – Double Word, decimal value between 50 and 100
			</li>
			</ul>

			===Internet Explorer===
			<ul>
			<li>You can use the Search Companion in Internet Explorer to perform many Internet related actions. Try:
			</li><li>go msft
			</li><li>Go yp cheesecake factory
			</li></ul>

			===Interface===
			<ul>
			<li>
			Get rid of balloon tips: Start regedit, expand HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced
			Set EnableBalloonTips=0x00000000
			</li><li>
			Disable the Search companion to go back to the traditional search view: Start regedit, expand HKEY_CURRENT_USER and in Software – Microsoft – Windows – CurrentVersion – Explorer – CabinetState, set the value of Use Search Asst to "no"
			</li><li>
			Turn off the low disk space notification: Start regedit, expand HKEY_CURRENT_USER and in Software – Microsoft – Windows – CurrentVersion – Policies - Explorer, and set the value of NoLowDiskSpaceChecks to DWORD = 1. (This modification requires a logoff or reboot to work)
			</li><li>
			You can force your taskbar to group windows. Any application that has any number you decide of windows open will automatically cluster, regardless of how full your taskbar is. To set this: Start regedit, expand HKEY_CURRENT_USER and in Software – Microsoft – Windows – CurrentVersion – Explorer - Advanced, and create a REG_DWORD with the name TaskbarGroupSize and the value you want to reach before the windows are clustered together on the taskbar. (This modification requires a logoff to work).
			Top of pageTop of page
			</li></ul>

			===Keyboard===
			<ul>
			<li>windowskey + Break opens the Control Panel System applet.
			</li><li>windowskey + Tab moves through the taskbar buttons
			</li><li>windowskey + D shows the Desktop
			</li><li>windowskey + L shows the Welcome screen, or locks the machine if Fast User Switching is disabled.
			</li>
			</ul>

Admin: /* Reasons Microsoft Access (2007) Sucks */

2008-03-09T03:03:59Z

Reasons Microsoft Access (2007) Sucks

← Older revision		Revision as of 03:03, 9 March 2008
Line 13:		Line 13:
	<LI>Delete it</LI>		<LI>Delete it</LI>
	</UL>		</UL>
			Of course, it could be something else. ANY name change to a dependant query or table could cause grief -- they matriculate properly about half the time, judging from the last 12 hours. Changing the "Total" type of a field in a query for example will rename a field MaxOfxxxx without asking, and in some cases without propagating to other queries that reference the field. It's enough to drive one mad!

Admin: Major Windows Annoyances So far

2008-03-09T02:56:55Z

Major Windows Annoyances So far

New page

==Reasons Microsoft Vista Sucks==
<UL><LI>"search in files" is gone. Yes, people THINK it's there, but it's not. I can't get the search to find a simple string in an .aspx file (which I have ridiculously associated with Notepad, just in case Vista is only looking for files it knows how to read). I've changed a boatload of settings, and read some other pages that seem to indicate that people got this working, but following their instruction does nothing. Is it the file type? WTF?</LI>
<LI>Really there are a lot of these, but given how lazy I've been updating the rest of the site you probably won't see them here unless they REALLY bother me... like whatever I have listed so far</LI>
</UL>

==Reasons Microsoft Access (2007) Sucks==

If you have a query that constantly asks you for a prompt for no reason, it could be that you've accidentally sorted by a column that you later renamed or removed. Access 2007 doesn't quite pick up on the change, so you have to do the following:
<UL>
<LI>Open the Query in Design View (or SQL View)</LI>
<LI>Open the Properties Sheet (which by default is open and docked on the right -- if not, right click somewhere and highlight "Properties")</LI>
<LI>See if there's anything in the "Order By" Property that shouldn't be there</LI>
<LI>Delete it</LI>
</UL>