Tag Archives: OPENROWSET

Populate table from Stored Procedure resultset in SQL Server

I was answering post in MSDN website today and found one question which was asking “How to populate table from stored procedure in SQL Server?”. This is quite a common question and I have answered it in Experts-Exchange.com forum so many times but today I felt to share it with my blog reader.

Actually there is no direct way to populate result of stored procedure into SQL Server table but yes, you can use OPENROWSET to achieve this task. Let us look at it practically.

USE AdventureWOrks2012
GO

CREATE PROC uspGetSaleOrderHeader
AS
SELECT
SalesOrderNumber,
RevisionNumber,
OrderDate,
ShipDate,
AccountNumber,
CustomerID,
TotalDue
FROM
Sales.SalesOrderHeader
GO

IF OBJECT_ID('tempdb..#TempSalesTable') IS NOT NULL
DROP TABLE #TempSalesTable

SELECT * INTO #TempSalesTable FROM OPENROWSET('SQLNCLI', 'Server=WIN-9H6QATRKY81\SQL2K12DEV;UID=sa;PWD=upgrade1;','EXEC AdventureWOrks2012..uspGetSaleOrderHeader')

SELECT * FROM #TempSalesTable

If “Ad Hoc Distributed Queries” is disabled in your SQL Server, you will not be able to use OPENROWSET. You can enable “Ad Hoc Distributed Queries” with small TSQL Script, click here, for more details about this.

BTW, I have few more article where I have used OPENROWSET, click on the link to see more examples of OPENROWSET.

http://blog.extreme-advice.com/2011/07/05/openrowset-and-openquery-in-sql-server-20052008/

http://blog.extreme-advice.com/2011/07/15/filestream-in-sql-server-2008/

http://blog.extreme-advice.com/2011/07/06/msg-7301-cannot-obtain-the-required-interface-iid_idbschemarowset-from-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-linktoaceess/

http://blog.extreme-advice.com/2009/08/10/insert-and-update-image-field-in-sql-server-20082005/

http://blog.extreme-advice.com/2009/04/25/load-relational-xml-data-in-sql-server-2005/

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles but examples and explanations

OpenRowSet and OpenQuery in SQL Server 2005/2008

