Connecting an PLC Siemens S7-1500 to an SQL Server Database

Question

The connection guide is here. I track the guide and do a lot of stuff. The connection between PLC and SQL server is ok, I've login successfully to SQL.
I can insert to tables, update, and execute store procedures.

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

I use TIA PORTAL v16.

PLC: S7 1200 siemens

I have a simple table, I wanna simply read an integer, first with one row! then expand my work.

This is my table structure:

CREATE TABLE MyTable(
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [int] NOT NULL,
)
) ON [PRIMARY]

and this is my select query:

SELECT [value]
FROM [dbo].[MyTable]
WHERE id = 12033

expected result:

20

https://support.industry.siemens.com/tf/ww/en/posts/connecting-an-plc-siemens-s7-1500-to-an-sql-server-database/239392/?page=0&pageSize=10#post939400

UPDATE

I answered my own question, Please attention, Siemens recently (11/20/2020) published a document about Connecting an S7-* series to Microsoft SQL Server Database, directly. So the verfied anwser is the classic and normal way to connect PLC to SQL Server and the next answer is the latest native solution. Have fun

Haskins

Answer 1

Noticable Update!!!

Siemens recently (11/20/2020) published a document about Connecting S7-* series to Microsoft SQL Server Database, directly and with NO middle-ware like OPC Servers or S7netplus package. It means you can run SQL queries like select and insert via an internal function in PLC.
This is a faster & native solution.

First, via TiaPortal I made a string array in a Program block.

You should set up connection strings like shown in the picture below, which the IP Address of Microsoft Sql Server is 172.16.62.1:

Then I made a built-in PLC function by following instruction in these pdf published by Simense.

I have a bunch of queries in my predefined string array, I choose one of them by its index as an input parameter to the PLC function.
then I downloaded changes to PLC. as a result I have hello world in MSSQL Server. By far I ran a SQL Server Query by PLC itself!
Let's run another query and the hardest one: select.
I said the hardest because select has a bunch of records with various data type: like varchar, bool, DateTime.

The "select" instruction Using the "select" instruction, you can read data out of a database table and perform other operations on them in your controller. Below, we use an example to show how this instruction works and which modifications you will need to make for your query. The example uses the following query:

Select * from Mytable

The data queried are stored in the "SqlReceive" data block in the data type structure "typeUseCaseSpecificData". You must modify these data types individually for each query. You can do this as follows: 1. Start a Wireshark recording to find the packet length of the metadata. 2. Run the query "Select Amount from PLCDATA_2 where Fruit = $'Apple$'". 3. Stop the Wireshark recording and search for the response frame from the SQL server using the filter "TDS". 4. Select the frame and click on the line "Token – ColumnMetaData".

Ok, Now I wanna choose select query in the sqlqueries array:

SELECT _barcode FROM mytable where id = 4

Again I Active/deactive the input then I get my result, here I get 'Peyman Majidi' string by its id.

Output:

'Peyman Majidi'

I can show you how it works by Wireshark by listening to tds protocol.
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.

Haskins

I'm so happy to have found this! I'm struggling to do SQL insert operations from s7-1200 plc, and the Siemens pdf guide you linked seems to be no longer available. Please can you upload your sample TIA Portal project? It would be sooooo helpful! – Arbitration

Answer 2

After a lot of searches and reading documents, I found out Siemens still developing the MSSql Server Module, and now, it isn't completed. The data will be inside the Receivebuffer: InstLsql_Microsoft.statRcvData.bytes[]. But you have to search for your information.

But still, the best way to read and write on PLC Registers is using S7netplus library (A .NET Library for Siemens S7 Connectivity). S7.Net Plus is a continuation of the work done on the S7.Net project by Juergen1969. I found the library simple and effective, but the project has languished unchanged since late 2009.

Download and install on C# Project:

PM> Install-Package S7netplus -Version 0.4.0

To create an instance of the driver you need to use this constructor:

public Plc(CpuType cpu, string ip, Int16 rack, Int16 slot)

Example:

This code creates a Plc object for a S7-300 plc at the IP address 127.0.0.1, for a plc in rack 0 with the cpu in slot 2:

Plc plc = new Plc(CpuType.S7300, "127.0.0.1", 0, 2);

Connecting to the PLC

public void Open()

For example this line of code open the connection:

plc.Open();

Disconnecting from the PLC

public void Close()

For example this closes the connection:

plc.Close();

Read bytes / Write bytes

The library offers several methods to read variables. The basic one and the most used is ReadBytes.

public byte[] ReadBytes(DataType dataType, int db, int startByteAdr, int count)

public void WriteBytes(DataType dataType, int db, int startByteAdr, byte[] value)

This reads all the bytes you specify from a given memory location. This method handles multiple requests automatically in case the number of bytes exceeds the maximum bytes that can be transferred in a single request.

dataType: you have to specify the memory location with the enum DataType

public enum DataType
{
    Input = 129,
    Output = 130,
    Memory = 131,
    DataBlock = 132,
    Timer = 29,
    Counter = 28
}

db: the address of the dataType, for example, if you want to read DB1, this field is “1”; if you want to read T45, this field is 45. startByteAdr: the address of the first byte that you want to read, for example, if you want to read DB1.DBW200, this is 200. count: contains how many bytes you want to read. Value[]: array of bytes to be written to the plc. Example: This method reads the first 200 bytes of DB1:

var bytes = plc.ReadBytes(DataType.DataBlock, 1, 0, 200);

More Documention is here:
S7.Net documentation

Haskins