Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Errors Executing stored procedure sp_OACreate

Posted on 2016-09-12
6
Medium Priority
?
53 Views
Last Modified: 2016-10-30
I have moved the database from Server 2005 to Server 2012. While running Stored Procedures I get consistent error:
Msg 50000, Level 16, State 1, Procedure sp4_rec_declare_email, Line 77
OLE Automation Error Information
Creating object(lkDeclareDocSrvr.lkDeclare) failure
 Source: ODSOLE Extended Procedure
 Description:  Invalid class string
I have tried to activate OLE Automation Procedures
sp_configure 'show advanced options', 1  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1  
GO  
RECONFIGURE;  
GO  
sp_configure 'show advanced options', 1  
GO  
RECONFIGURE;

I enclose the code below

EXEC @vHr = master..sp_OACreate 'lkDeclareDocSrvr.lkDeclare', @vObject OUT, 4
IF @vHr <> 0
BEGIN
      /* instance failed; get error info */
      SET @vOutput = 'OLE Automation Error Information' + Char(13) + 'Creating object(lkDeclareDocSrvr.lkDeclare) failure'

      EXEC @vHr = master..sp_OAGetErrorInfo @vObject, @vSource OUT, @vDescription OUT
      IF @vHr = 0
          SET @vOutput = @vOutput + Char(13) + ' Source: ' + ISNULL(@vSource, '') + Char(13) + ' Description: ' + ISNULL(@vDescription, '')
      ELSE
          SET @vOutput = @vOutput + ' sp_OAGetErrorInfo failed.'
      
      RAISERROR (@vOutput, 16, 1)
      RETURN @LK_ERR_FOR_INNER_USE
END
0
Comment
Question by:josephdts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 41795092
Did you ran the DUA before the DB upgrade from SQL 2005 to SQL 2012?
DUA = Database Upgrade Advisor
0
 

Author Comment

by:josephdts
ID: 41795183
I have backed up and restored the database. It is supposed to remain in 2005 mode
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41795516
It is supposed to remain in 2005 mode
So you didn't change the compatibility level to SQL Server 2012? This might explain the error.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 41796082
"I have backed up and restored the database. It is supposed to remain in 2005 mode"

If you backed it up ON the SQL 2005 Server and restored it to the SQL 2012 Server did you noticed the "upgrade" that takes place after restore percent = 100? You cannot prevent that and "It is supposed to remain in 2005 mode" means that ONLY the compatibility level is still 2005 not that the database wasn't brought up to SQL 2012 level. There's no need for me to prove that but if you want to confirm it for yourself try backup that same DB on SQL 2012 and restore it on SQL 2005....will never work!

Anyway, did you ran the DUA = Database Upgrade Advisor ON the SQL 2005 Server? If and only if that comes clean we should start looking at different things on the new SQL 2012 home in my opinion. If not then you have to fix all issues revealed by DUA before using it on SQL 2012.

Good luck!
0
 

Accepted Solution

by:
josephdts earned 0 total points
ID: 41796351
I got what is going on. I need to register the DLL referred to by the query: lkDeclareDocSrvr.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question