Start up
Log on to the network
Start Enterprise Manager
Connect to Reliant\MIS431S05 and your
SalesOrders database
(from EM …) Start Query Analyzer
Start Books Online
MIS 431
Dr. Steve Ross
Spring 2006
Transferring Data and
Linking Servers
Material for this lecture is drawn from Guerrero and Rojas, SQL Server 2000 Programming,
and the professor’s experience.
Importing Data I
In Enterprise Manger
• Choose Tools …
• Data Transformation
Services …
• Import Data
Importing Data II
Choose data source
(type)
Designate the ODBC
source
Enter user name
and password if
necessary
Importing Data III
Indicate the
destination server
and database
Importing Data IV
Specify what is to be
copied
Importing Data V
Select source table(s)
to copy
Designate destination
table name(s)
Click the … button
(not visible in picture)
to
• Specify data conversion
• Drop and recreate table
Importing Data VI
OPENROWSET
Includes all connection information
necessary to access remote data from
an OLE DB data source.
• Used in FROM clause of a query as though it
is a table name
OPENROWSET ( 'provider_name'
, 'datasource' ; 'user_id' ; 'password'
, 'query' )
OpenRowSet Example
CREATE PROCEDURE dbo.uspPersonData (@PIDM int)
AS
SET NOCOUNT ON
DECLARE @AUTHQuery varchar(2500), @PIDMTEXT varchar(8)
SELECT @PIDMTEXT = CAST(@PIDM AS varchar(8))
SELECT @AUTHQuery = 'SELECT *
FROM OPENRowset(''ORAOLEDB.ORACLE'',
''DATAW'';''userID'';''**password**'',
''SELECT * FROM G_PERSON WHERE PIDM=' + @PIDMTEXT + ''')'
EXEC (@AUTHQuery)
SET NOCOUNT OFF
GO
Linked Servers I
General tab includes
server name and
data source
Linked Servers II
Security tab contains
login information
Next Lecture
(none )