Variable Tables

Toad Data Point & Toad Intelligence Central
1

Declare @ActivityHldr TABLE (USR_NAME CHAR(40),ACTIVITY_DATE CHAR(20),TABLE_ID_NAME VARCHAR(255),FIELD_NAME CHAR(60),OLD_VALUE VARCHAR(255),NEW_VALUE VARCHAR(255))

INSERT INTO @ActivityHldr(USR_NAME,ACTIVITY_DATE,TABLE_ID_NAME,FIELD_NAME,OLD_VALUE,NEW_VALUE)

ACTIVITY.USR_NAME,

ACTIVITY.ACTIVITY_DATE,

ACTIVITY_VALUES.FIELD_NAME,

ACTIVITY_VALUES.OLD_VALUE,

ACTIVITY_VALUES.NEW_VALUE

INNER JOIN PMM.dbo.ACTIVITY ACTIVITY

AND (ACTIVITY_CHILDREN.ACTIVITY_IDB = ACTIVITY.ACTIVITY_IDB))

ON (ACTIVITY_VALUES.ACTIVITY_ID = ACTIVITY.ACTIVITY_ID)

WHERE (ACTIVITY.ACTIVITY_DATE >= Case When datepart(dw,getdate()) = 2 Then getdate()-3 Else getdate()-1 End)

UPDATE @ActivityHldr

SET TABLE_ID_NAME = UPPER(TABLE_ID_NAME)

declare @ItemHldr TABLE (ITEM_NO CHAR(15),DESCR VARCHAR(255), [NAME]CHAR(40))

SELECT DISTINCT ITEM.ITEM_NO, ITEM.DESCR, LOC.[NAME]

PMM.dbo.REQ REQ,

PMM.dbo.REQ_ITEM REQ_ITEM,

PMM.dbo.LOC LOC

AND (REQ_ITEM.ITEM_IDB = ITEM.ITEM_IDB)

Hello, I have written a query using variable tables and the query executes fine in Toad. The results are correct. However, when I try to run this using automation in Toad it errors out saying the I must declare @results. I don’t undertand becuase it is declared and the query works fine if I run it by itself. I need to be able to have automation run this and email the report everymorning. Any help would be appreciated.

SELECT DISTINCT
SUBSTRING(ACTIVITY.TABLE_ID_NAME, 8,15) AS TABLE_ID_NAME,FROM (PMM.dbo.ACTIVITY_CHILDREN ACTIVITY_CHILDRENON (ACTIVITY_CHILDREN.ACTIVITY_ID = ACTIVITY.ACTIVITY_ID)INNER JOIN PMM.dbo.ACTIVITY_VALUES ACTIVITY_VALUESAND (ACTIVITY_VALUES.ACTIVITY_IDB = ACTIVITY.ACTIVITY_IDB)AND (ACTIVITY.TABLE_ID_NAME LIKE ‘Item :%’)INSERT INTO @ItemHldr (ITEM_NO, DESCR, [NAME])FROM PMM.dbo.ITEM ITEM,WHERE (REQ_ITEM.ITEM_ID = ITEM.ITEM_ID)AND (REQ_ITEM.REQ_ID = REQ.REQ_ID)

2

Hello John,

I created my own script trying to repeat the structure of yours as close as possible. I created two table variables - @ActivityHldr based on AdventureWorks2008.Person.Person table and @ItemHldr based on AdventureWorks2008.Person.PersonPhone table. Then I created the result @result variable with data joined from @ActivityHldr and @ItemHldr variables and produced final select from @result. All worked just fine in 2.6, 2.7 and current 3.0 version.

Seems like we need additional information from your side. What version of TDA are you working with? If you could post the screenshot showing the error and the log file created with Logging Level = Verbose, I would be able to further investigate this.

Thanks,

Igor.

3

Hey Igor, below I've attached a screen shot from the automation section of Toad. The version I'm using is 2.7.0.348. Also, I noticed that in that log it is showing multiple exports of the file I want. It should only export it once after the query is run. Also, the exports it is showing exported before the error are not actually even being created, unless the error is deleting them? I'm pretty new at this program, so still learning it.
Automation Error.jpg

4

Hi John,

 The Export Script activity will execute a whole script and put any result sets into a variable. The Save to File activity parses a script, SQL statement by SQL statement and tries to export multiple result sets. When we do this to your SQL Server script it is parsing off the declare statements and causing the error.

 I haven't looked at your file. If you need to just execute the script in it's entirety then use the Export Script activity. If you need to export the results, try using a single SQL statement and definig the variable outside of the script using the set variable activity. See my blog posting on this topic.

Debbie