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';

Change Job Owner

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_id=N'9b72dfd8-0845-4632-8ffb-43d35e723ace', @owner_login_name=N'sa'
GO

--select * from msdb.dbo.sysjobs where job_id=N'9b72dfd8-0845-4632-8ffb-43d35e723ace'

Purge MSDB Mail History

use msdb
go


DECLARE @d datetime
select @d = min(last_mod_date) from sysmail_attachments with (nolock)
select @d
set @d = dateadd(dd,1,@d)
select @d

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @d
go

DBCC SHRINKFILE (N'MSDBLog' , 0)
GO
DBCC SHRINKDATABASE(N'msdb' )
GO

/*
select top 10 * from sysmail_attachments with (nolock)
select count(*) from sysmail_attachments with (nolock)
select min(last_mod_date) from sysmail_attachments with (nolock)
select max(last_mod_date) from sysmail_attachments with (nolock)
*/

Grant Permission to View Stored Procedures - Dynamic Creation for all DBs

master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)''
print ''set @user = ''''SPARKSTAGE\developers''''''
print ''DECLARE Cur_name CURSOR FOR ''
print ''select specific_name From information_Schema.routines''
print ''OPEN Cur_name''
print ''FETCH NEXT FROM Cur_name INTO @name''
print ''WHILE @@FETCH_STATUS = 0''
print ''BEGIN''
print ''set @string =''''GRANT VIEW DEFINITION ON [dbo].[''''+@name+''''] TO [''''+@user+'''']''''''
print ''print @string''
print ''exec sp_executesql @string''
print ''FETCH NEXT FROM Cur_name INTO @name''
print ''END''
print ''CLOSE Cur_name''
print ''DEALLOCATE Cur_name''
print ''go''
end
'
go

Grant View Permission on Stored Procedures

--select * from information_Schema.routines
--select specific_name from information_Schema.routines

declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)
set @user = 'SPARKSTAGE\developers'

DECLARE Cur_name CURSOR FOR
select specific_name From information_Schema.routines

OPEN Cur_name
FETCH NEXT FROM Cur_name INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
print '================================='
print @name
print '================================='
set @string ='GRANT VIEW DEFINITION ON [dbo].['+@name+'] TO ['+@user+']'
print @string
exec sp_executesql @string

FETCH NEXT FROM Cur_name INTO @name
END
CLOSE Cur_name
DEALLOCATE Cur_name

Extract Text after 2 commas in a string

select MemberGroupLangStr
, CHARINDEX(',',MemberGroupLangStr)
, substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr))
, CHARINDEX(',',substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr)))
, CHARINDEX(',',MemberGroupLangStr) + CHARINDEX(',',substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr)))
, substring(MemberGroupLangStr , CHARINDEX(',',MemberGroupLangStr) + CHARINDEX(',',substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr))) + 1 , len(MemberGroupLangStr))
From MembersWithUnapprovedTextAttributes

Extract Text between two commas

select MemberGroupLangStr,
CASE
-- when no second semi-colon,
WHEN CHARINDEX(',',(SUBSTRING(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr,1)+1,99))) = 0
-- then extract the full string from first semi-colon to the max length of 99
THEN LTRIM(RTRIM(SUBSTRING(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr,1)+1,99)))
-- else extract the full string from the first semi-colon up to second semi-colon
ELSE LTRIM(RTRIM(SUBSTRING(
/* > */ MemberGroupLangStr,
/* */ CHARINDEX(',',MemberGroupLangStr,1) + 1,
/* */ CHARINDEX(',', SUBSTRING(MemberGroupLangStr,
CHARINDEX(',', MemberGroupLangStr, 1) + 1,
99)) - 1
) ) )
END AS Result_string
From MembersWithUnapprovedTextAttributes

Grant access to database in a server for domain user

EXEC master..sp_MSForEachDB '

if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''CREATE USER [Domain\user] FOR LOGIN [Domain\user]''
print ''go''
print ''EXEC sp_addrolemember N''''db_datareader'''', N''''Domain\user''''''
print ''go''
print ''EXEC sp_addrolemember N''''db_executor'''', N''''Domain\user''''''
print ''go''

end
'
go

List objects per filegroup

IF OBJECT_ID('tempdb.dbo.#FileGroup') IS NOT NULL

DROP TABLE #FileGroup



IF OBJECT_ID('tempdb.dbo.#ObjectFileGroup') IS NOT NULL

DROP TABLE #ObjectFileGroup



CREATE TABLE #FileGroup (

FileGroup sysname

)

CREATE TABLE #ObjectFileGroup (

ObjectName sysname,

ObjectType varchar(20),

FileGroupID int,

FileGroup sysname

)



SET NOCOUNT ON



DECLARE @TableName sysname

DECLARE @id int



DECLARE cur_Tables CURSOR FAST_FORWARD FOR

SELECT TableName = [name], id FROM dbo.sysobjects WHERE type = 'U'

OPEN cur_Tables

FETCH NEXT FROM cur_Tables INTO @TableName, @id

WHILE @@FETCH_STATUS = 0

BEGIN

TRUNCATE TABLE #FileGroup

INSERT #FileGroup (FileGroup)

EXEC sp_objectfilegroup @id

INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)

SELECT @TableName, 'TABLE', FILEGROUP_ID(FileGroup), FileGroup

FROM #FileGroup

FETCH NEXT FROM cur_Tables INTO @TableName, @id

END



CLOSE cur_Tables

DEALLOCATE cur_Tables



INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)

SELECT OBJECT_NAME(id) + ' * ' +[name], 'INDEX', groupid, FILEGROUP_NAME(groupid) FROM dbo.sysindexes

WHERE FILEGROUP_NAME(groupid) IS NOT NULL

AND OBJECT_NAME(id) NOT LIKE 'sys%'

AND [name] NOT LIKE '_WA_Sys%'

AND [name] NOT LIKE 'Statistic_%'



SELECT FileGroupName = FILEGROUP_NAME(sf.groupid),/*ofg.FileGroup, */ofg.ObjectName, ofg.ObjectType, FileName = sf.filename, FileSize = sf.[size] / 128

FROM #ObjectFileGroup ofg

RIGHT JOIN dbo.sysfiles sf

ON ofg.FileGroupID = sf.groupid

ORDER BY FileGroup, ObjectName



SQL 2005 (very simplified)



SELECT

fg.data_space_id, fg.name,

ObjectName = OBJECT_NAME(p.object_id), p.index_id

,df.name, df.physical_name, [Size] = df.size*8/1024

FROM sys.filegroups fg

LEFT JOIN sys.database_files df

ON fg.data_space_id = df.data_space_id

LEFT JOIN sys.partitions p

ON fg.data_space_id = p.partition_number

WHERE (p.object_id>4096 or p.object_id IS NULL)

Change all db owners to 'sa'

sp_helpdb
go
EXEC master..sp_MSForEachDB '

if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''exec sp_changedbowner sa''
print ''go''
end
'
go

sp_helpdb
go

Revoke & Grant Execut to a Database Role

--Check if role not exisit then create new role
declare @User varchar(25), @Force bit

set @User = 'db_executor'
set @force = 0

if not exists (select * from sys.database_principals where name = 'db_executor' and (type = 'R' or type = 'A'))
begin
CREATE ROLE [db_executor] AUTHORIZATION [dbo]
end
GO


-- revoke all previous access
declare @User varchar(25), @Force bit

set @User = 'db_executor'
set @force = 0

set nocount on

declare @Name varchar(100),
@Command varchar(255),
@uid int
declare @ProcCount int
set @ProcCount = 0

select @Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'

while @Name is not null begin
select @uid = uid
from sysusers
where [name] = @User

if exists (select * from sysprotects where id = object_id(@Name) and action = 224 and uid = @uid) or
@Force = 1 begin
set @Command = 'REVOKE execute on ' + @Name + ' to ' + @User
print @Command
set @ProcCount = @ProcCount + 1
exec (@Command)
end

select @Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @Name
end


-- grant execute access

set @force = 0
set @ProcCount = 0

select @Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'

while @Name is not null begin
select @uid = uid
from sysusers
where [name] = @User

if not exists (select * from sysprotects where id = object_id(@Name) and
action = 224 and uid = @uid) or
@Force = 1 begin
set @Command = 'grant execute on ' + @Name + ' to ' + @User
print @Command
set @ProcCount = @ProcCount + 1
exec (@Command)
end

select @Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @Name
end

SQL server security Audit; script to expose logins and access to DBs

print replicate('-',100)
print 'Server: ' +@@servername
print replicate('-',100)
print '-------------sql_logins------------------'
SELECT * FROM sys.sql_logins


print '-------------sp_helpsrvrolemember------------------'
Exec sp_helpsrvrolemember
print replicate('-',100)

EXEC sp_MSforeachdb 'use [?]
print ''Database: ''+ db_name()
print replicate(''-'',100)
print ''-------------sp_helprolemember------------------''
Exec sp_helprolemember

print ''-------------sp_helprotect----------------------''
Exec sp_helprotect
print replicate(''-'',100)
'

Grant Trace permission to SQL user

grant alter trace to traceuser
revoke alter trace to traceuser

SELECT * FROM fn_my_permissions(null,null)

User : traceuser
Pass: tr@ceus3r

Find all tables with a specific column name within entire server

I put this query together to find all tables with specific column_name within the entire databases on a server.

Method 1:

use master
go
if(object_id('tempdb..#tmp')>0)
drop table #tmp

create table #tmp (dbname nvarchar(1000), tablename nvarchar(1000), columnname nvarchar(1000))
declare @dbname nvarchar(100), @sqlstring nvarchar(1000), @columnname nvarchar(100)
set @columnname = 'promotionid'
DECLARE dbs_name CURSOR FOR select name from sys.databases
OPEN dbs_name

FETCH NEXT FROM dbs_name INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
set @sqlstring = 'insert into #tmp select table_catalog, table_name, column_name from '+@dbname+'.information_schema.columns where column_name = '''+@columnname+''' '
exec sp_executesql @sqlstring
FETCH NEXT FROM dbs_name INTO @dbname
end

CLOSE dbs_name
DEALLOCATE dbs_name

select * from #tmp


Method 2:

EXEC master..sp_MSForEachDB '
IF EXISTS
(
SELECT 1
FROM [?].INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = ''promotionid''
)
BEGIN
SELECT ''[?]'';


SELECT ''['' + TABLE_SCHEMA + '']'', ''['' + TABLE_NAME + '']''
FROM [?].INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = ''promotionid'';
END';

Create Insert Command for SQL tables

declare @columnnames nvarchar(4000) , @insertcommand nvarchar(4000) , @tablename nvarchar(100) ,@str1 nvarchar(1000), @tmptablename nvarchar(100), @whereclause nvarchar(1000)
set @columnnames = ''
set @tablename = 'planresource'
set @whereclause = ' where planid in (select planid from [plan] where planid >= 1500) '

set @tmptablename = '##tmp__'+@tablename
set @str1 = 'select * into '+@tmptablename+' from ['+@tablename+'] '+@whereclause
--print @str1
exec sp_executesql @str1
set @str1 = 'alter table '+@tmptablename+' add insertcommand nvarchar(3000)'
exec sp_executesql @str1



select @columnnames = @columnnames + '['+column_name+'], ' from information_schema.columns where table_name = @tablename
--print @columnnames
--print left(@columnnames , len(@columnnames)-1)
set @columnnames = left(@columnnames , len(@columnnames)-1)
--print @columnnames
set @insertcommand = 'insert into ['+@tablename+'] ('+@columnnames+') values ('
--print @insertcommand


set @str1 = 'update '+@tmptablename+' set insertcommand = '''+@insertcommand +''''
--print @str1
exec sp_executesql @str1


declare @sqlstring2 nvarchar(4000), @colname nvarchar(100)
set @sqlstring2 = 'update '+@tmptablename+' set insertcommand = insertcommand + '''
DECLARE tmptable_name CURSOR FOR select column_name from information_schema.columns where table_name = @tablename
OPEN tmptable_name

