Data Stream In - Temp table SQL Server

I'm trying to upload records into a temp table on a SQL Server. The connection for the temp table has been used previously in the workflow but I don't seem be able to "re-use" the same connection. If I create a new connection for the Data Stream In, it works fine. But when using the same connection that was used previously in the same workflow, I get this error:

In Database

In-Database

Accepted answers

Kieran

I think I might have figured out whats going on here when doing up a mock dataset/workflow for you. When I started testing it, surprisingly it worked. I ran a few more tests to figure out why it worked, and realised that the error is happened because the workflow isnt getting enough time to pull down all the records from the first connection, before trying to upload them to the second connection.

When I re-run the workflow and limit the sample to just a few records, it runs just fine! Dissappointing behaviour. But at least I know to account for it now.

Thanks for your help Sean...

All comments

SeanAdams

Hey Kieran,

Temp tables can be interesting in SQL - they are context / connection / transaction specific - so if I'm on a specific connection and pump data out to a temp table, as soon as SQL hits a commit it scrubs that temp table. So what we always tried to do, is if you need to share a temp table across different processes (where one may commit before the other) is create a regular table, but call it "tempWorkingXX" so that you can recognise it and so that SQL doesn't throw it away. Temp tables also live in a different place (from recollection, they live in TempDB, not in your own database) and as a result, the SQL server itself is responsible for garbage collecting these - so if you disconnect from the server, it will also throw away your temp table.

This may not be the problem you're having, but I have spent many nights unpicking synchronisation issues across triggers or stored procedures on a SQL server that use temp tables.

If you try to create a normal table (named with some flag to show that it's a working table), you should get round this - but if not, feel free to post a stripped down version of your flow, with the input and outcome you're trying to achieve, and we can see if we can get you to where you need to be.

Cheers

sean

Kieran

Thanks Sean. I think the issue I have is a little different. When I say "re-use" I dont mean reuse the temp table I created earlier, I just mean the same connection. So when I add a "Data stream In" too, I want to be able to select the same connection I was using earlier. Alteryx doesnt seem to like that though per the error message

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703