SP_oacreate
Mar 31, 2008pls send me some code samples to import a html/txt file using the sp_oacreate procedure
am getting the error number -2147221005
please let me know
ADVERTISEMENT
Sp_OACreate With Excel
Jan 3, 2002I'm having problems creating and saving an Excel object.
I can create a Word document, but not an Excel file
It fails and Excel is in the task manager but nothing else happens.
I have to kill the processes, from my Exe/Dll viewer "ProcExp"
(It's a cool app, that saves me from rebooting to free up my system)
Anyway, here is both my VBS and TSQL code.
TIA
JeffP...
/*
'vbs code......saved in Notepad
Dim myexcel
set myexcel = createobject("Excel.Application")
myExcel.Application.UserName = "JeffP"
myExcel.Application.Workbooks.Add
myExcel.Application.ActiveWorkbook.SaveAs ("Me.xls")
myExcel.Application.ActiveWorkbook.Save
myExcel.Workbooks.Close
myExcel.Application.Quit
'end vbs code
*/
--begin tsql
declare @Obj int ,@file varchar(100) ,@user varchar(100) ,@res int
,@sheet int ,@saveas varchar(255) ,@save varchar(255)
set @user = 'Jackx'
set @file = 'me.xls'
set @saveas = 'ActiveWorkbook.SaveAs("C:Documents and SettingsjeffMy Documents")'
set @save = 'ActiveWorkbook.Save'
exec @res = sp_oacreate 'excel.application' ,@obj output
print 'Obj'
print @res
print @obj
print '-------'
exec @res = sp_oamethod @obj ,'workbooks.add' ,@sheet output
print 'add'
print @res
print @sheet
print '-------'
exec @res = sp_oamethod @obj ,@saveas
print 'saveas'
print @res
print @file
print '-------'
exec @res = sp_oamethod @obj ,'ActiveWorkbook.Save'
print 'save'
print @res
print @file
print '-------'
exec @res = sp_oamethod @obj ,'Workbooks.Close'
print 'close'
print @res
print @file
print '-------'
exec @res = sp_oamethod @obj ,'quit'
print 'quit'
print @res
print @sheet
print '-------'
exec @res = sp_oadestroy @obj
print 'end'
print @res
--end tsql
Sql Server : Sys.sp_OACreate Error
May 21, 2008I tried to send the html format email alert through sql server. Its working fine in sql server 2000 (Dev Server). then i
moved to production server (sql server 2005).
when i run this email program (USP_SendHolidayAlert) in sql server 2005, i got the folloing errors,
Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is
turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole
Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface
Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OASetProperty, Line 1SQL Server blocked access to procedure 'sys.sp_OASetProperty' of component 'Ole Automation Procedures' because this component
is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole
Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface
Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OASetProperty, Line 1SQL Server blocked access to procedure 'sys.sp_OASetProperty' of component 'Ole Automation Procedures' because this component
is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole
Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface
Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OAMethod, Line 1SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this component is
turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole
Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface
Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 1SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this component is
turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole
Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface
Area Configuration" in SQL Server Books Online.
Stored Procedure----------------
CREATE Procedure USP_SendHolidayAlert /* Name: USP_SendHolidayAlert Description: Send Email holiday Alert Author: Gopalan Mani Modification Log: Create Description Date Changed By Created procedure 13-05-2008 Gopalan Mani */ @SenderName varchar(250), @SenderAddress varchar(250), @RecipientName varchar(250), @RecipientAddress varchar(250), @Subject varchar(250) , @Title varchar(350) , @HolidayDate varchar(350) as DECLARE @HtmlContent varchar(8000) DECLARE @object int DECLARE @executable int DECLARE @RecCount int EXEC @executable = sp_OACreate 'CDONTS.NewMail', @object OUT EXEC @executable = sp_OASetProperty @object, 'BodyFormat', 0 EXEC @executable = sp_OASetProperty @object, 'MailFormat', 0 select @HtmlContent =MailText from Tbl_EmailContent where RecID=2 select @HtmlContent= REPLACE(@HtmlContent,'Holiday_Title',@Title) select @HtmlContent= REPLACE(@HtmlContent,'Holiday_Date',@HolidayDate) EXEC sp_OAMethod @object, 'Send', NULL,@SenderAddress ,@RecipientAddress,@Subject,@HtmlContent EXEC sp_OADestroy @object
please do some necessery steps to solve this problems.
Sp_OACreate And Sp_OAMethed Heelllllpppp...
Mar 23, 2008I have a question and I hoping to not sound to lame. I am trying to use the sp_oacreate and the sp_oamethed to run my dll from sql sever 2005
It looks some thing like this
EXEC @hr = sp_OACreate 'MyApplication.Adcom', @object OUT,5
IF @hr <> 0
BEGIN --Not able to create the object
Return @hr
END --Not able to create the object
EXEC @hr = sp_OAMethod @object, 'Authenticate', @FunctionReturn OUT, strUser, strPassword, strDoman
IF @hr <> 0
BEGIN --Not able to execute Com function
Return @hr
END --Not able to execute Com function
I have created my dll in .net and placed it in the root drive of the server. How does OACreate know where to locate my dll?
One executing this fucntion I am returning -2147221005
-214721148 error code.
Have I created my dll incorrectly? The name of the dll is ActiveDirectoryCom and the Class with in the dll is called Adcom and the function that need to run is called AuthenticateUserFullPath. Have I correctly Written the sp_Oa's
Sp_OACreate And Sp_OAMethed Heelllllpppp...
Mar 23, 2008I have a question and I hoping to not sound to lame. I am trying to use the sp_oacreate and the sp_oamethed to run my dll from sql sever 2005
It looks some thing like this
EXEC @hr = sp_OACreate 'MyApplication.Adcom', @object OUT,5
IF @hr <> 0
BEGIN --Not able to create the object
Return @hr
END --Not able to create the object
EXEC @hr = sp_OAMethod @object, 'Authenticate', @FunctionReturn OUT, strUser, strPassword, strDoman
IF @hr <> 0
BEGIN --Not able to execute Com function
Return @hr
END --Not able to execute Com function
I have created my dll in .net and placed it in the root drive of the server. How does OACreate know where to locate my dll?
One executing this fucntion I am returning -2147221005
-214721148 error code.
Have I created my dll incorrectly? The name of the dll is ActiveDirectoryCom and the Class with in the dll is called Adcom and the function that need to run is called AuthenticateUserFullPath. Have I correctly Written the sp_Oa's
Need To Call Com Object Using Sp_OACreate
Apr 11, 2008Hi,
I have com object on another machine, and I want to call from diffetent machine,Is It possible to use sp_oACreate to creating object of com object that resides on another computer.
Thanks
.Net COM Interop Class Not Working Via Sp_OACreate
Aug 2, 2006this thread is actually to help another guy out in a seperate thread lol...I am not a COM expert but I do have a basic understanding of it from a dev perspective. I am trying to get COM interop via VS2005 to work from a sql2005 sp_OACreate call. below is the code. I can replace my custom object with say a sqldmo reference and IT WORKS! So I was thinking maybe security issues, but even when I run sql under an admin account it does not work. and i am connecting via sa for the code.
Also, I have tested the COM object via windows script host/.vbs file: dim oOjbect set oObject = CreateObject(myCOMObject.Math) msgbox oObject.Add(1,1). And the object appears to be listed correctly in the registry under HKEY_Classes
Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class Math
Public Function Add(ByVal iFirstNum As Integer, ByVal iSecondNum As Integer)
Return (iFirstNum + iSecondNum)
End Function
End Class
------------------------------------------------------------------------------------------
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate 'myCOMObject.Math', @object OUT,5
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
Sp_OAcreate, Access.Application Can Not OpenCurrentDatabase
Jan 19, 2007Hi all,
trying to use MS Access Automation from within SQL Server 2000 .
Seems that OpenCurrentDatabase (Access Application property) just hangs...
Following, the code that is used:
--BOF--
--object vars
DECLARE @w_object INT,
@s_object INT
--error handling vars
DECLARE @error INT
DECLARE @src VARCHAR(255),
@desc VARCHAR(255)
--execution vars
DECLARE @command NVARCHAR(100),
@property NVARCHAR(20)
--create an Access application
EXEC @error = sp_OACreate 'Access.Application', @w_object OUT
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @w_object, @src OUT, @desc OUT
SELECT CONVERT(VARBINARY(4),@error) AS Error, @src AS Source, @desc AS [Description]
RETURN
END
--get default security level
EXEC sp_OAGetProperty @w_object, 'AutomationSecurity', @property OUT
SELECT @property AS default_security_level
--set application security level to low (no sandbox)
--thought this would help, but no luck
EXEC @error = sp_OASetProperty @w_object, 'AutomationSecurity', '1' --Low Security
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @w_object
GOTO Cleanup
END
--Test security level
EXEC sp_OAGetProperty @w_object, 'AutomationSecurity', @property OUT
SELECT @property AS my_security_level
--check current user
EXEC sp_OAGetProperty @w_object, 'CurrentUser', @property OUT --should be Admin
SELECT @property AS current_application_user --it is Admin
--Open non password protected database,
SET @command = 'OpenCurrentDatabase("C:mikros.mdb")'
--unfortunately this hangs...
EXEC @error = sp_OAMethod @w_object, @command
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @w_object, @src OUT, @desc OUT
SELECT 'Failed to Open database', CONVERT(VARBINARY(4),@error) AS Error, @src AS Source, @desc AS [Description]
GOTO Cleanup
END
--Close current database
EXEC @error = sp_OAMethod @w_object, 'CloseCurrentDatabase'
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @w_object, @src OUT, @desc OUT
SELECT 'Failed to Close database', CONVERT(VARBINARY(4),@error) AS Error, @src AS Source, @desc AS [Description]
END
Cleanup:
--close Application
EXEC @error = sp_OAMethod @w_object, 'Quit'
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @w_object
END
--destroy Application Object
EXEC @error = sp_OADestroy @w_object
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @w_object
END
--EOF--
Any help would be appreciated.
Thanks in advance
EXECUTE Permission Denied On Object 'sp_OACreate', Database 'mssqlsystemresource'
Oct 5, 2006HI There
I have a user login that is executing an sp. It gets the follwoing error:
Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1
EXECUTE permission denied on object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAGetProperty, Line 1
EXECUTE permission denied on object 'sp_OAGetProperty', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAGetProperty, Line 1
EXECUTE permission denied on object 'sp_OAGetProperty', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OADestroy, Line 1
EXECUTE permission denied on object 'sp_OADestroy', database 'mssqlsystemresource', schema 'sys'.
Everything ic an find on the net refers to Sql Server 2000 but this is 2005, all the resolutions say you must grant exec permissions to the user account for these sp's in the master database.
BUT is SS2005 they are in the mysqlsystemresource database.
WHen i try the following
grant exec on mssqlsystemresource.sys.sp_OACreate to UserLogin:
I get this error:
Cannot find the object sp_OACreate, becuase the object does not exist or you do not have permission.
I am logged in as sysadmin so i doubt it is permission.
How do i get a user login to be able to exec these sp's?
Thanx
Sp_OACreate In SQL Server 2005, Fails With 'Not Enough Storage Is Available To Complete This Operation'
Jan 20, 2007Hi all,
I have created a small COM in C# so that I can programatically create and execute stored procedures with SMO. At this point the COM has nothing in it but just a test prototype.
But when I tried to create the object as follows, I get the error indicated below.
It is not a memory issue because I have adequate storage and RAM.
Please Help!
DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @src varchar(255), @desc varchar(255)
-- Create an object.
EXEC @hr = sp_OACreate 'SQLInterop.CsharpHelper', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
This is the error I am getting:
Error Code: 0x8007000E
Description: Not enough storage is available to complete this operation.
Source: ODSOLE Extended Procedure
This is the C# code for the COM:
using System;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.EnterpriseServices;
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
[assembly: AssemblyTitle("CSServer")]
[assembly: AssemblyDescription("Test SQL .NET interop")]
[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("MyKey.snk")]
// Setting ComVisible to false makes the types in this assembly not visible
// to COM components. If you need to access a type in this assembly from
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible(true)]
// The following GUID is for the ID of the typelib if this project is exposed to COM
[assembly: Guid("ff35c6b4-81bf-47dd-9290-fcbbb49008d9")]
// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
[assembly: AssemblyVersion("1.0.0.1")]
[assembly: AssemblyFileVersion("1.0.0.1")]
// the ApplicationName attribute specifies the name of the
// COM+ Application which will hold assembly components
[assembly: ApplicationName("SQLInterop")]
// the ApplicationActivation.ActivationOption attribute specifies
// where assembly components are loaded on activation
// Library : components run in the creator's process
// Server : components run in a system process, dllhost.exe
[assembly: ApplicationActivation(ActivationOption.Server)]
namespace SQLInterop
{
public interface ITest
{
string SayHello();
string SayIt(String strMessage);
}
//[SecurityRole("RBSecurityDemoRole", SetEveryoneAccess = true)]
[ComVisible(true)]
[CLSCompliant(false)]
[ClassInterface(ClassInterfaceType.None)]
public class CSharpHelper : ITest
{
public string SayHello()
{
return "Hello from CSharp";
}
public string SayIt(String strMessage)
{
return strMessage + ": from CSharp";
}
}
}
EXECUTE Permission Denied On Object 'sp_OACreate', Database 'master', Owner 'dbo'.
Sep 21, 2005I want to send email using sql stored procedure.my code is work fine in my local sqlserver account. when I use my online sql server it display this error.
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OAMethod', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OADestroy', database 'master', owner 'dbo'.
How canI solove this problem?
EXECUTE Permission Denied On Object Sp_OACreate/method/GetProperty/Destroy
Aug 3, 2002Hi friends
I copy script that use SQL proc to call DTS package from this site.
When I run this procedure in SQL query alalyzer. I got an error messages:
EXECUTE permission denied on object sp_OACreate/method/GetProperty/Destroy.
I did not change anything in spExecutePKG.
Even I used sa as ServerPWD. How to fix this problem? my code is
exec spExecutePKG 'myserver','execl_DTS','sa','0',''
Thanks
jimmy
------proc original code
CREATE PROC spExecutePKG
@Server varchar(255),
@PkgName varchar(255), -- Package Name (Defaults to most recent version)
@ServerPWD varchar(255) = Null,-- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@IntSecurity bit = 0,-- 0 = SQL Server Security, 1 = Integrated Security
@PkgPWD varchar(255) = ''-- Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
-- Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Check Pkg Errors
EXEC @ret=spDisplayPkgErrors @oPKG
-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
RETURN @ret
GO
EXECUTE Permission Denied On Object 'sp_OACreate', Database 'master', Owner 'dbo'.
Sep 21, 2005I want to send email using sql stored procedure.my code is work fine in my local sqlserver account. when I use my online sql server it display this error.
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OASetProperty', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OAMethod', database 'master', owner 'dbo'.
EXECUTE permission denied on object 'sp_OADestroy', database 'master', owner 'dbo'.
How canI solove this problem?
Sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkload' In 2005
Jan 31, 2006With my Sql server I install SQLxml3.0 and run this with problem.
DECLARE @ObjectError INT
, @Object INT
, @ErrMsg VARCHAR(50)
EXEC @ObjectError = sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkload', @Object OUT,4I plan to go with the Sql server 2005 but this code give me a error...:eek: Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
My user right not change. And I dont see some "SQLXML" in the "Surface Area Configuration"???
Can someone can give my suggestion?