Snippet Name: Detecting_Nested_CTEs

Tags:

Created Date: 2025-06-16 13:12:43
Last Modified Date: 2025-05-31 10:31:41

Dependencies:

DBA Notes:

Sample Output:

Title: Detecting Nested CTEs
Author: user
in SLQ server, i want to do a static code review of the code stored in the [definition] column in sys.sql_modules. specifically, i want to detect if nested Common Table Expressions are used. can you develop code to assist in that detection

Author: ChatGPT
Certainly! To perform a static code review of the code stored in the `[definition]` column in `sys.sql_modules` to detect nested Common Table Expressions (CTEs), you can write a SQL query to extract and analyze this data. Here's a step-by-step approach to achieve this using SQL and a bit of string manipulation:

1. **Extract the code from `sys.sql_modules`**: You'll retrieve the definitions of the modules (views, stored procedures, functions, etc.) from the `[definition]` column.

2. **Search for CTE patterns**: You'll need to look for patterns that match nested CTEs. A simple way to detect CTEs is to search for the `WITH` keyword, but for nested CTEs, you'll have to look for multiple `WITH` keywords within the same definition.

Here's a SQL script that outlines this approach:

```sql
-- Temporary table to store module definitions for analysis
CREATE TABLE #ModuleDefinitions (
    ObjectID INT,
    Definition NVARCHAR(MAX)
);

-- Insert definitions into the temporary table
INSERT INTO #ModuleDefinitions (ObjectID, Definition)
SELECT sm.object_id, sm.definition
FROM sys.sql_modules AS sm;

-- Function to count occurrences of a substring in a string
CREATE FUNCTION dbo.fn_CountSubstringOccurrences (@string NVARCHAR(MAX), @substring NVARCHAR(100))
RETURNS INT
AS
BEGIN
    DECLARE @Count INT = 0;
    DECLARE @Index INT = 1;
    WHILE @Index > 0
    BEGIN
        SET @Index = CHARINDEX(@substring, @string, @Index);
        IF @Index > 0
        BEGIN
            SET @Count = @Count + 1;
            SET @Index = @Index + LEN(@substring);
        END
    END
    RETURN @Count;
END;

-- Query to detect modules with nested CTEs
SELECT 
    md.ObjectID,
    OBJECT_NAME(md.ObjectID) AS ObjectName,
    md.Definition,
    dbo.fn_CountSubstringOccurrences(md.Definition, 'WITH') AS CTECount
FROM 
    #ModuleDefinitions AS md
WHERE 
    dbo.fn_CountSubstringOccurrences(md.Definition, 'WITH') > 1
ORDER BY 
    md.ObjectID;

-- Clean up temporary table
DROP TABLE #ModuleDefinitions;

-- Clean up function
DROP FUNCTION dbo.fn_CountSubstringOccurrences;
```

### Explanation

1. **Temporary Table Creation**: A temporary table `#ModuleDefinitions` is created to store the `ObjectID` and `Definition` from `sys.sql_modules`.

2. **Insert Module Definitions**: Definitions of all modules are inserted into the temporary table.

3. **Function to Count Substring Occurrences**: A scalar function `dbo.fn_CountSubstringOccurrences` is created to count occurrences of a given substring (in this case, `WITH`) in a string.

4. **Query to Detect Nested CTEs**: The query selects modules where the count of the `WITH` keyword is greater than 1, indicating the presence of nested CTEs. The result includes the `ObjectID`, `ObjectName`, `Definition`, and the count of CTEs.

5. **Cleanup**: The temporary table and the function are dropped after the analysis.

This script will help you identify modules that contain nested CTEs by detecting multiple occurrences of the `WITH` keyword in their definitions.

Author: user
in sql server, how can i use the built in function sys.fn_xe_file_target_read_file to read just the latest file

Author: ChatGPT
To read just the latest file using the built-in function `sys.fn_xe_file_target_read_file` in SQL Server, you can follow these steps:

1. **Identify the Latest File**: First, you need to identify the latest Extended Events file. Extended Events files typically have a numeric suffix in their names, which allows you to determine the order in which they were created.

2. **Read the Latest File**: Once you have identified the latest file, you can use the `sys.fn_xe_file_target_read_file` function to read from that file.

