.Net COM Interop Class not working via sp_OACreate
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
SystemImports
System.IOImports
System.DataImports
System.Data.SqlClientImports
System.Data.SqlTypesImports
Microsoft.SqlServer.ServerImports
Microsoft.VisualBasicImports
System.DiagnosticsPublic
Class MathPublicFunction Add(ByVal iFirstNumAsInteger,ByVal iSecondNumAsInteger)Return (iFirstNum + iSecondNum)EndFunctionEnd
ClassDECLARE @objectint
DECLARE @hrint
DECLARE @srcvarchar(255), @descvarchar(255)
EXEC
@hr= sp_OACreate'myCOMObject.Math', @object OUT,5IF
@hr<> 0BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUTSELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@descRETURNEND
HI,
I find an extended stored procedure "xp_cmdshell" to create and append the text file from stored procedure. whose example is as given below:
xp_cmdshell "@ECHO test message >> C:\file.txt"
By using this method currently it seems that my goal can achieve such that i fectch the record and write in the file, but I just want to know how much this extended method is secure to use if i call it from my own DB's stored procedure.
Although, I am also looking forward for the solution of exception by using "OA_Create"
Looking forward for quick response.
Best Regards,
Jawad Naeem
Security risks depends on how the SPs are designed, how users access the SP/SQL Server, your application design (how it calls the SPs etc) among many other things. Generally, xp_cmdshell is a security risk since it can be used to compromise entire server/network/organization if used improperly. This is one of the reasons as part of SQL Server 2005 installation we now disable it by default along with features like CLR, OLE automation SPs etc that allow access to resources outside of SQL Server.
So you need to think about the following: Is it absolutely necessary to create text files from SPs? Why do you need to do that? What purpose does this process serve by creating text files from SPs? It is going to be an inefficient process anyway since you have to do it row-by-row thereby sacrificing performance & scalability. You can do this with few lines of code on the client side or using utilities like BCP without writing any code or SSIS/DTS in an efficient and robust manner.
HI,
Yes, i know there is security risk to use xp_cmdshell extended SP.
Actualy, the goal which i want to achieve is: A job should run through SQL Server at some specific time like fortnight, which fetches the record from database and write them in a file. So that's why i create a dll in Visual Basic.Net to fetch the record and write them in a txt file. And i write a stored procedure to call the dll. but i am getting an exception as already mentioned in the very first posting of this thread. If i success to remove the exception then i will create a job which runs at some specific time and date and will call my designed stored procedure which will be create to call the dll
so this is my problem and i need its solution badly,
Thanks
Jawad Naeem
For your problem, what you are doing seems too complicated. Any reason, why you want to write VB.NET code that runs in the database to extract rows to a file? I am still lost as to purpose of using a SP to call the VB.NET code. You didn't mention the version of SQL Server you are using, running .NET code from the engine is not supported under older versions of SQL Server. See below KB article for more details.
In any case, depending on the file format that you need you just need to create a BCP format file (you may not need one at all) and use BCP to dump data to a file. So you don't really need to write any line of code. You could schedule the BCP using SQLAgent or NT Task Scheduler. You can also write VB Script code in a SQLAgent ActiveScript task or DTS/SSIS package to do the same.
So the answer to your sp_OA* problem is that it is not supported to run .NET code in SQL Server using OLE automation. Using even with xp_cmdshell might be a problem. You will have to try and see. My suggestion would be that you can simplify your implementation and make it more robust by using one of the methods described in the previous paragraph.
