Latest Event Updates

Isolation Level and NOLOCK

Posted on Updated on

In short,

  • SET TRANSACTION ISOLATION LEVEL XXXXX is for the entire script
  • WITH (NOLOCK) is for that specific table

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM MyTable WITH (NOLOCK)

READ COMMITTED

Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting innonrepeatable reads or phantom data. This option is the SQL Server default.

READ UNCOMMITTED

Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.

REPEATABLE READ

Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.

SERIALIZABLE

Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

SSRS Subscription List

Posted on Updated on

If you need to get all SSRS that has a Subscription.

SELECT SubscriptionOwner = u.UserName
,s.ModifiedDate
,s.[Description]
,s.EventType
,s.DeliveryExtension
,s.LastStatus
,s.LastRunTime
,sch.NextRunTime
,ScheduleName = sch.Name
,ReportName = c.Name
,ReportPath = c.[Path]
,ReportDescription = c.[Description]
FROM ReportServer.dbo.Subscriptions AS s
INNER JOIN ReportServer.dbo.Users AS u ON s.OwnerID = u.UserID
INNER JOIN ReportServer.dbo.[Catalog] AS c ON s.Report_OID = c.ItemID
INNER JOIN ReportServer.dbo.ReportSchedule AS rs ON s.Report_OID = rs.ReportID
AND s.SubscriptionID = rs.SubscriptionID
INNER JOIN ReportServer.dbo.Schedule AS sch ON rs.ScheduleID = sch.ScheduleID
ORDER BY u.UserName
,c.[Path];

Populate States Table

Posted on

I always find myself recreating a States table for web applications. Here it is.

CREATE TABLE [States](

[StatesID] [int] IDENTITY(1,1) NOT NULL,
[Abbreviation] [nchar](2) NOT NULL,
[Name] [nvarchar](128) NOT NULL,

CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
( [StatesID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
ON [PRIMARY]

GO

INSERT INTO [States] VALUES
(‘AL’, ‘Alabama’),
(‘AK’, ‘Alaska’),
(‘AZ’, ‘Arizona’),
(‘AR’, ‘Arkansas’),
(‘CA’, ‘California’),
(‘CO’, ‘Colorado’),
(‘CT’, ‘Connecticut’),
(‘DE’, ‘Delaware’),
(‘DC’, ‘District of Columbia’),
(‘FL’, ‘Florida’),
(‘GA’, ‘Georgia’),
(‘HI’, ‘Hawaii’),
(‘ID’, ‘Idaho’),
(‘IL’, ‘Illinois’),
(‘IN’, ‘Indiana’),
(‘IA’, ‘Iowa’),
(‘KS’, ‘Kansas’),
(‘KY’, ‘Kentucky’),
(‘LA’, ‘Louisiana’),
(‘ME’, ‘Maine’),
(‘MD’, ‘Maryland’),
(‘MA’, ‘Massachusetts’),
(‘MI’, ‘Michigan’),
(‘MN’, ‘Minnesota’),
(‘MS’, ‘Mississippi’),
(‘MO’, ‘Missouri’),
(‘MT’, ‘Montana’),
(‘NE’, ‘Nebraska’),
(‘NV’, ‘Nevada’),
(‘NH’, ‘New Hampshire’),
(‘NJ’, ‘New Jersey’),
(‘NM’, ‘New Mexico’),
(‘NY’, ‘New York’),
(‘NC’, ‘North Carolina’),
(‘ND’, ‘North Dakota’),
(‘OH’, ‘Ohio’),
(‘OK’, ‘Oklahoma’),
(‘OR’, ‘Oregon’),
(‘PA’, ‘Pennsylvania’),
(‘PR’, ‘Puerto Rico’),
(‘RI’, ‘Rhode Island’),
(‘SC’, ‘South Carolina’),
(‘SD’, ‘South Dakota’),
(‘TN’, ‘Tennessee’),
(‘TX’, ‘Texas’),
(‘UT’, ‘Utah’),
(‘VT’, ‘Vermont’),
(‘VA’, ‘Virginia’),
(‘WA’, ‘Washington’),
(‘WV’, ‘West Virginia’),
(‘WI’, ‘Wisconsin’),
(‘WY’, ‘Wyoming’);

Show all current running Stored Procedures

Posted on Updated on

There are times when you need to figure out what Stored Procedures are currently running and if they are blocking any if all other processes.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.start_time,
req.blocking_session_id,
req.wait_type,
req.wait_time,
req.last_wait_type,
req.reads,
req.writes,
req.logical_reads,
req.row_count
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE DB_NAME(database_id) = ‘MyDatabaseName

Concatenate rows into CSV

Posted on Updated on

If you have the need to take multiple rows into 1 comma separated row then this should do the trick. One of the biggest usages for this is when you’re using SSRS with multiple parameters.

DECLARE @COLUMNS VARCHAR(MAX)

SELECT @COLUMNS =
COALESCE (@COLUMNS + ‘,’ + CAST(FirstName AS NVARCHAR) + ”,” + CAST(FirstName AS NVARCHAR) + ”)
FROM MyCustomers

SELECT @COLUMNS

SELECT Linked Server

Posted on Updated on

If you already have a Linked Server setup, here are a way to access the Linked Server tables.

SELECT * FROM LinkedServer..Schema.Table

In the example below

Linked Server name: CACHE

Schema: SQLUser

Table: P

SELECT * FROM CACHE..SQLUser.P

OpenRowSet

Posted on Updated on

Using OpenRowSet to read all data from an Excel worksheet into a global temp table.

INSERT INTO ##ABC
SELECT *
FROM
OPENROWSET(‘Microsoft.Jet.OLEDB.4.0‘,
Excel 8.0;Database=D:\EDI\SAMPLE_051311_1.xls‘, [Sheet1$])