SQL SERVER – Marking Filegroup as ReadOnly with SQL Server

My love for writing about ReadOnly databases have pushed me to explore more about this topic. Based on previous blogs, few readers did ask me that they heard about marking filegroups as ReadOnly and they wanted to know how this can be done. This blog post is in continuation to this journey where-in we will learn about the Readonly attribute.

TSQL Script to mark ReadOnly Filegroup

Let us create the database first.

CREATE DATABASE [ReadOnlyDB]
CONTAINMENT
= NONE
ON  PRIMARY
( NAME = N'ReadOnlyDB', FILENAME = N'C:\Temp\ReadOnlyDB.mdf' , SIZE = 4024KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ReadOnlyDB_log', FILENAME = N'C:\Temp\ReadOnlyDB_log.ldf' , SIZE = 20480KB , FILEGROWTH = 10%)
GO

Let us next create an Filegroup which will get marked as ReadOnly.

ALTER DATABASE ReadOnlyDB ADD FILEGROUP ReadOnlyDB_FG;
GO
ALTER DATABASE ReadOnlyDB ADD FILE (
name = ReadOnlyDB_FG,
FILENAME = 'c:\temp\ReadOnlyDB_FG')
TO FILEGROUP ReadOnlyDB_FG
GO

Next we would like to create a table on this specific filegroup. This will form the base for our experiment.

USE ReadOnlyDB
GO
-- Creating our table
CREATE TABLE tbl_SQLAuth (id INT, Longname CHAR(8000))
ON ReadOnlyDB_FG
GO

Let us start our experiment in marking filegroups as ReadOnly. One of the important learning here is to understand, we cannot mark our PRIMARY filegroup as readonly.

-- Mark the PRIMARY filegroup read-only
ALTER DATABASE ReadOnlyDB MODIFY FILEGROUP [PRIMARY] READ_ONLY;
GO

Msg 5047, Level 16, State 1, Line 29
Cannot change the READONLY property of the PRIMARY filegroup.

We will be presented with the above errors. Now, let us mark the other Filegroup next.

-- Mark the Other filegroup read-only
ALTER DATABASE ReadOnlyDB MODIFY FILEGROUP ReadOnlyDB_FG READ_ONLY;
GO

The filegroup property ‘READ_ONLY’ has been set.

Once this succeeds, we cannot do anything with the filegroup. Let us do few tasks to check the errors.

-- Create a table on the Read_Only Filegroup
-- Explicitly call the FG where the table will be created.
CREATE TABLE tbl_SQLAuth_1 (id INT, Longname CHAR(8000))
ON ReadOnlyDB_FG
GO

Msg 1924, Level 16, State 2, Line 3
Filegroup ‘ReadOnlyDB_FG’ is read-only.

As you can see, we cannot create any tables on this Filegroup. If we try to insert any values into an table which is attached to this Filegroup, we will be presented with an error too.

INSERT INTO tbl_SQLAuth VALUES (1, 'SQLAuth');

Msg 652, Level 16, State 1, Line 4
The index “” for table “dbo.tbl_SQLAuth” (RowsetId 72057594040549376) resides on a read-only filegroup (“ReadOnlyDB_FG”), which cannot be modified.

As you can see, the Read_Only Filegroups are functioning just like our ReadOnly Databases which have been explained in our previous blogs. The best part of Read_Only filegroups is that, we get an flexibility to lock just a part of database files when compared to marking the whole database.

-- Clean up time
USE MASTER
GO
DROP DATABASE ReadOnlyDB
GO

As I wrap up this blog, I would want to know how many of us out there have used Read_Only filegroups in our environments. What are those scenarios you made these changes? I would like to learn from your experience. Let me know via your comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Interesting Observation with Currency Symbols

The currency symbols like $ and £ are implicitly treated as money value in SQL Server

If you run the following code

SELECT $

SELECT £

You can see that both of the above statements return the value 0.00

It is also possible to use them in arithmetic calculations

SELECT $+5

returns 5.00

SELECT 45-£

returns 45.00

How many of you know this?

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #011 – Script to Convert List to Table and Table to List

Now today’s interview question is really more of daily routine task for many of the developer. 11 years ago, when I went to my very first interview call. My interview was scheduled at 6 PM in one of the very well known corporation. The corporation was known for its first initiatives for social media. When I reached there at 6 PM there were many candidates and we all got the same task to complete. The task was as follows:

Task 1) Write a script to convert a list to a table

