Internet Sellout

Azure SQL Freakshow - Part 1

April 12, 2023 by sellout in Azure, SQL

The odyssey to Azure SQL has been difficult and I fear it is not over, hence the preemptive "Part 1"

Reset MySQL root password on Windows

May 24, 2017 by sellout in SQL

This was done on MySQL 5.7.9. Probably MySQL is running on windows as a service. Stop the servi

This SQL is to demonstrate a dynamic pivot query. It is dynamic because the number of columns is not known in advance. Or because it is known but the number of columns is large but incremental. In this sample the pivot column is week of year.

SELECT * INTO #DATA FROM (SELECT 
  CAST('Women' as varchar(50)) as UserName
, CAST('1/1/2014' As datetime) As DateDied
, CAST(48 As int) As Age
, CAST(5 As int) As Dollars
, CAST(1 As int) As Heirs) X
INSERT INTO #DATA SELECT 'Men', '1/5/2014', 50, 300, 2
INSERT INTO #DATA SELECT 'Men', '1/20/2014', 55, 5000, 4
INSERT INTO #DATA SELECT 'Women', '2/20/2014', 23, 55, 0
INSERT INTO #DATA SELECT 'Women', '2/20/2014', 43, 22, 1

--this part will blow out the number of @days in the past and future of @date
DECLARE @date datetime = CAST(GetDate() as date)
DECLARE @days int = 365

;WITH CTE1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE1 x, CTE1 y),
CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTEFINAL AS (SELECT TOP (@days) CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as int) AS RowNum FROM CTE4 x, CTE4 y)
SELECT 
Offset,
DateRangeDate As [Date]
, DatePart(yyyy,DateRangeDate) As [Year]
, DatePart(mm,DateRangeDate) As [Month]
, DateName(mm,DateRangeDate) As [MonthName]
, DatePart(dd,DateRangeDate) As [Day]
, DatePart(dy,DateRangeDate) As [DayOfYear]
, DatePart(wk,DateRangeDate) As [Week]
, DatePart(dw,DateRangeDate) As [DayOfWeek]
, DateName(dw,DateRangeDate) As [DayName]
, CASE WHEN DatePart(mm,DateRangeDate) IN (1,2,3)
 THEN 1 WHEN DatePart(mm,DateRangeDate) IN (4,5,6)
 THEN 2 WHEN DatePart(mm,DateRangeDate) IN (7,8,9)
 THEN 3 ELSE 4 END As [Quarter]
   INTO #DATEFACTS FROM 
(SELECT RowNum -1 As Offset, @date + RowNum -1 AS DateRangeDate  FROM CTEFINAL
UNION 
SELECT -RowNum As Offset, @date - RowNum AS DateRangeDate  FROM CTEFINAL) X
--Now you have some date facts

--This is your query
SELECT td.[Week], td.[Month], td.[Quarter], td.[Year], f.UserName, f.DateDied, f.Age, f.Dollars, f.Heirs
 INTO #TEMP FROM #DATA f WITH (NOLOCK)
INNER JOIN #DATEFACTS td ON td.[Date] = f.DateDied

--Edit Defaults Below 
DECLARE @col2prefix varchar(50) = 'A'
DECLARE @col3prefix varchar(50) = 'D'
DECLARE @col4prefix varchar(50) = 'H'
DECLARE @col2label varchar(50) = 'Avg Age Week'
DECLARE @col3label varchar(50) = 'Sum Dollars Week'
DECLARE @col4label varchar(50) = 'Avg Heirs Week'
DECLARE @col2data varchar(50) = 'Age'
DECLARE @col3data varchar(50) = 'Dollars'
DECLARE @col4data varchar(50) = 'Heirs'
DECLARE @order varchar(50) = @col2label
DECLARE @sort varchar(50) = 'DESC'
DECLARE @grouprow varchar(50) = 'UserName'
--Edit Defaults Above 

declare @col1 varchar(MAX), @col2 varchar(MAX), @col3 varchar(MAX), @col4 varchar(MAX), @colorder varchar(MAX)
declare @sql varchar(MAX)

