Advertisements

Archive

Archive for April, 2015

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.
 


Advertisements

DDL and DML in Single Script with GO (batch separator) – MSDN TSQL forum

April 16, 2015 Leave a comment

–> 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

April 15, 2015 Leave a comment

–> 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.


Export XML column data to a file.xml

April 13, 2015 3 comments

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.

Categories: XML Tags: , , , ,