Welcome › Forums › General PowerShell Q&A › BCP does not export any records
This topic contains 7 replies, has 3 voices, and was last updated by
-
AuthorPosts
-
October 7, 2015 at 7:42 pm #30536
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 $QueryI 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.
-
October 7, 2015 at 7:47 pm #30537
I think the problem is the double quotes you've added inside the query.
-
October 7, 2015 at 8:10 pm #30538
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.
-
October 7, 2015 at 9:05 pm #30540
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?
-
October 7, 2015 at 9:46 pm #30544
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" -
October 8, 2015 at 1:58 pm #30574
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 -
October 8, 2015 at 3:19 pm #30575
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
-
October 8, 2015 at 6:07 pm #30582
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 $QueryTried 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, $ParentTableThe 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 $QuerySo 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 + "'"' -
AuthorPosts
The topic ‘BCP does not export any records’ is closed to new replies.