SQL Server

How to Activate Microsoft SQL Server 2008 R2 180-day Evaluation Version After Expiration with a Product Key (Step-by-step)

Aalam Rangi2 Comments

The trial period of the Microsoft SQL Server 2008 R2 180-day Evaluation version had come to an end on my laptop and the SQL Server had stopped working. I had bought the Developer Edition media with product key and wanted to use that to have the SQL Server running again. The following are the step-by-step screenshots of the entire activation experience.

Click on images to enlarge.

Following is the error message that comes up while trying to start the SQL Server Management Studio on an expired SQL Server 2008 R2 showing that the trial period has expired.

1.ErrorMessage

I have Windows 7 Home Premium (64-bit) running on Intel Core 2 Duo 2.2 GHz, 4 GB RAM. To begin the activation, go to Start > All Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Installation Center (64-bit). Go to Maintenance and click on Edition Upgrade.

2.SQLServerInstallationCenter

An in-progress message appears briefly.

3.InProgress

A setup rule verification window appears and shows the progress while the rules are verified. Each status message can be checked by clicking on the link. A summary of all the rules is also presented in HTML format. The path for the HTML file is mentioned at the end of the blog post. Wait for the setup to verify the rules to and click OK.

4.SQLServer2008R2SetupWindow1

Another set of rules are verified. Wait for this to complete and click Next.

5.SQLServer2008R2SetupWindow2

The next screen presents an option to enter the product key. Enter the key and click Next.

6.UpgradeTheEditionForSQLServer2008R2ProductKeyWindow

Read and accept the license terms by checking the checkbox. Click Next.

7.UpgradeTheEditionForSQLServer2008R2EULAWindow

Select the SQL Server instance from the dropdown and click Next.

8.UpgradeTheEditionForSQLServer2008R2SelectInstanceWindow

One more set of rules are verified. After completion click on Next. The next window shows a summary for review and a confirmation that the setup is ready to upgrade the edition. Click on Upgrade. The Upgrade button will be disabled but there will not be any other activity indication like a hour-glass cursor or a progress bar. This could be a little puzzling but wait for it to complete. It took about 4 minutes to complete on my laptop. Your mileage could vary.

10.UpgradeTheEditionForSQLServer2008R2_ReadyToUpgradeWindow

The final screen shows a completion message and the path of the setup log file. Click on the log file path to open it. Click OK on the setup window to close it.

11.UpgradeTheEditionForSQLServer2008R2_CompleteWindow

HTML report of the setup rules:
C:Program FilesMicrosoft SQL Server100Setup BootstrapLogYYYYMMDD_HHMISSSystemConfigurationCheck_Report.htm

Text log files:
C:Program FilesMicrosoft SQL Server100Setup BootstrapLog
and its child folders.

How to monitor IDENTITY column values in SQL Server

Aalam RangiLeave a comment

One (lesser known) task of a DBA should be to keep an eye on the values of the IDENTITY columns so that they do not hit the ceiling for their data type and catch you by surprise. Not a pleasant one, of course. This is all the more important if you have tables that grow quickly.

Vyas Kondreddi has a nice script to look for current IDENTITY values in all tables of a database and compare that value against its datatype. It could be useful to automate it as an alert or run it manually once in a while. He has versions of the script for SQL Server 7.0, 2000, 2005 and later but all of them run against one database at a time which might make it a little inconvenient to run against all your 200 databases.

I’ve taken the script compatible with SQL Server 2005 and later, and enhanced it a bit to run it against all databases in one go using the undocumented stored procedure SP_MSFOREACHDB. There goes your excuse to procrastinate this!

Change the value against the TOP keyword to suit your preference. I’ve used TOP 5.

Download my script from the TechNet Gallery link –
http://gallery.technet.microsoft.com/Monitor-IDENTITY-column-fd9c6552

The output would look like the screenshot below.

MonitorIdentityColumns

How to Fix Error Code: 80004005 in DTS Caused by 64-bit DB2 ODBC Driver

Aalam RangiLeave a comment

Issue:

Data Transformation Services packages (DTS) that use DB2 as an ODBC source, fail when the execution environment is upgraded to 64-Bit Windows Server 2008, Windows 7 etc.

Error Message:

The error logs of the DTS would show the following message –

Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

Cause:

DTS are 32-bit applications and need the Data Source Names (DSN) based on 32-bit ODBC drivers. A 64-bit Operating System points to the 64-bit drivers by default and uses the ODBC Data Source Administrator at –

C:WINDOWSsystem32odbcad32.exe

The standard DSN creation scripts used by system administrators might use the 64-bit version because it is at the default location.

The 32-bit ODBC Data Source Administrator is in –

C:WINDOWSSysWOW64odbcad32.exe

The required DSN should be created again using this 32-bit version and it will update the Windows registry or the environment path variables appropriately.

Fix:

Start the 32-bit ODBC Data Source Administrator Wizard from the SYSWOW64 directory.

C:WINDOWSSysWOW64odbcad32.exe

Go to System DSN tab and click on Add.

clip_image001

Select the available IBM ODBC driver.

clip_image002

Give a DSN name.

clip_image003

Confirm the action.

clip_image004

The last message box verifies if you want to overwrite the existing DSN alias in DB2CLI.INI file. This file is at the below location on a 64-bit machine –

C:ProgramDataIBMDB2DB2_V95db2cli.ini

Clicking on No will abort the wizard. Click on Yes to continue. The timestamp for the INI file would be updated. If you are just recreating an existing alias as in the example above, the file content would appear to be the same, though the ODBC Data Source Administrator Wizard will create or modify the appropriate registry and environment variable entries.

Summary:

Microsoft maintains a backward compatibility feature known as WOW64 (Windows-32-On-Windows-64) that lets older 32-bit applications to run smoothly. This is implemented by maintaining a separate copy of the necessary 32-bit run time resources (configuration utilities, drivers, registry settings, other DLLs etc.) in different folders/locations.

This example refers to an ODBC DSN for a DB2 server but the concept could be relevant in other such scenarios where there is a conflict between 32 and 64-bit drivers.

Reference:

How to point to 32-bit IBM DB2 ODBC driver on 64-bit Microsoft system.
http://www-01.ibm.com/support/docview.wss?uid=swg21384435

Use a One-Time Schedule for Adhoc Job Executions

Aalam RangiLeave a comment

If there is ever a need to run a SQL Server job on an adhoc basis outside of its normal schedule, don’t change its original schedule. Rather, create a new one-time only schedule for that job and leave it like that even after the the execution.

If you modify the original schedule then you’d have to remember to revert it back. This can be easily missed out and then the job would be running at the modified schedule after that! Oh, so you say you have a water-tight reminder mechanism and you’ll never forget to revert it?! Please read the next line.

If there was a requirement to run the job out-of-schedule once, then it could happen again too. The one-time schedule would be handy at that time and can be reused.

How to Store (and Retrieve) Non-English Characters (e.g. Hindi, Czech, Arabic etc.) in SQL Server

Aalam Rangi20 Comments
Store multiple language strings in SQL Server
How to store text in multiple languages in SQL Server.

If you have to store and retrieve characters of any other language besides English in SQL Server, you must do the following –

  1. Use a Unicode compatible data type for the table column. NVACHAR, NCHAR, NTEXT are the datatypes in SQL Server that can be used for storing non-English characters.
  2. Precede the Unicode data values with an N (capital letter) to let the SQL Server know that the following data is from Unicode character set. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.
  3. The N should be used even in the WHERE clause.

REFERENCE: Microsoft Support KB 239530
You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server
http://support.microsoft.com/kb/239530

If the correct data-type is not used or the data is not preceded with an N, SQL Server will save the data to the table as ‘?’ or another garbled character.

The following scripts demonstrate saving and retrieving multi-lingual data to and from SQL Server. I have used Google Translate to get the characters of other languages. I left out far-east languages like Japanese and Chinese from the following example on purpose because those languages have a few other considerations that I’ll save for another blog post.

DROP TABLE dbo.unicodeData;
GO

CREATE TABLE dbo.unicodeData
( languageUsed VARCHAR(50)
, unicodeData NVARCHAR(200)
, nonUnicodeData VARCHAR(200) -- same data in a normal VARCHAR column for comparison
, comments VARCHAR(100)
);
GO

INSERT INTO dbo.unicodeData
( languageUsed
, unicodeData
, nonUnicodeData
, comments)
VALUES
('English'
, N'This is an example'
, N'This is an example'
, NULL)

,('Hindi'
, N'यह एक उदाहरण है.'
, N'यह एक उदाहरण है.'
, 'Using the preceding N in both strings but VARCHAR is still a ?')

,('Hindi'
, 'यह एक उदाहरण है.'
, 'यह एक उदाहरण है.'
, 'Not using the preceding N in both strings so both are a ?')

,('Kannada'
, N'ಈ ಒಂದು ಉದಾಹರಣೆಯಾಗಿದೆ.'
, N'ಈ ಒಂದು ಉದಾಹರಣೆಯಾಗಿದೆ.'
, NULL)

,('Arabic'
, N'هذا مثال على ذلك.'
, N'هذا مثال على ذلك.'
, NULL)

,('Czech'
, N'To je příklad.'
, N'To je příklad.'
, NULL);
GO

SELECT *
FROM dbo.unicodeData;
GO

-- Example of using N' in the WHERE clause
SELECT *
FROM dbo.unicodeData
WHERE unicodeData like N'%एक%';
Unicode Results
Unicode Results

Further Reading:

How To Have a Colorful Status Bar in SSMS. And Why is that Useful!

Aalam Rangi1 Comment

colorful-status-bar-aalamrangi.wordpress.com

Would it be helpful if the status bar of SSMS query windows for PROD environment were red, QA were green and DEV were blue? Yes. The query windows will be distinguished and reduce the chances of oops moments. If you frequently open simultaneous query windows for multiple environments/servers in SSMS, then you must learn about this SSMS feature. Don’t miss reading a word of caution at the end!

The following is the default status bar for a query window in SSMS.

The status bar can be made Green (or any other color) like the example below.

All you need to do is to expand the Options on the connection dialog …

… and pick a color of your liking as shown below.

These color choices will persist even when you close and reopen the SSMS.

Although, setting up a different color for all individual servers is possible but having too many colors could be confusing! You might find it useful to set one specific color per environment. For example, Red for PROD servers, Green for QA servers and Yellow for TEST servers.

How to Reset it Back to Default Color:

Open the Connect to Database Engine dialog box and remove the check for the Use Custom Color checkbox. Click on Connect. All new query windows for the server you are trying to connect will revert back to the default color. Any open query windows for that server or others will not be affected. I’m not aware of any option to reset this for all connections at once.

A Word of Caution:

A new color selection is applied to the query window immediately but the effect of removing a color is visible only with new query windows, not the ones currently open. Whenever you change the connection of any open query window, always verify that the expected color is showing on the status bar.