FETCH NEXT FROM tmptable_name INTO @colname

WHILE @@FETCH_STATUS = 0
BEGIN
--set @sqlstring2 = @sqlstring2 + ' convert(nvarchar(1000),isnull(replace(['+@colname+'],'''''''',''''''''''''),''''))+'''''')'' '
set @sqlstring2 = @sqlstring2 + '''''''+ convert(nvarchar(1000),isnull(replace(['+@colname+'],'''''''',''''''''''''),''''))+'''''', '
FETCH NEXT FROM tmptable_name INTO @colname
end
--print @sqlstring2
CLOSE tmptable_name
DEALLOCATE tmptable_name
set @sqlstring2 = left(@sqlstring2 , len(@sqlstring2)-1)+')'''
--print @sqlstring2
exec sp_executesql @sqlstring2

--select insertcommand from ##tmp__landingpage
--select insertcommand from #tmp




declare @string3 nvarchar(1000)
set @string3 =
'
declare @sqlstring nvarchar(4000)
DECLARE tmptable_name CURSOR FOR select insertcommand from '+@tmptablename+'
OPEN tmptable_name

FETCH NEXT FROM tmptable_name INTO @sqlstring

WHILE @@FETCH_STATUS = 0
BEGIN
print @sqlstring
print ''go''
FETCH NEXT FROM tmptable_name INTO @sqlstring
end

