SQL Query Works in MS SQL Server 2008 but not when using Database Toolkit

[DavidBoyd]

R^2 (cool username, BTW!),

I'm going to point you to an earlier thread, specifically to a post I made there, which itself even links to one or two other threads.  If you have the time, I strongly recommend you review them all.  There is a lot you can do with the Database Connectivity toolkit to write a really robust solution to any database problem, but it mostly revolves around a few salient points:

 - no good can come from putting SQL statements on-the-fly in your LabVIEW code (or counting semicolons, yikes!) - use stored procedures

- the toolkit does have one very longstanding bug related to handling multi-recordset replies, but there is a simple fix

- SQL can help you with some of its built-in support for OUTPUT [INSERTED | DELETED | UPDATED] clauses.

Happy reading! Here's the first thread of the ol' rabbit hole.

Dave

[wiebe@CARYA]

---

@DavidBoyd wrote:

- SQL can help you with some of its built-in support for OUTPUT [INSERTED | DELETED | UPDATED] clauses.

---

SQL Server can help you with that build-in support. AFAIK, The OUTPUT clause isn't standard SQL.

As a friendly warning:

I recently got burned by using "output inserted" on SQL Server. Worked fine on my development machine, cleaned up code a lot.

Then the customer called... Their database is replicated and apparently triggers on tables cause errors when using "output inserted". See sql server - Output with trigger - Stack Overflow and it's link to sql - Cannot use UPDATE with OUTPUT clause when a trigger is on the table - Stack Overflow.

Some solutions include temporary tables (as far as I understand this mess).

I don't quite get why "standard SQL" doesn't have a way to return (automatically created) inserted values. Maybe the 2023 SQL standard does have support for this, but it won't do us much good as 1) apparently you have to pay for it, 2) it's unclear what products support it or when they will, 3) event the "standard SQL" wildly differs between databases, you can't really reuse code anyway...

I don't think I'll ever like (SQL) databases or SQL.