Advertisements
Home > SQL Errors > SQL Error – SQLState = S0002 NativeError = 208 [SQL Server Native Client 11.0][SQL Server]Invalid object name ‘xyztable’ Unable to resolve column level collations

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.
 


Advertisements
  1. Tom Wheatley
    February 7, 2017 at 11:31 pm

    Searched everywhere until I found this page – your solution fixed my problem – thank you 🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: