SQL with Manoj

While working with the BCP command to dump data from a Source table to a data file I was getting some errors.

I was trying to execute following BCP command from SSMS by using xp_cmdshell Extended Stored Procedure:

DECLARE @str VARCHAR(1000)
SET @str = 'bcp "Select * FROM dbo.xyzTable" ' 
	+ 'queryout "D:\BulkOut\xyzTable.dat" '
	+ '-S "sourceServer.database.windows.net" '
	+ '-U "saUserName" -P "saPassword"  -f "D:\Bulk\xyzTable.fmt" '

EXEC xp_cmdshell @str 
GO

… and encountered following error:

NULL
Starting copy…
SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name ‘xyzTable’.
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Unable to resolve column level collations
NULL
BCP copy out failed
NULL

After searching a bit on Internet I found that I missed to provide the Database name, thus the error “Invalid object name”. What a silly mistake 🙂

So, there are 2 ways you can provide Database name while querying your table.
1. First is by prefixing Database name with the SELECT statement.
2. Second is by using the -d flag with the Database name.

let’s check both the options here:

-- 1. By Providing DB name with the SELECT statement:
DECLARE @str VARCHAR(1000)
SET @str = 'bcp "Select * FROM DBName.dbo.xyzTable" ' -- DB name SELECT stmt
	+ 'queryout "D:\BulkOut\xyzTable.dat" '
	+ '-S "sourceServer.database.windows.net" '
	+ '-U "saUserName" -P "saPassword"  -f "D:\Bulk\xyzTable.fmt" '

EXEC xp_cmdshell @str
GO

-- 2. By providing DB name as an argument with the BCP statement:
DECLARE @str VARCHAR(1000)
SET @str = 'bcp "Select * FROM dbo.xyzTable" ' 
	+ 'queryout "D:\BulkOut\xyzTable.dat" -d "DBName" ' -- DB name with -d flag
	+ '-S "sourceServer.database.windows.net" '
	+ '-U "saUserName" -P "saPassword"  -f "D:\Bulk\xyzTable.fmt" '

EXEC xp_cmdshell @str 
GO

As I saw lot of people faced the same error and there was not proper answer, so I thought to blog it here.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.