Troubleshooting
Why a WinCC Advanced SQL Read Script Returns Nothing
Short answer
The read script fails for faults the working INSERT never meets: a missing space turns SELECT * FROM into FROMKayitlar, a dangling ORDER BY has no column, and MoveFirst after MoveNext pins the cursor on row one. Clear them in order: DSN visible to the runtime, SQL text valid, recordset non-empty, value inside the DInt range. Each fault masks the next.
On this page
- Why does the INSERT work while the read returns nothing?
- What is wrong with the SQL string itself?
- Why does the runtime say the data source name was not found?
- How do you know the recordset actually holds a row?
- Why does the tag always end up on row one?
- When does the bigint column overflow the DInt tag?
- In what order should you clear these faults?
- Questions asked
A read script in WinCC Runtime Advanced that returns nothing, while an INSERT into the same database works, is usually not a database fault. The read path stacks several independent defects: the SQL text is malformed before the driver ever parses it, the navigation logic undoes itself, and a 64-bit key is being pushed into a 32-bit tag. On Error Resume Next swallows the first error, so the tag simply never changes. Clear the faults in the order they mask each other: the DSN must be visible to the runtime process, the SQL string must parse, the recordset must hold a row, the value must fit the tag, and the cursor must be read at the right moment. The examples below come from a TIA Portal V13 project on WinCC Runtime Advanced reading SQL Server through the MSDASQL provider over a DSN.
Why does the INSERT work while the read returns nothing? #
Because the read path carries faults the write path never meets: the SELECT text is malformed twice, the result navigation resets itself, and On Error Resume Next hides the first failure — a single INSERT with no recordset meets none of them. A clean compile in the script editor proves only that the VBScript is syntactically valid; the SQL statement is a string literal that nothing parses until the driver receives it at runtime.
What you see on the panel points straight at the section that fixes it:
| Symptom on the panel | Where the fault sits |
|---|---|
| Alarm: data source name not found and no default driver specified | DSN visibility |
| Syntax error near FROMKayitlar or at the end of the statement | SQL text |
| Tag stays 0 and never changes | Empty recordset, cursor sequence or overflow |
| Error 6 overflow | bigint into DInt |
What is wrong with the SQL string itself? #
Two syntax errors: the concatenation "SELECT * FROM" & "Kayitlar" produces SELECT * FROMKayitlar with no space between FROM and the table name, and the second statement ends with ORDER By and no column; both are rejected before a single row is read. Build the statement in one variable and display it with ShowSystemAlarm before executing, so the exact text the driver will see is on the panel.
The faulty pair, exactly as it fails:
Kayitlar = "SELECT * FROM" & "Kayitlar"
Kayitlar = "SELECT * FROM " & "Kayitlar" & " ORDER By "
The first Execute in this pattern runs with no Err.Number test after it, so its failure is invisible; test after every Execute. The corrected literal puts the space inside the quotes and names a real column:
sqlText = "SELECT KeyCol FROM Kayitlar ORDER BY KeyCol"
KeyCol is a placeholder for the real bigint column name. Paste the displayed string into SQL Server Management Studio against the same database: if it returns rows there, the string is valid and any remaining failure belongs to the session, not the text. Prefer naming the one column you need over SELECT * across the table's 11 columns — rst.Fields("KeyCol") survives a column reorder, rst.Fields(0) does not.
Why does the runtime say the data source name was not found? #
The DSN does not exist in the ODBC administrator the runtime process reads: create it as a System DSN in the bitness that matches the runtime, and on 64-bit Windows remember that the 32-bit list lives in odbcad32.exe under SysWOW64 and the two lists are separate. Take two readings right after conn.Open: Err.Number must be 0 and conn.State must be 1, meaning open. The existing ShowSystemAlarm line already prints both; read the description text, because ADODB's error number is generic and the driver message carries the cause.
Three cases, by message:
- "Data source name not found and no default driver specified": the DSN is invisible to the runtime. A User DSN created under your login does not follow the runtime's account — make it a System DSN, in the ODBC administrator matching the runtime's bitness; if the bitness is unclear, create it in both.
- DSN exists, the open still fails: the DSN's server setting decides which SQL Server instance answers. A machine that gained a second instance later, such as an Express edition beside the WinCC one, offers more than one server in the wizard; select the instance holding the reporting table, finish, and press Test Data Source. Express installs often ship with TCP/IP disabled until it is enabled in SQL Server Configuration Manager.
- Open succeeds, the first query reports an invalid object name: the default database is wrong. The write example sets
Initial Catalog=egitimin its connection string; the read example sets nothing and depends on the DSN default. Set the default database in the DSN, or add Initial Catalog to both strings consistently.
How do you know the recordset actually holds a row? #
Test rst.EOF immediately after Execute: EOF and BOF are both True only on an empty recordset, and a fresh recordset already sits on its first row, so MoveFirst is never needed and RecordCount cannot help because the default cursor from Connection.Execute is forward-only, read-only and reports -1. One If rst.EOF Then right after Execute answers the question; if it fires, run SELECT COUNT(*) FROM Kayitlar in Management Studio against the database the DSN defaults to — zero means an empty table or a query that filters everything out.
Two cleanups while you are in there. The pre-created Set rst = CreateObject("ADODB.Recordset") is discarded the moment Set rst = conn.Execute(...) runs, so delete the first line. And if the rows are destined for a grid on the panel rather than one tag, an empty display has further causes of its own — resolving a WinCC VBScript DataGrid fed by an empty ADODB recordset works through them.
Why does the tag always end up on row one? #
Because MoveNext is followed by MoveFirst inside the same call, the cursor lands back on row one whatever the Next tag says, and since every call opens a new connection and recordset, cursor position cannot persist between calls at all. A forward-only cursor will MoveNext but never MovePrevious, so paging must be done by value, not by position: keep the last key the operator saw and query past it.
On Error Resume Next
Dim conn, rst, sqlText
sqlText = "SELECT KeyCol FROM Kayitlar WHERE KeyCol > " & _
SmartTags("LastKey") & " ORDER BY KeyCol"
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=MSDASQL;DSN=Deneme"
If Err.Number <> 0 Then
ShowSystemAlarm "Error #" & Err.Number & " " & Err.Description
Else
Set rst = conn.Execute(sqlText)
If Err.Number <> 0 Then
ShowSystemAlarm "Error #" & Err.Number & " " & Err.Description
ElseIf rst.EOF Then
ShowSystemAlarm "No row past key " & SmartTags("LastKey")
Else
SmartTags("DB1") = CLng(rst.Fields("KeyCol").Value)
SmartTags("LastKey") = SmartTags("DB1")
End If
rst.Close
End If
conn.Close
Set rst = Nothing
Set conn = Nothing
KeyCol and LastKey are placeholders for your real column and tag names; SmartTags, ShowSystemAlarm and the ADODB calls are the runtime's documented script interface. Concatenate only numeric tag values you control into SQL text. The test that proves the fix: each button press advances the displayed key, the "No row past key" alarm appears only at the end of the table, and no error alarm fires on the way. This snippet assumes the key fits the DInt tag — the next section decides that.
When does the bigint column overflow the DInt tag? #
Above 2,147,483,647: a SQL Server bigint is a signed 64-bit integer, a DInt tag is the IEC 61131-3 signed 32-bit integer spanning -2,147,483,648 to 2,147,483,647, and CLng on a value past the top raises VBScript error 6 while On Error Resume Next leaves the tag holding its old value. Run SELECT MAX(KeyCol) FROM Kayitlar in Management Studio and compare the answer with the DInt ceiling — the same class of fault as why an HMI writes the wrong PLC value above 9: a tag type too small for the number.
Worked example, with illustrative numbers:
SELECT MAX(KeyCol)returns 2,684,354,560.- 2,684,354,560 - 2,147,483,647 = 536,870,913 past the DInt ceiling.
- Decision: out of range. CLng overflows, the assignment never completes, and the untouched tag looks exactly like "nothing happens".
Contrast: if MAX returns 2,000,000,000, then 2,147,483,647 - 2,000,000,000 = 147,483,647 of headroom, the key fits, and the explicit conversion SmartTags("DB1") = CLng(rst.Fields("KeyCol").Value) is the correct line. Convert explicitly even inside the range: the ODBC layer can deliver a bigint as a Decimal or a String variant, and an implicit assignment then behaves unpredictably — a temporary ShowSystemAlarm with TypeName(rst.Fields("KeyCol").Value) shows what the driver actually returns. When the key is out of range, do not carry it in a tag at all: page on a column that fits and read a smaller column for what the operator sees. If the key must live in a tag, the HMI tag type list of your TIA Portal version decides whether an integer wider than 32 bits exists; where it does not, this decision stands.
In what order should you clear these faults? #
In this order — DSN visible, SQL valid, row present, value in range, cursor read at the right moment — because each fault masks the next and no later check means anything until the earlier one passes:
- Connection: after
conn.Open, Err.Number = 0 and conn.State = 1. The DSN is a System DSN in the ODBC administrator matching the runtime's bitness, its server field names the instance holding the table, and its default database holds Kayitlar. - SQL text: the displayed statement reads
SELECT <real column> FROM Kayitlar ORDER BY <real column>, with the space inside the literal, and returns rows in Management Studio under the DSN's login. - Row presence: rst.EOF is False immediately after Execute; if it is True, the table or the filter is the problem, not the script.
- Value fit:
SELECT MAX(KeyCol)is at or below 2,147,483,647 for a DInt tag; above it, the key stays out of tags. - Cursor: no MoveFirst after MoveNext; the row is read after the optional MoveNext, and paging across calls goes by key value.
With one value reading correctly, feeding the whole recordset into a grid for the operator is the next step — displaying a WinCC SQL table from an ADODB recordset covers that wiring. And if the underlying job is logging rather than operator reads, logging PLC data to SQL through a gateway instead of the HMI is the sturdier architecture. None of this belongs in a safety path: reporting scripts live on the operator layer, and a failed read must degrade to an alarm, never to a machine state.
Questions asked
Why does the panel show error 6 overflow only after months of working?
CLng raises VBScript error 6 when the bigint value crosses 2,147,483,647, the top of the signed 32-bit DInt range. An identity key grows monotonically, so the script can run cleanly on a young table and start failing the day the key passes that ceiling, which looks like a new fault rather than a range problem. Test Err.Number right after the assignment to catch it.
Should On Error Resume Next stay in the finished script?
While debugging, comment it out: the runtime then stops at the exact failing line instead of continuing with a stale tag. In the production version keep it only together with an Err.Number test immediately after every call that can fail, and clear each error you handle, so one swallowed failure cannot mask the next one.
Does the SQL Server instance have to run on the runtime PC?
No. The DSN's server setting decides which instance answers, so it can sit on the same machine or on a separate server; the runtime needs only a DSN visible to its process and network access to that instance. After installing a second instance, reopen the DSN and confirm the server field still names the one holding the table.
Can RecordCount tell you how many rows came back?
Not on this cursor. Connection.Execute returns a forward-only, read-only recordset, and RecordCount stays at -1 there no matter how many rows exist. If the operator needs a count, run SELECT COUNT(*) FROM Kayitlar as its own Execute call and read the single field it returns.