Notice
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |
Tags
- vscode
- 윈도우즈 터미널
- otel
- log
- pg_stat_statements
- opentelemetry
- 옵션 패턴
- openrc
- DotNet
- docker
- mysql
- SQL Server
- go
- sqlc
- 무선 디버깅
- Linux
- rc-service
- AnimatedPositioned
- IDX Project
- Android
- option pattern
- shared_preload_libraries
- module upgrade
- golang
- Flutter
- 맥미니
- user-secrets
- User
- version 2
- elasticSearch
Archives
- Today
- Total
CATCHV Blog
SQL Server OPENROWSET 본문
반응형
Configuring SQL Server Surface Area before using OPENROWSET
01 |
USE [master] |
02 |
GO |
03 |
sp_configure 'show advanced options' ,1 |
04 |
GO |
05 |
reconfigure with override |
06 |
GO |
07 |
sp_configure 'Ad Hoc Distributed Queries' ,1 |
08 |
GO |
09 |
reconfigure with override |
10 |
GO |
Connecting from SQL Server to SQL Server with OPENROWSET (Windows Authentication)
1 |
-- server: server1 |
2 |
-- database: AdventureWorks |
3 |
4 |
SELECT a.* |
5 |
FROM OPENROWSET( 'SQLNCLI' , 'Server=server1;Trusted_Connection=yes;' , |
6 |
'SELECT GroupName, Name, DepartmentID |
7 |
FROM AdventureWorks.HumanResources.Department |
8 |
ORDER BY GroupName, Name' ) AS a; |
Connecting from SQL Server to MS Access Database with OPENROWSET
1 |
-- Access MDB File: c:\Northwind.mdb |
2 |
3 |
SELECT CustomerID, CompanyName |
4 |
FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0' , |
5 |
'C:\Northwind.mdb' ; |
6 |
'admin' ; '' ,Customers) |
7 |
GO |
Connecting from SQL Server to an Excel File using OPENROWSET
1 |
-- Excel File: c:\data.xls |
2 |
-- Worksheet : Sheet1 |
3 |
4 |
SELECT * |
5 |
FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0' , |
6 |
'Excel 8.0;Database=c:\data.xls' , [Sheet1$]) |
Connecting from SQL Server to text file using OPENROWSET
1 |
-- file name: c:\data.txt |
2 |
3 |
SELECT * |
4 |
FROM OPENROWSET |
5 |
( BULK 'C:\data.txt' ,SINGLE_CLOB) |
6 |
AS a |
Generating an excel document with the output of a query
1 |
insert into OPENROWSET( 'Microsoft.Jet.OLEDB.4.0' , |
2 |
'Excel 8.0;Database=D:\testing.xls;' , |
3 |
'SELECT * FROM [SheetName$]' ) select * from SQLServerTable |
반응형
'Database > MS SQL' 카테고리의 다른 글
SSMS에서 실행(F5) 후 한글 입력으로 자동 변경 문제 (0) | 2013.01.15 |
---|---|
Procedure에서 PRINT(출력) 바로 내보내기 (0) | 2012.02.16 |
특정 문자로 구분되는 데이터 분리 Query (split) (0) | 2012.02.06 |
sp_who2 LastBatch 문자열 오류 수정 (0) | 2012.02.05 |
SQL Profiler 결과 데이터베이스로 읽어오기 (0) | 2012.02.04 |
Comments