BCP does not export any records

This topic contains 7 replies, has 3 voices, and was last updated by Profile photo of Roustam Akhmetov Roustam Akhmetov 1 year, 5 months ago.

  • Author
    Posts
  • #30536
    Profile photo of Roustam Akhmetov
    Roustam Akhmetov
    Participant

    Hi,

    I am running the below powershell script to export some data into a text file:

    $DBName = 'MYDB'
    #$Srv = '$(ESCAPE_SQUOTE(SRVR))'
    $Srv = 'MYServer\MyInstance'
    SL SQLSERVER:\SQL\"$Srv"\Databases\"$DBName"\Tables
    $bcpconn = '-T'

    $IDColumn = 'MyID'

    $TableName = 'MyTable'
    $ParentTable = 'MyParentTable'

    $Query = '"select * from ' + $DBName + '.dbo.' + $TableName + ' WHERE ' + $IDColumn + ' IN (SELECT ' + $IDColumn + ' FROM ' + $DBName + '.dbo.' + $ParentTable + ' WHERE $Partition.WBC_' + $ParentTable + '_pf(PartitionDate) = 2)"'
    $Saveas = Invoke-Sqlcmd -query "SELECT [value] FROM [SupportDB].[Support].[PartitioningConfiguration] (nolock) WHERE name = 'ArchivePath' and TableName = '$TableName'"
    $ReplaceServername = $Srv -replace '\\', '_'
    $Saveas.value = $Saveas.value + $ReplaceServername + "_" + $DBName + "_" + $TableName + '.txt'
    bcp $Query QUERYOUT $Saveas.value -n $bcpconn -S $Srv

    I get a message: "0 rows copied." and the output file is empty.

    When I take the value of the $Query variable and run it in Management Studio, it returns around 150 records.

    When I run:
    Invoke-Sqlcmd -query $Query

    I get:
    Invoke-Sqlcmd : The identifier that starts with 'select * from MyDB.dbo.MyTable WHERE MyID IN (SELECT MyID FROM MY' is too long. Maximum length is 128.

    Any ideas?

    Thanks.

  • #30537
    Profile photo of Don Jones
    Don Jones
    Keymaster

    I think the problem is the double quotes you've added inside the query.

  • #30538
    Profile photo of Roustam Akhmetov
    Roustam Akhmetov
    Participant

    If I remove the double quotes, the BCP still does not copy any records. Besides, I have another script with BCP and a query with double quotes, which works fine. The only difference is the working query is less than 128 characters.

  • #30540
    Profile photo of Roustam Akhmetov
    Roustam Akhmetov
    Participant

    Besides, when I changed the query into a stored procedure:
    $Query = '"exec ' + $DBName + '.dbo.[WBC_Query_Unpartitioned_Tables] "' + $DBName + "',"' + $TableName + "',"' + $ParentTable + "',"' + $IDColumn + "'"'

    It worked fine, even though there are double quotes in the command. Why can't I put the query dynamically in powershell and have to wrap it into a stored procedure?

  • #30544
    Profile photo of Sebastian Neumann
    Sebastian Neumann
    Participant

    I'd never bother trying to concat values like that:

    You can either just use a Here-string:

    $IDColumn = 'MyID'
    $DBName = "MyDB"
    $TableName = 'MyTable'
    $ParentTable = 'MyParentTable'
    
    $Query = @"
    select * from $DBName.dbo.$TableName WHERE $IDColumn IN (SELECT $IDColumn FROM $DBName.dbo.$ParentTable WHERE `$Partition.WBC_$ParentTable`_pf(PartitionDate) = 2)
    "@
    

    Or you can use a replacement string...

    $Query = 'select * from {0}.dbo.{1} WHERE {2} IN (SELECT {3} FROM {4}.dbo.{5} WHERE $Partition.WBC_{5}_pf(PartitionDate) = 2)' -f `
             $DBName, $TableName, $IDColumn, $IDColumn, $DBName, $ParentTable, $ParentTable
    

    ... which you could shorten to this:

    $Query = 'select * from {0}.dbo.{1} WHERE {2} IN (SELECT {2} FROM {0}.dbo.{3} WHERE $Partition.WBC_{3}_pf(PartitionDate) = 2)' -f `
             $DBName, $TableName, $IDColumn, $ParentTable
    

    Both return this:

    select * from mydb.dbo.MyTable WHERE MyID IN (SELECT MyID FROM mydb.dbo.MyParentTable WHE
    RE $Partition.WBC_MyParentTable_pf(PartitionDate) = 2)
    

    Also about your set-location line, I'd personally prefer to expand strings like this:
    Set-Location "SQLSERVER:\SQL\$Srv\Databases\$DBName\Tables"

  • #30574
    Profile photo of Roustam Akhmetov
    Roustam Akhmetov
    Participant

    When I try the first option I get an error about unexpected token where the @ character is. If I remove @ in the beginning and the end the below command works, but when I try to bcp using this query, I get 0 records. Invoke-sqlcmd works fine after removing "@" characters, but bcp doesn't:

    PS SQLSERVER:\SQL\OTPDEV\OLB01\Databases\ACHWire\Tables> $Query = @"select * from $DBName.dbo.$TableName WHERE $IDColumn IN (SELECT $IDColumn FROM $DBName.dbo.$ParentTable WHERE `$Partition.WBC_$ParentTable`_pf(PartitionDate) = 2)"@
    Unrecognized token in source text.
    At line:1 char:10
    + $Query = <<<< @"select * from $DBName.dbo.$TableName WHERE $IDColumn IN (SELECT $IDColumn FROM $DBName.dbo.$ParentTable WHERE `$Partition.WBC_$ParentTable`_pf(PartitionDate) = 2)"@ + CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException + FullyQualifiedErrorId : UnrecognizedToken

  • #30575
    Profile photo of Sebastian Neumann
    Sebastian Neumann
    Participant

    My example with the Here-string works as intended. A Here-string is special – you have to use it exactly as I posted it in the example above.
    The @" indicates the start of the Here-string and "@ the end. The latter cannot be indented.

    Here's everything you need to know about them: https://technet.microsoft.com/en-us/library/ee692792.aspx

  • #30582
    Profile photo of Roustam Akhmetov
    Roustam Akhmetov
    Participant

    Found it out, copy/paste was removing carriage returns in the string, making it like this:
    @"select * from $DBName.dbo.$TableName WHERE $IDColumn IN (SELECT $IDColumn FROM $DBName.dbo.$ParentTable WHERE `$Partition.WBC_$ParentTable`_pf(PartitionDate) = 2)"@

    Instead of:
    @"
    select * from $DBName.dbo.$TableName WHERE $IDColumn IN (SELECT $IDColumn FROM $DBName.dbo.$ParentTable WHERE `$Partition.WBC_$ParentTable`_pf(PartitionDate) = 2)
    "@

    After I put @" on a separate line, the error message was gone, however the bcp copied 0 rows. The query returns 155 rows when run from Management Studio or via:
    Invoke-Sqlcmd -query $Query

    Tried the second approach:
    $Query = 'select * from {0}.dbo.{1} WHERE {2} IN (SELECT {2} FROM {0}.dbo.{3} WHERE $Partition.WBC_{3}_pf(PartitionDate) = 2)' -f $DBName, $TableName, $IDColumn, $ParentTable

    The command works, but the bcp also copies 0 records. The query also returns 155 rows when run on Manangement Studio or via:
    Invoke-Sqlcmd -query $Query

    So far the only option with which bcp is working is the stored procedure one:
    $Query = '"exec ' + $DBName + '.dbo.[WBC_Query_Unpartitioned_Tables] "' + $DBName + "',"' + $TableName + "',"' + $ParentTable + "',"' + $IDColumn + "'"'

You must be logged in to reply to this topic.