Archive
SQL Error – SQLState = S0002 NativeError = 208 [SQL Server Native Client 11.0][SQL Server]Invalid object name ‘xyztable’ Unable to resolve column level collations
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.