Use T-SQL for data export \ import (sqlserver-> sqlserver)

Source: Internet
Author: User
Tags microsoft sql server 2005 sql server books
Today, I tried to use Transact-SQL to export and import data. It is much more efficient than DTs! 1. Enable the OpenDataSource function

Start> All Program-> Microsoft SQL Server 2005-> Configuration tool-> SQL Server peripheral application configurator-> function Peripheral application configurator-> Instance name-> Database Engine-> ad hoc remote query-> enable OpenRowSet and OpenDataSource. Otherwise, an error is reported:
Message 15281, level 16, state 1, 1st rows
The SQL Server blocks access to the statement 'openrowset/OpenDataSource 'of the 'ad hoc distributed queries' component because the component is already used as the server
The security configuration is disabled. The system administrator can enable 'ad hoc distributed queries 'by using sp_configure '. Enable 'ad hoc distributed
For more information about queries, see "peripheral application configurator" in SQL Server books online ". 2. Open a remote connection
Start> All Programs> Microsoft SQL Server 2005> Configuration tool> SQL Server peripheral application configurator> service and connected peripheral application configurator> Instance name> Database engine-> remote connection-> Local Connection and remote connection-> only use TCP/IP-> application-> restart Database Engine

Otherwise, an error is reported:
The ole db access interface "sqlncli" of the linked server "(null)" returns the message "Logon timeout expired ".
An error occurred while connecting to the ole db access interface "sqlncli" of the "(null)" server "to return the message" Establishing a connection to the server. Default settings when connecting to SQL Server 2005
The fact that SQL server does not allow remote connection may cause failure. ".
Message 65535, level 16, state 1, 0th rows
SQL Network Interface: An error occurred while obtaining the list of enabled protocols from the registry [xffffffff].

3. Open the client TCP/IP protocol
Start> All Programs> Microsoft SQL Server 2005> Configuration tool> SQL Server Configuration Manager>
SQL Server 2005 network configuration: Enable the TCP/IP protocol
SQL native client configuration: Enabled
Default port-1433
4. Create a connection Server

Exec sp_addmediaserver @ Server = 'condb04 ',
@ Provider = 'sqloledb ',
@ Srvproduct = '',
@ Datasrc = 'winnie \ iidc'
Exec sp_addjavassrvlogin @ rmtsrvname = 'winnie \ iid ',
@ Useself = 'false ',
@ Locallogin = 'admin ',
@ Rmtuser = 'admin ',
@ Rmtpassword = 'Password ';
[Note] Here, the login account should be an account that uses SQL Server Authentication. Otherwise, an error will be reported: XX users cannot log on...

5. Create an empty table in the import target database
Use wwww
Go
Create Table pro_unit_sort (
Culid varchar (40 ),
Coll_unit varchar (400 ),
Cul_sort varchar (400) vi. Execute export \ Import
Select * into wwww. DBO. pro_unit_sort from conndb04.wwww _ xx... DBO. XX. _ unit_sort;
[Note] 1. Steps 4 and 6 are equivalent:
Use wwww
Go
Insert into OpenDataSource (
'Sqlodb ',
'Data source = Winnie \ iidc; user id = admin; Password = password'
). Wwww. DBO. pro_unit_sort
Select * From wwww_xx.dbo.xx.unit_sort; 2. When this operation is performed, UDP port 1434 is used. If the firewall closes this port, open it. Otherwise, an error is reported:
The ole db access interface "sqlncli" of the linked server "(null)" returns the message "Logon timeout expired ".
An error occurred while connecting to the ole db access interface "sqlncli" of the "(null)" server "to return the message" Establishing a connection to the server. Default settings when connecting to SQL Server 2005
The fact that SQL server does not allow remote connection may cause failure. ".
Message 65535, level 16, state 1, 0th rows
SQL Network Interface: An error occurred while obtaining the list of enabled protocols from the registry [xffffffff].
Reference:
1. Use Transact-SQL to import and export data:
Http://tech.it168.com/db/s/2006-08-16/200608160913336_1.shtml 2. SQL server connection Server Technology Summary
Http://www.linuxmine.com/33943.html

This article is from 51cto. com technical blog

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.