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.
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
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
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.
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
@@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.
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
@@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 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
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:ruclips.net/video/bE29LBvxJpc/видео.html
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?
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?
@@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.
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.
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
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.
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.
![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)
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 RUclips video.
Many Thanks@@System-Restored
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.
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.
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
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.
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.
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, 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
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:ruclips.net/video/bE29LBvxJpc/видео.html
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?
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.
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.
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
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.