Online Forums

Technical support is provided through Support Forums below. Anybody can view them; you need to Register/Login to our site (see links in upper right corner) in order to Post questions. You do not have to be a licensed user of our product.

Please read Rules for forum posts before reporting your issue or asking a question. OPC Labs team is actively monitoring the forums, and replies as soon as possible. Various technical information can also be found in our Knowledge Base. For your convenience, we have also assembled a Frequently Asked Questions page.

Do not use the Contact page for technical issues.

sp_OAMethod @easyDAClient, 'writeItemValue'

Start
Prev
1
Next
End

1

support
Offline
Moderator

More

19 Sep 2012 17:10 #1030 by support

Replied by support on topic Re: sp_OAMethod @easyDAClient, 'writeItemValue'

The reference documentation lists every method, its arguments etc. This is based on the IDL definition of the interfaces, which is then consumed by different tools. We have so many possible tools consuming the interfaces that it is unrealistic to provide reference to everything in every tool. We aim at providing basic example with each tool, and then you can use them to infer how other methods would be called.
I am willing to help you with any concrete issue you have. In this particlar case, you might be out of luck, though, because T-SQL does not have (to my knowledge) a support for passing in array values to OLE Automation methods (with sp_OAMethod). All our methods that work with multiple items at once use arrays on input and output.
How many items you want to pass in? Is that a performance problem to write them one by one?
If so, a possible workaround might be to use repeated calls to InvokeWriteItemValue. This method does not wait for any result (not even for the operation being executed - it just queues the requests internally and processes them in a background). You can therefore quickly call them in a loop.
Let me know your requirements and hopefully we can find some solution.

Please Log in or Create an account to join the conversation.

Sknight
Topic Author
Offline
New Member

More

19 Sep 2012 15:28 #1029 by Sknight

Replied by Sknight on topic Re: sp_OAMethod @easyDAClient, 'writeItemValue'

Thanks this works great.
All of the help shows the quick-com reference for Vb Scribt which see how this works, however, I would like some reference to the SQL Stored procedure reference for all of the methods.
Ie I now need to test WriteMultipleItemValues from TSQL, it would be nice just to have the info online or emailed to me. thanks.

Thanks

Please Log in or Create an account to join the conversation.

support
Offline
Moderator

More

19 Sep 2012 11:52 #1025 by support

Replied by support on topic Re: sp_OAMethod @easyDAClient, 'writeItemValue'

Hello. Below is an example that successfuly writes into our simulation OPC server. You should be able to adapt it to your needs. Let me know should you need further help.

-- WriteValueAndReadBack.sql: Write a value into an OPC item, and reads it back.

-- The 'sp_configure' calls below update the configuration options in order to prevent following error:
-- 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.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

-- Stop the OLE Automation execution environment. This is useful if you are experimenting with the registration or security,
-- so that the modified settings always take effect before attempting to execute our code.
EXEC sp_OAStop;
GO

-- The actual example starts here.
DECLARE @hr int;
DECLARE @src varchar(255), @desc varchar(255);

-- Create EasyOPC-DA component
DECLARE @easyDAClient int;
EXEC @hr = sp_OACreate 'OPCLabs.EasyDAClient.5.1', @easyDAClient OUT, 4;
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @easyDAClient, @src OUT, @desc OUT;
raiserror('Error Creating COM Component 0x%x, %s, %s', 16, 1, @hr, @src, @desc);
RETURN;
END;

-- Write item value
DECLARE @writeValue int;
SET @writeValue = 12345;

EXEC @hr = sp_OAMethod @easyDAClient, 'WriteItemValue', NULL, '', 'OPCLabs.KitServer.2', 'Simulation.Register_I4', @writeValue;
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @easyDAClient, @src OUT, @desc OUT;
raiserror('Error Calling COM Method 0x%x, %s, %s', 16, 1, @hr, @src, @desc);
-- If you get following error: "Error Calling COM Method 0x80070005, OPCLabs.EasyDAClient.5.1, Access is denied.",
-- start DCOMCNFG, and for applications "EasyOPC-COM 5.1" and "OPCKitServer", configure their security for the SQL Server
-- process identity. Typically, this means adding Network Service or Local Service (depending on the account the SQL Server
-- uses) to "Launch and Activation Permissions -> Local Launch, Local Activation", and to "Access Permissions -> Local
-- Access".
RETURN;
END;
PRINT 'Write successful';

-- Read item value and display it
DECLARE @readValue int;
EXEC @hr = sp_OAMethod @easyDAClient, 'ReadItemValue', @readValue OUT, '', 'OPCLabs.KitServer.2', 'Simulation.Register_I4';
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @easyDAClient, @src OUT, @desc OUT;
raiserror('Error Calling COM Method 0x%x, %s, %s', 16, 1, @hr, @src, @desc);
RETURN;
END;
PRINT @readValue;

GO

Please Log in or Create an account to join the conversation.

support
Offline
Moderator

More

17 Sep 2012 07:51 #1019 by support

Replied by support on topic Re: sp_OAMethod @easyDAClient, 'writeItemValue'

Hello, I will try to make an example with sp_OAMethod and "WriteItemValue". Please wait for next post.
Regarding the "SQL CLR Database Project", it is quite possible that Visual C# Express does not have it. I think that in general it should be possible to develop assemblies for SQL Server anyway, because they are just class libraries, but Visual Studio gives nice integration, deployment and debugging support, so we do not support it without proper Visual Studio edition.
Best regards

Please Log in or Create an account to join the conversation.

Sknight
Topic Author
Offline
New Member

More

14 Sep 2012 15:07 #1017 by Sknight

sp_OAMethod @easyDAClient, 'writeItemValue' was created by Sknight

I am in the process of trialing this software for current project.
The main use is to Read and Write from SQL to PLC using OPC.

I have successfully used the sp_OAMethod to read data.
EXEC @hr = sp_OAMethod @easyDAClient, 'readItemValue',@value out,'','RSLinx OPC Server','[1245]A109:907';

However I can not get the method to write data to the PLC. With the returning error of :-
Error Calling COM Method 0x80042725, ODSOLE Extended Procedure, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval OUT [, additional IN, OUT, or BOTH params]]

I have tried to change the syntax to the following
Declare @Value Varchar(20)
Set @Value = ‘24’
EXEC @hr = sp_OAMethod @easyDAClient, 'writeItemValue',@Value’,'RSLinx OPC Server','[1245]A109:907';

And also
EXEC @hr = sp_OAMethod @easyDAClient, 'writeItemValue','RSLinx OPC Server','[1245]A109:907',@Value;

Please can you provide the information for the sp_OAMethod and each usage for the TSQL command.

PS I did try and use the assembly example but It seems that VS c# Express does not have the database template. Is there any way to create the assembly without using the database template?

Thanks

Please Log in or Create an account to join the conversation.

Start
Prev
1
Next
End

1

Moderators: support

Time to create page: 0.057 seconds

Powered by Kunena Forum

Joomla SEF URLs by Artio