Changing the Restrict Access Option in SQL Server

Ever get something like this annoying popup when you try to change a SQL Server database from SINGLE_USER to MULTI_USER?

This is what always happens to me when a database is already locked down into SINGLE_USER mode and I try to change the property in the GUI. In order to change it, I run this T-SQL in SSMS (SQL Server Management Studio):

ALTER DATABASE [MyDatabase]
SET MULTI_USER WITH ROLLBACK IMMEDIATE;

If there is already an operation going on in the database, the "WITH ROLLBACK IMMEDIATE" will take things back to their previous state before the transaction started. After the statement completes, more than one authorized user should be able to access the database. Always use caution when performing any ALTER DATABASE statement on a production server. When a database is locked down in SINGLE_USER mode, an icon of a person will appear next to the database in SSMS:

If you refresh your view of the databases in object explorer after running the above T-SQL, the person icon should disappear from the database indication that it is in MULTI_USER mode.

The DATALENGTH() Function

Yesterday I was getting a very interesting error in SSRS. A user was trying to export data to Excel and it was basically telling her that it was too much data to fit into Excel. I began to suspect that one of the fields in the report was quite large. I went to run a standard T-SQL query to see exactly how large the field in question was. The field was named [Description] and it's type was text:

USE [MyDatabase]

SELECT LEN([Description])
FROM MyTable

Of course, you know what happened:

Msg 8116, Level 16, State 1, Line 3
Argument data type text is invalid for argument 1 of len function.

So, the LEN() function cannot be used on text type fields. Was there a LEN() equivalent for the text type? A little googling revealed that there was:

DATALENGTH()

This function was exactly what I needed to get the length for a text type field.

Finding the Name of the Primary Domain Controller

1. Open a command prompt
2. Type echo %logonserver%

That's it - not very complicated at all. The output in the command prompt is that machine's PDC (Primary Domain Controller).

Rebuilding Indexes with T-SQL

In my last position, I ended up writing a lot of T-SQL. I have recently taken on a new position and I am the company's sole DBA, so there is less T-SQL, but it certainly has not gone away completely. Case in point, SQL Server's maintenance plans are pretty cool and very simple to implement, but they are not bullet proof. Last week I kept getting an alert an index rebuild job had failed. I took a look at the maintenance plan, troubleshot it, and even rebuilt it but it continued to fail. I then decided to look at the underlying T-SQL associated w/ this job. It was failing on one particular index rebuild and succeeding on the rest of them. However, this still shows up in the job as a failure. I decided to script out the index rebuild and since then I have had no issues with the job. I opted to leave the CATCH block empty. Depending on how important the job is, it may be wise to have something in the CATCH that writes to a table so that one can be aware of exactly which index rebuilds failed.Additionally, I opted to sort in the tempdb as opposed to the DB itself. Once again, that one is your call. It will depend on how your environment is set up as to whether or not this is a good idea.

USE [DB_NAME_GOES_HERE]

DECLARE @SQL VARCHAR(3000)
, @START INT
, @LAST INT
, @TABLE_NAME VARCHAR(200)
, @IX_NAME VARCHAR(200)

CREATE TABLE #TEMP(ID INT IDENTITY(1,1), [TABLE_NAME] VARCHAR(200)
, [IX_NAME] VARCHAR(200))

INSERT #TEMP
SELECT '[' + S.NAME + '].[' + T.NAME + ']' AS [TABLE_NAME], I.NAME AS [IX_NAME]
FROM SYS.INDEXES I
INNER JOIN sys.tables T
ON I.object_id = T.object_id
INNER JOIN sys.schemas S
ON T.schema_id = S.schema_id
WHERE I.INDEX_ID > 0
ORDER BY T.NAME, I.NAME

SET @START = (SELECT MIN(ID) FROM #TEMP)
SET @LAST = (SELECT MAX(ID) FROM #TEMP)

WHILE @START <= @LAST
BEGIN

SET @TABLE_NAME = (SELECT [TABLE_NAME] FROM #TEMP WHERE ID = @START)
SET @IX_NAME = (SELECT [IX_NAME] FROM #TEMP WHERE ID = @START)

SET @SQL = ' BEGIN TRY
ALTER INDEX [' + @IX_NAME + '] ON ' + @TABLE_NAME + '
REBUILD WITH ( PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF
, SORT_IN_TEMPDB = ON )
END TRY

BEGIN CATCH ' +
-- If you want something to happen in the Catch block
-- , place that code here
' END CATCH'

EXEC (@SQL)

SET @START = @START + 1
END

DROP TABLE #TEMP

Great Quote of the Day

This concept is simple and primary, but often overlooked:
"...it is a good idea to create non-clustered indexes on all foreign keys. You will always run lots of queries, and also use the JOIN operator, based on your primary and foreign keys."
- Wagner Crivelini
From the article SQL and the JOIN Operator

Converting a Julian Date

I had to accomplish this at work yesterday. I did not see anything in the pre-defined SQL Server functions that would do it, so I started Googleing. Thank goodness for stackoverflow.com.


DECLARE @JULIAN_DATE INT = 2012146

SELECT DATEADD(D, CAST(SUBSTRING(
CAST(@JULIAN_DATE AS VARCHAR), 5, LEN(@JULIAN_DATE) - 4) AS INT) - 1,
        CAST('1/1/' + SUBSTRING(CAST(@JULIAN_DATE AS VARCHAR),1,4) AS DATE))
AS [NEW DATE]
     
http://stackoverflow.com/questions/2692361/most-concise-way-to-convert-julian-date-yyyyday-of-year-to-sql-datetime

This T-SQL is a little too verbose to be writing over and over again so later today I'm going to roll it up into a UDF (User Defined Function).

Modifying Data Through a View

The following requirements MUST be met to modify data through a view in SQL Server:

• The modification can reference exactly ONE table
• Columns in the view must reference columns in a table directly
• The column cannot be derived from an aggregate
• The column cannot be computed as the result of a UNION/UNION ALL, INTERSECT, EXCEPT, or CROSSJOIN
• The column being modified cannot be affected by the DISTINCT, GROUP BY, or HAVING clause
• The TOP operator cannot be used