Here's a step-by-step approach:

1. **Identify the Latest File**:

You can use the `xp_cmdshell` system stored procedure to list the files in the directory where the Extended Events files are stored. You need to make sure `xp_cmdshell` is enabled on your SQL Server instance.

```sql
-- Enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- List files in the Extended Events directory
DECLARE @path NVARCHAR(256) = N'C:\Path\To\Your\XE\Files\';
DECLARE @cmd NVARCHAR(4000);

SET @cmd = N'dir /b ' + @path + '*.xel';
CREATE TABLE #Files (FileName NVARCHAR(256));
INSERT INTO #Files (FileName) EXEC xp_cmdshell @cmd;

-- Select the latest file
DECLARE @latestFile NVARCHAR(256);
SELECT TOP 1 @latestFile = @path + FileName
FROM #Files
WHERE FileName IS NOT NULL
ORDER BY FileName DESC;

DROP TABLE #Files;

-- Disable xp_cmdshell (optional)
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
```

2. **Read the Latest File**:

Now that you have the latest file name stored in `@latestFile`, you can use the `sys.fn_xe_file_target_read_file` function to read it.

```sql
-- Read from the latest Extended Events file
SELECT * 
FROM sys.fn_xe_file_target_read_file(@latestFile, NULL, NULL, NULL);
```

### Complete Script

Here's the complete script combining both steps:

```sql
-- Enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- Define the path to the Extended Events files
DECLARE @path NVARCHAR(256) = N'C:\Path\To\Your\XE\Files\';
DECLARE @cmd NVARCHAR(4000);

-- List files in the Extended Events directory
SET @cmd = N'dir /b ' + @path + '*.xel';
CREATE TABLE #Files (FileName NVARCHAR(256));
INSERT INTO #Files (FileName) EXEC xp_cmdshell @cmd;

-- Select the latest file
DECLARE @latestFile NVARCHAR(256);
SELECT TOP 1 @latestFile = @path + FileName
FROM #Files
WHERE FileName IS NOT NULL
ORDER BY FileName DESC;

DROP TABLE #Files;

-- Disable xp_cmdshell (optional)
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

-- Read from the latest Extended Events file
SELECT * 
FROM sys.fn_xe_file_target_read_file(@latestFile, NULL, NULL, NULL);
```

### Notes

- **Security**: Enabling `xp_cmdshell` poses security risks. Ensure you understand these risks and consider disabling `xp_cmdshell` after use.
- **File Path**: Update the `@path` variable to the actual path where your Extended Events files are stored.
- **Permissions**: Ensure the SQL Server service account has read access to the directory containing the Extended Events files.

Author: user
in the SSISDB catalog, i want to assign the parameter name 'CM.CIG_Report.ConnectionString' to be the referenced_variable_name 'cobalt.CIG_Report.ConnectionString' in the associated environment. what procedure to i need to call to do that fromt eh SSISDB

Author: ChatGPT
To assign a parameter name to a referenced variable name in the associated environment in the SSISDB catalog, you can use the `catalog.set_object_parameter_value` stored procedure. This procedure allows you to assign values to parameters for SSIS packages, including environment variable references.

Here???s a step-by-step guide on how to achieve this:

1. **Identify the necessary IDs**: You need the `object_type`, `object_id`, `parameter_name`, and `environment_variable_id`. The `object_type` will typically be 50 (for a package), and you can find the `object_id` from the `catalog.packages` or `catalog.projects` view.

2. **Find the environment variable ID**: Use the `catalog.environment_variables` view to find the ID of the environment variable.

3. **Set the parameter value**: Call the `catalog.set_object_parameter_value` stored procedure to map the parameter to the environment variable.

Here???s an example of how you can do this in T-SQL:

### Example Code

1. **Find the Object ID and Environment Variable ID**:

