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

[DavidBoyd · Accepted solution]

MGould,

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.

Dave

[MGould]

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.

[MGould]

For anyone interested I've added this to the idea exchange:

http://forums.ni.com/t5/LabVIEW-Idea-Exchange/Add-Support-for-Temporary-Tables-in-the-Database-Conne...

[MGould]

I just tried this and it appears to work.  Thanks for tip!

[DavidBoyd]

You're welcome!

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.

Best regards,

Dave

[MGould]

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 🙂

[DavidBoyd]

I can appreciate your closing comment - my background is automated test with years of programming (HP Basic, C, and about 15 years of LV at this point), but I'm purely self-taught when it comes to DBs over the past 10-12 years. If I can get away with offering you advice at this point - forgive me if you're past this, and maybe someone else reading this will find it useful - get a good book on relational database theory. I got one by Rebecca Riordan from Microsoft Press - several years ago but the theory stays the same - and it was invaluable when helping me decide how to structure the tables and keys. After you've started to populate the tables, it's a lot more heartache to go back and fix bad design.

Good luck!
Dave

[MGould]

Thanks David, I will put a PO in for the book.  I have been looking for a good book on database design.

[tim_vdh]

Hello everybody,
here is a solution that works for me.

Problem: ODBC Connection, Declare variables and get Result of "Select" SQL Statement

Hope it helps you

 

[R^2]

Very cool. This method works for me.

What I found, if I run the query in Microsoft SQL Server Management Studio, after the query runs look in the Messages tab (beside the Results window) it will say something like:

(5896 row(s) affected) 

(5896 row(s) affected) 

(1257row(s) affected) 

(858 row(s) affected) 

This would indicate that I need at least 4 semicolons for this example to work. In my above example, I create 3 temp tables to eventually combine to get the data that I actually need. The last 858 is where my output is actually generated.

Keep in mind, if the query is setup to display data for multiple steps then you need to be selective on what data you keep or check all the responses for the data you need. 

@tim_vhd, Thank you for the solution!