I 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
I 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
I 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.
Hi, 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.
this 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)
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
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?
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"; } } }
I 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?
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
I 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'.
With 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"???
