none
SQL Server PowerShell-bcp parse error RRS feed

  • Question

  • MicroSoft: Save the following as a .ps1 file and run from DOS. Edit the bcp command in the script to use any SQL Server, any test database, and any table.

    # Attention Microsoft PowerShell & SQL Server bcp, SQL bulk copy utility
    # PowerShell bcp parsing bug!!
    # Reported by 
    # Paul Poole
    # email: ccaptn@gmail.com
    # 9/24/2018

    # Description of the problem:
    # The bcp command below pulls table column schema information from a SQL Server 2014 database.
    # Specifically, the query creates a .csv file with the following table column information:
    # Column Name,Data type,Max Length,precision,scale,is_nullable,Primary Key
    # xID,int,4,10,0,0,1
    # Txt,varchar,50,0,0,1,0

    # Testing environment:
    # Windows 7 Professional
    # Microsoft SQL Server 2014
    # C:\ powershell $PSVersionTable.PSVersion
    #Major  Minor  Build  Revision
    #-----  -----  -----  --------
    #4      0      -1     -1

    # Note that the bcp command fails when run from within powershell. Specifically, 
    # 1. The bcp command fails on the "-t," parameter which specifies the terminator/separator between output fields of the query result.
    # 2. If a different character, such as 'z' is specified as the terminator i.e.: -tz, then the bcp command runs successfully.
    # 3. If the bcp command is run from DOS, the the bcp command runs successfully with a comma terminator -t, 
    # Conclusion: there is a parsing issue when bcp is run from within PowerShell when the bcp command 
    # has a -t, parameter. A comma is a valid delimeter, but PowerShell fails to run the bcp command with a comma terminator.
    # As noted, PowerShell runs the bcp command successfully if the terminator is set to a character such as 'z' for example.
    # Note that the bcp command with a comma terminator runs -verbatim- successfully from DOS!

    $PSVersionTable.PSVersion

    $bcpcmd = 'bcp "SELECT c.name Column_Name, t.Name Data_type, c.max_length Max_Length, `
    c.precision, c.scale, c.is_nullable, ISNULL(i.is_primary_key, 0) Primary_Key `
    FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT OUTER JOIN `
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT OUTER JOIN `
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id `
    WHERE c.object_id = OBJECT_ID(''T_Exclude'')" queryout test.csv -tz -c -S vm-cyberpawn -d CyberWatch -T'

    # Execute bcp command for columns
    Invoke-Expression $bcpcmd

    # If the following bcp command is copied (without the # character), it runs successfully from a DOS command line, WITH THE terminator as a comma: -t, !

    #bcp "SELECT c.name Column_Name, t.Name Data_type, c.max_length Max_Length, c.precision, c.scale, c.is_nullable, ISNULL(i.is_primary_key, 0) Primary_Key FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id WHERE c.object_id = OBJECT_ID('<YOUR TABLE NAME>')" queryout test.csv -t, -c -S <YOUR SERVER> -d <YOUR DATABASE> -T

    # Below is the script output:

    #C:\Powershell>powershell .\powershell_bcp_error.ps1
    #
    #Major  Minor  Build  Revision
    #-----  -----  -----  --------
    #4      0      -1     -1
    #Invoke-Expression : At line:6 char:65
    #+ WHERE c.object_id = OBJECT_ID('T_Exclude')" queryout test.csv -t, -c -S vm-cyber ...
    #+                                                                 ~
    #Missing argument in parameter list.
    #At C:\Powershell\Powershell_bcp_error.ps1:42 char:1
    #+ Invoke-Expression $bcpcmd
    #+ ~~~~~~~~~~~~~~~~~~~~~~~~~
    #    + CategoryInfo          : ParserError: (:) [Invoke-Expression], ParseException
    #    + FullyQualifiedErrorId : MissingArgument,Microsoft.PowerShell.Commands.InvokeExpressionCommand
    #
    #C:\Powershell>bcp "SELECT c.name Column_Name, t.Name Data_type, c.max_length Max_Length, c.precision, c.scale, c.is_nullable,
    # ISNULL(i.is_primary_key, 0) Primary_Key FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT OU
    #TER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT OUTER JOIN sys.indexes i ON i
    #c.object_id = i.object_id AND ic.index_id = i.index_id WHERE c.object_id = OBJECT_ID('T_Exclude')" queryout test.csv -t -c -
    #S vm-cyberpawn -d CyberWatch -T
    #
    #Starting copy...
    #
    #2 rows copied.
    #Network packet size (bytes): 4096
    #Clock Time (ms.) Total     : 1      Average : (2000.00 rows per sec.)
    #

    Tuesday, September 25, 2018 7:06 PM

All replies

  • You have to add a space after the -t and put the comma in quotes, like: queryout test.csv -t "," -c -S vm-cyberpawn -d CyberWatch -T
    Monday, July 8, 2019 9:11 PM