CLOSE tmptable_name
DEALLOCATE tmptable_name
'

--print @string3
exec sp_executesql @string3


set @string3 = 'drop table '+@tmptablename
exec sp_executesql @string3

sample for "execute as"

use analysisreporting
go
-- current user
SELECT SUSER_NAME(), USER_NAME();
select top 10 * from audit
go
-- deny select for a user
DENY SELECT ON [dbo].[Audit] TO [reportuser]
GO
-- change execute as
EXECUTE AS USER = 'reportuser';
SELECT SUSER_NAME(), USER_NAME();
-- this will fail
select top 10 * from audit
REVERT;
Go
-- revoke select for a user
REVOKE SELECT ON [dbo].[Audit] TO [reportuser]
GO
EXECUTE AS USER = 'reportuser';
SELECT SUSER_NAME(), USER_NAME();
-- this will run
select top 10 * from audit
REVERT;
Go
-- this is logged in user again
select top 10 * from audit
SELECT SUSER_NAME(), USER_NAME();
go

Permission to run Profiler for a user who is not SYSADMIN

GRANT ALTER TRACE TO [User]

Date ETL Routines

Most of the DBAs are involved in daily routine jobs such as checking backups, security issues, performance tunings and many other tasks that are defiled to be line of DBA responsibilities.
On the other hand Data Warehousing is a challenge for database developers and programmers. Import, export and processing data is part of each DW task. I found myself very interested and amused when I’m involved in such a task. Most of the small companies don’t have a dedicated DW developer, so the requests are directed to DBA, and the expectation is that we (as DBAs and Data guys) should be able to complete any requests in a short amount of time.
In a world that each company is trying to expand and inquires another companies and competitors, it’s obvious that synchronizing the technologies of those companies are challenges for all IT personal, and when it comes to Data and information synchronization, then DBA’s and DW developers have important role to be able to provide solid data synchronization and reporting solutions.

