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

[MGould]

I have this SQL query:

DECLARE @DataTypeTable TABLE (
Name varchar(128),
TypeID INT)

--Add comma delimeted data type names to temp table
INSERT INTO @DataTypeTable (Name)
SELECT * FROM WhatWeShouldDoRead.func_Split(@DataTypeTrimmed,',')

SELECT Name FROM @DataTypeTable

Which takes a comma delimited string and returns the string as a table.  It works correctly in Microsoft SQL Server Management Studio.  When I run this as a stored procedure  I get back nothing.  There are no errors, SQL or otherwise.  I've verified that I am connected to the correct database and that the stored procedure is loaded by changing the no error string that is reported from this stored procedure (that code is not shown in the above example).  Has anyone seen this problem before, or have any experiance with SQL/Labview interfaces to tell me what I'm doing wrong?

Thanks in advance. 

[MGould]

I should also mention that I removed the "INSERT INTO" command and I get the correct table from the function.  The problem seems to revolve around the return of a temporary table.  Any suggestions on returning results for a temp table?

[smercurio_fc]

---

@MGould wrote:

I should also mention that I removed the "INSERT INTO" command and I get the correct table from the function.  The problem seems to revolve around the return of a temporary table.  Any suggestions on returning results for a temp table?

---

Which function are you referring to?

[MGould]

WhatWeShouldDoRead.func_Split(@DataTypeTrimmed,',')   is a function that splits a comma delimited string into a temp table which it passes back.

[MGould]

After doing some more research it appears that the database toolkit cannot interface with any table results from any type of temp table.  It may have to do with the fact that MS SQL 2008 stores temp tables in a seperate database (tempdb) and not the database you are currently connected to.  See this link for a good artical on temp tables:

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

If possible,  I'd like a someone to prove me wrong, but for now will have to settle for exporting the contents of a temp table through a string.

[ianidas300]

One debugging step you might be interested in trying is to run your SQL query as a stored procedure within a SQL servor and see if similar issues arise as seen with the LabVIEW database toolkit.

[MGould]

Thanks for your reply,

The store procedure works flawlessly in the server.  Its when I try to run it through labview that I have any problem.  

[ianidas300]

From the research that I've done I believe your conclusion is correct regarding temp tables and our database toolkits not being compatible.

[MGould]

That is unfortunate. Is the tool kit still being supported?  Is there any chance of this being rectified in the future?

My boss came up with a work around, which is to output the temp table as a comma delimited string and then parse the string in labview.  The sting can be formed by using the COALESE command with a clever SELECT statement.

[ianidas300]

The tool kit is still being supported but the temp table that you are trying to use in your query is not compatible with our database connectivity toolkit. The best way to get the proper visibility for this issue would be to address it in the LabVIEW Idea Exchange at ni.com/ideas