```sql
-- Replace these values with your actual project and package names
DECLARE @project_name NVARCHAR(128) = 'YourProjectName';
DECLARE @package_name NVARCHAR(260) = 'YourPackageName.dtsx';
DECLARE @environment_name NVARCHAR(128) = 'YourEnvironmentName';
DECLARE @environment_folder_name NVARCHAR(128) = 'YourFolderName';

-- Get the project and package IDs
DECLARE @project_id BIGINT;
DECLARE @package_id BIGINT;
SELECT @project_id = project_id 
FROM catalog.projects 
WHERE name = @project_name;

SELECT @package_id = object_id 
FROM catalog.packages 
WHERE project_id = @project_id 
AND name = @package_name;

-- Get the environment ID
DECLARE @environment_id BIGINT;
SELECT @environment_id = environment_id 
FROM catalog.environments 
WHERE name = @environment_name 
AND folder_id = (SELECT folder_id FROM catalog.folders WHERE name = @environment_folder_name);

-- Get the environment variable ID
DECLARE @environment_variable_id BIGINT;
SELECT @environment_variable_id = variable_id 
FROM catalog.environment_variables 
WHERE environment_id = @environment_id 
AND name = 'cobalt.CIG_Report.ConnectionString';
```

2. **Set the Object Parameter Value**:

```sql
-- Replace these values with your actual parameter and environment variable names
DECLARE @parameter_name NVARCHAR(128) = 'CM.CIG_Report.ConnectionString';

-- Set the object parameter value to reference the environment variable
EXEC [SSISDB].[catalog].[set_object_parameter_value] 
    @object_type = 50, -- 50 stands for packages
    @object_id = @package_id,
    @parameter_name = @parameter_name,
    @parameter_value_type = 'EnvironmentVariable',
    @parameter_value = CAST(@environment_variable_id AS NVARCHAR(128));
```

### Explanation

1. **Identify IDs**:
   - First, identify the `project_id` and `package_id` by querying `catalog.projects` and `catalog.packages`.
   - Then, find the `environment_id` and `environment_variable_id` by querying `catalog.environments` and `catalog.environment_variables`.

2. **Set Object Parameter Value**:
   - Use the `catalog.set_object_parameter_value` procedure to map the package parameter to the environment variable.

Make sure to replace the placeholder values (`YourProjectName`, `YourPackageName.dtsx`, `YourEnvironmentName`, `YourFolderName`) with your actual SSIS project, package, environment, and folder names. This script assumes that you have appropriate permissions to query the `SSISDB` catalog and modify parameter values.

Author: user
i need to update one project, but it has many packages, all of which have the same parameter_name of  'CM.CIG_Report.ConnectionString'

Author: ChatGPT
To update all packages within a single project to use the same environment variable for the parameter `CM.CIG_Report.ConnectionString`, you can loop through all the packages in the project and set the parameter for each package.

Here's a complete script to achieve this:

1. **Identify the necessary IDs**: Project ID, environment ID, and environment variable ID.
2. **Loop through all packages in the project**: Update the parameter for each package.

### Step-by-Step Script

```sql
-- Replace these values with your actual project and environment details
DECLARE @project_name NVARCHAR(128) = 'YourProjectName';
DECLARE @environment_name NVARCHAR(128) = 'YourEnvironmentName';
DECLARE @environment_folder_name NVARCHAR(128) = 'YourFolderName';
DECLARE @parameter_name NVARCHAR(128) = 'CM.CIG_Report.ConnectionString';
DECLARE @environment_variable_name NVARCHAR(128) = 'cobalt.CIG_Report.ConnectionString';

-- Get the project ID
DECLARE @project_id BIGINT;
SELECT @project_id = project_id 
FROM catalog.projects 
WHERE name = @project_name;

-- Get the environment ID
DECLARE @environment_id BIGINT;
SELECT @environment_id = environment_id 
FROM catalog.environments 
WHERE name = @environment_name 
AND folder_id = (SELECT folder_id FROM catalog.folders WHERE name = @environment_folder_name);

-- Get the environment variable ID
DECLARE @environment_variable_id BIGINT;
SELECT @environment_variable_id = variable_id 
FROM catalog.environment_variables 
WHERE environment_id = @environment_id 
AND name = @environment_variable_name;

-- Loop through all packages in the project and update the parameter
DECLARE @package_id BIGINT;
DECLARE @package_name NVARCHAR(260);

DECLARE package_cursor CURSOR FOR
SELECT object_id, name 
FROM catalog.packages 
WHERE project_id = @project_id;

OPEN package_cursor;

FETCH NEXT FROM package_cursor INTO @package_id, @package_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Set the object parameter value to reference the environment variable
    EXEC [SSISDB].[catalog].[set_object_parameter_value] 
        @object_type = 50, -- 50 stands for packages
        @object_id = @package_id,
        @parameter_name = @parameter_name,
        @parameter_value_type = 'EnvironmentVariable',
        @parameter_value = CAST(@environment_variable_id AS NVARCHAR(128));

    FETCH NEXT FROM package_cursor INTO @package_id, @package_name;
END

CLOSE package_cursor;
DEALLOCATE package_cursor;
```

