3

NativeError = 208

 2 years ago
source link: https://sqlwithmanoj.com/2015/04/16/sqlstate-s0002-nativeerror-208-sql-server-native-client-11-0sql-serverinvalid-object-name-xyztable-unable-to-resolve-column-level-collations/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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.

Related


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK