Guide to the Director of the Encyclopedia (2004-12-13)

Guide to the Director of the Encyclopedia (2004-12-13)

Http://builderman.home.sunbo.net/show_hdr.php?xname=U53DTV0&dname=517DTV0&xpos=1



One, open another data library

1.

Open a table in another data library of SQL server2000

(1). SELECT * FROM OPENROWSET (' SQLOLEDB ', ' sprogram '; ') Develop '; ' 11111 ', ' select * from kind ')

(2). SELECT * FROM OPENROWSET (' SQLOLEDB ', ' 190.1.1.247 '; ') Develop '; ' 11111 ', ' select * from kind ')

(3). Select * from OpenDataSource (' SQLOLEDB ', ' Data source=sprogram;user id=develop; Password=11111 '). New_ks.dbo.kind



2.

Open a table in Access2000

(1). Select * from OpenDataSource (' microsoft.jet.oledb.4.0 ', ' Data source=e:\north.mdb; User id=admin; Password= ') ... supply

(2). SELECT * FROM OPENROWSET (' Microsoft.Jet.OLEDB.4.0 '),

' E:\north.mdb '; ' Admin '; ', supply

(3). SELECT * FROM OPENROWSET (' Microsoft.Jet.OLEDB.4.0 '),

' E:\north.mdb '; ' Admin '; ', ' select * from supply where provider number >10 ')



3.

Open a table in Excel

(1). Select * from OPENROWSET (' MICROSOFT. JET. oledb.4.0 '

, ' Excel 5.0; Hdr=yes;database=e:\test.xls ', sheet1$)

(2). SELECT * Into table from OPENROWSET (' MICROSOFT. JET. oledb.4.0 '

, ' Excel 5.0; Hdr=yes;database=e:\test1.xls ', sheet3$)



II. Guidance and guidance

1. Use bcp tool to teach

(1) XLS file

EXEC Master.. xp_cmdshell ' bcp ' select Class_no,kind_no,cn_name from New_ks.dbo.kind order by 1,2 "queryout" E:\test3.xls "-c-q-S" Spro Gram "-U" develop "-P" "12345"



(2)/** Import text file

--Into SQL Server

SELECT * into your table from OpenRowset (' msdasql ', ' Driver={microsoft Text Driver (*.txt; *.csv)};D efaultdir=c:\temp; ', ' Select * FROM Contact.txt ')

--The inquiry leads

EXEC Master.. xp_cmdshell ' bcp ' select Class_no,kind_no,cn_name from New_ks.dbo.kind order by 1,2 "queryout" E:\TX1.txt "-c-q-S" sprogr Am "-U" develop "-P" 12345 "'

--Direct the

EXEC Master.. xp_cmdshell ' bcp new_ks.dbo.class out ' e:\TX3.txt '-c-s ' Sprogram '-u ' develop '-p ' 12345 '



--Direct into

EXEC Master.. xp_cmdshell ' bcp ' data library. dbo. Data table "in C:\DT.txt-c-S service name-u user-p password"

EXEC Master.. xp_cmdshell ' bcp hdh.dbo.kind in ' e:\TX1.txt '-c-q-s-u-P '

EXEC Master.. xp_cmdshell ' bcp hdh.dbo.kind in ' E:\test3.xls '-c-q-s-u-P '



--With BULK insert

BULK INSERT Hdh.dbo.kind

From ' E:\tx1.txt '

With (

FieldTerminator = ' t ',

Rowterminator = ' \ n '

)

The prerequisite is a table such as kind to exist, and the fields and Test.txt fields are counted as many

Run in the Search analyzer, and is logged with the SA user.



Bulk Insert Tbyhhk from ' C:\t.txt ' with (formatfile= ' c:\bcp.txt ')





3. Other data Library's guidance method:

(1)--/* dBase IV file
SELECT * FROM
OPENROWSET (' MICROSOFT. JET. oledb.4.0 '
, ' DBase IV; Hdr=no;imex=2;database=c:\ ', ' select * from [Customer profile 4.dbf] ')
--*/
(2)--/* DBase III Document
SELECT * FROM
OPENROWSET (' MICROSOFT. JET. oledb.4.0 '
, ' DBase III; Hdr=no;imex=2;database=c:\ ', ' select * from [Customer profile 3.dbf] ')
--*/
(3)--/* FoxPro Database
SELECT * FROM OPENROWSET (' MSDASQL ',
' Driver=microsoft Visual FoxPro Driver; SOURCETYPE=DBF; Sourcedb=c:\ ',
' SELECT * from [AA]. DBF] ')
--*/