SQLTeam.com | Weblogs | Forums

ForEachLoop - SQL Task scope


#1

Hello, I am trying to perform an SQL statement on all databases on a PDW appliance. I would normally do this with a CURSOR, but the PDW appliance does not recognize CURSORS. I have resorted to using ForEachLoop container with 'Execute SQL Task' objects.... My initial SQL task contains the following code -

SELECT 'USE ' + NAME FROM sys.databases
WHERE database_id > 4 

I pass the output to the ForEachLoop as a parameter. The ForEachLoop has 2 additional 'Execute SQL Task' inside. The first SQL Task executes

USE DatabaseName1 

A 2nd SQL task follows with the code that I would like to execute on DatabaseName1.

CREATE STATISTICS name on table

For some reason, I do not think that the 2nd SQL task is being applied to DatabaseName1 because I should see new objects created in the remaining databases collected from the code in the db list.

Is this a scope issue? How can I get code in each SQL Task to 'see each other' ?

Regards