Ron Michael Zettlemoyer ronmichael
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* thanks to http://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server */ | |
| /* partition on the columns that make the row unique */ | |
| with dupes as ( | |
| select *, rn = ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col1) | |
| from sources | |
| ) | |
| delete from dupes | |
| where rn > 1 |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // add some functions (or override some functions) | |
| // e.g. $(selector).autocomplete("doMore", data); | |
| $.widget("ui.autocomplete", $.ui.autocomplete, { | |
| doMore: function (data) { | |
| // this = autocomplete object | |
| // this.element = auto complete input element | |
| }, |
ronmichael
/ script-backup-and-restore.sql
Created
June 6, 2013 18:44
This script will generate the scripts you need to backup all of your databases to a specific folder (@sourcebackupfolder) and then restore them from a specific folder (@targetbackupfolder) on the new server. You could enable COMPRESSION in the backup if your server supports that.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| declare @sourcebackupfolder varchar(255), @targetbackupfolder varchar(255), @targetdatafolder varchar(255), @targetlogfilder varchar(255) | |
| select @sourcebackupfolder = 'c:\moving\', @targetbackupfolder='d:\moving\', @targetdatafolder='c:\sql\data\', @targetlogfilder='c:\sql\logs\'; | |
| select 'BACKUP DATABASE [' + name + '] TO DISK = ''' + @sourcebackupfolder + name | |
| + '.bak'' WITH INIT , NOUNLOAD , NAME = ''' + name + ''', NOSKIP , STATS = 100, NOFORMAT' | |
| from sys.databases | |
| where name not in ('master','msdb','model','tempdb'); |
ronmichael
/ script-users.sql
Last active
December 18, 2015 04:09
Script out all your MSSQL users - with passwords and group memberships - so you can recreate them on another server. Credits to http://weblogs.sqlteam.com/billg/archive/2010/07/08/Scripting-out-SQL-Server-Logins.aspx for the original version; this variation does not require you to create a new function (fn_hexadecimal); it uses the fn_varbintohe…
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| select | |
| 'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''') | |
| CREATE LOGIN [' + [name] + '] | |
| WITH PASSWORD=' + master.sys.fn_varbintohexstr(password_hash) + ' HASHED, | |
| SID = ' + master.sys.fn_varbintohexstr([sid]) + ', | |
| DEFAULT_LANGUAGE=[us_english], | |
| CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=OFF | |
| GO | |
| IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''') | |
| ALTER LOGIN [' + [name] + '] |
ronmichael
/ fixEventReceivers.ps1
Created
April 10, 2013 21:10
After migrating from SharePoint 2010 to 2013 we found that some workflows ran multiple times. The cause was multiple event receivers left over on lists from SharePoint 2010. So we created this script to go through all the lists on all the sites on your server and remove the old SharePoint 2010 event receivers. Credits to http://sharelockpoint.wo…
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # delete old SharePoint 2010 event receivers to eliminate duplicate workflow actions | |
| function examineWeb($web) { | |
| $spLists = $web.Lists; | |
| for ($listnum=0; $listnum -lt $spLists.Count; $listnum+=1) | |
| { | |
| $($web.Title + " - " + $spList.Title) |
ronmichael
/ objectToTurtle.cs
Last active
December 15, 2015 22:19
Convert a dotNet C# object into RDF (Turtle)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| using System; | |
| using System.Collections.Generic; | |
| using System.Linq; | |
| using System.Text; | |
| using System.IO; | |
| // just some rough playing around converting an object to RDF Turtle. | |
| class ObjectToTurtle | |
| { |
ronmichael
/ ssrs-logs.sql
Created
January 21, 2013 00:16
Analyze SQL Server Reporting Services (SSRS) report usage. Note that the log tables do get cleared automatically after 30-60 days or so, so you need to run this regularly.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| ex.UserName, ex.Format, | |
| cat.Path, | |
| cat.Name, ex.Parameters, | |
| CONVERT(nvarchar(10), ex.TimeStart, 101) AS rundate, | |
| ex.TimeStart, ex.TimeEnd, ex.TimeDataRetrieval, ex.TimeProcessing, ex.TimeRendering, ex.Status, | |
| ex.ByteCount, ex.[RowCount] | |
| FROM ReportServer..ExecutionLog AS ex INNER JOIN | |
| ReportServer..Catalog AS cat ON ex.ReportID = cat.ItemID | |
| ORDER BY ex.TimeStart DESC |
ronmichael
/ add-cert.sql
Created
January 21, 2013 00:15
Add a certificate to a stored procedure in MSSQL
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- in this case we're giving the cert permission to alter the schema | |
| -- (maybe explicitly disably identify and insert something into an identity column) | |
| -- set up cert with permission to alter schema... | |
| CREATE MASTER KEY ENCRYPTION BY password = '**opensesame**'; | |
| CREATE CERTIFICATE SchemaCert WITH SUBJECT = 'Schema user Certificate' | |
| CREATE USER SchemaCertUser FROM CERTIFICATE SchemaCert | |
| GRANT ALTER ANY SCHEMA TO SchemaCertUser | |
| -- run or rerun the following line whenever you alter procedure, |
ronmichael
/ fragmented-indexes.sql
Last active
December 11, 2015 09:49
Analyze index fragmentation in your MSSQL database
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| DB_NAME(DPS.DATABASE_ID) AS [DatabaseName] | |
| ,OBJECT_NAME(DPS.OBJECT_ID) AS TableName | |
| ,SI.NAME AS IndexName | |
| ,DPS.INDEX_TYPE_DESC AS IndexType | |
| ,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation | |
| ,DPS.PAGE_COUNT AS PageCounts | |
| FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS | |
| INNER JOIN sysindexes SI | |
| ON DPS.OBJECT_ID = SI.ID |
ronmichael
/ analyze-queries.sql
Created
January 21, 2013 00:01
Analyze your top MSSQL queries with this script
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| select top 100 | |
| qs.max_elapsed_time/1000000.00, | |
| db.name db, | |
| ob.name object, | |
| substring(st.text,1,100) preview, | |
| total_worker_time/execution_count AS [Avg CPU Time], | |
| SUBSTRING(st.text, (qs.statement_start_offset/2)+1, | |
| ((CASE qs.statement_end_offset | |
| WHEN -1 THEN DATALENGTH(st.text) | |
| ELSE qs.statement_end_offset |