A couple of months ago, I ran into a strange issue. Whenever I fired the “RECONFIGURE” statement, the SQL Server returned an error message – “Ad hoc update to system catalog is not supported.” I was able to trace this to the fact that somebody had set the “allow updates” setting on the server to 1. This setting is 0 by default on a new install of SQL Server. This setting was used in SQL Server 2000 to allow direct ad-hoc updates to system catalogs and tables. This setting is now deprecated since SQL Server 2005 and all SQL Server installations come with this set to 0 by default. Per Books On Line (read the BOL help on this option here), this setting has no effect, except that the RECONFIGURE statement will fail, just as it was doing in my case.

The root cause in my case was that it was a shared environment and because this configuration switch lies in full public view when sp_configure is run, some novice developer attempting to learn SQL Server inadvertently set the switch but forgot to reset the same. You can read about my experience here: http://beyondrelational.com/blogs/nakul/archive/2011/02/14/ad-hoc-update-to-system-catalogs-is-not-supported-when-using-reconfigure.aspx

Just recently, I ran into the problem again – this time on a friend’s test configuration. That’s what prompted me to open a case in Microsoft Connect. You can find the case filed here: https://connect.microsoft.com/SQLServer/feedback/details/662305/sp-configure-allow-updates-should-be-an-advanced-option

My belief is that if something is deprecated, and is not meant to be used – it should not be in obvious view. We don’t keep unused stuff on the front lawns – we stuff it away in the garages and therefore, this option should at least be made an advanced option.

If you agree with me, please vote for fixing this at: https://connect.microsoft.com/SQLServer/feedback/details/662305/sp-configure-allow-updates-should-be-an-advanced-option

Until we meet next time,

Be courteous. Drive responsibly.

I recently completed running a series on the “Underappreciated Features of Microsoft SQL Server”, under which I introduced the SQLCMD mode available within the SQL Server Management Studio. SQLCMD mode is a means of using SQLCMD functionality within the SSMS, which would normally have been available on the command prompt.

Now, SQLCMD is a very powerful utility – highly productive and flexible. I learnt that myself when researching for the original post. Here’s one feature that caught my immediate attention, and I won’t shy away from saying that it has made me use SQLCMD even more!

Default Editor on SQLCMD

The need of a script editor in SQLCMD

SQLCMD, as we all know, is used to run T-SQL scripts against a SQL Server instance via the command line interface. For example, I can run the following query as shown below:

USE AdventureWorks2008R2
SELECT BusinessEntityID,JobTitle FROM HumanResources.Employee
GO

This works great on small queries, as shown in this example. However, what about complex or queries running into multiple lines? Those would become difficult to write on the command line. That leads us to a the need of a script editor in SQLCMD.

The default Editor of SQLCMD

SQLCMD comes with a set of highly commands. One such command is “[:] ED”. If we type in “ED” on the SQLCMD prompt and hit the Enter key, we find that it launches the default editor of the command prompt – the very familiar – blue & white interface!

Using the set script editor in SQLCMD

So, now that we have our SQLCMD editor open, we can write our multi-line/complex queries that we would want to run as a batch. Let’s use the following formatted query as an example:

-- Declare a Table variable, and insert some data into it
DECLARE @MyTable TABLE (ID INT IDENTITY (1,1), MyName VARCHAR(100))

INSERT INTO @MyTable
SELECT msc.name + CONVERT(VARCHAR(10),ROUND(RAND()*1000,0))
FROM msdb.sys.objects mso (NOLOCK)
CROSS JOIN msdb.sys.columns msc (NOLOCK)

SELECT * FROM @MyTable
GO

Once you save & exit out of the editor, the entire query is available in the SQLCMD editor:

 

Hit “Enter” and watch the query zoom by.

Setting a default SQLCMD Editor

Now, not everyone would want to use the default DOS editor. Lots of better editors are now available and in use, and here’s how you can set your environment to use this editor as default in SQLCMD:

1. Navigate out to Control Panel –> System
2. Click on “Advanced System Settings”
3. Click on the “Environment Variables” button in the “Advanced” tab
4. Add a user variable (to keep this setting restricted to your user/login):

Variable Name: SQLCMDEDITOR

Variable Value: Executable name (e.g. NOTEPAD.EXE)

Now, launch the editor from the SQLCMD, and NOTEPAD would open up as the query editor. Once the query editing is complete, simply save & exit out of the editor to return back to SQLCMD.

  

Clean Up – Where do my SQLCMD files go? 

We saw above that each of these SQLCMD editors create temporary files which are edited by the users. Where do all of these files go? They simply go to the path specified by the environment variable %TEMP%. Normally, after the SQLCMD session ends, this file should be removed. In case it is not removed, one can always remove this file from the user’s temporary folder.

Reference: http://msdn.microsoft.com/en-us/library/ms162773.aspx

Do let me know if you have any other cool SQLCMD tricks. I will publish them on my blog with due credit.

Until we meet next time,

Be courteous. Drive responsibly.

