LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

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

Solved!
Go to solution

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. 

0 Kudos
Message 1 of 19
(2,385 Views)

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?

0 Kudos
Message 2 of 19
(2,381 Views)

@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?

0 Kudos
Message 3 of 19
(2,378 Views)

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

0 Kudos
Message 4 of 19
(2,374 Views)

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.

0 Kudos
Message 5 of 19
(2,363 Views)

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.

Ian M.
National Instruments
0 Kudos
Message 6 of 19
(2,336 Views)
Highlighted

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.  

0 Kudos
Message 7 of 19
(2,333 Views)

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

Ian M.
National Instruments
Message 8 of 19
(2,321 Views)

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.

 

 

0 Kudos
Message 9 of 19
(2,318 Views)

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

Ian M.
National Instruments
0 Kudos
Message 10 of 19
(2,315 Views)