Recently, one of the project teams at the office had a customer-specified requirement to use a SQL Server running a server collation other than the English default (SQL_Latin1_General_CP1_CI_AS).

Please NOTE: Collation is a vast topic. The scope of this post is only present the means to choose the server collation at the time of installation or afterwards.

We have two major variants of the English language supported by Windows:

• “English (United Kingdom)”, Windows LCID = 2057 (Hex: 0x809)
• “English (United States)” , Windows LCID = 1033 (Hex: 0x409)

Each Windows LCID maps to a SQL Server side’s SQL LCID. Per the article “Collation Settings in Setup”, both 2057 and 1033 map to a single collation – 1033 (0x409), which is SQL_Latin1_General_CP1_CI_AS.

Unfortunately, the IT team who setup the servers for them had used the default values during the installation. When the issue was realized, they tried to change the server collation by changing the collation of the master database (because the collation of the SQL Server instance is that collation of the master database), which returned the following error:

Cannot alter the database ‘master’ because it is a system database.

Workaround (Reactive)

When their attempts failed, they called me and my instant reason was that because this is a customer-specified requirement, they would have to do the following:

1. Backup and drop all user databases
2. Rebuild the master database by specifying the new collation name in the SQLCOLLATION property of the SQL Server setup (command-line) as shown in the Books On Line here
3. Re-create all user databases

Ideal solution (Proactive)

