2

I have a VERY long query, so to make it readable I divided it in two.

Select field_1, field_2
from table
into #temp;

and 

Select field_1, field_2, field_1b
from #temp TMP 
Inner Join table_2 ON TMP.field_2b = field_2

That works fine in SQL Server Management Studio.

Now I need to make a job that loads this data to another database. I have some import-export wizards projects that work without problem.

In this particular case I can't make the wizard work, it throws an error on #temp.

I tried 

 set fmtonly off

but I get timeout (the timeout value is set to 0)

  • Source is SQL Server 2014 (v12)
  • Destination is SQL Server 2016 (v13)

Any Idea of how can I make this work, my last resource is to make one query out of two, but like to try to maintain some order and readability if possible.

Thank you!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alejandro
  • 519
  • 1
  • 6
  • 32
  • By "SQL Server 12" - do you mean SQL Server **2012** - or do you mean SQL Server **version 12** (= SQL Server **2014**) - please be more precise! – marc_s Jul 16 '18 at 19:23
  • 1
    Temp tables are only accessible from the same session and I guess your job is creating one session per step. Check this out https://stackoverflow.com/q/18614344/7415759 – Pablo Henkowski Jul 16 '18 at 21:11
  • @marc_s I mean version 12 (SQL Server 2014) – Alejandro Jul 16 '18 at 21:19
  • @PabloHenkowski I tried ## (global temp) with the same results – Alejandro Jul 16 '18 at 21:20
  • @Alejandro how are you importing/exporting? Via SSIS wizard, exporting the result of query 2 to a flat file? Or are you using LinkedServer? – Pablo Henkowski Jul 16 '18 at 21:34
  • @PabloHenkowski yes, using import-export wizard from SSDT, the servers are not linked. – Alejandro Jul 16 '18 at 22:21
  • on your connection manger, do you have RetainSame Connection = True? If not then it will drop all temp tables each time it moves to another step. – Holmes IV Jul 18 '18 at 23:25
  • https://www.mssqltips.com/sqlservertip/2826/how-to-create-and-use-temp-tables-in-ssis/ http://www.sqlerudition.com/how-to-use-temp-table-in-ssis/ – Yahfoufi Jul 19 '18 at 08:08
  • 3
    I imagine the problem is your temporary table, which is only going to exist for the duration of the connection. Instead of using a temporary table, create a **persistant** staging table. Perhaps use create a new database, or a new schema, and create the table there. Then, before your ETL process, make sure your `TRUNCATE` it. – Thom A Jul 19 '18 at 08:43
  • Have u tried different options, try merge procedures and create a staging table instead of temp tables – Ven Jul 19 '18 at 13:13

3 Answers3

1

If you want to split your query into two purely for readability purposes, then there are alternative ways of formatting your query, such as a CTE:

WITH t1 AS (
    SELECT field_1, field_2
    FROM table
)
SELECT t1.field_1, t1.field_2, table_2.field_1b
FROM t1
    INNER JOIN table_2 ON t1.field_2 = table_2.field_2b;

While I can't begin to speculate on performance (because I know nothing of your actual query or your underlying schema), this will probably improve performance as well because it removes the overhead of populating a temp table. In general, you shouldn't be compromising performance just to make your query 'readable'.

knuckles
  • 388
  • 2
  • 9
  • So simple and excellent solution showing me that I was trying to "force solve" something in a way (using temp tables) when I could use this kind of statement (that I wasnt aware of), plus saving me from creating a temp table. Thank you – Alejandro Jul 19 '18 at 14:15
1

First and foremost, please post the error message. That is most often the most enlightening point of a question, and it can enable others to help you along.

Since you're using SSIS, please copy/paste what gets printed in the "output" window? That's where your error messages would go.

A few points

fmtonly has a rather different purpose, so if the difference between fmtonly on/off would be whether or not you get headers or headers and data. Please see the link below with the documentation for fmtonly:

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-fmtonly-transact-sql?view=sql-server-2017

Have you tried alternatives to the temp table solution? That seems to be the most likely culprit. Here are some alternative ideas:

  1. using a staging table (permanent table used for ETL); you would truncate it, populate it using the two queries mentioned in your question, pass it along to the destination server and voila!
  2. Using a CTE. This can avoid the temp table issues, though they aren't the easiest to read.
  3. Not breaking up the query for readability. This may not be fun, though it will eliminate the trouble.

I have other points which may help you along, though, without the error message, this is enough to give you a head start.

Eli
  • 2,538
  • 1
  • 25
  • 36
  • Thank you I was trying to solve something with a very bad approach, I will use the very simple @knuckles solution, obviously your ideas are very welcome and I'll take note of them! – Alejandro Jul 19 '18 at 22:18
0

This is hard to identify problem when you are not posting how you loads the data to another database

But I suggest you use semi-permanent Temp table.

So you create real table at the start of job and drop the said table when the job is done. Remember you can have multiple steps on the job, it's doesn't have to be in the same query.

Adinugraha Tawaqal
  • 361
  • 1
  • 7