locked
Cannot use SQL DISTINCT keyword from PS RRS feed

  • Question

  • (corrected again, thank you)

    Hello,

    I have a simple PS script to copy tables between servers that works well but it fails when I pass DISTINCT keyword:

    Function ConnectionString([string] $ServerName, [string] $DbName) 
      {
        "Data Source=$ServerName;Initial Catalog=$DbName;Integrated Security=True;"
      }
    
      $SrcConnStr = ConnectionString $SrcServer $SrcDatabase
      $SrcConn  = New-Object System.Data.SqlClient.SQLConnection($SrcConnStr)
      $CmdText = "SELECT distinct col1, cold2 from " + $SrcTable 
      $SqlCommand = New-Object system.Data.SqlClient.SqlCommand($CmdText, $SrcConn)  
      $SrcConn.Open()
      [System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()
    
    
    Try
      {
        $DestConnStr = ConnectionString $DestServer $DestDatabase
        $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
        $bulkCopy.DestinationTableName = $DestTable
        $bulkCopy.BatchSize = $BatchSize
        $bulkCopy.BulkCopyTimeout = $QueryTimeOut
        $bulkCopy.WriteToServer($sqlReader)
      }
    
      Catch [System.Exception]
      {
        $ex = $_.Exception
        Write-Host $ex.Message
      }
    
      Finally
      {
        $SqlReader.close()
        $SrcConn.Close()
        $SrcConn.Dispose()
        $bulkCopy.Close()
      }

    The same script works just fine if I replace " $CmdText = "SELECT distinct col1, cold2 from " + $SrcTable " to " $CmdText = "SELECT * from " + $SrcTable ".

    The error is :

    Exception calling "ExecuteReader" with "0" argument(s): "Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."
    At C:\CopyTables.ps1:32 char:3
    +   [System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()
    +   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : SqlException
     
    Invalid attempt to call FieldCount when reader is closed.

    Can someone help me on this?

    Thanks much!






    • Edited by BrianKang Friday, November 10, 2017 10:15 PM
    Friday, November 10, 2017 7:12 PM

Answers

  • $sqlCommand.CommandTimeout = 0

    \_(ツ)_/

    • Marked as answer by BrianKang Friday, November 10, 2017 11:17 PM
    Friday, November 10, 2017 10:46 PM

All replies

  • Please fix you post.  We do not allow posting of colorized code.  It is hard to read on most browsers and cannot be copied and pasted correctly.  Use the provided code posting tool.


    \_(ツ)_/


    • Edited by jrv Friday, November 10, 2017 7:41 PM
    Friday, November 10, 2017 7:16 PM
  • FYI reading the title; I see no issues with use of "DISTTINCT" in a SQL query using any method.  Since we cannot read yur code there is no way to guess at what your issue is.  You also need to post the complete error message.


    \_(ツ)_/



    • Edited by jrv Friday, November 10, 2017 7:41 PM
    Friday, November 10, 2017 7:40 PM
  • Thank for reply.  I already fixed my posting as you suggested.  Please let me know if you still can't see the code I posted.  Also, tere's the error I got:

    Exception calling "ExecuteReader" with "0" argument(s): "Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."
    At C:\CopyTables.ps1:32 char:3
    +   [System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()
    +   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : SqlException
     
    Invalid attempt to call FieldCount when reader is closed.

    Thanks again.

    Friday, November 10, 2017 7:49 PM
  • Thank you for fixing this.

    You should post the error with the original question as it tells us what you are seeing.

    The code you posted is incomplete and structured wrong.  The first line makes no sense. It cannot create a connection string.

    The last line is illegal as it has two commands on the same line.  Both of these errors are basic PowerShell issues.  You should take the time to learn how to write PowerShell and how the syntax is structured and how it works.

    As you can now see, copying and pasting code from the web can create many issues with the code.  If you do not know PowerShell you will not be able to recognize this issue.

    Try fixing the posted code and run it again.  Use a new copy of PS and use the CLI version to simplify your experience.


    \_(ツ)_/

    Friday, November 10, 2017 7:56 PM
  • Thanks, I tried to simplify my question but guess that actually caused more issue :)

    I'll fix my posting with the correct and enough information.

    Thanks again.

    Friday, November 10, 2017 10:08 PM
  • Who wrote that. It has nothing to do with your original question.  It is code to do a bulk copy.

    Have you actually read the error?

    Exception calling "ExecuteReader" with "0" argument(s): "Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."
    At C:\CopyTables.ps1:32 char:3

    If this is a very large table this can happen. It has nothing to do with PowerShell. The server query governor has likely booted you because the DDBAs have set a time limit on a query. You need to ask them to exempt you query from the limit


    \_(ツ)_/

    Friday, November 10, 2017 10:32 PM
  • Also you have not defined these variables:

        $bulkCopy.BatchSize =$BatchSize
        $bulkCopy
    .BulkCopyTimeout =$QueryTimeOut


    \_(ツ)_/

    Friday, November 10, 2017 10:34 PM
  • Dang, sorry, I'm really bad about this :)

    here are the parameters:

    Param (
          [parameter(Mandatory = $true)] [string] $SrcServer,
          [parameter(Mandatory = $true)] [string] $SrcDatabase,
          [parameter(Mandatory = $true)] [string] $SrcTable,
          [parameter(Mandatory = $true)] [string] $DestServer,
          [parameter(Mandatory = $true)] [string] $DestDatabase,
          [parameter(Mandatory = $true)] [string] $DestTable,
          [Parameter(Mandatory=$false)] [Int32]$BatchSize=50000,
          [Parameter(Mandatory=$false)] [Int32]$QueryTimeout=0
      )


    Friday, November 10, 2017 10:36 PM
  • This:

    Catch [System.Exception]
     
    {
        $ex
    =$_.Exception
       
    Write-Host$ex.Message
     
    }

    Should be:

    Catch{
        Write-Host $_.Exception.Message
    }
    Your code would be more readable if you didn't use hanging closure. Also variables, by convention, begin with a lowercase letter.  This is called "lower camelCase".  Functions, properties and methods are in upper CamelCase.


    \_(ツ)_/




    • Edited by jrv Friday, November 10, 2017 10:41 PM
    Friday, November 10, 2017 10:38 PM
  • Dang, sorry, I'm really bad about this :)

    here are the parameters:

    Param (
          [parameter(Mandatory = $true)] [string] $SrcServer,
          [parameter(Mandatory = $true)] [string] $SrcDatabase,
          [parameter(Mandatory = $true)] [string] $SrcTable,
          [parameter(Mandatory = $true)] [string] $DestServer,
          [parameter(Mandatory = $true)] [string] $DestDatabase,
          [parameter(Mandatory = $true)] [string] $DestTable,
          [Parameter(Mandatory=$false)] [Int32]$BatchSize=50000,
          [Parameter(Mandatory=$false)] [Int32]$QueryTimeout=0
      )


    Well your issue is with the timeout on the DISTINCT.  It is a server issue.

    You can try setting the command timeout to 0 to see if it still times out.


    \_(ツ)_/


    • Edited by jrv Friday, November 10, 2017 10:45 PM
    Friday, November 10, 2017 10:44 PM
  • $sqlCommand.CommandTimeout = 0

    \_(ツ)_/

    • Marked as answer by BrianKang Friday, November 10, 2017 11:17 PM
    Friday, November 10, 2017 10:46 PM
  • I tried that but still getting the same error.  What I don't understand is that the exact same code works just fine if I remove DISTICNT keyword.  Does this DISTICNT keyword has special meaning to PS or am I using wrong command?

    sorry, I'm very new to PowerShell and thanks for your help.

    Friday, November 10, 2017 10:53 PM
  • I tried that but still getting the same error.  What I don't understand is that the exact same code works just fine if I remove DISTICNT keyword.  Does this DISTICNT keyword has special meaning to PS or am I using wrong command?

    sorry, I'm very new to PowerShell and thanks for your help.

    You tried what?

    Remember that DISTINCT aggregates the results before the first row is returned. Without the key word the first row is returned before the default 30 second timeout. 


    \_(ツ)_/

    Friday, November 10, 2017 10:56 PM
  • Thanks, it works after specified the timeout, $sqlCommand.CommandTimeout = 0

    It is good to know the default timeout is 30 second. 

    Thanks much for your help again!

    Friday, November 10, 2017 11:17 PM