OPENROWSET | SQL with Manoj

How resolve error OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error – MSDN TSQL forum

May 14, 2016 Leave a comment

–> Question:

Im getting following error:

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

I am using the following query:

select * 
from openrowset('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=\\abc\k\excel.xlsx',[sheet1$])

driver Microsoft.ACE.OLEDB.12.0 installed and configure properly.

Any help ?

–> Answer:

Check this link: https://sqlwithmanoj.com/2012/07/10/querying-excel-2010-from-sql-server-in-64-bit-environment/

You might need to register the ACE OLEDB provider, go thru all the steps I’ve mentioned in my blog, may help.

Ref link.

Categories: SQL Errors Tags: Linked Server, MSDN TSQL forum, OPENROWSET

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.

Categories: SQL Server Questions Tags: FileStream, FileTables, Full-Text Search, MSDN TSQL forum, OPENROWSET

Save XML in a file | Read XML from a file (in SQL Server)

October 20, 2011 6 comments

USE [AdventureWorks]
GO

-- Save XML records to a file:
DECLARE @fileName VARCHAR(50)

DECLARE @sqlStr VARCHAR(1000)
DECLARE @sqlCmd VARCHAR(1000)

SET @fileName = 'E:\SQL_Queries\test.xml'
SET @sqlStr = 'select TOP 50 [ContactID], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailAddress], [Phone] from AdventureWorks.Person.Contact FOR XML PATH(''Contact''), ROOT(''Person.Contact'')'

SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout ' + @fileName + ' -w -T'

EXEC xp_cmdshell @sqlCmd

Progress:-
NULL
Starting copy...
NULL
6 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 15     Average : (400.00 rows per sec.)
NULL

Check the file in the directory set above in @filename variable.

Now we will see how can we read the XML file saved above. Will store the XML in into a SQL Server XML variable
and query it with SELECT statement:

DECLARE @xmlStr XML

-- Storing XML records into a XML variable:

SELECT @xmlStr = BulkColumn
FROM OPENROWSET(BULK 'E:\SQL_Queries\test.xml', SINGLE_CLOB) AS X

The above statement gave me ERROR:
SINGLE_CLOB requires a double-byte character set (DBCS) (char) input file. The file specified is Unicode.
Because the XML BCP’d to the file above was saved in unicode format. So use SINGLE_NCLOB instead of SINGLE_CLOB.

SELECT @xmlStr = BulkColumn
FROM OPENROWSET(BULK 'E:\SQL_Queries\test.xml', SINGLE_NCLOB) AS X

This again gave me ERROR:
XML parsing: line 1, character 2034, illegal qualified name character
Because the file saved in xml file has CrLf (Carriage Return & Line Feed) in between.
So I removed the CrLf from the XML string as shown below.

SELECT @xmlStr = REPLACE(REPLACE(BulkColumn,CHAR(10),''),CHAR(13),'')
FROM OPENROWSET(BULK 'E:\SQL_Queries\test.xml', SINGLE_NCLOB) AS X

-- Querying the XML stored in @xmlStr variable with SELECT statement:
SELECT
	T.C.value('ContactID[1]', 'INT') as [ContactID],
	T.C.value('Title[1]', 'VARCHAR(8)') as [Title],
	T.C.value('FirstName[1]', 'VARCHAR(50)') as [FirstName],
	T.C.value('MiddleName[1]', 'VARCHAR(50)') as [MiddleName],
	T.C.value('LastName[1]', 'VARCHAR(50)') as [LastName],
	T.C.value('Suffix[1]', 'VARCHAR(10)') as [Suffix],
	T.C.value('EmailAddress[1]', 'VARCHAR(50)') as [EmailAddress],
	T.C.value('Phone[1]', 'VARCHAR(50)') as [Phone]
FROM @xmlStr.nodes('//Person.Contact/Contact') as T(C)