How to route query variable to object/property variable?

Welcome Forums General PowerShell Q&A How to route query variable to object/property variable?

Viewing 3 reply threads
  • Author
    Posts
    • #226329
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

      Given the following query which creates statement to recreate foreign key constraint, it prints the result into @cmd variable, however, I can’t figure out how to route such variables set in a SQL query to a PowerShell variable…

      Function Query($Query) {
      $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
      $SqlConnection.ConnectionString = $connectionstring
      $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
      $SqlCmd.Connection = $SqlConnection 
      $SqlCmd.CommandText = $Query 
      $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
      $SqlAdapter.SelectCommand = $SqlCmd 
      $DataSet = New-Object System.Data.DataSet 
      $a=$SqlAdapter.Fill($DataSet)
      $SqlConnection.Close() 
      $DataSet.Tables[0] }
      
      $AddFKConstraint = Query "
      SET NOCOUNT ON  
      
      DECLARE @operation VARCHAR(10)  
      DECLARE @tableName sysname  
      DECLARE @schemaName sysname  
      
      SET @operation = 'DROP' --ENABLE, DISABLE, DROP  
      SET @tableName = '$csvFileBaseName'  
      SET @schemaName = '$schemaName'  
      
      DECLARE @cmd NVARCHAR(1000) 
      
      DECLARE   
         @FK_NAME sysname,  
         @FK_OBJECTID INT,  
         @FK_DISABLED INT,  
         @FK_NOT_FOR_REPLICATION INT,  
         @DELETE_RULE smallint,     
         @UPDATE_RULE smallint,     
         @FKTABLE_NAME sysname,  
         @FKTABLE_OWNER sysname,  
         @PKTABLE_NAME sysname,  
         @PKTABLE_OWNER sysname,  
         @FKCOLUMN_NAME sysname,  
         @PKCOLUMN_NAME sysname,  
         @CONSTRAINT_COLID INT  
      
      
      DECLARE cursor_fkeys CURSOR FOR   
         SELECT  Fk.name,  
                 Fk.OBJECT_ID,   
                 Fk.is_disabled,   
                 Fk.is_not_for_replication,   
                 Fk.delete_referential_action,   
                 Fk.update_referential_action,   
                 OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name,   
                 schema_name(Fk.schema_id) AS Fk_table_schema,   
                 TbR.name AS Pk_table_name,   
                 schema_name(TbR.schema_id) Pk_table_schema  
         FROM    sys.foreign_keys Fk LEFT OUTER JOIN   
                 sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id --inner join   
         WHERE   TbR.name = @tableName  
                 AND schema_name(TbR.schema_id) = @schemaName  
      
      OPEN cursor_fkeys  
      
      FETCH NEXT FROM cursor_fkeys   
         INTO @FK_NAME,@FK_OBJECTID,  
             @FK_DISABLED,  
             @FK_NOT_FOR_REPLICATION,  
             @DELETE_RULE,     
             @UPDATE_RULE,     
             @FKTABLE_NAME,  
             @FKTABLE_OWNER,  
             @PKTABLE_NAME,  
             @PKTABLE_OWNER  
      
      WHILE @@FETCH_STATUS = 0   
      BEGIN   
         -- create statement for recreating FK  
         IF @operation = 'DROP'  
         BEGIN    
             -- create process  
             DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT  
      
             -- create cursor to get FK columns  
             DECLARE cursor_fkeyCols CURSOR FOR   
             SELECT  COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name,   
                     COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name  
             FROM    sys.foreign_keys Fk LEFT OUTER JOIN   
                     sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id INNER JOIN   
                     sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID   
             WHERE   TbR.name = @tableName  
                     AND schema_name(TbR.schema_id) = @schemaName  
                     AND Fk_Cl.constraint_object_id = @FK_OBJECTID -- added 6/12/2008  
             ORDER BY Fk_Cl.constraint_column_id  
      
             OPEN cursor_fkeyCols  
      
             FETCH NEXT FROM    cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME  
      
             SET @COUNTER = 1  
             SET @FKCOLUMNS = ''  
             SET @PKCOLUMNS = ''  
      
             WHILE @@FETCH_STATUS = 0   
             BEGIN   
      
                 IF @COUNTER > 1   
                 BEGIN  
                     SET @FKCOLUMNS = @FKCOLUMNS + ','  
                     SET @PKCOLUMNS = @PKCOLUMNS + ','  
                 END  
      
                 SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'  
                 SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'  
      
                 SET @COUNTER = @COUNTER + 1  
      
                 FETCH NEXT FROM    cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME  
             END  
      
             CLOSE cursor_fkeyCols   
             DEALLOCATE cursor_fkeyCols   
      
             -- generate create FK statement  
             SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + ']  WITH ' +   
                 CASE @FK_DISABLED   
                     WHEN 0 THEN ' CHECK '  
                     WHEN 1 THEN ' NOCHECK '  
                 END +  ' ADD CONSTRAINT [' + @FK_NAME   
                 + '] FOREIGN KEY (' + @FKCOLUMNS   
                 + ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] ('   
                 + @PKCOLUMNS + ') ON UPDATE ' +   
                 CASE @UPDATE_RULE   
                     WHEN 0 THEN ' NO ACTION '  
                     WHEN 1 THEN ' CASCADE '   
                     WHEN 2 THEN ' SET_NULL '   
                     END + ' ON DELETE ' +   
                 CASE @DELETE_RULE  
                     WHEN 0 THEN ' NO ACTION '   
                     WHEN 1 THEN ' CASCADE '   
                     WHEN 2 THEN ' SET_NULL '   
                     END + '' +  
                 CASE @FK_NOT_FOR_REPLICATION  
                     WHEN 0 THEN ''  
                     WHEN 1 THEN ' NOT FOR REPLICATION '  
                 END  
      
            PRINT @cmd  
      
         END  
      
         FETCH NEXT FROM    cursor_fkeys   
            INTO @FK_NAME,@FK_OBJECTID,  
                 @FK_DISABLED,  
                 @FK_NOT_FOR_REPLICATION,  
                 @DELETE_RULE,     
                 @UPDATE_RULE,     
                 @FKTABLE_NAME,  
                 @FKTABLE_OWNER,  
                 @PKTABLE_NAME,  
                 @PKTABLE_OWNER  
      END  
      
      CLOSE cursor_fkeys   
      DEALLOCATE cursor_fkeys
      " | Select ["@cmd"].Value;

      How do I accomplish this ( ...| Select ["@cmd"].Value;)?

      related

    • #226374
      Senior Moderator
      Topics: 8
      Replies: 1215
      Points: 4,334
      Helping Hand
      Rank: Community Hero

      You cannot use Select-Object cmdlet here. You may explore the members of $SqlCmd object once the query is executed.

      Do a

      $SqlCmd | Get-Member
      

      Then there should be some property which shows the output of the query, if you can output @cmd value from the query itself.

      Note: I didn’t use SQL connection objects using PowerShell much.

      • This reply was modified 2 weeks, 5 days ago by kvprasoon. Reason: post correction
    • #226416
      Participant
      Topics: 12
      Replies: 1610
      Points: 2,508
      Helping Hand
      Rank: Community Hero

      Just return it in a Select and then you can capture it like an other Select statement:

      ...
      CLOSE cursor_fkeys   
      DEALLOCATE cursor_fkeys
      
      SELECT @cmd As Command
      " | Select Command
      
    • #226479
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

      Just return it in a Select and then you can capture it like an other Select statement:

      PowerShell
      7 lines

      <textarea class=”ace_text-input” style=”opacity: 0; height: 17.9048px; width: 7.2381px; left: 45px; top: 0px;” spellcheck=”false” wrap=”off”></textarea>

      1
      2
      3
      4
      5
      6
      7
      CLOSE cursor_fkeys
      DEALLOCATE cursor_fkeys
      SELECT @cmd As Command
      | Select Command
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

      Oh I didnt realize I can just select in sql. I thought anytime you select, has to be followed by FROM, interesting…

Viewing 3 reply threads
  • You must be logged in to reply to this topic.