Start up

1
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

2
MIS 431Dr. Steve RossSpring 2006

• Transferring Data and Linking Servers

Material for this lecture is drawn from Guerrero
and Rojas, SQL Server 2000 Programming, and the
professors experience.
3
Importing Data I

• In Enterprise Manger
• Choose Tools
• Data Transformation Services
• Import Data

4
Importing Data II

• Choose data source (type)
• Designate the ODBC source
• Enter user name and password if necessary

5
Importing Data III

• Indicate the destination server and database

6
Importing Data IV

• Specify what is to be copied

7
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

8
Importing Data VI
9
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'    )

10
OpenRowSet Example

• CREATE PROCEDURE dbo.uspPersonData (_at_PIDM int)AS
• SET NOCOUNT ON
• DECLARE _at_AUTHQuery varchar(2500), _at_PIDMTEXT
  varchar(8)
• SELECT _at_PIDMTEXT CAST(_at_PIDM AS varchar(8))
• SELECT _at_AUTHQuery 'SELECT FROM
  OPENRowset(''ORAOLEDB.ORACLE'',''DATAW''''userID
  ''''password'',''SELECT FROM G_PERSON
  WHERE PIDM' _at_PIDMTEXT ''')'
• EXEC (_at_AUTHQuery)
• SET NOCOUNT OFF
• GO

11
Linked Servers I

• General tab includes server name and data source

12
Linked Servers II

• Security tab contains login information

13
Next Lecture

• (none ?)