Parse XML file to read in SQL

DECLARE @hdoc int
DECLARE @doc nvarchar(max)
SelecT @doc = convert(nvarchar(max),xml_data) from XmlImportTest

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

--select @hdoc

SELECT *
FROM OPENXML (@hdoc, '/strings/enum')
with (name varchar(100) '@name')

SELECT *
FROM OPENXML (@hdoc, '/strings/enum/field')
with ( name varchar(100) '../@name',
val varchar(100) '@value',
txt varchar(100) '.')

-- Remove the internal representation.
exec sp_xml_removedocument @hdoc

Import XML data into a table

CREATE TABLE XmlImportTest(
xmlFileName VARCHAR(300) NOT NULL,
xml_data XML NOT NULL
)
GO

DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'C:\Documents and Settings\palexander\Desktop\Mingle Meta Data\black_strings.xml'

--dynamic sql is just so we can use @xmlFileName variable in OPENROWSET

EXEC('INSERT INTO XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO

How to Identify Blocking Problems with SQL Profiler

Detach all databases on a server

use master
go

exec master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use master''
print ''go''
print ''EXEC master.dbo.sp_detach_db @dbname = ''''?'''', @keepfulltextindexfile=N''''true''''''
print ''go''
end
'
go

Using SP_MSForEachDB and SP_MSForEachTable

Insert into an IDENTITY column Table

SET IDENTITY_INSERT MemberMailID ON;
insert into MemberMailID (pkid,inputvalue,insertdate) values(589297975,1,getdate());
SET IDENTITY_INSERT MemberMailID OFF;



create table #tmp (lastid int)

declare @MemberMailID int
insert into #tmp
exec up_PrimaryKey_Select 'MemberMailID'
select @MemberMailID = lastid from #tmp
select @MemberMailID


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

ALTER proc [dbo].[up_PrimaryKey_Select] @KeyName varchar(75),@InputValue int = null
as
set nocount on
declare @ExecString nvarchar (1000)
declare @PKID int
if @InputValue is null
begin
set @InputValue = 1
end
set
set @ExecString = 'insert '+@KeyName+'(InputValue) values(@InputValue)
select @PKID = scope_identity()'
exec sp_executesql @ExecString,
N'@InputValue int,@PKID int output',@InputValue, @PKID = @PKID output
select @PKID as PKID
return

Finding the Worst Performing T-SQL Statements on an Instance

Introduction to Attribute Discretization

SQL SERVER - Guidelines and Coding Standards

Find a Stored Procedure within entire server

EXEC master..sp_MSForEachDB '
IF EXISTS
(
SELECT 1
FROM [?].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = ''up_AggregateStatistics_SingleDate''
)
BEGIN
SELECT ''[?]'';


SELECT ''['' + SPECIFIC_CATALOG + '']'', ''['' + ROUTINE_NAME + '']''
FROM [?].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = ''up_AggregateStatistics_SingleDate'';
END';