### Explanation

1. **Identify IDs**:
   - The script first identifies the `project_id` from `catalog.projects`.
   - Then, it identifies the `environment_id` from `catalog.environments`.
   - The `environment_variable_id` is obtained from `catalog.environment_variables`.

2. **Loop through Packages**:
   - A cursor is used to loop through all packages within the project.
   - For each package, the `catalog.set_object_parameter_value` stored procedure is called to set the `CM.CIG_Report.ConnectionString` parameter to reference the specified environment variable.

Make sure to replace the placeholder values (`YourProjectName`, `YourEnvironmentName`, `YourFolderName`, and `cobalt.CIG_Report.ConnectionString`) with your actual project, environment, folder names, and environment variable name.

This script assumes that you have the necessary permissions to query and update the `SSISDB` catalog.

Author: user
your call to the procedure references a parameter @object_id which does not exist, should that be @object_name

Author: ChatGPT
You are correct; the `catalog.set_object_parameter_value` stored procedure does not have an `@object_id` parameter but instead uses `@object_name` and `@project_name` to identify the specific package. Here is the corrected script to set the parameter for each package in the project:

### Step-by-Step Script

```sql
-- Replace these values with your actual project and environment details
DECLARE @project_name NVARCHAR(128) = 'YourProjectName';
DECLARE @environment_name NVARCHAR(128) = 'YourEnvironmentName';
DECLARE @environment_folder_name NVARCHAR(128) = 'YourFolderName';
DECLARE @parameter_name NVARCHAR(128) = 'CM.CIG_Report.ConnectionString';
DECLARE @environment_variable_name NVARCHAR(128) = 'cobalt.CIG_Report.ConnectionString';

-- Get the project ID
DECLARE @project_id BIGINT;
SELECT @project_id = project_id 
FROM catalog.projects 
WHERE name = @project_name;

-- Get the environment ID
DECLARE @environment_id BIGINT;
SELECT @environment_id = environment_id 
FROM catalog.environments 
WHERE name = @environment_name 
AND folder_id = (SELECT folder_id FROM catalog.folders WHERE name = @environment_folder_name);

-- Get the environment variable ID
DECLARE @environment_variable_id BIGINT;
SELECT @environment_variable_id = variable_id 
FROM catalog.environment_variables 
WHERE environment_id = @environment_id 
AND name = @environment_variable_name;

-- Loop through all packages in the project and update the parameter
DECLARE @package_name NVARCHAR(260);

DECLARE package_cursor CURSOR FOR
SELECT name 
FROM catalog.packages 
WHERE project_id = @project_id;

OPEN package_cursor;

FETCH NEXT FROM package_cursor INTO @package_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Set the object parameter value to reference the environment variable
    EXEC [SSISDB].[catalog].[set_object_parameter_value] 
        @object_type = 50, -- 50 stands for packages
        @object_name = @package_name,
        @parameter_name = @parameter_name,
        @parameter_value_type = 'EnvironmentVariable',
        @parameter_value = CAST(@environment_variable_id AS NVARCHAR(128));

    FETCH NEXT FROM package_cursor INTO @package_name;
END

CLOSE package_cursor;
DEALLOCATE package_cursor;
```

### Explanation

1. **Identify IDs**:
   - The script first identifies the `project_id` from `catalog.projects`.
   - Then, it identifies the `environment_id` from `catalog.environments`.
   - The `environment_variable_id` is obtained from `catalog.environment_variables`.

2. **Loop through Packages**:
   - A cursor is used to loop through all packages within the project.
   - For each package, the `catalog.set_object_parameter_value` stored procedure is called to set the `CM.CIG_Report.ConnectionString` parameter to reference the specified environment variable.

### Corrections

- The `@object_id` parameter was changed to `@object_name`.
- The cursor now fetches `name` from `catalog.packages` instead of `object_id`.

Make sure to replace the placeholder values (`YourProjectName`, `YourEnvironmentName`, `YourFolderName`, and `cobalt.CIG_Report.ConnectionString`) with your actual project, environment, folder names, and environment variable name.

This script assumes that you have the necessary permissions to query and update the `SSISDB` catalog.

Author: user
similarly, you reference @parameter_value_type, but the actual parameter seems to be @value_type

Author: ChatGPT
Thank you for pointing that out. Let's correct the script accordingly. The actual parameter in `catalog.set_object_parameter_value` is indeed `@value_type` and not `@parameter_value_type`. Here's the corrected script:

### Step-by-Step Script

```sql
-- Replace these values with your actual project and environment details
DECLARE @project_name NVARCHAR(128) = 'YourProjectName';
DECLARE @environment_name NVARCHAR(128) = 'YourEnvironmentName';
DECLARE @environment_folder_name NVARCHAR(128) = 'YourFolderName';
DECLARE @parameter_name NVARCHAR(128) = 'CM.CIG_Report.ConnectionString';
DECLARE @environment_variable_name NVARCHAR(128) = 'cobalt.CIG_Report.ConnectionString';

-- Get the project ID
DECLARE @project_id BIGINT;
SELECT @project_id = project_id 
FROM catalog.projects 
WHERE name = @project_name;

-- Get the environment ID
DECLARE @environment_id BIGINT;
SELECT @environment_id = environment_id 
FROM catalog.environments 
WHERE name = @environment_name 
AND folder_id = (SELECT folder_id FROM catalog.folders WHERE name = @environment_folder_name);

-- Get the environment variable ID
DECLARE @environment_variable_id BIGINT;
SELECT @environment_variable_id = variable_id 
FROM catalog.environment_variables 
WHERE environment_id = @environment_id 
AND name = @environment_variable_name;

-- Loop through all packages in the project and update the parameter
DECLARE @package_name NVARCHAR(260);

DECLARE package_cursor CURSOR FOR
SELECT name 
FROM catalog.packages 
WHERE project_id = @project_id;

OPEN package_cursor;

FETCH NEXT FROM package_cursor INTO @package_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Set the object parameter value to reference the environment variable
    EXEC [SSISDB].[catalog].[set_object_parameter_value] 
        @object_type = 50, -- 50 stands for packages
        @object_name = @package_name,
        @parameter_name = @parameter_name,
        @value_type = 'EnvironmentVariable',
        @parameter_value = CAST(@environment_variable_id AS NVARCHAR(128)),
        @project_name = @project_name;

    FETCH NEXT FROM package_cursor INTO @package_name;
END

CLOSE package_cursor;
DEALLOCATE package_cursor;
```

### Explanation

1. **Identify IDs**:
   - The script first identifies the `project_id` from `catalog.projects`.
   - Then, it identifies the `environment_id` from `catalog.environments`.
   - The `environment_variable_id` is obtained from `catalog.environment_variables`.

2. **Loop through Packages**:
   - A cursor is used to loop through all packages within the project.
   - For each package, the `catalog.set_object_parameter_value` stored procedure is called to set the `CM.CIG_Report.ConnectionString` parameter to reference the specified environment variable.

### Corrections

- The `@parameter_value_type` parameter was corrected to `@value_type`.
- Added the `@project_name` parameter in the `catalog.set_object_parameter_value` procedure call.
- The cursor now fetches `name` from `catalog.packages` instead of `object_id`.

Make sure to replace the placeholder values (`YourProjectName`, `YourEnvironmentName`, `YourFolderName`, and `cobalt.CIG_Report.ConnectionString`) with your actual project, environment, folder names, and environment variable name.

This script assumes that you have the necessary permissions to query and update the `SSISDB` catalog.

Author: user
@value_type is a CHAR(1) datatype, but you are assigning a much longer string

Author: ChatGPT
Thank you for the clarification. The `@value_type` parameter indeed expects a single character value. For environment variables, the appropriate value type is `'V'`. Let's correct the script accordingly.

Here is the revised script with the correct value type:

### Step-by-Step Script

```sql
-- Replace these values with your actual project and environment details
DECLARE @project_name NVARCHAR(128) = 'YourProjectName';
DECLARE @environment_name NVARCHAR(128) = 'YourEnvironmentName';
DECLARE @environment_folder_name NVARCHAR(128) = 'YourFolderName';
DECLARE @parameter_name NVARCHAR(128) = 'CM.CIG_Report.ConnectionString';
DECLARE @environment_variable_name NVARCHAR(128) = 'cobalt.CIG_Report.ConnectionString';

-- Get the project ID
DECLARE @project_id BIGINT;
SELECT @project_id = project_id 
FROM catalog.projects 
WHERE name = @project_name;

-- Get the environment ID
DECLARE @environment_id BIGINT;
SELECT @environment_id = environment_id 
FROM catalog.environments 
WHERE name = @environment_name 
AND folder_id = (SELECT folder_id FROM catalog.folders WHERE name = @environment_folder_name);

-- Get the environment variable ID
DECLARE @environment_variable_id BIGINT;
SELECT @environment_variable_id = variable_id 
FROM catalog.environment_variables 
WHERE environment_id = @environment_id 
AND name = @environment_variable_name;

-- Loop through all packages in the project and update the parameter
DECLARE @package_name NVARCHAR(260);

DECLARE package_cursor CURSOR FOR
SELECT name 
FROM catalog.packages 
WHERE project_id = @project_id;

OPEN package_cursor;

FETCH NEXT FROM package_cursor INTO @package_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Set the object parameter value to reference the environment variable
    EXEC [SSISDB].[catalog].[set_object_parameter_value] 
        @object_type = 50, -- 50 stands for packages
        @object_name = @package_name,
        @parameter_name = @parameter_name,
        @value_type = 'V', -- 'V' stands for EnvironmentVariable
        @parameter_value = CAST(@environment_variable_id AS NVARCHAR(128)),
        @project_name = @project_name;

    FETCH NEXT FROM package_cursor INTO @package_name;
END

CLOSE package_cursor;
DEALLOCATE package_cursor;
```

### Explanation

1. **Identify IDs**:
   - The script first identifies the `project_id` from `catalog.projects`.
   - Then, it identifies the `environment_id` from `catalog.environments`.
   - The `environment_variable_id` is obtained from `catalog.environment_variables`.

2. **Loop through Packages**:
   - A cursor is used to loop through all packages within the project.
   - For each package, the `catalog.set_object_parameter_value` stored procedure is called to set the `CM.CIG_Report.ConnectionString` parameter to reference the specified environment variable.

### Corrections

- The `@value_type` parameter is set to `'V'` for environment variables.
- The cursor fetches `name` from `catalog.packages`.

Make sure to replace the placeholder values (`YourProjectName`, `YourEnvironmentName`, `YourFolderName`, and `cobalt.CIG_Report.ConnectionString`) with your actual project, environment, folder names, and environment variable name.

This script assumes that you have the necessary permissions to query and update the `SSISDB` catalog.

Author: user
when calling a stored procedure, you cannot have a calculation or formula, just a parameter or static value.  @parameter_value = CAST(@environment_variable_id AS NVARCHAR(128)), is invalid, it has to be assigned to a variable prior to the call

Author: ChatGPT
You're right. Stored procedure parameters need to be simple values or variables, not expressions. Let's correct the script by assigning the cast value to a variable before calling the stored procedure.

### Corrected Script

```sql
-- Replace these values with your actual project and environment details
DECLARE @project_name NVARCHAR(128) = 'YourProjectName';
DECLARE @environment_name NVARCHAR(128) = 'YourEnvironmentName';
DECLARE @environment_folder_name NVARCHAR(128) = 'YourFolderName';
DECLARE @parameter_name NVARCHAR(128) = 'CM.CIG_Report.ConnectionString';
DECLARE @environment_variable_name NVARCHAR(128) = 'cobalt.CIG_Report.ConnectionString';

-- Get the project ID
DECLARE @project_id BIGINT;
SELECT @project_id = project_id 
FROM catalog.projects 
WHERE name = @project_name;

-- Get the environment ID
DECLARE @environment_id BIGINT;
SELECT @environment_id = environment_id 
FROM catalog.environments 
WHERE name = @environment_name 
AND folder_id = (SELECT folder_id FROM catalog.folders WHERE name = @environment_folder_name);

-- Get the environment variable ID
DECLARE @environment_variable_id BIGINT;
SELECT @environment_variable_id = variable_id 
FROM catalog.environment_variables 
WHERE environment_id = @environment_id 
AND name = @environment_variable_name;

-- Assign the environment variable ID to a variable with the correct data type
DECLARE @parameter_value NVARCHAR(128);
SET @parameter_value = CAST(@environment_variable_id AS NVARCHAR(128));

-- Loop through all packages in the project and update the parameter
DECLARE @package_name NVARCHAR(260);

DECLARE package_cursor CURSOR FOR
SELECT name 
FROM catalog.packages 
WHERE project_id = @project_id;

OPEN package_cursor;

FETCH NEXT FROM package_cursor INTO @package_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Set the object parameter value to reference the environment variable
    EXEC [SSISDB].[catalog].[set_object_parameter_value] 
        @object_type = 50, -- 50 stands for packages
        @object_name = @package_name,
        @parameter_name = @parameter_name,
        @value_type = 'V', -- 'V' stands for EnvironmentVariable
        @parameter_value = @parameter_value,
        @project_name = @project_name;

    FETCH NEXT FROM package_cursor INTO @package_name;
END

CLOSE package_cursor;
DEALLOCATE package_cursor;
```

### Explanation

1. **Identify IDs**:
   - The script first identifies the `project_id` from `catalog.projects`.
   - Then, it identifies the `environment_id` from `catalog.environments`.
   - The `environment_variable_id` is obtained from `catalog.environment_variables`.

2. **Assign Parameter Value**:
   - The environment variable ID is cast to `NVARCHAR(128)` and assigned to the `@parameter_value` variable.

3. **Loop through Packages**:
   - A cursor is used to loop through all packages within the project.
   - For each package, the `catalog.set_object_parameter_value` stored procedure is called to set the `CM.CIG_Report.ConnectionString` parameter to reference the specified environment variable.

### Corrections

- The `CAST` expression is now assigned to a variable (`@parameter_value`) before the stored procedure call.
- The script maintains the structure for looping through and updating each package.

Make sure to replace the placeholder values (`YourProjectName`, `YourEnvironmentName`, `YourFolderName`, and `cobalt.CIG_Report.ConnectionString`) with your actual project, environment, folder names, and environment variable name.

This script assumes that you have the necessary permissions to query and update the `SSISDB` catalog.

Author: user
refering back to detecting nested CTEs, i sit possible to use a recursive CTE instead of needing to create the function fn_CountSubstringOccurrences 

Author: ChatGPT
Yes, you can use a recursive Common Table Expression (CTE) to count the occurrences of a substring within a string, which would help in detecting nested CTEs. Below is an approach to achieve this:

1. **Extract the SQL code from the `sys.sql_modules` table.**
2. **Use a recursive CTE to count occurrences of CTE definitions (`WITH` keyword).**

Here's how you can do it:

### Recursive CTE to Count Substring Occurrences

```sql
-- Define the substring we are looking for
DECLARE @substring NVARCHAR(100) = 'WITH';
DECLARE @module_definition NVARCHAR(MAX);

-- Get the definition of the stored procedure/function/view/etc.
SELECT @module_definition = definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('YourObjectName'); -- Replace with your object name

-- Add a space at the end to ensure the last occurrence is counted
SET @module_definition = @module_definition + ' ';

-- Recursive CTE to count occurrences of the substring
WITH CTE_SubstringOccurrences AS (
    SELECT 
        CHARINDEX(@substring, @module_definition) AS StartIndex,
        1 AS Occurrence
    UNION ALL
    SELECT 
        CHARINDEX(@substring, @module_definition, StartIndex + LEN(@substring)),
        Occurrence + 1
    FROM CTE_SubstringOccurrences
    WHERE CHARINDEX(@substring, @module_definition, StartIndex + LEN(@substring)) > 0
)
SELECT MAX(Occurrence) - 1 AS NumOccurrences
FROM CTE_SubstringOccurrences
OPTION (MAXRECURSION 0);
```

