February 11, 2009 at 2:06 am
I have a stored procedure that connects to a linked server against sybase (with ODBC and OLE DB provider from OpenLink) and it works fine when i call it from management studio. When i use an SQL agent job to execute the same procedure it does not work at all. The funny thing is that it works fine on the other server in our failover cluster works as intended. :w00t: The servers seem to be identical.
I guess it must be some kind of permission issue, but i can't find it.
Does anyone have any idea?
/Håkan
Senior Development DBA
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
February 11, 2009 at 3:19 am
Can you post the error message?
And did you check the account of the SQL Agent service? Is it the same as the account compared to the server that executes correct?
February 17, 2009 at 6:15 am
The error message that we get is :
- Cannot create a column accessor for OLE DB provider "OpenLinkODBC"
Finally I found a solution that solved the problem. It seems like the OLE DB providers are installed in different order in the clustered environment and when i execute this statement sp_enum_oledb_providers everything works fine. I guess it some cache that is updated.
To solve this once and for all, when the servers fail over, I set this as an startup procedure like this:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_enum_oledb_providers]
AS
exec sp_enum_oledb_providers
GO
sp_procoption 'usp_enum_oledb_providers', 'startup', 1
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply