Toad Data Point - SQL Server - #Temp Tables

Toad Data Point & Toad Intelligence Central
1

I was using version 3.2 and recently got a new computer, after which I installed version 3.3 base.

Existing automation tasks, which contain a single ‘select to file’ activity, are failing because: “Toad has detected some symbols within the SQL indicating you might be using bind variables…” The problem is that the SQL statement has temp tables involved. I didn’t design the query, so that’s the way it is.

Is there a setting in Toad that I can somehow ignore this validation? The SQL runs fine from the editor. And it ran fine in Automation back in the older version.

2

Hi,

Please note that this is the SQL Optimizer for SQL Server Forum. You will have to post your question in the Toad Data Point forum if your question is about using Toad Data Point.

Thanks,

Alex

3

Can you send your SQL Script and automation script with log file to me directly so I can take a look at how to fix?

debbie.peabody@quest.com

4

I entered CR110454 for this but it will help if I can get the scripts.

5

Debbie, thanks for the help! I think I pinpointed the problem and found a workaround.

The problem seems to be a formatting issue with the SQL Script.

In an automation file, with a single activity [select to file]

This does not work:

USE [comtrac_dw];

SELECT TOP 1 *
INTO #TMP1
FROM dbo.Codes

SELECT TOP 1 *
INTO #TMP2
FROM dbo.Codes

SELECT rs.*
INTO #TMPFINAL
FROM (SELECT #TMP1.* FROM #TMP1 UNION ALL SELECT #TMP2.* FROM #TMP2) rs

SELECT * FROM #TMPFINAL

However… if I format my subquery with new lines, it works fine.

SELECT TOP 1 *
INTO #TMP1
FROM dbo.Codes

SELECT TOP 1 *
INTO #TMP2
FROM dbo.Codes

SELECT rs.*
INTO #TMPFINAL
FROM (SELECT #TMP1.* FROM #TMP1
UNION ALL
SELECT #TMP2.* FROM #TMP2
) rs

This bug appears in other manifestations such as JOIN - but I’ve found as long as the #tmp table is listd on a different line, it seems to work ok.

1 Like
6

I am glad you found a work around. I did some testing on the user of SQL Server temp tables and automation and I do see some issues here. I entered CR110492 to look into deeper support for these types of scripts.

Debbie