Category Archives: Tech Notes

Databases, MS Azure SQL Database V12, Tech Notes

Create Read Only User in Azure DB

2016-03-10 Jon Culp Leave a comment

I have been struggling with creating a user who has read only access to an azure database and finally figured it out. The key is connecting to the right database in a new session when executing the t-sql commands. This is because you can not execute a “use” statement, change the database in the current session via the GUI, and you can not make named references to databases in your t-sql.

~~server_name.database_name.~~schema_name.object_name

--OPEN NEW SESSION TO MASTER DB
CREATE LOGIN ReadOnlyLogin
	WITH PASSWORD = 'ENTER STRONG PASSWORD'
;

CREATE USER ReadOnlyUser
	FOR LOGIN ReadOnlyLogin
	WITH DEFAULT_SCHEMA = dbo
;

--OPEN NEW SESSION TO MASTER DB

CREATE LOGIN ReadOnlyLogin

WITH PASSWORD = 'ENTER STRONG PASSWORD'

;

CREATE USER ReadOnlyUser

FOR LOGIN ReadOnlyLogin

WITH DEFAULT_SCHEMA = dbo

;

--OPEN NEW SESSION TO YOUR AZURE DB
CREATE USER ReadOnlyUser FROM LOGIN ReadOnlyLogin;
EXEC sp_addrolemember 'db_datareader', 'ReadOnlyUser';

--OPEN NEW SESSION TO YOUR AZURE DB

CREATE USER ReadOnlyUser FROM LOGIN ReadOnlyLogin;

EXEC sp_addrolemember 'db_datareader', 'ReadOnlyUser';

Validate user has read only permissions by connecting to database using these new credentials and executing something like “CREATE TABLE dbo.test (test datetime);”, which should return an error message indicating permission denied.

More information on managing database access and login security in azure: https://azure.microsoft.com/en-us/documentation/articles/sql-database-manage-logins/

Networking, Tech Notes, Uncategorized

WiFi Range Heat Mapping

2016-03-10 Jon Culp 3 Comments

I wanted to compare WiFi coverage of two different wireless access points and their various configurations in my house, and also check for dead spots. One quick and cheap way to do this that I have come across is using a free product called Ekahau HeatMapper.

Ekahau HeatMapper allows you to walk around your home or office and capture information about WiFi signals in various spots, and then the software aggregates this information into a banded topographical map.

Continue reading WiFi Range Heat Mapping →

MS SQL Server 2008 R2, Tech Notes

Drop Login and Users in MS SQL Server

2016-02-22 Jon Culp 1 Comment

This code will drop the login and users for all databases for a provided user name. and reports on its progress. This only works where the user and login match. This script does not check if users own any objects.

/*********************************************
DROP LOGIN AND USERS FOR ALL DBs IN A SERVER
Possible errors could be if a user is the owner of a database object.
*********************************************/
 
       SET NOCOUNT ON
       USE master
 
       --DECLARE VARIABLES
       DECLARE @Dropping_Windows_Logon_Name NVARCHAR(MAX) = N'starwars\amlegault' --SPECIFY USER TO DROP, typical format: domain\user
       DECLARE @tbl_Databases TABLE (Name NVARCHAR(MAX))
       DECLARE @Database_Name NVARCHAR(MAX)
       DECLARE @SQL_Drop_Login NVARCHAR(MAX)   
       DECLARE @SQL_Drop_DB_Users NVARCHAR(MAX)
      
 
       PRINT 'Begin Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME
 
             
       --DROP LOGIN FROM SERVER
       PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin Server Principal Drop Procedure'
       IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @Dropping_Windows_Logon_Name)
       BEGIN TRY
              SET @SQL_Drop_Login = 'DROP LOGIN [' + @Dropping_Windows_Logon_Name +']'
              EXEC (@SQL_Drop_Login)
              PRINT 'Server Principal Dropped'
       END TRY
       BEGIN CATCH
              PRINT 'Server Principal Found But Could Not Drop'
       END CATCH
       ELSE
              PRINT 'Server Principal Not Found'
      
 
       --USER DROP PROCEDURE
       PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin User Drop Procedure'
      
       --LOAD DATABASE NAMES INTO TEMP TABLE TO USE FOR LOOP
       INSERT INTO @tbl_Databases (Name)
       SELECT Name
       FROM master.sys.databases WITH(NOLOCK)
      
       --LOOP THROUGH EACH DATABASE AND DROP USER IF THEY EXIST
       WHILE EXISTS (SELECT TOP 1 * FROM @tbl_Databases)
       BEGIN
              SET @Database_Name = (SELECT TOP 1 Name FROM @tbl_Databases)
              SET @SQL_Drop_Login ='USE '+@Database_Name
              +' IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + @Dropping_Windows_Logon_Name + ''' AND sys.database_principals.type = ''U'')
              BEGIN TRY
                     DROP USER [' + @Dropping_Windows_Logon_Name +']
                     PRINT '' Dropped User in '+@Database_Name+'''
              END TRY
              BEGIN CATCH
                     PRINT '' User Found But Could Not Drop in '+@Database_Name+'''
              END CATCH
              ELSE PRINT '' User Not Found in '+@Database_Name+'''
              '
              EXEC (@SQL_Drop_Login)
              DELETE FROM @tbl_Databases WHERE Name = @Database_Name
       END
 
       PRINT 'End User Drop Procedure'
 
       --END
       PRINT CHAR(0x0D) + CHAR(0x0A) + 'End Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME
 
       SET NOCOUNT OFF

/*********************************************

DROP LOGIN AND USERS FOR ALL DBs IN A SERVER

Possible errors could be if a user is the owner of a database object.

*********************************************/

SET NOCOUNT ON

USE master

--DECLARE VARIABLES

DECLARE @Dropping_Windows_Logon_Name NVARCHAR(MAX) = N'starwars\amlegault' --SPECIFY USER TO DROP, typical format: domain\user

DECLARE @tbl_Databases TABLE (Name NVARCHAR(MAX))

DECLARE @Database_Name NVARCHAR(MAX)

DECLARE @SQL_Drop_Login NVARCHAR(MAX)

DECLARE @SQL_Drop_DB_Users NVARCHAR(MAX)

PRINT 'Begin Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME

--DROP LOGIN FROM SERVER

PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin Server Principal Drop Procedure'

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @Dropping_Windows_Logon_Name)

BEGIN TRY

SET @SQL_Drop_Login = 'DROP LOGIN [' + @Dropping_Windows_Logon_Name +']'

EXEC (@SQL_Drop_Login)

PRINT 'Server Principal Dropped'

END TRY

BEGIN CATCH

PRINT 'Server Principal Found But Could Not Drop'

END CATCH

ELSE

PRINT 'Server Principal Not Found'

--USER DROP PROCEDURE

PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin User Drop Procedure'

--LOAD DATABASE NAMES INTO TEMP TABLE TO USE FOR LOOP

INSERT INTO @tbl_Databases (Name)

SELECT Name

FROM master.sys.databases WITH(NOLOCK)

--LOOP THROUGH EACH DATABASE AND DROP USER IF THEY EXIST

WHILE EXISTS (SELECT TOP 1 * FROM @tbl_Databases)

BEGIN

SET @Database_Name = (SELECT TOP 1 Name FROM @tbl_Databases)

SET @SQL_Drop_Login ='USE '+@Database_Name

+' IF EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + @Dropping_Windows_Logon_Name + ''' AND sys.database_principals.type = ''U'')

BEGIN TRY

DROP USER [' + @Dropping_Windows_Logon_Name +']

PRINT '' Dropped User in '+@Database_Name+'''

END TRY

BEGIN CATCH

PRINT '' User Found But Could Not Drop in '+@Database_Name+'''

END CATCH

ELSE PRINT '' User Not Found in '+@Database_Name+'''

EXEC (@SQL_Drop_Login)

DELETE FROM @tbl_Databases WHERE Name = @Database_Name

END

PRINT 'End User Drop Procedure'

--END

PRINT CHAR(0x0D) + CHAR(0x0A) + 'End Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME

SET NOCOUNT OFF

MS SQL Server 2008 R2, Tech Notes, Uncategorized

Orphan Users in MS SQL Server

2016-02-22 Jon Culp Leave a comment

Checks all databases on a server for any users who do not have a correlated login, based on SID. Uses the undocumented command of sp_msforeachdb. You could put in a loop if you want to avoid this command.

DECLARE @orphan_users TABLE (Server NVARCHAR(MAX),DBName sysname, [user_name] sysname,type_desc nvarchar(60),default_schema_name sysname, create_date datetime, modify_date DATETIME, [sid] VARBINARY(85))
INSERT INTO @orphan_users (Server, DBname,[user_name],type_desc,default_schema_name,create_date,modify_date,[sid])
EXEC sp_msforeachdb
'use [?]
SELECT
@@SERVERNAME
,DB_NAME() DBname
,name
,type_desc
,default_schema_name
,create_date
,modify_date
,[sid]
from sys.database_principals where type in (''G'',''S'',''U'')
and [sid] not in ( select [sid] from sys.server_principals where type in (''G'',''S'',''U'') )
and name not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',''MS_DataCollectorInternalUser'')'
SELECT * FROM @orphan_users
--WHERE user_name LIKE '%SomeUser%' /*optional*/
ORDER BY user_name

DECLARE @orphan_users TABLE (Server NVARCHAR(MAX),DBName sysname, [user_name] sysname,type_desc nvarchar(60),default_schema_name sysname, create_date datetime, modify_date DATETIME, [sid] VARBINARY(85))

INSERT INTO @orphan_users (Server, DBname,[user_name],type_desc,default_schema_name,create_date,modify_date,[sid])

EXEC sp_msforeachdb

'use [?]

SELECT

@@SERVERNAME

,DB_NAME() DBname

,name

,type_desc

,default_schema_name

,create_date

,modify_date

,[sid]

from sys.database_principals where type in (''G'',''S'',''U'')

and [sid] not in ( select [sid] from sys.server_principals where type in (''G'',''S'',''U'') )

and name not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',''MS_DataCollectorInternalUser'')'

SELECT * FROM @orphan_users

--WHERE user_name LIKE '%SomeUser%' /*optional*/

ORDER BY user_name

Business Intelligence, Tech Notes

Publish Google Analytics Reports to the Web using Power BI

2016-02-22 Jon Culp Leave a comment

There is a feature in Power BI which allows you to connect to your google analytics data, and there is a new preview feature which allows publishing content to the web in an iframe. In this post I have combined both features together to display my website statistics to the web.

Connect to Google Analytics: https://powerbi.microsoft.com/en-us/documentation/powerbi-content-pack-google-analytics/
Publish to Web: https://powerbi.microsoft.com/en-us/blog/announcing-power-bi-publish-to-web/

Here is the result:
(View Larger)

Note: PowerBI has trouble rendering in Chrome in some cases. If you are using Chrome and can not see the dashboard then try another browser. Hopefully Microsoft will resolve this problem.

Business Intelligence, Tech Notes

Video: Business Intelligence with Power BI

2016-02-18 Jon Culp Leave a comment

I’ve been catching myself up on what Microsoft is doing lately regarding Business Intelligence. After spending days reading Microsoft documentation and testing some various configurations of Power BI, Gateway,Azure, and SSRS, I came across this video that goes over some history and then gets right into Power BI and some competing tools. I suspect Microsoft is working toward merging Power BI and SSRS based on some of their SQL Server public previews.

PostgreSQL 9, Tech Notes

Get Database Sizes in PestgreSQL

2016-02-18 Jon Culp Leave a comment

Returns the database name, owner, and size on a given server. Tested in PostgrSQL 9.2 and 9.3.

SELECT 
 d.datname AS Name
 ,pg_catalog.pg_get_userbyid(d.datdba) AS Owner
 ,CASE 
   WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
   THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
   ELSE 'No Access'
  END AS SIZE
FROM
 pg_catalog.pg_database d
ORDER BY
 CASE
  WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
  THEN pg_catalog.pg_database_size(d.datname)
  ELSE NULL
 END DESC

SELECT

d.datname AS Name

,pg_catalog.pg_get_userbyid(d.datdba) AS Owner

,CASE

WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')

THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))

ELSE 'No Access'

END AS SIZE

FROM

pg_catalog.pg_database d

ORDER BY

CASE

WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')

THEN pg_catalog.pg_database_size(d.datname)

ELSE NULL

END DESC

Source: https://wiki.postgresql.org/wiki/Disk_Usage

MS SQL Server 2008 R2, Tech Notes

SQL Server Job to Capture Reads

2016-02-05 Jon Culp Leave a comment

We needed to capture physical reads over time from our SQL server. One method we decided to deploy was to check sys.dm_exec_query_stats periodically and capture the differences between each check. The way I am doing it below is not 100% accurate because sys.dm_exec_query_stats is based on cached plans and the server will let clear the plan data over time. This means that when the differences are calculated any plan that was cleared from cache would not be included in the current check, but would still be in the comparison record. This could be solved by storing and comparing at the plan level but for my purposes I prefer to store summary information and do not mind this issue because I am running my comparison every hour and then will be averaging the results over time, and the result does not need to be exactly precise, just close enough to give us a general understanding.

This is the query inside the job:

IF  NOT EXISTS (SELECT * FROM dba.sys.objects WHERE object_id = OBJECT_ID(N'dba.[dbo].[dm_exec_query_Stats_summary]') AND type in (N'U'))
CREATE TABLE dba.dbo.dm_exec_query_Stats_summary (
	SysDateTime DATETIME NOT NULL, PhysicalReads BIGINT NOT NULL, LogicalReads BIGINT NOT NULL
	, PhysicalReads_Delta BIGINT NULL, LogicalReads_Delta BIGINT NULL
	)

INSERT INTO dba.dbo.dm_exec_query_Stats_summary (SysDateTime, PhysicalReads, LogicalReads, PhysicalReads_Delta, LogicalReads_Delta)

SELECT A.SYSDATETIME, A.PhysicalReads, A.LogicalReads
,A.PhysicalReads - B.PhysicalReads
,A.LogicalReads - B.LogicalReads
FROM(
	SELECT 
	SYSDATETIME() AS SYSDATETIME
	,SUM(total_physical_reads) as PhysicalReads
	,sum(total_logical_Reads)  as LogicalReads
	FROM sys.dm_exec_query_Stats A
)A
OUTER APPLY (
	SELECT TOP 1 B.PhysicalReads, B.LogicalReads 
	FROM dba.dbo.dm_exec_query_Stats_summary B
	WHERE B.sysdatetime < A.SYSDATETIME
	ORDER BY B.SysDateTime DESC
)B

IF NOT EXISTS (SELECT * FROM dba.sys.objects WHERE object_id = OBJECT_ID(N'dba.[dbo].[dm_exec_query_Stats_summary]') AND type in (N'U'))

CREATE TABLE dba.dbo.dm_exec_query_Stats_summary (

SysDateTime DATETIME NOT NULL, PhysicalReads BIGINT NOT NULL, LogicalReads BIGINT NOT NULL

, PhysicalReads_Delta BIGINT NULL, LogicalReads_Delta BIGINT NULL

)

INSERT INTO dba.dbo.dm_exec_query_Stats_summary (SysDateTime, PhysicalReads, LogicalReads, PhysicalReads_Delta, LogicalReads_Delta)

SELECT A.SYSDATETIME, A.PhysicalReads, A.LogicalReads

,A.PhysicalReads - B.PhysicalReads

,A.LogicalReads - B.LogicalReads

