Browsing: SQL DBA tips

How to Download OLE DB Provider for DB2

This article is about how to download OLE DB Provider for DB2. This is a Microsoft Data Provider which offers tools and technologies using which SQL Server can access DB2 Databases.

The requirement was just to install DB2 driver and so change time was kept minimal. But I had to spend more than an hour just to find the required file in Microsoft Site. So this five minutes read can save your lot of time.

You may give it a try to search it yourself before reading this article and will get you know why simple things are not always easy.

If you are in the middle of change like me and your search engine lands you here then you can directly go to the end of this article where I have provided direct links to download. Later you can come back and continue to read.

The following instructions are listed in Microsoft Document. This example shows the download of OLE DB Provider for DB2 Version 5.

To Install the Product:
  1. Go to the Microsoft Download Center.
  2. Download either the x86 (32-bit) or the x64 (64-bit) version of DB2OLEDB5_x64.msi installation program.
  3. Double-click the .msi file to start the Installation Wizard
  4. Click Next to get started.
  5. License Agreement page, review the license terms, click the I accept the terms in the license agreement option, and then click Next.
  6. Registration Information page, enter your Name and Company, and then click Next.
  7. Feature Selection page, optionally click Browse to change the Folder name in which to install the product, optionally click Disk Cost to space required to install the product, and then click Next.
  8. Ready to Install the Program page, click Install.
  9. When prompted by Windows User Account Control, click Yes.
  10. On the Installing page, view the status of the installation process.
  11. On the Completion page, click Finish.

Install looks like a cakewalk until you pass the very 1st step.

Once you go to Microsoft Download Center:

Download OLE DB Provider for DB2

Wow! Now what? Which option to choose or what should you search for?

Right here I wasted a lot of time. What you should search for is Feature Packs of SQL Server Version for which you want to install the OLE DB provider for DB2.

Download OLEDB Provider For DB2 Microsoft Download Center

Choose the link showing Feature Pack as shown below:

OLEDB Provider for DB2 Microsoft Download Center

You will get the download option as shown:

OLEDB Provider for DB2 Microsoft Download Center

Click on Download and you get the options of DB2OLEDB5_x64.msi file to choose from:

Download OLE DB Provider for DB2

Select the checkbox for DB2 and click on Next and the download process should start.

For SQL Server 2012 and prior versions you need to expand Install Instructions:

Download OLE DB Provider for DB2

Then search for DB2 keyword and you will get the option to download:

Download OLE DB2 Provider for DB2

Hope this is useful and please let me know by your valuable comments.

Direct links  to Download OLE DB provider for DB2:

SQL Server 2016 Feature Pack:

https://www.microsoft.com/en-us/download/details.aspx?id=52676

SQL Server 2014 Feature Pack:

https://www.microsoft.com/en-us/download/details.aspx?id=42295

SQL Server 2012 Feature Pack:

https://www.microsoft.com/en-us/download/confirmation.aspx?id=29065

SQL Server 2008 R2 Feature Pack

https://www.microsoft.com/en-us/download/confirmation.aspx?id=16978

You may also like below articles:
SQL Database Restore Using NetBackup
SSRS Encryption Key Backup Information
Myth around Remote Query Timeout Option
User Defined Profiler Template Location
Fact about Dedicated Administrator Connection (DAC)

{ Add a Comment }

User Defined Profiler Template Location

First of all let me explain you why you need to know the user defined profiler template location.

In situations you may need to define a template and share it with other DBAs to run or vice versa. You have saved the profiler and request the other person to run by providing the name of the profiler. The other person called you (when you are not in shift) since he/she is not able to find the profiler you created, but that is hard for you to believe as you are quite sure you did it right.

Once you go through this article you will get to know:

  1. How to create a user defined template?
  2. Why others are not able to see the template?
  3. How to resolve this?

You can watch the following video demo which shows the stuffs I am going to explain here:

1. How to create a user defined template?

Launch SQL Server Management Studio (SSMS) and open SQL Server profiler:

Open a new Template:

Provide a template name and choose a base template from the drop down menu. In the below example I have chosen Standard (default) template. Then click on Events Selection tab.

In the above example I have not selected the check box for “Use as a default template for selected server type”. You can choose this option if you want your template to be default template.

Choose the Events as per your requirement. Click on “Show all events” and “Show all columns” to view all the events and columns to choose from. Then click on “Column Filters” to put filter for the trace.

In the below example I have put a filter for database id since I wanted to capture the transactions only for one database. The filter may differ for your requirement.

Once you are done with the filter selection click on OK and then Save the template.
Now if you go back and check the drop down list of templates you will find your custom template.
Go to File > New Trace and then connect to any SQL Instance

The following screen shot shows the newly created template “Demo Template (user)” in the drop down list.

2. Why others can’t see this template under the same drop down?

Here is the physical location of the user defined template file:

C:\Users\UserName\AppData\Roaming\Microsoft\SQL Profiler\11.0\Templates\Microsoft SQL Server\110

The main point to note here is the template gets created under the user profile folder of the login id, used to login to the server and that is why it is not visible to others.
AppData is a hidden folder and 11.0 and 110 will change depending upon the SQL Server version.

3. How to resolve this?

Let’s see the location of SQL Server Provided profiler templates.

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Profiler\Templates\Microsoft SQL Server\110

You guessed it right! You just need to copy the user defined template from
C:\Users\UserName\AppData\Roaming\Microsoft\SQL Profiler\11.0\Templates\Microsoft SQL Server\110
To
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Profiler\Templates\Microsoft SQL Server\110

Hope you enjoyed this article and feel free to share if you think it is helpful. Please let me know if you have any questions on this.

You might also want to check Myth around Remote Query Timeout Option

{ Add a Comment }

SQL Cluster Instance Failover Time and Node Name prior to Failover

You may have come across situations where SQL Server cluster instance has failed over from one node to other and you were asked to find the SQL Cluster Instance failover time and node name prior to failover.

Who can ask this question? Your approver, manager or colleague anyone can ask you for different reasons, to know since when the instance is running on wrong/passive node, what alert system you have and it worked properly or not etc.

Whatever may be the reason, you want to find it quickly. In this article I am going to explain how to find the required information from SQL error log.

You need to search for the messages containing the text NETBIOS in the error log files.

Launch SSMS (SQL Server Management Studio) and start with the first error log to view as shown below:

Click on Filter and type in “netbios” against the field “Message Contains Text”. Mark the check box for Apply filter and click on OK as shown below. The message you are searching for, can be typed in Capital/Lower case.

Start checking each error logs until you find the required information as shown in the following screen shot.

The Message is like:

“The NETBIOS name of the local node that is running the server is ‘NodeName’. This is an informational message only. No user action is required.”

The message provides the information about the current Node, hosting the SQL Server Instance and the time it came up on the Node. Hence you need to keep checking the prior error logs which will show a different Node name and time in case it failed over from other node.

In the above example it shows the particular SQL Instance was running on Node “N02” prior to the failover.

The following information you can find using the NETBIOS filter:
  1. What is the current node? (N01)
  2. Is failover happened? Yes, as it shows a different node (N02) in prior error log file.
  3. Which node it was running prior to failover?(N02)
  4. What time failover started (Between 9/9/2017 11:28:10 PM the instance was on N02 and 9/9/2017 11:47:07 PM the instance started on No1. Check the error log around the time removing the filter and you will be able to find the exact time as shown in the below screen shot.)


You can also find what time SQL Instance came online/ what is the last SQL restart time. Of course you can find it from tempdb creation time? (Date 9/9/2017 11:47:07 PM the instance started on No1).

Hope this helps and I would like to hear from you if you have any other ways to find the same information quickly.

You may also want to check the following articles:
Myth around Remote Query Timeout Option
All Logins got disabled due to trigger execution

{ Add a Comment }

Logon Failed Due to Trigger Execution: Video Demo

I got a call in the middle of the night about all the logins in SQL Server has been disabled. Logon failed  due to trigger execution. The situation is quite scary, isn’t it? Well if you know what to do, then it is not except from the fact that you have to login in odd hours.

At first try to connect to the SQL Instance to find out the error:

The error message says “Logon failed for Login ‘sa’ due to trigger execution”. The second and third line of the error is little misleading.

It indicates that there must be a server level trigger someone has created due to which all the logins got disabled. The solution would be, find out the problem trigger and disable it. But you need to find out a way to get in to SQL Instance first.

You guessed it right!  You need to use DAC (Dedicated Admin Connection) to connect in this situation. If you have not read the article where I have explained different ways to enable and connect using DAC click the following link.

Dedicated Administrator Connection (DAC)

Here I am going to show using command prompt. In the video link given at the end of the article, I have demonstrated using SSMS.

Open command prompt and use DAC to connect to the instance. In the below screen shot I am connecting to default instance using windows authentication and then run the below query:

SELECT name, create_date FROM MASTER.sys.server_triggers ORDER BY create_date DESC


As shown in the above example the trigger name is “TestLogin” and if you have observed I have used order by create_date desc, this is because after the latest trigger creation, nobody is able to login.

Now you can disable the trigger using the below query:

disable TRIGGER [TestLogin] ON ALL server


Once the trigger is disabled you will be able to connect to your SQL Instance. You can script out the trigger to check and rectify it.

If you can play sound, go ahead and watch the demo to fix the issue using SSMS:

I hope this helps. I would love to hear from you about any other similar issue or if you have any questions.

{ Add a Comment }

Remove or Delete Tempdb data file without restarting SQL Server

You may want to delete that extra Tempdb data file which someone created by mistake or to accommodate a query. Whatever may be the reason, today I am going to show you how to do it and what issues you may face.

You can run the below query to remove the data file:

ALTER DATABASE tempdb
REMOVE FILE LogicalFileName;
GO

Or you can use GUI:

SQL Server throws the following error:

The error message says that it cannot remove the file because it is not empty.

If you just add a file and there is no ongoing activity in the file then it would allow to remove the file this way.

For each file you want to remove, you need to run the following command to empty the file and then run the above query to remove the file:
USE [tempdb];
GO
DBCC SHRINKFILE (LogicalName, EMPTYFILE);
GO

USE [tempdb];
GO
DBCC SHRINKFILE (LogicalName, EMPTYFILE);
GO

But what if it throws the below error:

The error message is about a work table page which can not be removed. Work table is related to cached information, which indicates that you need to clear the cache.

Execute the below queries one by one and after clearing each, try to empty the file again:

DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ('ALL')
GO

The successful run shows as below:

Please keep in mind that whenever you are clearing cache, it can cause performance issues. I am leaving the decision up to you to handle the risk factor according to the environment.

If your environment allows you to restart SQL Service you can just restart which will empty the tempdb files and you can run the remove command:

ALTER DATABASE tempdb
REMOVE FILE LogincalName;
GO

I hope you have already debunked the Myth around Remote Query Timeout option.

You can go ahead and watch this video:

{ Add a Comment }