.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 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

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.

http://support.microsoft.com/default.aspx?scid=kb;en-us;322884

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.

|||sorry but he is right...I DID NOT KNOW ABOUT THE CONTENT OF THIS KB article...I guess you can ONLY call TRUE COM objects via sp_OA procs. If your going to leave your database at CompLevel 80 then you must either use a pre.Net COM tool OR use another tier of your application to create/manipulate flat files with.