Have you tried adding an 'OUTPUT INSERTED' clause in your SP? Without thinking about this too deeply, I'm wondering if this will get you the result set you need.
I've moved on to another project, but I'll circle back and try this out in the next couple weeks. I think you may be on to something.
I'm still unsure about this issue that's been characterized as "the DCT doesn't support temp tables", even after the NI AE "confirmed" it. The DCT uses ADOX to do all of its work - the VIs are mostly a set of wrappers around calls to ActiveX property and invoke nodes on a set of ADOX classes. There ARE a few limitations I've run into over the years, but mostly they're related to DCT design decisions made about mapping LV datatypes to ADO datatypes, and some weirdness about handling DB nulls, etc. And the datatype mapping issues are, I think, partly due to the variability in datatypes available across the large number of underlying database engines that MDAC supports. Personally speaking, I've only used it against Jet and SQL Server.
But the DCT, in this case, is just calling an Execute method on a Command object, and getting return parameters (if any) and a recordset (if any) in return. I'm tempted to believe that your original problem may have had more to do with permissions - was the logged-in user running the LabVIEW application the same userID who verified that the SP worked correctly when it was tested in SSMS? If you're like me, you are a DB admin whose queries always work when in SSMS, but then the LabVIEW executable runs out on the factory when the PC is logged in to a production account, and... hunh.
Permission failures in SQL-S can be subtle. Since I haven't used temptables, I don't know what permissions need to be held to instanciate them, and whether the user automatically holds SELECT permissions on the temptable just created.
Just food for thought. But I'm glad that you were able to reformulate your SP to just use an OUTPUT clause - seems cleaner that way, to me.
This was all running off my local DB, which is a copy of what we have running out on the factory floor. I am working on developing new database integration into older tools so we can capture manufacturing data a little better. That being said, it could be a permissions thing. None of us have a real solid grip on database managment and we are kind of learning as we go. I know we do have schemea setup that limit permissions between being on the factory floor and developing, but I have no idea how that would effect what I'm working on. I'm just a labview guy who has been drafted into the SQL army :-)
Thanks David, I will put a PO in for the book. I have been looking for a good book on database design.
here is a solution that works for me.
Problem: ODBC Connection, Declare variables and get Result of "Select" SQL Statement
Hope it helps you