The ideal solution here would be to setup the SQL Server such that it uses the required collation during installation itself. The Server Configuration page (step #12) in the SQL Server setup allows the administrator to specify the collation that SQL Server would use:

By default, the SQL Server setup chooses a collation that matches to the operating system settings. However, the administrator can use the “Customize” button to choose a collation of their choice.

References/Further Reading:

• Using SQL Server Collations: http://msdn.microsoft.com/en-us/library/ms144260.aspx
• Collation Settings in Setup: http://msdn.microsoft.com/en-us/library/ms143508.aspx
• Setting and changing the Server Collation: http://msdn.microsoft.com/en-in/library/ms179254(v=sql.110).aspx
• Collation & Unicode support: http://msdn.microsoft.com/en-us/library/ms143503.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Recently, one of my colleagues asked me an age-old question – “How to ensure that I am able to access my SQL Server instance using my Windows login even if I forget the ‘sa’ login?” They had a long un-used instance of SQL Server for which they had forgotten the “sa” password. They had attempted to use Windows authentication, but had failed to login.

This incident was fresh in my memory when I re-installed the SQL Server instance on my test environment at home. Hence, it was the perfect time to write this post.

Proactive step

The ideal risk management strategy is to mitigate all them before they arise. Keeping this in mind, a simple step from the IT team (or whoever is in-charge of installing the SQL Server instance) can help ensure that at least one user always has access to a SQL Server instance at any given point in time.

As we all know, SQL Server supports two login mechanisms:

• Windows Authentication (default)
• Mixed Mode – SQL Server Authentication & Windows Authentication

In the interest of security, SQL Server has been designed such that Windows Authentication can never be turned OFF.

In SQL Server 2005, all members of the BUILTINAdministrators group were automatically members of the sysadmin fixed server role, and were therefore SQL Server administrators. Starting SQL Server 2008 however, this was no longer the case. In the “Database Configuration” screen of the SQL Server installer, one now needs to explicitly specify windows logins (or groups) that need to be added as sysadmins on the new SQL Server instance being installed.

Database Configuration Screen

When running the SQL Server 2012 installer, this information is requested in the “Database Configuration” screen (step #13):

As shown in the screen-shot above, you can do one of the following:

1. Add Current User – adds the currently logged-in user (under whose context the installer is running)
2. Add – Choose the domain or local user to add
3. Remove – Choose to remove any users added accidentally

The screen-shot below shows that when installing my SQL Server 2012 instance, I choose Mixed mode authentication and added myself as an admin:

Typically what happens is that when IT teams install SQL Server instances for development and QA teams, they set Mixed Mode authentication to ON, but forget to add one windows or local user as a SQL sysadmin. If the team now forgets the “sa” password, they are left with a SQL Server instance to which the applications can connect using other roles and users but cannot administer it anymore.

Moral of the story: The SQL Server installation checklists being followed by your team must have a check to ensure that at least one login (either that of the configuration manager on the team, or a local login) be registered as an administrator on the SQL Server.

Word of caution: Do note that whoever has been added to the sysadmin fixed server role has unrestricted access to the Database Engine.

Reactive step

While managing a risk in a proactive way is the ideal way, one might always find exceptions. As in the case of my colleague, it is possible to be left with an instance that one cannot administer because of not having the correct login credentials.

In such cases, I refer to an excellent blog post by my friend, Chintak Chhapia (blog) on How to access a newly installed SQL Server 2008 R2 instance if you do not know the login and password. Thanks to his recommendations, the colleague of mine had access to their server in almost no time!

Until we meet next time,

Be courteous. Drive responsibly.

At the start of the year, I wrote about the SQL Server upgrade advisor. I was setting up my home test environment recently and this prompted me to write a couple of posts on SQL Server installation and cover some of the nice features which I believe are quite important.

As you begin the SQL Server 2012 installation, SQL Server goes through a series of checks to ensure that environment configurations that could result in a failed installation are identified in advance and the user is given a chance to rectify them. In this set of checks, I noticed that the media also checks for long path names to the files:

What this check does is to simply check if all files on the installer can be accessed by a path that is less than or equal to the 256-character normal limit on path names. Most product installers do not perform this check and the installation ultimately fails when one has an unusually long file path (such that the number of characters is greater than 256 characters).

In my humble opinion, this is a great example of a check that all product installers must adopt to avoid installation failures for a reason as simple as a long path.

Until we meet next time,

Be courteous. Drive responsibly.

Have you ever encountered a need to find and/or validate the SQL Server features and their respective patch levels for any given server running a single or multiple SQL Server instance(s)? If so, the Installed SQL Server Feature Discovery Report generation utility is just the tool you are looking for.

Today’s post show you how to use this small utility for your documentation/inventory purposes.

Launch the SQL Server 2012 installer by clicking on the SQL Server Installation Center [You can also run the installer from the setup disc]

Under “Tools”, you will find the “option to launch the “Installed SQL Server Features Discovery Report”

Clicking on the tool starts the processing and you will be presented with the progress dialog, standard to all progress operations of the SQL Server 2012 installer.

Once execution is complete, the report will be opened automatically in Internet Explorer

If you need to send this report over to your IT team or preserve it for documentation purposes, you can find a copy at:

Path for Discovery Report file: <<SQL Server installation path>>Setup BootstrapLog<<Date time>>SqlDiscoveryReport.htm

Example: C:Program FilesMicrosoft SQL Server110Setup BootstrapLog20130407_103300SqlDiscoveryReport.htm

I trust this quick post will be helpful to you in the future.

Until we meet next time,

Be courteous. Drive responsibly.

Support for Microsoft SQL Server 2000 ends this year and yet, one can find systems (production or otherwise) that continue to run on and support SQL Server 2000. Recently, we were tasked with porting a piece of functionality backwards, i.e. from versions that support SQL Server 2012/2008 to SQL 2000/2005. Unfortunately, this piece of functionality was designed to work with newer versions of Microsoft SQL Server and involved the use of table valued parameters which is not possible in SQL Server 2000.

After a couple of hours of brain-storming, we realized that we could share temporary tables with the underlying stored procedures as long as the connection remained the same. Because the connection that created a temporary table is still active, the temporary table will continue to be accessible if the stored procedure is executed on the same connection. Here’s a small demo to help explain the solution.

Demo

For this demonstration, we will create a simple mathematical temporary table which will be updated from within a stored procedure. Assuming that the temporary table is named #tTableValues and has columns iA, iB and iC ( = iA * iB), let us first create a stored procedure which is designed to update the value of iA:

USE tempdb ;
GO
IF OBJECT_ID('UpdateTableValues') IS NOT NULL
DROP PROCEDURE dbo.UpdateTableValues
GO

CREATE PROCEDURE dbo.UpdateTableValues
@replacementValue INT
AS
BEGIN
SET NOCOUNT ON ;

IF OBJECT_ID('#tTableValues') IS NOT NULL
BEGIN
UPDATE tv
SET tv.iA = @replacementValue
FROM #tTableValues AS tv
END
ELSE
BEGIN
PRINT 'Temporary table: #tTableValues does not exist or access denied.'
END
END
GO

As you can see from the T-SQL script above, the stored procedure assumes that the temporary table is already available to it and directly makes the update. All the calling application now has to do is:

1. Open a SQL Server connection, say C1
2. Create the temporary table
3. Insert data into the temporary table
4. Call the stored procedure for the desired computation
5. Fetch the value/result once the stored procedure execution is complete
6. Drop the temporary table
7. Close connection C1

The T-SQL script below demonstrates this workflow:

--1. Connection has been created
USE tempdb ;
GO
--2. Check for and create the required temporary table
IF OBJECT_ID('#tTableValues') IS NOT NULL
DROP TABLE #tTableValues
GO

CREATE TABLE #tTableValues
(
Id INT IDENTITY(1, 1)
PRIMARY KEY CLUSTERED,
iA INT,
iB INT,
iC AS iA * iB
) ;
GO

--3. Insert some test data
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 1 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 2 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 3 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 4 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 5 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 6 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 7 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 8 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 9 ) ;
INSERT INTO #tTableValues ( iA, iB )
VALUES ( 1, 10 ) ;
GO

--Debug point 01: Select from the temporary table
SELECT 'Debug Point 01' AS StepName,
Id,
iA,
iB,
iC
FROM #tTableValues ;
GO

--4. Execute the stored procedure
--NOTE that the connection has to be the same!
EXEC dbo.UpdateTableValues @replacementValue = 2 ;
GO

--5. Fetch the result of the stored procedure execution
SELECT 'Step 05 - Results' AS StepName,
Id,
iA,
iB,
iC
FROM #tTableValues ;

--6. Cleanup - the connection can now be closed
IF OBJECT_ID('#tTableValues') IS NOT NULL
DROP TABLE #tTableValues
GO

The screen-shot shown below shows the output of the execution and it can be seen that the value for iA in the temporary table created by the calling application was indeed updated by the stored procedure and the results were available after the stored procedure execution completed.

Conclusion

As demonstrated, temporary tables may be used for data exchange between an application and stored procedures or even between any two programmability objects (procedures, functions, etc). This functionality continues to remain valid even in the days of SQL Server 2012. The question, however is whether it would be preferable to use newer features like table valued parameters or not. Do let me know your thoughts on the same.

As for those who need to continue support of Microsoft SQL Server 2000 for their products, I trust this post was helpful.

Further Reading

• Temporary Table Naming – Maximum Allowed Length and associated naming logic
• Microsoft SQL Server 2000 Support Life Cycle

Until we meet next time,

Be courteous. Drive responsibly.