xp_cmdshell | SQL with Manoj

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

April 16, 2015 4 comments

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.

Categories: SQL Errors Tags: BCP, BCP QUERYOUT, BCP Utility, NativeError = 208, QueryOut, xp_cmdshell

SQL Error – Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access because this component is turned off

April 14, 2015 Leave a comment

I was trying to execute a Windows Shell command from SSMS by using xp_cmdshell Extended Stored Procedure and encountered an error highlighted below in RED color:

EXEC xp_cmdshell 'DIR *.exe'

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure.

Even if you are a sysadmin and as by default this option is turned off after a new SQL Server installation, you need to manually turn on this option, let’s see how:

-- To change the advanced options:
EXEC sp_configure 'show advanced options', 1
GO
-- To update the current value for advanced options:
RECONFIGURE
GO
-- To enable the xp_cmdshell option:
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the new configured value for xp_cmdshell option:
RECONFIGURE
GO

–> If you want to suppress the messages returned by the xp_cmdshell extended Stored Procedure use the optional parameter no_output.

exec xp_cmdshell 'DIR *.exe', no_output

Please Note: If xp_cmdshell is executed within a BATCH and returns an error, the complete batch will fail. In earlier versions of SQL Server the batch used to continue executing.

If the user is not member of sysadmin role and want to use this extended SP then a Proxy account credential need to be created by using sp_xp_cmdshell_proxy_account. Check this KB article for the same: link.