Video není dostupné.
Omlouváme se.
TIA Portal: S7-1500 to an SQL Database - LMicrosoft_SQL v1.0 Example
Vložit
- čas přidán 14. 08. 2024
- Presentation: • TIA Portal: Presentati...
The Tabular Data Stream protocol (TDS) gives you the ability to establish a direct connection with a Microsoft SQL server. Using TDS, you can log in to an SQL server database and transmit SQL instructions. In this way, it is possible to read data from the database or send them to the database for storage.
support.indust...
NOTE: Don’t forget to check permissions of the user in SQL DB, Login from PLC can be blocked. This is the most common issue to date.
The application example may have changed in-between versions of this video, please review the manual for block versions. This was filmed with version 0.8; please reference the latest version 2.0 in the below weblink.
Uses software to create a completely virtualized twin for pre-testing, end goal is to show how TCP/IP communications can be simulated from a PC using Siemens TIA Portal v16. Communication is obtained with a Microsoft SQL server and the example shows moving three integer values. All steps are exactly the same as integrating with a physical S7-1500 and server.
Read the manual for redaction of some code for the integration into S7-1200.
S7-1200 is possible with the same function block, but not an option for PLCSIM Advanced; physical s7-1200 only.
0:00 Introduction
0:29 Launch and configure PLSIM Advanced V3.0 for TIA Portal v16
0:39 Configure a PLCSIM Virtual Eth Adapter that matches the PLC configuration in the project
1:14 PLCSIM Advanced is capable of TCP/IP-based protocols and mechanisms
1:27 PLCSIM Virtual Eth. The adapter is a mapped device in network properties
1:42 Download programming
2:24 Microsoft Server 2016 Standard for SQL Server
2:39 Configure VMnet connection to bridge with PLCSIM Network adapter
3:37 Install SSMS and SQL Configuration software
4:29 Configure SQL Server Management Studio
6:33 Restart the SQL server for changes to take effect
9:31 SQL Server Configuration Manager server version command
11:03 Restart the server for changes to take effect
11:30 Windows Logs Application Filter Current Logs, ID 26022
13:06 To make a new rule, select Port for the rule type and use 1433 for SQL
14:21 Toggle the SQL Command
![COM13a. [1/5]Connecting a Siemens PLC(S7-1200/S7-1500) to an SQL Database [SQL Section]](http://i.ytimg.com/vi/fRKTqoNKcXU/mqdefault.jpg)
![COM13a. [1/5]Connecting a Siemens PLC(S7-1200/S7-1500) to an SQL Database [SQL Section]](/img/tr.png)
thanks for the tutorial! how can I do the inverse and write on the sql to receive on tia ?
This is definetly not covered here, but is shown in the newest version of the SIOS application example on the Siemens website. The example there shows select feature and the return tokens being parsed for details.
Very nice work. Siemens has released a new library for communicating with sql. lsql v3.0. Are you going to make an example for this library?
I have waited long enough I suppose..? I have not had a customer needing it at the moment. It came up again last week. Keep your hopes up, maybe soon.
Hi there, If you don't mind, can I ask you a question. SQL server is it fee
Microsoft SQL Express was free in the past and what was used in this CZcams video.
Many Thanks@@System-Restored
Hey
I am using TIA V17 and im using the newer lib from siemens.
When I try to connect i first get Status 7002 for about 15 secs, then it changes to 8602.
When configering the SQLconfig block, Do you have to put the IP Adress of the Server PC or the Sql server itself?
What is the sub function error code, not the one from the main block? Probably not finding remote partner.
Nic of the computer the sql is on. There is a spot in the video that shows how to align the nic to the sql studio. @5:52 IP number is the NIC.
You must make sure the ip address and subnet mask scheme are setup to mesh as well.
@Til Steinhorst Do know it is more about knowing the sub-function error than the actual error you see at the use of the block. This is going to be located under the instance data. The Subfunction directly relates to the errors you see by the OUC block
@@System-Restored I made it work a few weeks ago. I used the wrong connection ID.
Thanks:)
*interface ID
@@tilsteinhorst 🙂Been there done that. Also it helps if you do alot of open user communcations to break it up over several connections. For example with a larger system maybe it make sense to break up the connections for your PLC to breathe a bit.
lot video expect.and nice thriller music
Yeah I like to mix it up, sometimes I get a nice suprise thiller experience too. Thanks for the like.
Boa tarde, teria esta versão V11 para disponibilizar, segui todos os passos porem em minha maquina não funcionou e artigo para Download no site da Siemens não existe mais.
I'm not certain that you will get these blocks in a version 11 format. I do not have that. The site shows L-SQL v3 library for tia portal v17. support.industry.siemens.com/cs/document/109779336/connecting-a-s7-1200-plc-s7-1500-plc-to-a-sql-database-?dti=0&lc=en-WW
Hi, I have a problem. I do it all from this tutorial but my LSQL block stuck on status 7002 and nothing happens
I would check the IP Address, or the server information. support.industry.siemens.com/tf//WW/en/posts/tcon-fb65-not-establishing-an-active-connection/44998?page=0&pageSize=10#:~:text=If%207002%20is%20displayed%20continuouslyand,Suggestion
Hi, I'm trying to use this block in a 1200 but i'm getting an Area length error. Do you have an example of how to use it in a 1200? or do you know how I get fix the error? thanks in advance
In the FB, you have to change line : 373 (region receive data) and change DATA := #data, To Data := #Data.bytes,
@@MarkThimm thank you for this. I don't have any Area Length errors anymore. I am now however getting a status out of the block of 16#8602 (subfunctionStatus 16#80C6) which is the TCON connection error. I have checked the ip address and port number etc and they look ok would you have any ideas?
@@MarkThimm I've got it working now. Thanks for the help
@@shanehodgson4290 Hi, i have the same problem but haven't figured it out yet, let me know if you find the solution on that problem :)
@@MarkThimm hi Mark. My issue was with the permissions of the log in in the sql database. I only had viewing permissions. I granted all permissions and it started working
On your video, are the Network 2 and 3 are an obligation or we can do it without them ?
Not required in this way, but you do need a string command of some sort. Here is what they were doing, network 2 turns a tag value to string, network 3 concatenates all the necessary text together for the SQL command before it is sent in later network using sql function block.
@@System-Restored okay thanks a lot
@Binor please repost your 80C6 comment i accidental deleted it.
Hi, I've been stuck in Error code 80C6 ("The connection partner cannot be reached') for siemens S7 - 1500. I can open the port for the SQL server IP through telnet. It's failing in the connection state and doesn't get beyond there. Are there any recommendations to tackle this? Thanks in advance
Which sql engine are you trying? Be sure the double check as I'm sure you have, the password and username along with ip address. Remember the IP address can appear in hex and be a bit confusing. Also in SQL the user has to have the read/write access as part of the policy.
The one thing that stopped me for the longest was port blockages, and adding the pass thru in the sql express service configuration. I use IP3, but it may not be the right config for you. See @6m the port config. It's always important to restart the server after such a change.
@@System-Restored Thanks for the uber-fast reply, I'm using the Microsoft SQLserver engine I believe (it came as part of the SQLEXPRESS installation). Just to verify if my account credentials are working I tried to log in using my R script and it worked.
Now I tried a different IP on my VM (the siemens PLC Virtual ethernet adapter) and I was able to make the connection from the PLC (I also changed to Listen to all ports option to TRUE and added a 0 on the dynamic port entry for the IP). When I try to execute a simple insert query like in the video, I get stuck at 'Done Token has wrong status' #8606 for AnalyzeMetadata block. I don't see the data being inserted through SSMS either.
@@samsundar427 Okay that's a different error. so just to be clear you are past the other,? This one is probably that you are trying a newer block than the demo you see. Be sure to take your wireshark sniff, and expand the datatype for "custom user datatype" that comes with the newer versioned example. If you are unsure of how many tokens you are expecting back just make a big array of bytes to dump stuff in. This should get you going, but technically the insert should write to regardless of your token being landed. Wireshark that thing, see if you can see the message.
@@System-Restored Yessuh, the first issue was solved, thanks. TheseToken issues #8606 (wrong token status) and #8607 (wrong token type) are where I'm still at. I tried monitoring that IP address using Wireshark and saw a lot of random packets but when I filter just for port 1443 I don't see anything (even though the SQLserver is configured to use that IP at 1443 for transactions).
I'm actually receiving back some data of length 54 on TDS telegram but that array is in hex bytes and it doesn't make sense when I convert it to ASCII. Since I'm getting stuck at the SQL send state for the main LSql block there is no insertion happening :( Sorry to make your comment section a siemens forum.
@@samsundar427 Filter by TDS, and you should be able to first see the command, then the tokens need to be the right size. Theoretically, you could just make an array large enough just to get past the point of being able to insert. The TRCV I think hold the buffer there. The Token part is just a UDT array that you have to custom fit to recieve back the tokens. An insert typically just sends back a single status token saying the values received. Parsing HEX to strings:czcams.com/video/bE29LBvxJpc/video.html
Please provide the Program used in Tia portal, Main Block, Network 1 to 4.
support.industry.siemens.com/cs/document/109779336/connecting-a-s7-1200-s7-1500-to-a-sql-database-?dti=0&lc=en-WW
Hi, thanks for your guidance. I use version 2.0 of the SQL block of the tia portal and it works fine. My only problem is how to use a select query to read the value from the SQL table. Can you explain it, please? Or if you can suggest me some guides
Hi yes, the version 2.0 is a great new addition and well needed. Although it was fun seeing the app note become updated a week or so after my video was posted. I didn't want to take the video down none-the-less. Sorry for the confusion here if any.
In the lastest version they got rid of referencing the "array" db that was an in/out for the archive system that we once used to have to take out for the S7-1200 as you saw referenced in the slide set (part 1 video). So what you see in the video is now defunct as I show v1.1, hence the major revision changes to v2.x. Your v2.x is definitely the one you want to integrate with especially if your going the 1200 route.
cache.industry.siemens.com/dl/files/336/109779336/att_1038920/v3/109779336_SQL_S7_1500_DOC_en_V20.pdf
Here in v2.0 the blocks are setup to work well with writing and receiving a simple reply, but to return queried information you have to rely on the information that is provided in the TRCV block, and if you see the 8088 sub-function error then you need to fix your received data buffer from the TRCV block. One thing you will want to do is reference the newest application note that is also at the other end of the link the video information. There is a UDT that is referenced as this data received and you must custom configure this as the resolved data. The udt that says "type use case specific" is what you are after. This document will show you how to use Wireshark to sniff (pg.16) the TDS packet and then use this sniff to reverse how many bytes, and/or the create the structure to accommodate the reply from the SQL server.
I'm also making plans to show this v2.0 block on an S7-1200. I hope to also maybe record voice this time, hate to make them so boring without me in there. :-)
Love to know what you end up with. Best wishes!
@@System-Restored Thank you for replaying xD.Yes I am following the Siemens v20 document and in the Trcv function instance I have no errors. But I still can't get the data. I'm checking the permissions on the SQL side, because the INSERT and the UPDATE query work, or as you suggested I'll try to set the UDT 'typeUseCaseSpecificData' well. Let's see if I can ... eventually I'll wait for your vide xD.
Thanks again and have a good day.
Can someone point me to the 2.0 version? Have spent a couple of hours lookin but can’t find anything...
Hey please find the link the video comments. support.industry.siemens.com/cs/document/109779336/connecting-a-s7-1200-s7-1500-to-a-sql-database-?dti=0&lc=en-WW
V2.1, came out in February, do you need this one specifically? v2.0
can you make the same stuff but hoseted by a linux os database
Great idea. I haven't worked in that realm much but In my opinion, there are other products that make this more dynamic than hard setting code. Siemens Industrial DataBridge also provides an SQL driver they maybe have the solution already here?
Great...keep going
Thanks for the positive message!
Ideal goal is SQL to S210 positioning for youtube video'd. As you all know hobbies and life keep things from happening. I have it completed, but it's nothing I can actively share yet. Work In progress for now. There is another library I'm looking at to help parse the return tokens. Untested though, don't hold me accountable just yet. It serves for use with MQTT but I think the method inside for grabbing the token will work.
LSTREAM:support.industry.siemens.com/cs/document/109781165/library-for-data-streams-(lstream)?lc=en-us
Here I discovered another method that I want to show soon. OPCUA client using Excel. I think from here I can manage recipes easier. Otherwise with SQL, if anything changes, I have to recode and download to the PLC. Here within lies the issues with remaining as dynamic in code changes as possible. Downloads are the GMP devil.
@@System-Restored great keep u r knowledge sharing ....
Work in oracle database?
Never tried that one. The title for the library does say Microsoft. I know the WinCC Unified System can now easily query a lot using database snippets in the scripting windows.
Work in Tia V14?
Have not tested in that version and they do not show that version on the demo on the website. You may call Siemens helpline and request one for this version maybe?
@@System-Restored I rewrited to V14 and work
@@guilhermesilva13ss Thank you for the confirmation here! That is great news. Assuming a s7-1500?
Some have trouble exporting FB's or FC's. This is in SCL so it should have been easier since it is just text based. I also know of a tool for Openness which has a dashboard that let's you export blocks as XML files, and then re-importan them to another project. Again not sure this tool works back in v14 though.
@@guilhermesilva13ss Any hints you wanna share on how you managed to rewrite it for V14? Do you need to open the V16 version first and then export-import to V14? I don't know of a way to get thet SQL FB in V14 so I'll assume it's imported somehow.