Liên kết với MS Access Không ngạc nhiên, SQL Server dễ dàng để liên kết cơ sở dữ liệu MS Access. SQL Server sử dụng OLE DB Jet nhà cung cấp để kết nối với Jet và yêu cầu dữ liệu từ file mdb MS Access Hình 31-3 Trước khi chuyển đổi sang SQL Server, các Cape Hatteras cuộc phiêu lưu của công ty được quản lý lịch trình tour của mình trong bảng tính . | PartV Data Connectivity @datasrc C SQLServerBible @provstr Excel R71ë r Excel spreadsheets are not multi-user spreadsheets. SQL Server can t perform a distributed query that accesses an Excel spreadsheet while that spreadsheet is open in Excel. Linking to MS Access Not surprisingly SQL Server links easily to MS Access databases. SQL Server uses the OLE DB Jet provider to connect to Jet and request data from the MS Access .mdb file. FIGURE 31-3 Prior to the conversion to SQL Server the Cape Hatteras Adventures company was managing its tour schedule in the spreadsheet. 702 Executing Distributed Queries 31 FIGURE 31-4 Tables are defined within the Excel spreadsheet as named ranges. The CHA1_Schedule spreadsheet has five named ranges. Because Access is a database there s no trick to preparing it for linking as there is with Excel. Each Access table will appear as a table under the Linked Servers node in Management Studio. The Cape Hatteras Adventures customer prospect list was stored in Access prior to upsizing the database to SQL Server. The following code from the script links to the Access database so SQL Server can retrieve the data and populate the SQL Server tables EXEC sp_addlinkedserver CHA1_Customers 703 PartV Data Connectivity Access 2003 C SQLServerBible If you are having difficulty with a distributed query one of the first places to check is the security context. Excel expects that connections do not establish a security context so the non-mapped user login should be set to no security context EXEC sp_addlinkedsrvlogin @rmtsrvname CHA1_Schedule @useself false Developing Distributed Queries Once the link to the external data source is established SQL Server can reference the external data within queries. Table 31-2 shows the four basic syntax methods that are available which differ in .