FROM(

SELECT

SYSDATETIME() AS SYSDATETIME

,SUM(total_physical_reads) as PhysicalReads

,sum(total_logical_Reads) as LogicalReads

FROM sys.dm_exec_query_Stats A

OUTER APPLY (

SELECT TOP 1 B.PhysicalReads, B.LogicalReads

FROM dba.dbo.dm_exec_query_Stats_summary B

WHERE B.sysdatetime < A.SYSDATETIME

ORDER BY B.SysDateTime DESC

This is what the table looks like with a few records. Note that this is a result of running the job multiple times with a few seconds between each interval.

MS SQL Server 2008 R2, Tech Notes, Uncategorized

Disable then Enable SQL Agent Jobs

2016-01-22 Jon Culp Leave a comment

This script has three actions:
1) Put Current Job Information Into Temp Table
2) DISABLE All SQL Server Agent Jobs
3) ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling
Note: If you need to disconnect from the server after step 1 then step 3 will not be able to read from the temp table. Instead, you would have to feed a list of job IDs to the query. I have created a place for this just in case this is what you have to do.

Test Results:
Put Current Job Information Into Temp Table: 47 row(s) affected
DISABLE All SQL Server Agent Jobs: 33 row(s) affected
ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: 33 row(s) affected

SET NOCOUNT ON

USE MSDB;

--Put Current Job Information Into Temp Table
SELECT * 
INTO #SYSJOBS	/* DROP TABLE #SYSJOBS */
FROM MSDB.dbo.sysjobs 
PRINT 'Put Current Job Information Into Temp Table: ' + CONVERT(varchar,@@rowcount)  +' row(s) affected'

SELECT * FROM #SYSJOBS
;

--DISABLE All SQL Server Agent Jobs
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1
PRINT 'DISABLE All SQL Server Agent Jobs: ' + CONVERT(varchar,@@rowcount)  +' row(s) affected'

SELECT * FROM MSDB.dbo.sysjobs
;

--ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling
UPDATE A
SET Enabled = 1
FROM MSDB.dbo.sysjobs A
WHERE Enabled = 0
AND (
	EXISTS (SELECT 1 FROM #SYSJOBS B WHERE B.enabled = 1 AND A.job_id = B.job_id) /*READ FROM TEMP TABLE*/
	OR CONVERT(varchar(MAX),job_id) IN ('') /*MANUALLY LIST JOB IDS*/
	)
PRINT 'ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: ' + CONVERT(varchar,@@rowcount)  +' row(s) affected'

SELECT * FROM MSDB.dbo.sysjobs
;

SET NOCOUNT OFF

SET NOCOUNT ON

USE MSDB;

--Put Current Job Information Into Temp Table

SELECT *

INTO #SYSJOBS /* DROP TABLE #SYSJOBS */

FROM MSDB.dbo.sysjobs

PRINT 'Put Current Job Information Into Temp Table: ' + CONVERT(varchar,@@rowcount) +' row(s) affected'

SELECT * FROM #SYSJOBS

;

--DISABLE All SQL Server Agent Jobs

UPDATE MSDB.dbo.sysjobs

SET Enabled = 0

WHERE Enabled = 1

PRINT 'DISABLE All SQL Server Agent Jobs: ' + CONVERT(varchar,@@rowcount) +' row(s) affected'

SELECT * FROM MSDB.dbo.sysjobs

;

--ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling

UPDATE A

SET Enabled = 1

FROM MSDB.dbo.sysjobs A

WHERE Enabled = 0

AND (

EXISTS (SELECT 1 FROM #SYSJOBS B WHERE B.enabled = 1 AND A.job_id = B.job_id) /*READ FROM TEMP TABLE*/

OR CONVERT(varchar(MAX),job_id) IN ('') /*MANUALLY LIST JOB IDS*/

)

PRINT 'ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: ' + CONVERT(varchar,@@rowcount) +' row(s) affected'

SELECT * FROM MSDB.dbo.sysjobs

;

SET NOCOUNT OFF

MS SQL Server 2008 R2, Tech Notes

Get and Kill Executing Processes of Job

2016-01-19 Jon Culp Leave a comment

This query will identify the current SQL processes or executions which belong to a specific job.

SELECT *
FROM master.dbo.sysprocesses p
JOIN msdb.dbo.sysjobs J
	ON UPPER(SUBSTRING(replace(p.program_name, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34))
	= UPPER(CONVERT(VARCHAR(34),convert(varbinary(16), J.Job_Id),1))
WHERE J.name = 'Some Job'

SELECT *

FROM master.dbo.sysprocesses p

JOIN msdb.dbo.sysjobs J

ON UPPER(SUBSTRING(replace(p.program_name, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34))

= UPPER(CONVERT(VARCHAR(34),convert(varbinary(16), J.Job_Id),1))

WHERE J.name = 'Some Job'

We had a job process that was getting blocked for long periods, and in turn was causing a lot of blocking. The best solution here is to fix the root blocker, however, as a short term fix we want to allow the job to run but then kill it a specific time if it is still running. To do this, we create another job that looks for all of the SPIDS associated to executions which belong to the job getting blocked, and execute a kill commend for each SPID.

SET NOCOUNT ON

--DECLARE VARIABLES
DECLARE @Job_ID UNIQUEIDENTIFIER = 'YOUR JOB ID'
DECLARE @tbl_SPIDS TABLE (SPID SMALLINT)
DECLARE @SPID SMALLINT
DECLARE @SQL VARCHAR(MAX)

--GET ALL SPIDS FOR ACTIVE JOB PROCESSES
INSERT INTO @tbl_SPIDS ( SPID )

SELECT p.spid
FROM   master.dbo.sysprocesses p
WHERE UPPER(SUBSTRING(replace(p.program_name, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34))
    = UPPER(CONVERT(VARCHAR(34),convert(varbinary(16), @Job_Id),1))

--KILL ACTIVE JOB PROCESSES
WHILE EXISTS (SELECT TOP 1 1 FROM @tbl_SPIDS)
BEGIN
	SET @SPID = (SELECT TOP 1 SPID FROM @tbl_SPIDS)
	SET @SQL = 'KILL '+CONVERT(VARCHAR(10),@SPID)
	EXEC (@SQL)
	DELETE FROM @tbl_SPIDS WHERE SPID = @SPID
END	

SET NOCOUNT OFF

SET NOCOUNT ON

--DECLARE VARIABLES

DECLARE @Job_ID UNIQUEIDENTIFIER = 'YOUR JOB ID'

DECLARE @tbl_SPIDS TABLE (SPID SMALLINT)

DECLARE @SPID SMALLINT

DECLARE @SQL VARCHAR(MAX)

--GET ALL SPIDS FOR ACTIVE JOB PROCESSES

INSERT INTO @tbl_SPIDS ( SPID )

SELECT p.spid

FROM master.dbo.sysprocesses p

WHERE UPPER(SUBSTRING(replace(p.program_name, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34))

= UPPER(CONVERT(VARCHAR(34),convert(varbinary(16), @Job_Id),1))

--KILL ACTIVE JOB PROCESSES

WHILE EXISTS (SELECT TOP 1 1 FROM @tbl_SPIDS)

BEGIN

SET @SPID = (SELECT TOP 1 SPID FROM @tbl_SPIDS)

SET @SQL = 'KILL '+CONVERT(VARCHAR(10),@SPID)

EXEC (@SQL)

DELETE FROM @tbl_SPIDS WHERE SPID = @SPID

END

SET NOCOUNT OFF

Jon Culp [.com]

Category Archives: Tech Notes

Create Read Only User in Azure DB

WiFi Range Heat Mapping

Publish Google Analytics Reports to the Web using Power BI

Here is the result:
(View Larger)

Video: Business Intelligence with Power BI

Get Database Sizes in PestgreSQL

SQL Server Job to Capture Reads

Disable then Enable SQL Agent Jobs

Get and Kill Executing Processes of Job

An Information Archive for a Frivolous Human

Here is the result: (View Larger)

An Information Archive for a Frivolous Human

Here is the result:
(View Larger)