-- Replace all [Week] below with your pivot column value
;WITH CTE AS (SELECT DISTINCT [Week] FROM #TEMP)
		select   @col1 = COALESCE(@col1 + ', ','')
		+ 'MAX(' + @col2prefix + CAST([Week] as varchar(100)) + ') AS ' +  QUOTENAME(@col2label + ' ' + CAST([Week] as varchar(100))) 
		+ ',MAX(' + @col3prefix + CAST([Week] as varchar(100)) + ') AS ' +  QUOTENAME(@col3label + ' ' + CAST([Week] as varchar(100)))
		+ ',MAX(' + @col4prefix + CAST([Week] as varchar(100)) + ') AS ' +  QUOTENAME(@col4label + ' ' + CAST([Week] as varchar(100)))
		,@col2 = COALESCE(@col2 + ', ','') +  @col2prefix + CAST([Week] as varchar(100))
		,@col3 = COALESCE(@col3 + ', ','') +  @col3prefix + CAST([Week] as varchar(100))
		,@col4 = COALESCE(@col4 + ', ','') +  @col4prefix + CAST([Week] as varchar(100))
		,@colorder = COALESCE(@colorder + ', ','') + '[' + @order + ' ' + CAST([Week] as varchar(10)) + '] ' + @sort
		FROM cte ORDER BY [Week]

		SET @sql = 'SELECT ' + @grouprow + ', ' + @col1 + 'FROM ( SELECT ' + @grouprow + ',' + @col2 + ',' + @col3 + ',' + @col4 + ' FROM ('
		+ 'SELECT ' + @grouprow + '
		, ''' + @col2prefix + ''' + CAST([Week] as varchar(100)) As ' + @col2prefix + '
		, ''' + @col3prefix + ''' + CAST([Week] as varchar(100)) As ' + @col3prefix + '
		, ''' + @col4prefix + ''' + CAST([Week] as varchar(100)) As ' + @col4prefix + '
		, ' + @col2data + ', ' + @col3data + ', ' + @col4data + ' FROM #TEMP R
		) R2
		PIVOT ( AVG(' + @col2data + ') FOR ' + @col2prefix + ' IN (' + @col2 + ')) AS P2
		PIVOT ( SUM(' + @col3data + ') FOR ' + @col3prefix + ' IN (' + @col3 + ')) AS P3
		PIVOT ( AVG(' + @col4data + ') FOR ' + @col4prefix + ' IN (' + @col4 + ')) AS P4
		) AS X
		GROUP BY ' + @grouprow + ' ORDER BY '	+ @colorder



		EXEC (@sql);

DROP TABLE #TEMP
DROP TABLE #DATEFACTS
DROP TABLE #DATA

I Am Admin But I Can't Get Up

April 1, 2014 by sellout in SQL

addselftosqlsysadmin.cmd (5.25 kb)

Crap, why can't I connect to SQL Server, I am a local Admin what's the problem?

Moving MSSQL Databases to Different Partition

December 1, 2013 by sellout in SQL

I just moved from one Rackspace cloud server to another. The main purpose was to get IIS 8 so I could do TLS/SNI. I got a skimpy partition for the system and the preinstalled SQL Server Web Edition databases were taking up precious space so I moved them with help from this article:

http://technet.microsoft.com/en-us/library/ms345408.aspx

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO


USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'K:\Data\tempdb\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'K:\Data\tempdb\templog.ldf');
GO

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'reportserver');
GO


USE master;
GO
ALTER DATABASE ReportServer 
MODIFY FILE (NAME = ReportServer, FILENAME = 'K:\Data\reporting\ReportServer.mdf');
GO
ALTER DATABASE ReportServer 
MODIFY FILE (NAME = ReportServer_log, FILENAME = 'K:\Data\reporting\ReportServer_log.ldf');
GO

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'reportserverTempDB');
GO


USE master;
GO
ALTER DATABASE ReportServerTempDB 
MODIFY FILE (NAME = ReportServerTempDB, FILENAME = 'K:\Data\reporting\ReportServerTempDB.mdf');
GO
ALTER DATABASE ReportServerTempDB 
MODIFY FILE (NAME = ReportServerTempDB_log, FILENAME = 'K:\Data\reporting\ReportServerTempDB_log.ldf');
GO

Don't forget to delete the old tempdb after restart SQL Server and move the reportserver databases when the service is stopped after the commands and before turning SQL Server back on.

Tally Ho

November 7, 2013 by sellout in SQL

CREATE FUNCTION [dbo].[ufn_TallyHo] (@quantity int)
RETURNS TABLE AS
RETURN(
with cte(tally)as(select 1 union all select tally +  1 from cte where tally < @quantity)SELECT tally FROM cte
)

I found a table at work called Tally that was a list of numbers. I thought it was sad until I needed something like it, so I created the above function. But this one is better, no recursion:

ALTER FUNCTION [dbo].[ufn_TallyHo2] (@quantity bigint)
RETURNS TABLE AS
RETURN(
WITH CTE1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE1 x, CTE1 y),
CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTE5(N) AS (SELECT 1 FROM CTE4 x, CTE4 y),
CTE6(N) AS (SELECT 1 FROM CTE5 x, CTE5 y)
SELECT TOP (@quantity) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM CTE6 x, CTE6 y
)

Demand Unearned Rewards

SQL

Azure SQL Freakshow - Part 1

Reset MySQL root password on Windows

Dynamic SQL Pivot

I Am Admin But I Can't Get Up

Moving MSSQL Databases to Different Partition

Tally Ho