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