Said both of the techniques (OpenRowSetand OpenQuery) are almost same and may confuse many developers. I have been asked many time personally by few of my team member in recent years as well as I have seen the same question in few forums like Experts-Exchange and Forums.Asp.Net.
Basically both commands requires to access remote data from other data source like other SQL Server or may be Oracle or may be Access or Excel or anything else. Basically both of these commands requires connection information of source database, table information etc.
OpenQuery gets all these information from Linked Server where as in OpenRowSet, you have to provide all these information at run time. There is no other difference.
Syntaxes of both the commands from Microsoft’s website:
OPENROWSET
( { ‘provider_name’ , { ‘datasource’ ; ‘user_id’ ; ‘password’
   | ‘provider_string’ }
   , {   [ catalog. ] [ schema. ] object
       | ‘query’
     }
   | BULK ‘data_file’ ,
       { FORMATFILE = ‘format_file_path’ [ ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
::=
   [ , CODEPAGE = { ‘ACP’ | ‘OEM’ | ‘RAW’ | ‘code_page’ } ]
   [ , ERRORFILE = ‘file_name’ ]
   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]
   [ , MAXERRORS = maximum_errors ]
   [ , ROWS_PER_BATCH = rows_per_batch ]
   [ , ORDER ( { column [ ASC | DESC ] } [ ,…n ] ) [ UNIQUE ]
OpenSource:
OPENQUERY ( linked_server ,'query' )
Let us see how does it works, we are going to create one access file named “acc1.accdb” and will create one Employee table inside “acc1.accdb” file which will be stored in D drive of computer. You can have your own access file at your desired location.
Here is the query for the same:
–add link server
exec sp_addlinkedserver
@server=‘LinkToAceess’,
@srvproduct=‘AccessDatabase’,
–if you have older version of access then kindly use old jet provider
–Microsoft.Jet.OLEDB.4.0
@provider=‘Microsoft.ACE.OLEDB.12.0’,
@datasrc=‘D:\db1.accdb’
GO
–add MDB’s credental, if any
EXEC sp_addlinkedsrvlogin ‘LinkToAceess’, ‘false’
exec sp_serveroption [LinkToAceess],‘Data Access’,‘true’
–check whether ‘LinkToAccess’ has been added
select * from sys.servers
–list all tables available in Testing.MDB
exec sp_tables_ex ‘LinkToAceess’
–now query with OPENSOURCE
SELECT * FROM OPENQUERY(LinkToAceess, ‘SELECT id,empname FROM employee’)
–now query with OPENROWSET,
–which will not use our linked server
SELECT *
   FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
      ‘D:\db1.accdb’;
      ;,Employee);
     
–if you get following error
–Msg 15281, Level 16, State 1, Line 1
–SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.
–you have to enable “Ad Hoc Distributed Queries”
sp_configure ‘show advanced options’,1
reconfigure
GO
sp_configure ‘Ad Hoc Distributed Queries’,1
reconfigure
GO
–now try again same query
SELECT * FROM
OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
      ‘D:\db1.accdb’;
      ‘admin’;,Employee);
Hope you have enjoyed!!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Load Relational XML data in SQL Server 2005

When question comes to play with XML, even seasoned programmer try to get away from it, not because it is difficult task but lack of knowledge. I promise, loading XML data into SQL Server 2005 as a relational data, is not going to be as difficult as you are thinking.
Let me show you one example which will fill two relational tables in SQL Server from XML data. Let us first create one XML file at C:\vendor.XML with following data in it.
   <vendor>
<c>
  <venID>101venID>
  <first_name>Jamesfirst_name>
  <last_name>Butlerlast_name>
<po>
  <poid>1001poid>
  <venID>101venID>
  <po_date>2008-04-01T00:00:00po_date>
  <price>400.00price>
  po>
<po>
  <poid>1002poid>
  <venID>101venID>
  <po_date>2008-05-01T00:00:00po_date>
  <price>299.0000price>
 po>
<po>
  <poid>1002poid>
  <venID>101venID>
  <po_date>2009-04-01T00:00:00po_date>
  <price>600.0000price>
po>
 c>

vendor>

<

Now, create two tables to feed vendor details and purchase order detail, than we will load XML file into both tables.
–create table to hold Vendor information
CREATE TABLE vendor (
        venid INT NOT NULL
        , first_name VARCHAR(50)
        , last_name VARCHAR(50)
        )
–create table for holding Purchase order information.
CREATE TABLE PurcharOrder (
        poID INT NOT NULL
        , venid INT NOT NULL
        , po_date DATETIME
        , price MONEY
        )
GO
–hold xml from file
DECLARE @x XML
SET @x= (
SELECT xm.Col1 FROM OPENROWSET(BULK ‘c:\vendor.xml’,SINGLE_BLOB) AS xm(Col1)
         )
— show file contents
select @x
INSERT INTO vendor
SELECT
ven.value(‘./venID[1]’, ‘INT’) as vid
, ven.value(‘./first_name[1]’, ‘VARCHAR(50)’) as first_name
, ven.value(‘./last_name[1]’, ‘VARCHAR(50)’) as last_name
–, C.query(‘.’) 
FROM @x.nodes(‘/vendor/c’) tab(ven)
INSERT INTO PurcharOrder
SELECT
ven.value(‘./poid[1]’, ‘INT’) as POID
, ven.value(‘./venID[1]’, ‘INT’) as VenID
, ven.value(‘./po_date[1]’, ‘DATETIME’) as po_date
, ven.value(‘./price[1]’, ‘MONEY’) as price
–,ven.query(‘.’)
FROM @x.nodes(‘/vendor/c/po’) tab(ven)
GO
–check both table, whether data comes or not.
select * FROM Vendor
Select * from PurcharOrder


Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah