Configuring an S7-1200/S7-1500 to communicate with Microsoft SQL Database using Tabular Data Stream

[@piterNovy]

Great video!! A few weeks ago I set up a 1500 PLC as a FTP client sending about 1500 real values every 5 minutes to a .csv log file located in a simatic ipc!

[@AutomationStation]

Nice work!

[@Сергей-ц1в4щ]

Guys, everyone who has error 8602 - see the connection settings (SqlConfig DB). Recheck IP settings, InterfaceID, port number carefully . Also check the settings on the SQL side I had this same error. The problem turned out to be in the InterfaceID number. I put up the number 64, although I did not have it in the system constants (there were only 65 and 66 for each port) and it all worked Everything worked fine with the PLCSIM Advanced v3 upd1 (through PLCSIM Virtual Eth. Adapter) and latest for this moment MS SQL Now I am suffering with receiving a large amount of data from the database. Until I won (

[@sagamoreful]

I completely stuck there I don't know what else I can do

[@utkarshsingh175]

Wow.. this is a very nice demo... Is this feature supported by PLC Sim Advance 3.0 ??

[@AutomationStation]

Even better - it works with PLCSim Advanced V2 as FW V2.6 is perfectly fine. Just tested it just to be on the safe side ;) Fun fact? It didn't work! Why? Because I turned off my SQL server ... With the server on it works perfectly fine! Thanks for watching

[@yaroslavlyulko2298]

Have you any idea how to check result of sql query? For example if i execute command with query 'bla-bla-bla' - I will not see that this query not executed on SQL Server side.

[@chrissibrandner]

Thank you for the interesting video. Is it also possible to send sensor values instead of those predefined values?

[@AutomationStation]

Yes, absolutely. This was just a mere example to prove that it works, what you send is up to you and you can even read data back!

[@mafrafree]

Hello, in the Siemens link there is no longer the project for TIA V.16, Where i can find it ?

[@lcart1064]

Thanks for the informative video! I'm getting error 8605 - "Error when calling TRCV". Haven't been able to find much online about how to fix this... any ideas would be appreciated.

[@bassistemre]

Good video, first of all thank you! But I have a question. How can I run this project with S7-1200? I tried to copy paste the material from existing project to blank S7-1200 project and an error occured which says: Drop/Insert(0026:000071) A test cannot be copied. Only one test instance of type version Lsql_Microsoft V0.0.8 is permitted. Only released type instances can be copied. Please! What should I do?

[@AutomationStation]

Check the types folder in you project library - this message is indicating you have put one of the types (Lsql_Microsoft) in edit mode. Right click it and select “discard changes” to restore back to a released version.

[@shanehodgson4290]

HI, I'm trying to use V2.1 of this block in an S71200 and keep getting an area length error. This version doesn't require line 386 to be commented out as the manual states that the block will work with an S7-1200 from firmware V4.4 or higher (I am using V4.5) it does however state that the parameter "data" must be interconnected with an "Array of Byte" when using a S7-1200. A Structure is not permitted. Does it just mean i need to change the "data" datatype to be an array of bytes rather than the structure? Any help would be much appreciated.

[@tranngoctam8419]

Good video! I ask you to guide how to connect PLC S7-1200 with SQL server using domain name, Thanks

[@richardstatie5392]

Thank you for sharing this. Great tutorial. I did follow all the steps, but I'm at the end when I'm trying to start the communication from the S7-1200 with my PC, I'm getting the fault message: 8602. In the subfunctionstatus: 80C6. I don't know what I'm missing here. The fault message comes up even before I try to send a message. It looks like the communication is not working between the PLC and the PC. Do you have any suggestions?

[@AutomationStation]

I'd suggest checking our other video that shows you how to set SQL Express from scratch: kzbin.info/www/bejne/gKbPlqecj7SDY68 Once you go through this I'd confirm that you can access your database using Server Management Studio. If this doesn't work, you won't be able to connect from your PLC either and it means that you have either not configured your Microsoft SQL database correctly or something like a firewall or your specific network topology is causing issues

[@LuisCardoso-uj5co]

Hello, i'm having a problem on an S7-1200, I'm getting the Status 8605 and the SubfunctionStatus 8088. What am I missing here? Thanks in advance.

[@Plcwhiz]

Hi how do i send Timestamps from The S&-1200 to SQL. I have concatenated the time stamp data but it is not appearing in SQL

[@egors9279]

Thanks for the excellent demonstration of a really useful feature. Is it possible to read data back from the SQL database in the same way?

[@AutomationStation]

Sure! That's when you'd use the enableArchive, db and dbmax pins on the Lsql_Microsoft block.

[@garyharrison9146]

@@AutomationStation but how do you read back in 1200 without the archive?

[@AutomationStation]

Hi Garry, yeah I'm afraid that's only possible with the S7-1500

[@garyharrison9146]

@@AutomationStation you can actually do it. I managed to get it to read back in a 1200 . Also noticed the block isn't developed far enough for example it doesn't handle useful production db scenarios i.e. long insert or query commands. Had to modify that. Good concept but not good for memory space on plc or production ready for tracking parts etc.

[@PelleTampio]

@@garyharrison9146 Hi, we have a demo with the siemens "stock" example and we find the sqlcommand limited with string only. I can fit 6 analog measurements + column names with string [255]. Any tips how to change this before i hit siemens with support request? Requirement for us is max data length from plc - > sql to avoid manual splicing and time sync of the data to be send. Pdf Manual says 65kbyte send and receive limit so there should be a trick to use something else than string 255 for send. Also the datablocks have quite a lot of room for more than a string. Annoying siemens did not straight away give notes how to extend the example limits.

[@leminhhieu0912]

Thanks for your great video, and i have a question for you. Now, i dont have CPU S7-1500, maybe, can i use PLCSim to do it ? when i use PLC sim Advance to simulate S7-1511. Using example project of Siemens, when enableLsql =1 , Status = 16#8602, SubfunctionStatus = 80C5.

[@AutomationStation]

I tested it for another viewer and works perfectly fine with PLCSim Advanced. If you open the block itself you can check what these codes mean, hopefully they'll point you in the right direction. Also, did you check whether you can connect to the database using something like the Management Studio?

[@leminhhieu0912]

@@AutomationStation In my PC, i am using SQL server 2014. in this video, you use SQL server 2017, can SQL server 2014 connect ?

[@utkarshsingh175]

I am also getting the same Error, 16#8602 & 16#80C5 using PLCSImAdvance 3.0 & Example project of Siemens.

[@AutomationStation]

Well I haven't tested with V3 but works perfectly fine with V2

[@hectorhirammirandaflores661]

I have the same error and still can´t figure out what is the problem

[@MrChantid]

Able to write data in to MySQL or not?

[@cptchaos8]

Hi. What do I need that I can setup a permanent communication for data exchange between s7-300 plc and SQL Server? Is there a service necessary or do I have to stay online in Tia portal all the time?? I just wanna write data from plc every 10-20 seconds into sql server database. Else I have to use OPC UA and there you use a service that runs...

[@AutomationStation]

I don't think there's any library available for S7-300 so you might be best off having an intermediate IPC that will talk to your S7-300 and then access the SQL server. Or move to a S7-1500 ;)

[@muhammadzafariqbal9822]

hi nice video but how to transfer character data from tia portal string to sql DB like (apple mango) 1234 easy transfer but character not going can you explain thanks

[@曾亮中]

If you need to read the SQL DATA , how to use it.

[@lamsonlemai6516]

Thanks for your video, but can I use this method for MySQL, ? I have seen a topic of MySQL of siemens forum but it’s so difficult to understand totally

[@AutomationStation]

I'm afraid this would only work with Microsoft SQL

[@lamsonlemai6516]

When I use this example for S7-1200. An error occurs because FC Write to array is not supported. :(

[@AutomationStation]

@@lamsonlemai6516 ​ There are restriction on what S7-1200 can do which I mention in the video and are also listed in the documentation. For it to work with S7-1200 you need to comment few lines. Check 12:10

[@lamsonlemai6516]

@@AutomationStation thank you very much.

[@manjunathnagaling4872]

Hiiii....what are the preconditions to configure SQL data to siemens scada system.?

[@AutomationStation]

Not sure I get your question. If you talk about setting up SQL for WinCC I'd advise checking the WinCC manual, should have all the preconditions

[@alexisneculpan3932]

hi .....how many values ​​can you send from a 1200 to a database??

[@AutomationStation]

Good question, I have not seen any performance values. I would suggest monitoring communication load of the PLC

[@davidgemelli]

Hello, this work on tia portal V15?

[@YogeshPatil-wv8zj]

It is very good video. Can you provide same program in V15 version

[@AutomationStation]

Sorry I'm afraid I don't even have V15 installed and don't think the blocks support V15... You could get yourself a trial of V16 though and see if you can migrate the blocks to V15

[@曾亮中]

Archiving received data (enableArchive, db, dbmax)----------->How to use?

[@kaleshnarayan2010]

Hi dear thank you for the video ,,please tell me how can i connect sql with plc sim advance , Is it possible? please let me know

[@AutomationStation]

Well I tested it with PLCSim Advanced V2 as FW V2.6 and it works perfectly fine so should also work with V3 FW V2.8

[@曾亮中]

I run a select query I don't know how to retrieve data and read the values.

[@AutomationStation]

There's a new version of the block available on the Siemens support site that supports SELECT, make sure to use this one and check the documentation for more info on how to do it

[@williamvidorette3797]

Hi, do you have de example in TIA PORTAL V16 to share? On the siemens page is in TIA V17.

[@AutomationStation]

I dont think I do, sorry. You could try opening the v17 blocks in a trial version and copying them to v16 maybe

[@williamvidorette3797]

@@AutomationStation Hello friend, thanks for the reply, I got the v16 version

[@mafrafree]

@@williamvidorette3797 Where did you find ?

[@williamvidorette3797]

@@mafrafree Hello friend, I got it from the person who posted another video, if you want, give me your email, I'll try to send it to you

[@ashiqmumtaz8726]

HI, Is it possible to connect S7-1200/1500 to Azure Database?

[@AutomationStation]

Not this way. For Azure check our MQTT video. Most cloud services like Azure or AWS use MQTT brokers so you need to have an MQTT client on your PLC to connect and send data to it. An alternative would be a hardware solution like the Cloudconnect 7 for example.

[@曾亮中]

Archive len = 67174534 , Is it normal?

[@曾亮中]

How to use the select command? to s7-1500

[@AutomationStation]

There's a new version of the block available on the Siemens support site that supports SELECT, make sure to use this one and check the documentation for more info on how to do it

[@曾亮中]

S7-1500 Example 'insert into PLCDATA values (5,6,7)' If it's in the s7-1500 DB way, how to write

[@AutomationStation]

Hello again! If what you're after is writing some dynamic data rather than predefined values to your SQL Database, that's pretty easy. If you look at the command at 11:26 you just need to build the command yourself by concatenating some strings. I'd make another function which would take my values as the input, and the table name as another input, concatenate these into an SQL command and then spit out the command and then I'd use this command as the actual parameter for my sqlCommand input. I was actually going to do it but didn't want to add extra blocks and confuse people ;)

[@曾亮中]

@@AutomationStation Wait for your example, thank you.

[@曾亮中]

I can read and write To salsy Microsoft SQL Server?

[@AutomationStation]

@@曾亮中 Not sure what you mean by salsy, but it's possible to both read and write data from and to the SQL Server

[@曾亮中]

@@AutomationStation Archiving received data (enableArchive, db, dbmax)----------->How to use?

[@binor2286]

Guys I got an interesting error , so when I enable the SQL. The code turn to 7002 which is completely normal but after 17s (yes I'd count and each time it's 17sec) it turns to the error code 8602, sub function status 80C6 in state 3 . Does anyone had an idea on how to fix this problem ?

[@sagamoreful]

Did you solve it?

[@binor2286]

@@sagamoreful no I never found a solution even on Siemens forums

[@ag7258]

Sir how I get lsql microsoft function block

[@AutomationStation]

Check the video description

[@ag7258]

@@AutomationStation got link but how I download Plz help

[@ag7258]

Can it work for tia 15

[@曾亮中]

If you need to read the SQL DATA , how to use it.

[@曾亮中]

I run a select query I don't know how to retrieve data and read the values.

[@AutomationStation]

I'm afraid that SELECT might not be implemented in the block yet. Feel free to try to do it yourself though

[@lehel121]

did you implementet. or it was to hard?