[Use OpenDataSource and OpenRowset for Distributed Query and data import and export]

Use OpenDataSource and OpenRowset for Distributed Query and Data Import and Export

PS: This article is transferred from others' Materials

 

 OpenDataSource:

I. Data Import and Export of SQL Server and acess

1. query access data in SQL Server:
-- ===================================================== ====================
Select *
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C:/DB. mdb"; user id = admin; Password = ')... table name

Select * From OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "D:/DB. mdb "; user id = admin; Password = ')... student where name = 'me'
Select * From newtable
Bytes -------------------------------------------------------------------------------------------------
2. Import access to SQL Server
-- ===================================================== ====================
Run in SQL Server:
Select *
Into newtable
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C:/DB. mdb"; user id = admin; Password = ')... table name
Bytes -------------------------------------------------------------------------------------------------
3. insert data in the SQL Server table to the Access Table
-- ===================================================== ====================
Run in SQL Server:
Insert into OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C:/DB. mdb"; user id = admin; Password = ')... table name
(Column name 1, column name 2)
Select column name 1, column name 2 from SQL table

Instance:
Insert into OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'C:/DB. mdb '; 'admin'; '', test)
Select ID, name from test
Insert into OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'c:/trade. mdb'; 'admin'; '', table name)
Select *
From sqltablename

Insert into OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "D:/DB. mdb "; user id = admin; Password = ')... student (studentid, name)
Select staffid, name from staff

Insert into OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'D:/DB. mdb '; 'admin'; '', student)
Select accountid, name from tblbaccount

Select * From OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'd:/DB. mdb'; 'admin'; '', student)
Bytes -------------------------------------------------------------------------------------------------

 

 

Ii. Import and export data from SQL Server and Excel
1. query Excel Data in SQL Server:
-- ===================================================== ====================
Select *
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C:/book1.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... [sheet1 $]
The following is an example of a query. It queries an Excel spreadsheet through the ole db provider for jet.
Select *
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C:/finance/account.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... xactions

Select *
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "D:/ .xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... ViB
Bytes -------------------------------------------------------------------------------------------------

2. Import Excel Data to SQL Server:
-- ===================================================== ====================
Select * into newtable
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C:/book1.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... [sheet1 $]
Instance:
Select * into newtable
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C:/finance/account.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... xactions
Bytes -------------------------------------------------------------------------------------------------

3. Export the data queried in SQL Server into an Excel file
-- ===================================================== ====================
T-SQL code:
Exec master.. xp_mongoshell 'bcp database name. DBO. Table name out C:/temp.xls-C-Q-s "servername"-U "sa"-P ""'
Parameter: s indicates the SQL server name, u indicates the user, and P indicates the password.

Exec master .. xp_mongoshell 'bcp axzq. DBO. Staff out d: staff.xls-C-Q-s "."-U "sa"-P "gazx "'

Note: You can also export text files and other formats.
Instance: exec master .. xp_mongoshell 'bcp saletesttmp. DBO. cusaccount out C:/temp1.xls-C-Q-s "pmserver"-U "sa"-P "sa "'
Exec master .. xp_mongoshell 'bcp "select au_fname, au_lname from pubs .. authors order by au_lname" queryout C:/authors.xls-C-sservername-USA-ppassword'

Exec master .. xp_cmdshell 'bcp "Select name from axzq .. staff order by name "queryout D: staffname.xls-C-Q-s ". "-U" sa "-P" gazx "'

Bytes ------------------------------------------------------------------------------------------------

4. insert data to excel in SQL Server:
-- ===================================================== ====================
Insert into OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C:/temp.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... table1 (A1, A2, A3) values (1, 2, 3)

T-SQL code:
Insert
OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Extended properties = Excel 8.0; Data Source = C:/training/inventur.xls ')... [filiale1 $]
(Bestand, Produkt) values (20, 'test ')
Bytes -------------------------------------------------------------------------------------------------

 

OpenRowSet:

For example:

-- 1.

Select .*
From OpenRowSet ('sqlncl', 'server = seattle1; trusted_connection = yes ;',
'Select groupname, name, specified mentid
From adventureworks. HumanResources. Department
Order by groupname, name') as;

 

-- 2.

Select customerid, companyName
From OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'C:/program files/Microsoft Office/office11/samples/northwind. mdb ';
'Admin'; '', MERS mers)

-- 3.

Use northwind
Go
Select C. *, O .*
From northwind. DBO. Customers as C
Inner join OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'C:/program files/Microsoft Office/office11/samples/northwind. mdb '; 'admin'; '', orders)
As O
On C. customerid = O. customerid

 

-- 4

Use adventureworks
Go
Create Table mytable (filename nvarchar (60 ),
Filetype nvarchar (60), document varbinary (max ))
Go

Insert into mytable (filename, filetype, document)
Select 'text1.txt 'As filename,
'.Txt 'As filetype,
* From OpenRowSet (bulk N 'C:/text1.txt ', single_blob) as document

-- 5

Select a. * From OpenRowSet (bulk 'C:/test/values.txt ',
Formatfile = 'C:/test/values. FMT ') as;

 

Attached:
-- Enable xp_cmdshell
-- SQL Server blocked access to procedure 'xp _ Your shell'
Sp_configure 'show advanced options', 1
Go
Reconfigure
Go
Sp_configure 'xp _ Your shell', 1
Go
Reconfigure
Go

-- Enable sp_oacreate
-- SQL Server blocked access to procedure 'sys. sp_oacreate'
Sp_configure 'show advanced options', 1;
Go
Reconfigure;
Go
Sp_configure 'ole automation procedures ', 1;
Go
Reconfigure;
Go

Sp_configure 'ad hoc distributed queries ', 1;
Go
Reconfigure
Go