Tuesday, December 31, 2013
Monday, December 30, 2013
Using BizTalk Terminator
BTSTerminator is a handy tool for managing your BizTalk database environment. Unfortunately it's design is a little confusing.
1. Enter the database server name
2. Enter the name of the management database
BTSTerminator requires that you verify that all the BT databases are backed up, that all host instances on the application server have been stopped, and that all BT SQL Agent jobs are stopped before you can proceed. The thing to note here is that BTSTerminator does not verify these things for you so you need to verify that these are done and then check the check boxes before proceeding. Once everything is checked you can click the Connect button.
The next screen is where things get confusing.
The bottom row of buttons are not actions, they are categories. So if you want to delete orphaned instances you first click the Delete button which changes the options in the top drop down menu.
Basically, all BTSTerminator is, is a collection of various SQL Scripts that are accessed and executed through the (clunky) UI.
1. Select the category of action you want to do (View, Delete, Manage, etc)
2. Select the appropriate task from the drop down menu
3. Click the Parameters tab and verify the parameters (usually just a confirmation of the server and database names)
4. Click Execute
BTSTerminator will then execute the chosen script on the server/database and display the results on the Results tab.
1. Enter the database server name
2. Enter the name of the management database
BTSTerminator requires that you verify that all the BT databases are backed up, that all host instances on the application server have been stopped, and that all BT SQL Agent jobs are stopped before you can proceed. The thing to note here is that BTSTerminator does not verify these things for you so you need to verify that these are done and then check the check boxes before proceeding. Once everything is checked you can click the Connect button.
The next screen is where things get confusing.
The bottom row of buttons are not actions, they are categories. So if you want to delete orphaned instances you first click the Delete button which changes the options in the top drop down menu.
Basically, all BTSTerminator is, is a collection of various SQL Scripts that are accessed and executed through the (clunky) UI.
1. Select the category of action you want to do (View, Delete, Manage, etc)
2. Select the appropriate task from the drop down menu
3. Click the Parameters tab and verify the parameters (usually just a confirmation of the server and database names)
4. Click Execute
BTSTerminator will then execute the chosen script on the server/database and display the results on the Results tab.
Monitor BizTalk Server (BizTalkMgmtDb) job failing
In BizTalk Server 2010 there's a job called Monitor BizTalk Server (BizTalkMgmtDb). This job checks the management and tracking databases and looks for any inconsistencies. If there are none then the job completes successfully. If there are inconsistencies, then the job fails.
The job failing is simply telling you that you must take care of the inconsistencies.
Error message:
Executed as user: [ my user]. 2 Orphaned DTA Service Instances in USWBIZDBSTG01.BizTalkDTADb, 1 Messages with refcount less than 0 in USWBIZDBSTG01.BizTalkMsgBoxDb [SQSTATE 42000] (Error 50000). The step failed.
The numbers may change based on how many inconsistencies the job finds. So don't be surprised if you have more than what I've specified here.
To fix the issue simply run BizTalk Terminator. A search on Microsoft.com will point you to the free download.
The job failing is simply telling you that you must take care of the inconsistencies.
Error message:
Executed as user: [ my user]. 2 Orphaned DTA Service Instances in USWBIZDBSTG01.BizTalkDTADb, 1 Messages with refcount less than 0 in USWBIZDBSTG01.BizTalkMsgBoxDb [SQSTATE 42000] (Error 50000). The step failed.
The numbers may change based on how many inconsistencies the job finds. So don't be surprised if you have more than what I've specified here.
To fix the issue simply run BizTalk Terminator. A search on Microsoft.com will point you to the free download.
- To fix "messages with refcount less than 0" error run Repair Refcounts for All Messages in BTS Terminator.
- To fix "orphaned DTA service instances" error run DELETE Orphaned DTA Service Instance Expections in BTS Terminator.
Friday, December 27, 2013
Linked Server to Oracle Database
Installing Oracle drivers on a SQL Server box presents it's own set of challenges, but sometimes it must be done. I recently had to do just that. Unfortunately mixing Oracle with SQL Server is like mixing oil and water; I think it goes without saying that I had some issues.
SQL Server: 2012
Oracle: 11g
The installation of Oracle ODAC x64 and x86 went smoothly. When I tried to create the linked server in Management Studio I kept getting errors. The error was very obtuse and just said that SSMS couldn't create the linked server.
Testing the Oracle drivers via .UDL
I created a .udl to test the connection which worked fine. The issue didn't appear to be the drivers.
Allow Inprocess
After digging around online I found several blog posts about allowing inprocess for the provider. It's a simple check box that needed to be checked.
SSMS >> Server Objects >> Linked Servers >> Providers
Right-click OraOLEDB.Oracle and select Properties. Enable Allow inprocess.
The problem persisted.
I un-installed the drivers and re-installed them and nothing seemed to work. I double-checked the .ora files and they seemed to be fine.
(by the way, the .ora files you use must be installed into the driver installation directory under the ...\Network\Admin folder. For example mine is the following directory: C:\OracleODAC64\product\11.2.0\client_1\Network\Admin)
Still not satisfied I dug deeper on the Internet and discovered a forum where some folks had the exact same issue. One poster, deep within the thread, said that the allow inprocess check box was checked but it wasn't until he unchecked the check box did things work for him.
I tried it.
And it worked....!
I don't know why, but I was finally able to create my linked server without issue.
SQL Server: 2012
Oracle: 11g
The installation of Oracle ODAC x64 and x86 went smoothly. When I tried to create the linked server in Management Studio I kept getting errors. The error was very obtuse and just said that SSMS couldn't create the linked server.
Testing the Oracle drivers via .UDL
I created a .udl to test the connection which worked fine. The issue didn't appear to be the drivers.
Allow Inprocess
After digging around online I found several blog posts about allowing inprocess for the provider. It's a simple check box that needed to be checked.
SSMS >> Server Objects >> Linked Servers >> Providers
Right-click OraOLEDB.Oracle and select Properties. Enable Allow inprocess.
The problem persisted.
I un-installed the drivers and re-installed them and nothing seemed to work. I double-checked the .ora files and they seemed to be fine.
(by the way, the .ora files you use must be installed into the driver installation directory under the ...\Network\Admin folder. For example mine is the following directory: C:\OracleODAC64\product\11.2.0\client_1\Network\Admin)
Still not satisfied I dug deeper on the Internet and discovered a forum where some folks had the exact same issue. One poster, deep within the thread, said that the allow inprocess check box was checked but it wasn't until he unchecked the check box did things work for him.
I tried it.
And it worked....!
I don't know why, but I was finally able to create my linked server without issue.
Subscribe to:
Posts (Atom)