I was involved in many projects that importing and processing the data from other databases and even from other platforms were essential for business owners. We had multi platform production database servers and DW farms. It’s obvious that reports are of the most imports piece for management. We had to create a solid DW and reporting solution which be able to provide real-time and financial reports on both sides. We start by building a new DW farm, utilizing MSSQL 2005 SSAS, SSRS and SSIS. Loading data from MySQL and Oracle databases, in a daily or hourly base, becomes a challenge for me and after many attempts and different approaches; I was able to build and maintain a solid DW farm.
Most of the schemas I was importing had INSERTDATETIME (Timestamp) field. I took 2 different approaches and used each in different scenarios. I will try to describe each method below and list the pros and cons for each one.
Importing the records within a certain period of time
Importing records based on the previous import’s timestamp

1- Importing the records within a certain period of time
In this method, I built a table where I keep the history of the pull (or push) . StartTime / EndTime is basically the range that records are affected. The insert query will look like this:
Insert into “TargetTable”
(Field1, Field2,Field3)
Select Field1, Field2,Field3 from “SourceTable” where InsertDateTime >= @StartTime and InsertDateTime < @EndTime As you can see only records in the range are selected. StartTime is always calculated based on the previous successful history, plus the desired Interval. Select @interval = interval from PackageInformation Select top 1 @StartTime = EndTime , @EndTime = EndTime + @interval from PackageExecutionHostory order by InsertDateTime desc After defining the processing date range, package will execute and upon successful completion the new date range will be recorded. This will become the start point for next time. 2-Importing records based on the previous import’s timestamp In the method, I record only the max of timestamp, which is processed. Select top 1 @StartTime = LastMaxDate from PackageExecutionHistory order by InsertDateTime desc The insert query will look like this: Insert into “TargetTable” (Field1, Field2,Field3) Select Field1, Field2,Field3 from “SourceTable” where InsertDateTime >= @StartTime
For recording successful transactions history, will need to run another query as :
Select @LastMaxDate = max(TimeStamp) from TargetTable
Inert into PackageExecutionHistory (LastMaxDate, InsertDateTime) values (@lastMaxDate,getdate())
I need to mention that each of these processes are depending on Job Schedule and will be executed from a Job.

Pros & Cons of each method.
Importing records based on the previous import’s timestamp
No matter when you run the job, all the records, since last execution, will be pulled (or inserted)
No control for pulling records between a certain periods of time. If there are high volumes of transactions, this might be a slow process. Inserting and selecting large number of records is always a bottleneck.
If any record in target table is getting inserted, while the select statement is trying to prepare the data set, then there is potential deadlock situation.
It’s possible to not capture some of the records if using select statement with (nolock) option to prevent performance impact.

Importing the records within a certain period of time
Certain data range to be selected
The data size is mostly predictable and by tuning interval period, it’s possible to minimize the impact of loading large data set
If the SQL job is not executed, the next job will pull only the records which are within the range of selection, which were supposed to be pulled by earlier job. This is a potential problem if the SQL jobs are not executed for a while, and will cause delay on data delivery.
Need more controlling and trapping procedures to prevent false execution on not valid date range.

Working with tempdb in SQL Server 2005

Working with tempdb in SQL Server 2005

SQL Server 2005: Intro to XQuery - SQL Server Central

SQL Server 2005: Intro to XQuery - SQL Server Central