Where can I find a description of the methods and properties that are available for sp_OAMethod? I've looked in BOL and any other source I could think of but I come up dry everytime.
Sidney Ives
Database Administrator
Sentara Healthcare
Hi Guys,
SET @psFilepath = 'C: est.txt'
SET @psFilepath = '\XXXXXShareTest est.txt'
EXECUTE sp_OAMethod @FileSystem , 'OpenTextFile' , @FileHandle OUTPUT , @psFilepath , 2, 1
If I set the file path to '\XXXXXShareTest est.txt', the above statement does not create the test.txt file. But If I set the Filepath to a local drive it works.
Is it possible to create the files on a Share using sp_OAMethod.
Thanks
Hi!
I have a COM object with one method that I want to call from a SQL Server 7 stored procedure.
The method in the COM object looks like,
(Public Sub getXmlData(sqlStr As String, xslPath As String, sourceID As String, sendMethod As String)
When I call this method from the SP I get an error,
Source: ODSOLE Extended Procedure
Description: sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]
The call in the procedure looks like,
EXEC @hr = sp_OAMethod @object, 'getXmlData',
@sqlStr ='SQL statement...',
@xslPath='C:Temp',
@sourceID='Test',
@sendMethod='file'
What do I do wrong?
Best regards,
Carl
I am trying to call a working DLL from a stored procedure. The object is made, but when I invoke a method, I get an error message that I guess indicates that I am calling it incorrectly. But I can’t find what’s wrong:
The COM is TDMSQLUtil.dll and is registered on the server, and only has one class and one method.
Public Sub RemoveItems(ByVal ItemNumbers As String, ByRef Col1 As String, _
Col2 As String, ByRef Col3 As String, ByRef Col4 As String, ByRef Col5 As String, ByRef Col6 As String, ByRef Col7 As String, ByRef Col8 As String, ByRef Col9 As String)
I call it with:
1 ) Exec @HResult = sp_OACreate 'TDMSQLUtil.cHandleString', @Object out
(this does not return an error)
2) Exec @HResult = sp_OAMethod @Object, 'RemoveItems', @ItemNumbers, @Col1 out, @Col2 out, @Col3 out, @Col4 out, @Col5 out, @Col6 out, @Col7 out, @Col8 out, @Col9 out
The error I get is:
sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]
What am I missing? I followed the instructions in BOL, or at least, I tried to>
Any help greatly appreciated
Thanks,
Judith
Hello,I was trying to user the code below to run a DTS job. The job failswhen I get to the piece of code that begins:EXEC @rc = sp_OAMethod @PackageToken,'LoadFromSQLServer', ...It looks like it tries to look for the DTS package, because it takes 45seconds to execute when the server name is correct, leading me tobelieve that it is properly finding the server. Am "MyDTSPackage" isthe name of I saved my DTS Package under save as. I enter it in thelist below using single quotes just like I enter the server name.I am wondering if it is a permissions issue. I am running the thisstored procedure from SQL Query Analyzer:EXEC usp_OATest(The Stored Procedure gets created without a problem...it is running itthat is causing issues.)The error message is the custom one that shows at theError:line.Also, master..xp_cmdshell 'DTSRun....' works fine, but I would like tobe able to use this other method.Thanks in advance for any help!RyanCREATE PROC usp_OATestASDECLARE @rc intDECLARE @PackageToken intDECLARE @GlobalVariableToken int--Load DTS PackageEXEC @rc = sp_OACreate 'DTS.Package',@PackageToken OUTPUTIF @rc <> 0 GOTO ErrorEXEC @rc = sp_OAMethod @PackageToken,'LoadFromSQLServer',NULL,'ServerName',NULL,NULL,256,NULL,NULL,NULL,'MyDTSPackage'IF @rc <> 0 GOTO Error <------ERROR DETECTED HERE--execute packageEXEC @rc = sp_OAMethod @PackageToken,'Execute'IF @rc <> 0 GOTO Error--destroy packageEXEC sp_OADestroy @PackageTokenIF @rc <> 0 GOTO ErrorGOTO DoneError:EXEC sp_OAGetErrorInfo @PackageTokenRAISERROR('Error during package preparation or execution', 16, 1)Done:RETURN(@rc)GOEXEC usp_OATest
View 1 Replies View RelatedI recently enabled OLE Automation on a SQLSERVER 2005 database in order to be able to create files directly from the DB. When I attempt to use the sp_OAMethod to open or create a file, it fails with an error code of 0x800A0046. The sp_OACreate 'Scripting.FileSystemObject' call does not return an error and returns a valid object handle. I'm guessing the error has something to do with Windows permissions but I'm not sure what I need to do to correct it?
View 3 Replies View RelatedI use sp_OAMethod 'WriteLine' to write some VARCHAR data into a file. The problem is, that when the VARCHAR data contains special characters like "äüö", they not not correctly written and the file, if its a xml file is invalid.
VARCHAR data is "Datei abholenDateiname prüfen"
Statements are:
SET @XMLComment = '<!-- TestCase :' + @param_TestCase + ' -->'
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @XMLComment
Output is: <!-- TestCase Datei abholenDateiname pr�->
Is there any workaround to have it read/write special characters correctly in the file?
Any help is appreciated.
Thanks.
Added later:
I found a way to do the same with bcp, but also that utility has problems with "äöü" characters.
hey all.
im new to the ASP/SQL scene, so please bare with me.
i have to send an email (with two attachments) through sql server.
the mail arrives, but the attachments are not attachments... they are ascii text in the body. im using an existing stored proc, which apparently works. so there is no reason this shouldn't work.
The file exists, the path is correct. Its obviously seeing and reading the file (otherwise there would be no data to print as ascii)
thanks all.
Also, if you know of a better place where i can post this thread, please let me know