Task 2) Write a script to convert the table to a list

We all had two hours of time to complete the task. There were quite a few developers who had completed the task, but I had failed the interview as I was not good with SQL Scripting. After I return from the interview, I had decided to write scripts for my personal learning. I did not want to fail the interviews in the future if I was ever going to get that task.

Here are the answers for both the tasks.

Answer for Task 1: SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP

Answer for Task 2: SQL SERVER – UDF – Function to Convert List to Table

The irony of the life is that I never got the same task again in future. Here is the question back to you – can you guess the name of the organization which was based out of California and one of the early social media giants?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SCRIPT – SQL Listening on Dynamic Port?

With every release of SQL Server, sometimes I need to revisit some of the old blogs to see how they have enhanced. This learning experience is an ongoing process and the best way I learn is by doing a search on the blog from time to time. Long ago I had written a blog post to find the port number on which SQL Server is listening: SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

Recently one of my blog reader sent email asking below.

I read your blog which talks about reading ERRORLOG and finding the port. But is there any way to find if that port is fixed port or dynamic port?

I know that the port value is stored in registry hive of SQL Server and this is instance specific. Here is the registry key for SQL Server 2014 instance running on my laptop.

If SQL is configured to use Dynamic Ports then TcpDynamicPorts would have the port value. In case of fixed port (which I have on my SQL Instance) then we would see TcpPort value.

To get same information using T-SQL, I have written a small script which would help you. It would detect dynamic port and static port as well.

DECLARE     @ServerName SYSNAME
,@InstanceID NVARCHAR(128)
,
@InstanceName NVARCHAR(128)
,
@tcp_port NVARCHAR(10)
,
@InstanceKey NVARCHAR(255)
SELECT @ServerName = @@SERVERNAME
SELECT @InstanceName = ISNULL((CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(128))), 'MSSQLSERVER')
EXECUTE xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
,@key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
,@value_name = @InstanceName
,@value = @InstanceID OUTPUT
SELECT @InstanceKey = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\' + @InstanceID + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
,@key = @InstanceKey
,@value_name = 'TcpDynamicPorts'
,@value = @tcp_port OUTPUT
IF @tcp_port IS NOT NULL
SELECT 'SQL Server (' + @InstanceName + ') uses dynamic tcp port: ' + CAST(@tcp_port AS NVARCHAR(128))
ELSE
BEGIN
EXEC
xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
,@key = @InstanceKey
,@value_name = 'TcpPort'
,@value = @tcp_port OUTPUT
SELECT 'SQL Server (' + @InstanceName + ') on ' + @ServerName+ ' uses static tcp port: ' + CAST(@tcp_port AS NVARCHAR(128))
END
GO

Generating such scripts takes time and the best way to learn these is by sharing. So do you have any other script similar to this that is worth a share for other readers? Please share them via the comments section.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Interesting Function AGENT_DATETIME

One in a while I come across such interesting side of SQL Server that it even surprises me. Just the other day I came across function AGENT_DATETIME. I had never used it before and now I just found usage example of the same.

There can be several methods to convert integer values to a datetime value. But did you know that there is a system function in MSDB database that does this job? It is AGENT_DATETIME function. It accepts two parameters one is date value in YYYYMMDD format and another is time value in HHMMSS format

SELECT MSDB.DBO.AGENT_DATETIME(20150119,0)

The result is

2015-01-19 00:00:00.000

