You are viewing limited content. For full access, please sign in.

Question

Question

Workflow Error when SQL Query to temp table

Workflow
Updated January 8, 2019
asked on January 8, 2019 • Show version history

I just want to do a simple select to a temp table where data has been built.

Is workflow not able to query and return data from a temp table in SQL?

 

SELECT *
FROM #TempBalance

When the Query is executed in SSMS it works just fine.

I can build a table that loads this information and then delete the table information after I am done with it.  I just wanted to see if anyone has experienced this with Temp Tables before and if this is expected behavior.

 

Thanks!

 

 

0 0

Answer

SELECTED ANSWER
replied on January 8, 2019

I think what Miruna is getting at is that MSSQL temp tables are only visible within the session that created them, so for you to be able to successfully interact with a temp table in SSMS you must have created that table earlier in your SSMS session.  I think that restriction on temp tables is going to make them difficult to take advantage of from Workflow, unless you can guarantee that the query is run in the same connection that created the table.

2 0

Replies

replied on January 8, 2019

I assume you are using "Custom Query", did you configure your Data Source and validated that the connection is successful? 

Honestly, I am not 100% sure if you can select temp databases using the ODBC driver configuration, if you verified the question above, checking the data source config might be your next step.

0 0
View 6 previous replies
replied on January 8, 2019

Yes the connection works for many other queries in other workflows currently.

I have 30 workflows with some having up to 9 queries using that same one.

I have already built it to load the data instead into an actual table and I query from that but I wanted to make sure this was normal behavior.

I don't use temp tables often but when I do it would be nice to use them as intended within Laserfiche as well.

0 0
replied on January 8, 2019

Laserfiche does not do anything here, it just passes the query to the driver. The error comes from the driver.

I can't see the whole query, but maybe you have a typo on the definition? Also, different data activities make their own connections to the data source, so if you make the table in one activity and try to use it on another, that could be the cause as well.

0 0
replied on January 8, 2019

There query is above in my first post.  It's not complicated.  Just select * from #TempBalance.

 

I can't believe there is a typo is it 100% works perfect within SSMS.  

I have even tested the same query with Direct and not ODBC to verify and the results are the same.

 

 

 

0 0
replied on January 8, 2019

Have you tried specifying the database? Usually my temp tables are in tempdb not the database associated with the data source configuration.

Try adding USE [tempdb] before the select, but it also might depend on what is creating the temp table in the first place.

Like Miruna said, each activity is a separate connection. When you're in SSMS you're more likely to be using the same connection, and probably a different user account, to test.

0 0
replied on January 8, 2019

Is your activity actually making the table right there before the SELECT statement? The SSMS code looks like it's part of a bigger statement.

0 0
replied on January 8, 2019

I just wrote it below just so I can manually run the select.  I do not run it all in the same query at the same time.  The select is designed to only happen through Laserfiche in a query.  

I don't drop the table right now for testing.  The drop is manual.

And my stored procedure is set to use mydb. 

 

Though I can use the following queries in SSMS and they both provide the same information.

select * from tempdb..#TempBalance

select * from [mydb].dbo.#TempBalance

I have not tested connecting the Workflow data source to tempdb.

 

 

 

 

0 0
replied on January 8, 2019

The bigger question is how/when is the temp table being created?

Even if you plan on manually dropping the table, that doesn't necessarily mean that it is still available to the LF process.

0 0
SELECTED ANSWER
replied on January 8, 2019

I think what Miruna is getting at is that MSSQL temp tables are only visible within the session that created them, so for you to be able to successfully interact with a temp table in SSMS you must have created that table earlier in your SSMS session.  I think that restriction on temp tables is going to make them difficult to take advantage of from Workflow, unless you can guarantee that the query is run in the same connection that created the table.

2 0
replied on January 8, 2019

Right, temp tables are not accessible after the fact. So if you make it in SSMS, you can't query it from WF. You can't even query from another session of SSMS logged in as the same user. You have to make it and query within the same session, be it SSMS, a single WF activity or some other SQL client.

2 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...