### Explanation

1. **Initial Setup**:
   - Declare the `@substring` variable with the value `'WITH'`.
   - Retrieve the definition of the module (stored procedure, function, view, etc.) into `@module_definition`.
   - Add a space at the end of `@module_definition` to ensure the last occurrence is counted properly.

2. **Recursive CTE**:
   - The CTE `CTE_SubstringOccurrences` starts by finding the first occurrence of the substring.
   - The recursive part of the CTE finds the next occurrence of the substring starting after the previous occurrence.
   - Each recursive step increments the `Occurrence` count.

3. **Final Count**:
   - The final select statement retrieves the maximum occurrence count and subtracts 1 to get the actual number of occurrences (because the initial occurrence is counted as 1).

### Usage

Replace `'YourObjectName'` with the actual name of the object you want to check for nested CTEs. This script assumes the object is within the `sys.sql_modules` table.

### Note

This approach assumes that the SQL code contains only one CTE keyword per CTE definition. Nested CTEs should be detected by multiple occurrences of the `WITH` keyword within the same module definition. If you need to detect more complex nesting patterns, you might need a more sophisticated parsing mechanism.

Author: user
i don't think that is correct. nested CTE's will only have one WITH statement, but ther emay be more than onbe CTE. Second. the scope of the code produced is now limited to one, known definition isntead of all definitions in sys.sql_modules

Author: ChatGPT
You are correct. Nested CTEs will indeed only have one `WITH` statement but can have multiple CTEs defined within that scope. Also, the code should be able to process all definitions in `sys.sql_modules`.

Let's refine the approach to:
1. Process all definitions in `sys.sql_modules`.
2. Count occurrences of multiple CTE definitions within each `WITH` clause.

### Revised Approach

We'll use a recursive CTE to count the occurrences of `WITH` and nested CTE definitions for all entries in `sys.sql_modules`.

### Recursive CTE to Count Nested CTEs for All Definitions

```sql
-- Define the substring we are looking for
DECLARE @substring NVARCHAR(100) = 'WITH';
DECLARE @cte_separator NVARCHAR(100) = ',';

-- Recursive CTE to find and count nested CTEs
WITH ModuleDefinitions AS (
    SELECT object_id, definition
    FROM sys.sql_modules
),
CTE_Search AS (
    SELECT 
        object_id,
        CHARINDEX(@substring, definition) AS StartIndex,
        CHARINDEX(@cte_separator, definition, CHARINDEX(@substring, definition) + LEN(@substring)) AS NextSeparator,
        1 AS Occurrence
    FROM ModuleDefinitions
    WHERE CHARINDEX(@substring, definition) > 0
    UNION ALL
    SELECT 
        object_id,
        StartIndex,
        CHARINDEX(@cte_separator, definition, NextSeparator + 1),
        Occurrence + 1
    FROM CTE_Search AS cs
    JOIN ModuleDefinitions AS md ON cs.object_id = md.object_id
    WHERE CHARINDEX(@cte_separator, md.definition, cs.NextSeparator + 1) > 0
)
SELECT 
    object_id, 
    MAX(Occurrence) AS NumNestedCTEs
FROM CTE_Search
GROUP BY object_id
OPTION (MAXRECURSION 0);
```

### Explanation

1. **Initial Setup**:
   - The `@substring` variable is defined as `'WITH'` to locate the start of CTE definitions.
   - The `@cte_separator` variable is defined as `','` to locate additional CTE definitions within the `WITH` clause.

2. **ModuleDefinitions CTE**:
   - Selects all object IDs and definitions from `sys.sql_modules`.

3. **CTE_Search**:
   - The initial search locates the `WITH` keyword and the first comma following it.
   - The recursive part of the CTE continues to find subsequent commas within the same `WITH` clause, counting each as a nested CTE definition.

4. **Final Count**:
   - The final select statement groups by `object_id` and retrieves the maximum occurrence count of nested CTEs for each module.

### Usage

This script processes all definitions in `sys.sql_modules` and counts the number of nested CTE definitions for each module. The result shows the `object_id` and the number of nested CTEs detected. 

This should give you a good overview of how many nested CTEs are present in each module's definition.