In May 2011, I presented an Exclusive Microsoft VTD Webcast on the “Underappreciated Features of Microsoft SQL Server”. It was an exclusive webcast series wherein industry experts came in and presented on various topics to create a scalable and secure environment for solution with SQL Server 2008 (http://beyondrelational.com/blogs/nakul/archive/2011/04/29/exclusive-sql-server-webcast-series-on-security-amp-scalability-register-today.aspx).

The session went very smooth, and I was fortunate enough to have a strong following throughout the session. I presented the following underappreciated features during the session:

1. For Administration: Object Explorer Details and Object Explorer
2. For Development: Template Explorer and T-SQL Debugger

The presentation material is now available on the SQLServerFAQ MSDN blog and the webcast videos are available for download on MSDN.

For my session on “Learn Underappreciated Features of Microsoft SQL Server”

• PPT here: http://beyondrelational.com/media/p/12497.aspx
• Webcast Download:http://download.microsoft.com/download/6/0/C/60CBF7F0-5558-4C00-ACC0-ABFF25A92244/Day5_SQLServer webcast.wmv

Other speakers:

Refer the links below for the presentation deck and webcast video download link for other speakers of the Webcast series.

• Managing and Optimizing Resources for SQL Server [PPT | Webcast Download] – Balmukund Lakhani [Blog | @Blakhani]
• Optimizing and Tuning Full Text Search for SQL Server [PPT | Webcast Download] – Sudarshan Narasimhan [Blogs @ SQLServerFAQ]
• Understanding Performance Bottlenecks using Performance Dashboard [PPT | Webcast Download | QnA]
• Cool Tools to have for SQL Server DBA [Webcast Download] – Pradeep Adiga [Blog | @PradeepAdiga]

Have you registered for Community Tech Days (CTD) – Tech-Ed On The Road in Ahmedabad on June 11, 2011?

So, have you? If not, you are missing out on a lot of cool technology discussions! If you would like to change your mind, head over to my previous post here to know more and register!

See you on Saturday, June 11, 2011! Oh…and when you come down to the venue,

Be courteous. Drive responsibly.

Attention – ALL Professional Developers, Project Managers, Architects, IT Managers, IT Administrators and Implementers of Ahmedabad! Community Tech Days/Tech-Ed On The Road is back, and everyone is invited!

The focal point of the day would be some of the best sessions from TechEd India 2011, and will cover technologies like Database and BI, Windows 7, ASP.NET.

Where & When?

Venue	How to get there?	Timings
Ahmedabad Management Association (AMA) Dr. Vikram Sarabhai Marg, University Area, Ahmedabad, Gujarat 380 015	Google Maps	9:30AM – 5:30PM

Who would be presenting?

The biggest attraction of the event is session HTML5 – Future of the Web by Harish Vaidyanathan. Harish leads the Evangelism efforts for Microsoft India, with specific focus on Web technologies. With IE 9, “Fast is now Beautiful” (official IE9 page|My TechEd post), and as Microsoft takes us to “A more beautiful web”, I urge you all to attend the session to take understand the future of web technologies and Microsoft’s take on the subject.

Pinal Dave (blog) will be presenting a session on SQL Server Performance Tuning; and Jacob Sebastian (blog) will be presenting a session on T-SQL Worst Practices. For those who don’t know Pinal and Jacob (there would not be a single IT soul in India who does not know them!), Pinal works as a Technology Evangelist (Database and BI) with Microsoft India where as Jacob is a SQL Server MVP, Author, Speaker and Trainer.

In addition, we would have sessions from Mahesh Dhola, an Architect at a product development MNC and Tejas Shah, a blogger at http://sqlyoga.com/

Free Goodies!

As always, there will be a QUIZ during the event and we will have various gifts – Watches, USB Drives, T-Shirts and many more interesting gifts.

Some Useful Tips

• Everyone’s invited – If you work with Microsoft technologies, you should always attend the CTD events!
• Always register before the event, and carry a copy of the registration confirmation with you
• The venue has limited seating capacity. Those who attended the CTD last year know this for a fact. Arrive at the venue early to get a better seat, and reserve your spot

Agenda

You can get the agenda at: http://communitytechdays.com/agenda.aspx#ahmedabad

Registration

So, what are you waiting for? Register immediately at: http://www.communitytechdays.com/Registration1.aspx

Let’s plan to meet on June 11, 2011. Until that time,

Be courteous. Drive responsibly.

As you probably know by now, I use the Template Explorer a lot and encourage everyone to do the same. It is a great tool within the SSMS, and most developers and administrators adopt it with a smiling face. You can read all about it via the following posts:

1. Template Explorer – Underappreciated features of Microsoft SQL Server
2. Template Explorer – Deploying custom templates in SSMS – VTD Webcast feedback question
3. Template Explorer – Changes & Template updates in SQL 11 (Code Named- “Denali”) CTP01

Last week, I wrote about how to deploy customized templates to developer workstations (Template Explorer – Deploying custom templates in SSMS – VTD Webcast feedback question). This was in response to  a question that came up during a Microsoft Webcast that I presented on the “Underappreciated Features of Microsoft SQL Server”.

When replying to the query during the Webcast, I felt that it was important to have some sort of an “import” facility – a manual process simply would not do. I have therefore filed a Microsoft Connect case for the same.

If you are an administrator, and want the ability to deploy/import customized templates easily, vote for the associated Microsoft Connect item here: https://connect.microsoft.com/SQLServer/feedback/details/668497/ability-to-import-templates-deploy-custom-templates-into-the-template-explorer

Please cast your vote! You can make a difference!

Until we meet next time,

Be courteous. Drive responsibly.