July 31, 2013 at 11:19 am
Hi, I am getting the following error when running the following script. I am trying to copy the table 'MAMI.EngravingsForVendor' into a flat file 'C:\JohnTest\EngravedProducts.txt' and I am getting an error.
I am including the script, the error, and the content of variable "@sql" which I copied during debugging.
-- SCRIPT THAT IS NOT WORKING...
DECLARE@TableNVARCHAR(MAX)
DECLARE@filenameVARCHAR(MAX)
SET@table= 'MAMI.EngravingsForVendor'
SET@filename= 'C:\JohnTest\EngravedProducts.txt'
IF OBJECT_ID(@table) IS NOT NULL
BEGIN
DECLARE
@sql NVARCHAR(MAX),
@cols NVARCHAR(MAX) = N'';
SELECT @cols += ',' + name
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@table)
ORDER BY column_id;
SELECT @cols = STUFF(@cols, 1, 1, '');
SET @sql = N'EXEC master..xp_cmdshell ''bcp "SELECT '''''
+ REPLACE(@cols, ',', ''''',''''') + ''''' UNION ALL SELECT '
+ 'RTRIM(' + REPLACE(@cols, ',', '),RTRIM(') + ') FROM '
+ DB_NAME() + '.' + @table + '" queryout "' + @filename + '" -c -T''';
EXEC sp_executesql @sql;
END
ELSE
BEGIN
SELECT 'The table '+@table+' does not exist in the database';
END
-- THE ERROR THAT IS BEING PRODUCED...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
NULL
-- THE CONTENT OF VARIABLE @SQL.
EXEC master..xp_cmdshell 'bcp "SELECT ''VendorID'',''VendorEMailAddress'',''DocumentNumber'',''ProductCode'',''Quantity'',''VendorProductCode'',''Description'',''EngravedText'',''EngravedText2'',''ShippingAddressLine1'',''ShippingAddressLine2'',''ShippingAddressLine3'',''ShippingAddressLine4'',''ShippingAddressLine5'',''ShippingAddressLine6'',''ShippingAddressLine7'',''Country'',''ShipToTelephoneNumber'',''ShipToEMailAddress'',''Status'' UNION ALL SELECT RTRIM(VendorID),RTRIM(VendorEMailAddress),RTRIM(DocumentNumber),RTRIM(ProductCode),RTRIM(Quantity),RTRIM(VendorProductCode),RTRIM(Description),RTRIM(EngravedText),RTRIM(EngravedText2),RTRIM(ShippingAddressLine1),RTRIM(ShippingAddressLine2),RTRIM(ShippingAddressLine3),RTRIM(ShippingAddressLine4),RTRIM(ShippingAddressLine5),RTRIM(ShippingAddressLine6),RTRIM(ShippingAddressLine7),RTRIM(Country),RTRIM(ShipToTelephoneNumber),RTRIM(ShipToEMailAddress),RTRIM(Status) FROM DS_DEV.MAMI.EngravingsForVendor" queryout "C:\JohnTest\EngravedProducts.txt" -c -T'
July 31, 2013 at 1:22 pm
Hello,
The problem is that your query is using double quotation marks inside the SELECT statement, which breaks BCP.
I would change your SELECT statement that lists the columns to
SELECT @cols = coalesce(@cols, '')+name + '], ['
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@table)
ORDER BY column_id;
-- removes the last two chars ,[
set @cols = LEFT(@cols,len(@cols)-3)
-- adds the first missing [
Set @cols = '['+@cols
Why Coalesce? See
http://www.mssqltips.com/sqlservertip/1521/the-many-uses-of-coalesce-in-sql-server/
Now, if we changed how you get your list of columns... @COLS.
you need to change your BCP too to
SET @sql = N'EXEC master..xp_cmdshell ''bcp "SELECT '+@cols+ ' FROM '
+ DB_NAME() + '.' + @table + '" queryout "' + @filename + '" -c -T''';
Let me know how it goes
July 31, 2013 at 4:12 pm
Thank you for your response. I replaced my method with yours, and I have the same error.
Here is my resulting script...
USE DS_DEV
GO
DECLARE@TableNVARCHAR(MAX)
DECLARE@filenameVARCHAR(MAX)
SET@table= 'MAMI.EngravingsForVendor'
SET@filename= '\\OMSBVLSTO\Programming\EngravedProducts.txt'
IF OBJECT_ID(@table) IS NOT NULL
BEGIN
DECLARE
@sql NVARCHAR(MAX),
@cols NVARCHAR(MAX) = N'';
--SELECT @cols += ',' + name
-- FROM sys.columns
-- WHERE [object_id] = OBJECT_ID(@table)
-- ORDER BY column_id;
SELECT @cols = coalesce(@cols, '')+name + '], ['
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@table)
ORDER BY column_id;
-- removes the last two chars ,[
set @cols = LEFT(@cols,len(@cols)-3)
-- adds the first missing [
Set @cols = '['+@cols
--SELECT @cols = STUFF(@cols, 1, 1, '');
--SET @sql = N'EXEC master..xp_cmdshell ''bcp "SELECT '''''
-- + REPLACE(@cols, ',', ''''',''''') + ''''' UNION ALL SELECT '
-- + 'RTRIM(' + REPLACE(@cols, ',', '),RTRIM(') + ') FROM '
-- + DB_NAME() + '.' + @table + '" queryout "' + @filename + '" -c -T''';
SET @sql = N'EXEC master..xp_cmdshell ''bcp "SELECT '+@cols+ ' FROM '
+ DB_NAME() + '.' + @table + '" queryout "' + @filename + '" -c -T''';
EXEC sp_executesql @sql;
END
ELSE
BEGIN
SELECT 'The table '+@table+' does not exist in the database';
END
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply