October 7, 2015 at 7:42 pm

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'

$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.

October 7, 2015 at 7:47 pm

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

October 7, 2015 at 8:10 pm

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

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

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

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

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

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 + "'"'