SELECT MSDB.DBO.AGENT_DATETIME(20150119,171911)

The result is

2015-01-19 17:19:11.000

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – The Basics of the File System Task, Part 1 – Notes from the Field #071

[Note from Pinal]: This is a new episode of Notes from the Field series. Every time I give an introductory note, however, this time there is no need of intro note. This note is from Andy and as we all know he is amazing person when we have to understand the fundamentals. He has written this blog post with such an interesting way that you must read it to understand the very basic of the file system task.


Many data integration scenarios involve reading data stored in flat files or performing extracts from a relational (or legacy) system into flat files. Learning how to configure and use the SQL Server Integration Services (SSIS) File System Task will support your efforts when loading data to and from flat files. Remember: SSIS is a software development platform. With “SQL Server” included in the name, it is easy for people to confuse SSIS as a database tool or accessory, but Control Flow Tasks put that confusion to rest.

SSIS provides several Control Flow tasks. Here is a list that provides a good approximation of which tasks I use most, from most-used to least-used:

In this article I provide a basic example of configuring the SSIS File System Task, shown in Figure 1:

Figure 1: SSIS File System Task

The File System Task provides one way to implement an SSIS Design Pattern for source file archival. When you first open the File System Task Editor, you will note several properties in the property grid. Whenever you see an Operation property in an SSIS task editor, know that that property drives the other property selections. Options for the Operation property of the SSIS File System Task are shown in Figure 2:

Figure 2: SSIS File System Task Operation Property Options

The Operation options are:

  • Copy directory
  • Copy file (default)
  • Create directory
  • Delete directory
  • Delete directory content
  • Delete file
  • Move directory
  • Move file
  • Rename file
  • Set Attributes

I stated the Operation property drives the other property selections. Take a look at the File System Task Editor when I change the Operation option from “Copy file” (Figure 2) to “Delete file” as shown in Figure 3:

Figure 3: The File System Task Editor with the “Delete file” Operation Selected

See? There are less properties required for the “Delete file” operation. The available properties are even more different for the “Set Attributes” operation, shown in Figure 4:

Figure 4: The File System Task Editor with the “Set Attributes” Operation Selected

The Operation property changes the editable properties, exposing some and hiding others. With flexibility come complexity. Even though the File System Task is complex, I’ve found the task is stable and extremely useful. Let’s look at a practical example; using the File System Task to archive a flat file.

To begin configuring the SSIS File System Task for file archival, select the “Move file” operation as shown in Figure 5:

Figure 5: SSIS File System Task with the “Move file” Operation Selected

Using the IsSourcePathVariable and IsDestinationPathVariable properties extends the flexibility of the File System Task and further changes the list of available properties in the property grid, as shown in Figure 6:

Figure 6: Opting to Use Variables for Source and Destination Paths

Note the SourceConnection and DestinationConnection properties are hidden and the SourceVariable and DestinationVariable properties are available in their place. For the sake of simplicity, we’re not going to use SSIS Variables to define the source and destination paths in this article. Reset the IsSourcePathVariable and IsDestinationPathVariable properties to False. Click the SourceConnection property dropdown, and click “<New connection…>” as shown in Figure 7:

Figure 7: Selecting a New Connection for the SSIS File System Task SourceConnection Property

The File Connection Manager Editor displays with the “Usage type” property set to “Existing file” as shown in Figure 8:

Figure 8: File Connection Manager Editor

Click the “Browse…” button and select a flat file you wish to move, as shown in Figure 9:

Figure 9: Selecting the Source File

Once the File Connection Manager Editor is configured, it should appear similar to that shown in Figure 10:

Figure 10: A Configured File Connection Manager Editor

Click the OK button to close the File Connection Manager Editor. Next, click the DestinationConnection property in the SSIS File System Task Editor. As with the SourceConnection property, click the dropdown and select “<New connection…>”. When the File Connection Manager Editor displays, select the “Existing folder” Usage type, as shown in Figure 11:

Figure 11: Configuring the Destination File Connection Manager

Note the warning at the bottom of the File Connection Manager Editor window: “Folder name must be specified.” Click the “Browse…” button and navigate to the directory where you wish to archive the source file as shown in Figure 12:

Figure 12: Configuring the Location of the DestinationConnection File Connection

Click the OK button in the “Browse For Folder” dialog to return to the File Connection Manager Editor, as shown in Figure 13:

Figure 13: A File Connection Manager Configured for the DestinationConnection Property

Click the OK button to close the File Connection Manager Editor and return to the File System Task Editor, as shown in Figure 14:

Figure 14: An SSIS File System Task Configured to Archive a File

The SSIS File System Task is now configured to archive a file. Let’s test it! Click the OK button to close the File System Task Editor. Press the F5 key or select SSIS->Start Debugging to test your work. My result is shown in Figure 15:

Figure 15: Successful Test Execution of the SSIS File System Task

Viewing the source and destination directories, we see the file was successfully moved – shown in Figure 16:

Figure 16: The File, Moved!

One tricky part when configuring the SSIS File System Task to move a file is realizing that you need to select the actual file for the source and the directory for the destination.

As I stated earlier, the SSIS File System Task is powerful, flexible, and robust. This article has demonstrated one way you can use the File System Task to archive files. Archiving files after loading the data they contain is a common practice in data integration.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Error – Msg 6401, Level 16: Cannot roll back Transaction

In a recent Usergroup meet that I attended someone asked me this simple yet interesting question about how transactions work and is there any concept like nested transactions inside SQL Server. Though I have talked about this in the past, thought it would be worth revisiting this concept to drive home some learnings again to readers who are new to SQL Server. Let us learn this using the script code as shown below:

-- Create our database for testing
USE MASTER;
GO
CREATE DATABASE TransactionsDemo;
GO
USE TransactionsDemo;
GO
-- Create a table for testing
CREATE TABLE tbl_SQLAuth (Col1 INT);
GO
SET NOCOUNT ON;
GO

Once the database is created, our next logical step is to create some transactions to test if we can mimic the use of nested transactions.

-- Create an explicit transaction
BEGIN TRAN OuterTransaction;
GO
INSERT INTO tbl_SQLAuth VALUES (1);
GO 10
-- Create an explicit so called nested transaction
BEGIN TRAN InnerTransaction;
GO
INSERT INTO tbl_SQLAuth VALUES (2);
GO 10
-- Look at the trancount?
SELECT @@TRANCOUNT;
GO

In the example above we started using an explicit transaction called as “OuterTransaction” and then inserted values post which we created yet another transaction called as “InnerTransaction”. The @@TRANCOUNT will show a value of 2 now. Now that we know the transaction count is 2, let us try to rollback the inner transaction alone.

-- Rollback the inner transaction
ROLLBACK TRAN InnerTransaction;
GO

Msg 6401, Level 16, State 1, Line 33
Cannot roll back InnerTransaction. No transaction or savepoint of that name was found.
Now this is interesting to see the error, we have explicitly created a transaction called as “InnerTransaction” in our code and it is getting ignored.

-- Works?
ROLLBACK TRAN OuterTransaction;
GO

Or the below command works:

-- Works?
ROLLBACK TRAN;
GO

And it is so strange that this above commands worked. If you try to query the table, it will have no rows.

-- What hapenned?
SELECT COUNT(*) FROM tbl_SQLAuth;

This is an important learning one needs to know when working with ROLLBACK command inside SQL Server. There is nothing like a nested transaction perse inside SQL Server. Go ahead and do the clean up.

-- Cleanup time
USE MASTER;
GO
DROP DATABASE TransactionsDemo;
GO

I am curious to know from developers out there, have you ever faced this situation in your environment and learnt it the hard way? If you can share your learning, it would be great.

Reference: Pinal Dave (http://blog.sqlauthority.com)