BCP does not export any records

PowerShell Help
1

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.

2

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

3

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.

4

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?

5

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”

6

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: (:slight_smile: , ParentContainsErrorRecordException
    • FullyQualifiedErrorId : UnrecognizedToken
7

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

8

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 + ‘’‘"’