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.
DDL and DML in Single Script with GO (batch separator) – MSDN TSQL forum
–> Question:
Running SQL Server 2008 R2. We currently apply schema and data changes to databases (for software upgrades) using separate scripts. Sometimes, based on the changes required this results in several scripts for a single upgrade. I’d like to (if possible) be able to encapsulate all changes, DDL and DML in a single script with transaction handling, so the whole thing rolls back if there are any issues. Problem is that with DDL changes we use GO as a batch separator, which screws up the standard error handling we implement in scripts using transactions (GO results in the script unable to find the error handler).
I’m aware of the fact that we can strip out these batch separators, but what I’m not clear on is what, if any, negative impact that would have. Are there certain commands in T-SQL (executing procedures, altering objects, etc) that are required to be executed in their own batch?
Lastly, as part of our DDL and DML changes, we perform a check to verify that the impending change actually needs to be made (so for a new column we check to see whether the column exists first). If, for example we were to make a DDL change that added a column, set a default constraint on that column and updated existing rows to have a base value for that column, could we use BEGIN…END or something similar so the column existence check would only have to be performed once for that group of changes?
–> My Answer:
You can remove GO or batch separator if you don’t have:
1. same variables declared in more than 1 batch
2. CREATE/ALTER VIEW/PROCEDURE statements, they should be first statement in the batch otherwise will fail. (otherwise it will give you following error: CREATE/ALTER VIEW/PROCEDURE’ must be the first statement in a query batch.)
3. there could be more things, just check based upon my above 2 points.
Ref Link.
Table to store MS Word docs, only be accessible via SQL Queries – MSDN TSQL forum
–> Question:
In Microsoft SQL Server 2012 database, I want to create a table to store Microsoft Word documents.
Documents must only be accessible via Transact-SQL queries.
How to do this?
–> My Answer:
As you are in SQL Server 2012 you can try using Filetables, a new feature introduced with this version. Check how to implement this in my blog post.
This features gives you an abstraction layer to store your files in File System but cannot directly navigate to the files. You will have to use SSMS to open the folders to view, add, or update the files.
Do check this link also on how to setup & do a Full Text Search on those Documents stored in the File System, link.
–> Another Answer by Eric:
Create a file C:\test.docx in the Server where SQL Server is hosted.
CREATE TABLE dbo.FilesTbl ( name VARCHAR(99), extension VARCHAR(99), content IMAGE ) --insert the word document into table INSERT INTO FilesTbl SELECT 'test', 'docx', BULKCOLUMN FROM OPENROWSET (BULK N'C:\test\test.docx', SINGLE_BLOB) a;
Ref Link.
SQL Error – Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access because this component is turned off
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.
Export XML column data to a file.xml
There are times when you want to export XML data stored in Table’s columns to xml files for some purpose.
Here is a small hands on script that uses BCP command with QueryOut option to export data from SQL Server to file system:
USE [AdventureWorks2012] GO -- Save XML records to a file: DECLARE @fileName VARCHAR(50) DECLARE @sqlStr VARCHAR(1000) DECLARE @sqlCmd VARCHAR(1000) SET @fileName = 'D:\SQL_Queries\PersonAdditionalContactInfo.xml' SET @sqlStr = 'select TOP 1 AdditionalContactInfo from AdventureWorks2012.Person.Person where AdditionalContactInfo IS NOT NULL' SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout ' + @fileName + ' -w -T' EXEC xp_cmdshell @sqlCmd
Output: NULL Starting copy... NULL 1 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 140 Average : (7.14 rows per sec.) NULL
If you have to extract XMLs stored in all the rows then you can iterate through all the rows one by one by using a WHILE loop or a CURSOR and extract data in separate files. You can provide unique names to your files dynamically by tweaking the above code.
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 