Does anyone have any idea how to get a string back from an object call that is greater than 255 characters?
My sp call a method on an object that needs to return a string that is 680 characters long, but I get:
ODSOLE Extended Procedure
The source data specified for this string or binary column or parameter is too long.
I tried declaring the local variable that is passed as the returnvalue as varchar(4000) but still I get the error. According to the docs if the string coming back is >255 then sql uses text, but as you know you can't declare a local variable as text.
Any ideas?



Greeting SQLers,

I'm trying to call a method of an object, which the method will return an object but failed! Anyone can help?

I'm creating a FileSystemObject, which then I call the OpenTextFile method to get a TextStream object.

Syntax of get TextStream (in VB):
Set objTxtStream = objFileSystem.OpenTextFile(strFilename, otherParam...)

Trying to achieve the above in T-SQL:


Exec @intHResult = sp_OACreate 'Scripting.FileSystemObject', @objFile OUT
--routine check @intHResult...

Exec @intHResult = sp_OAMethod @objFile, 'OpenTextFile', @objTxtStream OUT, 'C:est.xxx', 2, True
--routine check @intHResult...

I get the error :
Source: ODSOLE Extended Procedure
Desc: Error in parameters, or attempt to retrieve a return value from a method that doesn't supply one.


View Replies View Related


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.


View Replies View Related

Why Can't I Load My DTS Job Using Sp_OAMethod

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 Replies View Related

Sp_OAMethod Question

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

View Replies View Related

How To Get Sp_OAMethod To Work

I'm trying to use sp_OAMethod, w/o success so far...

I have written an ActiveX exe in VB6 and installed it on the system hosting
SQL7. Then I wrote an trigger w/ the code below in it. sp_OACreate seems
to excute properly, but sp_OAMethod errors, with this:
HRESULT: 0x80020006
Source: ODSOLE Extended Procedure
Description: Unknown name.

Any help would be very appreciated. Thanks,

Code segment:

DECLARE @object int
DECLARE @hr int
EXEC @hr = sp_OACreate 'DataServer.CDataServer', @object OUT
IF @hr <> 0
EXEC sp_displayoaerrorinfo @object, @hr

DECLARE @rc int
EXEC @hr = sp_OAMethod @object, 'RaiseIncomingEvent', @rc OUTPUT
IF @hr <> 0
EXEC sp_displayoaerrorinfo @object, @hr

View Replies View Related

Problem With Sp_OAMethod

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

View Replies View Related

COM And Sp_OAMethod Problems...

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

What do I do wrong?

Best regards,

View Replies View Related

Sp_OAMethod Performance

I have a dll that is instantiated using sp_OACreate and sp_OAMethod to update my leads.

I have a nested cursor and wish to speed things up if I can.

I create the object out of my While Fetch_Status and simply reuse the execute method with new variables for each lead processed.

1. Can I get some performance using the sp_OASetValue then execute my method?

Or, will this only slow me down. I'm asking this as what I should expect as performance as a benchmark for my own testing.

2. My DTS package is also running slow using the Execute SQL, is there another way to execute my stored procedures?



View Replies View Related

Using Sp_oamethod To Add &#39;add-ins&#39; In Excel 2000

I'm trying to figure out the syntax of using the sp_oamethod SQL 7.0 ole extended stored procedure to execute the 'Add-Ins' in excel to add in the 'Analysis Tool Pak - VB'. I know that I can do this in an excel macro by executing:

AddIns("Analysis ToolPak - VBA").Installed = True


AddIns.Add("C:Program FilesMicrosoft Office 2000officeLibraryAnalysisAnalys32.xll").Installed = True

But, I'm not sure as to how to use them from SQL 7.0. Any help on the syntax of the sp_oamethod would be helpful.


View Replies View Related

Sp_OAMethod Fails With 0x800A0046

I 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 Replies View Related

Extended Stored Procedure Sp_OAMethod Failed

I got an problem while executing a extended stored procedure from SQL Server 2000.
I have a extended stored procedure (C++ com dll) called "TisNotificationCom.dll" which creates a Message and sends to MSMQ, When I am executing com dll from the stored procedure as shown below:
        select @objectname = 'TISNotificationCom.TISNotification',
               @methodname = 'SendXferNotificationTo'
    exec @sp_create_ret = sp_OACreate @objectname, @oaObject output
   exec @sp_method_ret = sp_OAMethod @oaObject,
         @method_ret output,

in the above code the sp_OACrete method is success full,  - The  'TISNotificationCom.TISNotification' is loaded successfully,
but sp_OAMethod is failed. - The Com class instance creation is failed.
When i debug the dll, the dll is loading and going to the 'TISNotificationCom.TISNotification' constructor and returning without creating instance of the class and unloading the dll.
When I execute the same on different machine with the same environment with Win2000 server and SQL server 2000, it is executing successfully and sending message to MSMQ whithout any issue.
What could be the problems?
Can any one provide me the solution


View Replies View Related

Problem With Sp_OAMethod 'WriteLine For Special Characters

I 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.
Added later:
I found a way to do the same with bcp, but also that utility has problems with "äöü" characters.

View Replies View Related

Sp_OAMethod, AttachFile, Attachment Comes Through As Ascii Text


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

View Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved